FindFirst sticking with first record?

I have a combo box that queries from a table of people to get a list of names,
ordered lastname, firstname, peopleID.  There are several people in this list
that have the same last name, and no matter which person I choose with that
last name, Access chooses the first person with that last name.  

(So effectively, if I have Smith Jon 1, Smith Jane 8, Smith Harold 9074395,
it'll always stick with Smith Harold 9074395.)

The  VBA behind this is: 
Private Sub Combo26_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[LastName] = '" & Me![Combo26] & "'"
    If Not rs.EOF Then
        Me.Bookmark = rs.Bookmark
    End If
Rem         MsgBox "Check back later this is being improved!"
    DoCmd.GoToControl "Command28"
End Sub


Advice please?

-- 
Message posted via http://www.accessmonster.com

0
BonnieW
5/7/2007 3:55:50 PM
access.forms 6864 articles. 2 followers. Follow

7 Replies
743 Views

Similar Articles

[PageSpeed] 3

You need to FindFirst by an ID number rather than the last name.  

BonnieW wrote:
>I have a combo box that queries from a table of people to get a list of names,
>ordered lastname, firstname, peopleID.  There are several people in this list
>that have the same last name, and no matter which person I choose with that
>last name, Access chooses the first person with that last name.  
>
>(So effectively, if I have Smith Jon 1, Smith Jane 8, Smith Harold 9074395,
>it'll always stick with Smith Harold 9074395.)
>
>The  VBA behind this is: 
>Private Sub Combo26_AfterUpdate()
>    ' Find the record that matches the control.
>    Dim rs As Object
>
>    Set rs = Me.Recordset.Clone
>    rs.FindFirst "[LastName] = '" & Me![Combo26] & "'"
>    If Not rs.EOF Then
>        Me.Bookmark = rs.Bookmark
>    End If
>Rem         MsgBox "Check back later this is being improved!"
>    DoCmd.GoToControl "Command28"
>End Sub
>
>Advice please?

-- 
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via http://www.accessmonster.com

0
ruralguy
5/7/2007 4:22:32 PM
Wonderful!  Thanks!  I changed it up so that it's no longer referencing
LastName, but PeopleID.

Private Sub Combo26_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[PeopleID] = '" & Me![Combo26] & "'"
    If Not rs.EOF Then
        Me.Bookmark = rs.Bookmark
    End If
Rem         MsgBox "Check back later this is being improved!"
    DoCmd.GoToControl "Command28"

Now it's giving me error 3464: Data type mismatch in criteria expression.  I
assume that this means now that it's a number field, not a text field, I need
to get rid of some of the quotation marks- but I don't know which ones. 

Sorry about my noobishness; this is not my code even in the slightest, nor
have I ever really had to deal with VBA til about a week ago. :(
ruralguy wrote:
>You need to FindFirst by an ID number rather than the last name.  
>
>>I have a combo box that queries from a table of people to get a list of names,
>>ordered lastname, firstname, peopleID.  There are several people in this list
>[quoted text clipped - 19 lines]
>>
>>Advice please?
>

-- 
Message posted via http://www.accessmonster.com

0
BonnieW
5/7/2007 4:38:54 PM
Which column in the ComboBox has the PeopleID?  If it is the bound column
then you can just change this like to remove the quotes:

rs.FindFirst "[PeopleID] = " & Me.Combo26

If it is other than the bound column then you can use the column property:

rs.FindFirst "[PeopleID] = " & Me.Combo26.Column(2)

...for the 3rd column since the column index is zero based. 

BonnieW wrote:
>Wonderful!  Thanks!  I changed it up so that it's no longer referencing
>LastName, but PeopleID.
>
>Private Sub Combo26_AfterUpdate()
>    ' Find the record that matches the control.
>    Dim rs As Object
>
>    Set rs = Me.Recordset.Clone
>    rs.FindFirst "[PeopleID] = '" & Me![Combo26] & "'"
>    If Not rs.EOF Then
>        Me.Bookmark = rs.Bookmark
>    End If
>Rem         MsgBox "Check back later this is being improved!"
>    DoCmd.GoToControl "Command28"
>
>Now it's giving me error 3464: Data type mismatch in criteria expression.  I
>assume that this means now that it's a number field, not a text field, I need
>to get rid of some of the quotation marks- but I don't know which ones. 
>
>Sorry about my noobishness; this is not my code even in the slightest, nor
>have I ever really had to deal with VBA til about a week ago. :(
>>You need to FindFirst by an ID number rather than the last name.  
>>
>[quoted text clipped - 3 lines]
>>>
>>>Advice please?

-- 
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via http://www.accessmonster.com

0
ruralguy
5/7/2007 4:48:35 PM
It's the third column that holds the PeopleID.  The second code bit you
provided worked perfectly!  Thank you very much!

ruralguy wrote:
>Which column in the ComboBox has the PeopleID?  If it is the bound column
>then you can just change this like to remove the quotes:
>
>rs.FindFirst "[PeopleID] = " & Me.Combo26
>
>If it is other than the bound column then you can use the column property:
>
>rs.FindFirst "[PeopleID] = " & Me.Combo26.Column(2)
>
>...for the 3rd column since the column index is zero based. 
>
>>Wonderful!  Thanks!  I changed it up so that it's no longer referencing
>>LastName, but PeopleID.
>[quoted text clipped - 22 lines]
>>>>
>>>>Advice please?
>

-- 
Message posted via http://www.accessmonster.com

0
BonnieW
5/7/2007 4:52:56 PM
That's great Bonnie.  Glad I could help.

BonnieW wrote:
>It's the third column that holds the PeopleID.  The second code bit you
>provided worked perfectly!  Thank you very much!
>
>>Which column in the ComboBox has the PeopleID?  If it is the bound column
>>then you can just change this like to remove the quotes:
>[quoted text clipped - 12 lines]
>>>>>
>>>>>Advice please?

-- 
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via http://www.accessmonster.com

0
ruralguy
5/7/2007 5:01:26 PM
findFirst is a DAO method; and DAO has been depecrated

move to ADO and use a real FILTER method



"BonnieW via AccessMonster.com" <u33846@uwe> wrote in message
news:71d3a638939d2@uwe...
> I have a combo box that queries from a table of people to get a list of
names,
> ordered lastname, firstname, peopleID.  There are several people in this
list
> that have the same last name, and no matter which person I choose with
that
> last name, Access chooses the first person with that last name.
>
> (So effectively, if I have Smith Jon 1, Smith Jane 8, Smith Harold
9074395,
> it'll always stick with Smith Harold 9074395.)
>
> The  VBA behind this is:
> Private Sub Combo26_AfterUpdate()
>     ' Find the record that matches the control.
>     Dim rs As Object
>
>     Set rs = Me.Recordset.Clone
>     rs.FindFirst "[LastName] = '" & Me![Combo26] & "'"
>     If Not rs.EOF Then
>         Me.Bookmark = rs.Bookmark
>     End If
> Rem         MsgBox "Check back later this is being improved!"
>     DoCmd.GoToControl "Command28"
> End Sub
>
>
> Advice please?
>
> -- 
> Message posted via http://www.accessmonster.com
>


0
Susie
5/8/2007 1:03:15 AM
I'm sorry but DAO has *not* been depreciated.  Where do you get this
information?

Susie Johnson wrote:
>findFirst is a DAO method; and DAO has been depecrated
>
>move to ADO and use a real FILTER method
>
>> I have a combo box that queries from a table of people to get a list of names,
>> ordered lastname, firstname, peopleID.  There are several people in this list
>[quoted text clipped - 19 lines]
>>
>> Advice please?

-- 
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via http://www.accessmonster.com

0
ruralguy
5/8/2007 1:24:14 AM
Reply:

Similar Artilces:

If record Does Not Exist?
Hi I have a form in which i have two Text Fields name "TxtAccountNo" And "TxtPartyName". When a user enters the Account No. in text field, the form automatically pull the Party Name from the table "TblParties" Now i want that if the user enteres an Account no. that doesnot exist in "TblParties", then a msgbox must appear with a message. Thank you -- Message posted via http://www.accessmonster.com Are you using a combobox to pull the Party Name? If not, how? What is the code you are using? If in a combobox you can add a Not In Li...

Modify report to print individual group of records on separate pag
I am new to report writer and I am trying to figure out how to print individual employees records separately. It is an employee training records report where it group all of an employees training classes together. However, unless it fills the page, the next person's records are printed and I'm trying to start each new group on a new page. I don't know where to find the group properties. Please help. Amytdev. I think you need to create an additional footer. The additional footer can have the "Page Break" option set, causing a new page to be started. Below is the p...

header and footer on every page except the first page?
how do i put header and footer on every page except the first page? One way: http://mcgimpsey.com/excel/subs/firstfooter.html In article <EBE1E559-C27E-47F9-BA16-0AA05EE9352E@microsoft.com>, marany <marany@discussions.microsoft.com> wrote: > how do i put header and footer on every page except the first page? ...

Update values on large amount of records
Hi. I need to change a value on 16000 leads. How can I do it without run a workflow rule form a View ? (A view can show at maximum 100 records, so I would need to run the rule 160 times...) Sebastiano Sebastiano, I would try running an update directly from Query Analyzer on the LeadBase table. This is assuming the that new values is something easy to set. If you need to do something more advanced (like changing the owner or another GUID field), then this still might work, but I would recommend testing first. Matt "Sebastiano Castrini" <[remove this]castrini@libero.it>...

No New Records
I have a form that is populated witha query. I would like to not allow the form to keep forwarding to new blank records. For example there are 12 records, when you use the record controls and get to record 12, you are not allowed to go to 13, or at least not 14. Thanks for the help. Also, the Allow No New Record property doesn't help me. I would like to allow a new record under certain conditions. I could do it in VBA if there is a way. Thanks "sturner333" wrote: > I have a form that is populated witha query. I would like to not allow the > form to keep forwarding ...

How to add more that one tables or queries to report record source
How to add more that one tables or queries to report record source? In Access 2003. Hi All, Add more than one tables or queries to report record source. Thanks. "TP88" wrote: > How to add more that one tables or queries to report record source? In Access > 2003. Create a new query in design view. Add as many tables and/or queries as is appropriate. Join them on their common fields. Select fields from them to display in the output. Save the query. Run it to make sure it retrieves the data you want. Create a report and point it to the new query ...

first tab to other tabs
Help Pls. i want to enter data in the first tab, without retyping them in individual tabs have the automatic sepeared in other tabs. exp. column a b c a b c a b c global 300 pcs 10 boxes global 300 pcs 10 boxes island 400 pcs 20 boxes island 400 pcs 20 boxes global 200 pcs 5 boxes island 300 pcs 10 boxes island 300 pcs 10 boxes global 200 pcs 5 boxes all vendors global island Thank You ...

no welcom screen when i first logon desktop
can anyone help me to modify "welcome screen" to "classic screen" when i first logon to desktop after i install a new computer. thank you. "marcus_tong" <marcustong@discussions.microsoft.com> wrote in message news:C75B252C-58CF-43CC-86D9-C640E9C9F92B@microsoft.com... > can anyone help me to modify "welcome screen" to "classic screen" when i > first logon to desktop after i install a new computer. > thank you. Click Start / Help, then type welcome screen in the search box and press the Enter key. Now click the...

can't delete record
I am trying to use a command button on a form to delete the current record displayed on the form. The record has several related fields in other tables, but I believe I have successfully deleted all related records with delete queries. When I run the code DoCmd.RunCommand acCmdDeleteRecord the confirm delete message comes up, and after clicking through it the form goes blank, appearing like a successful deletion. No error messages. However the record is still in the table. In table view there isn’t any problem deleting the record in question i.e. no related records. I’ve tried using a w...

Calculate change from record to record
Dear All, in my database, I am recording projects in different categories. I have now created a crosstab query that sums up all project per category and year, including a total number of projects per year. How can I also show, how the total number is changing compared to the previous year? Basically, I am looking for something like (No of projects of previous record's year-No of projects of current record's year)/(No of projects of current record's year) at the end of each record. A B C Total Change Y1 3 2 5 10 0 % Y2 1 7 6 ...

USB-stick
hi! is it possible to get any kind of ID of a USB-stick? thanks, philipp Volume ID? It's fat32 no? "Philipp Huber" <philipp.huber@innval.com> a �crit dans le message de news:bmonu5$7kk$03$1@news.t-online.com... > hi! > > is it possible to get any kind of ID of a USB-stick? > > thanks, philipp > > ...

E-mail lead, contact, & Account records directly from CRM
The ability to e-mail records direclty from crm to other people who are not users of the system. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=a61c4d61-21fb-4c97-a329-b23...

CDaoRecordset::FindFirst() usage
As a novice on database, I am trying to use CDaoRecordset::FindFirst() function just to check if certain 'Serial Number' exists in other table but it throws following error: Error(3077): Syntax error (missing operator) in expression. Please see following code: .... ASSERT(m_pDB->IsOpen()); CDaoTableDef td(m_pDB); CDaoRecordset rs(m_pDB); // convert serial number string to long number char *end; long UnitNum=lstrtol(GetSerialNum(),&end,10); BOOL faulty = FALSE; try { td.Open((LPCTSTR)(_T("Table 1"))); } catch (CDaoException* e) { m_pView->DisplayD...

Merging Duplicate Records
Is there a way to merge duplicate records? I'm specifically looking to merge duplicated opportunities and duplicated cases. ------=_NextPart_0001_066CF4A5 Content-Type: text/plain Content-Transfer-Encoding: 7bit Hi Cindy, Thanks for the post. You can merge only customer records, limited to accounts, contacts, or leads. Also, you can only merge similar records. For example, you can merge two account records; however, you cannot merge a contact record with an account record. ---------------------------------------------------------------------------- ---- Merge records You can ...

clear records on subform when go to knew record on main form
I am using the unbound combo fields on the main form (date, joblist, personnel) to populate and schedule fields in a subform when I navigate to a new record (new day) on the main form. I would like the subform records to be cleared out for the new record but remain for the day that has already been scheduled. What code and where do I put it. thanks, barb It should be automatic (no code) if your main form and subform are set up properly. Definitions that will make my explanation easier... just sub in your own names: FormMain - Your main form TableMain - The table or recordset und...

How to get date entered recording first time condition is true?
Would like a date entered in a column the first time a condition is met calculated on several other columns in the same row. How could this be implemented? You could modify the circular reference formula at http://www.mcgimpsey.com/excel/timestamp.html to substitute your conditional statement for A1="". Or you could use something like the event macro shown there, but using the Calculation event instead, for instance: Private Sub Worksheet_Calculate() Dim rCheck As Range Dim rCell As Range On Error Resume Next Set rCheck = Range("A...

Outlook rules, processing not terminated after first match?
I have a set of rules for incoming messages and I have been very careful about their relative ordering. All the rules have the same structure: If <condition> then move the message to folder <xxx> Although I have specified "move" and not "copy" within every rule I am surprised to experience messages being processed by more than one of my rules. Is this the way it is supposed to work? Some of my rules have "overlapping" conditions and I had expected the first rule with a matching <condition> to be the one and only rule applied to a message but ...

SPF record question
Hello! I think I understand SPF records now (thanks to Jim Martin in another group), but I would like to know if anyone can confirm my questions. I found this page http://www.kitterman.com/spf/validate.html that lets one test an SPF record. It does not test version 2 yet. Let's call my domain "mydomain.net" and assume that my mail server is named "mail.mydomain.net." I have my DNS controlled by www.zoneedit.com and I have a backup mail server there to grab any mail if my server is not answering. When I use www.dnsreport.com to check my domain, it shows two MX re...

Is there a function that counts distinct number od records in a ra
I am looking for a way to tell how many distinct values are in a range. For example, say I have values in Range A5:A2000 and I want to know of those 1995 cells how many distinct values are in the range. See: Counting Distinct Entries In A Range in: http://www.cpearson.com/EXCEL/Duplicates.aspx -- Gary''s Student - gsnu201001 "Ayo" wrote: > > I am looking for a way to tell how many distinct values are in a range. For > example, say I have values in Range A5:A2000 and I want to know of those 1995 > cells how many distinct values are in...

Access to News groups disappears after first use and exiting.
Have used OE for a number of years for email and News Group access. Thought I might experiment and then switch to Outlook. Set it up, and, received reply on the test. I know it was working. Then tried to set up newsgroups. Followed directions as outlined in help. Last direction was to drag News to "Go". Finished ... went to go and selected News. All the News Groups I had earlier setup in OE came up. Played around looking at some posts. Then exited. Then when I went back to Go, News was no longer there. Completely disappeared. I am using Outlook 2003. Actually recopied a "Gh...

findfirst
I have a form with 6 fields on it, some text boxes and some combo boxes. The first box is a combo box of names. The second box is the person's ID number. I have set up a findfirst method so that when you select a name from the first box, the id for that person shows in the second box (if that person is in the list). The rest of the boxes on the form are more info on that person. I want those fields to fill in as well as the ID field when I select the name from the first box. I can't seem to figure out the code. I keep getting the id number in all the fields after I select the na...

Advanced Find to include All Contacts related to Account Records
We have a need to run an Advanced Find where the results from the Accounts entity will display/print "ALL" Contacts associated with the Account - not just the Primary Contact. We may be missing something very simple here, but we have just not been able to do this. If we open an Account, the left pane Contacts section will display all Contacts associated with the Account, so there must be a way to have an Advanced Find pull Account information that would include all the Contacts associated with the Account. We would appreciate any assistance, and our client would be extremely g...

Listbox not allowing access when sheet is first opened
The listbox only allows the user to select a value when the spreadsheet is first opened after you touch a cell on another sheet. How do I get rid of this. If I can't get rid of it how do I get the spreadsheet to run a macro on startup to select another sheet to hide the problem? ...

Trying to combine multiple records into one record in a Query
I originally cross-posted this question in microsoft.public.access.queries & microsoft.public.access.tablesdbdesign newsgroups, but did not get a response. I am new to Access, and very little knowledge about SQL. I read allot about normalization, need to know if I went to far with it with my Family table. Here is the structures for the tables I have, and of the query I want to create. Contact Table: tabContactID (Autonumber) ContactID (Create using VBA in a form) (PK) FirstName LastName etc.... Example: tabContactID ContactID FirstName LastName etc.... 0001...

Read-only recommended not sticking
Actually, it's not UN-sticking. I have a document, when I open it, I get the "...should be opened as read-only unless changes to it need to be saved. Open as read-only?" message. Simple fix, right? Save as, select the General Options, and uncheck the "Read-only recommended" box. Click OK, click Save. Problem is, the document still spits out the "Open as read-only?" message whenever it's opened. This is a .doc file in Word 2007 (SP 2) so I tried converting it to .docx before doing save as. Still, I get the "Open as read-only?"...