Geting Combo Box to go Directly to Record 1

[RobotMush]

I have created a combo box that shows 2 fields (strLNCo and strFN) of the records I would like to get. The VBA After Update event is listed below

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[strLNCo] = '" & Me![Combo29] & "'"
Me.Bookmark = rs.Bookmark
End Sub

I have no trouble going to the very record I am wanting.
Although when I have 2 or more records with the same strLNCO field and differenct strFN field it only goes to the first record that matches the strLNCo field I selected.
My question is how do I get it to go Directly to the strLNCo field and strFN field record I am wanting.
Please ask any question or request any more information to help me on this matter.

Thank You

RobotMush(Technical User)

 

[RoyVidar]

The .FindFirst handles more than one criterion,

[tt]rs.FindFirst "[strLNCo] = '" & Me![Combo29] & _
"' and strFN = '" & Me![Combo29].column(1) & "'"[/tt]

- assuming the strFN field is text, and that it's located in the second column of the combo. I'm also a fan of testing for match prior to setting bookmark;

[tt]if not rs.nomatch then Me.Bookmark = rs.Bookmark[/tt]

Roy-Vidar

 

[RobotMush]

Thank you Very Much RoyVidar. First time I did a copy/paste and ran into a glitch, something about not being written correctly. But after cuting the combo box and doing another combo box, I went in and typed in the instructions from where the original program left off. IT WORKED! Does give a person some sence of pride and knowledge when a program works. Doesn't it?

Take care and thanks again for your help and quick responce.

RobotMush(Technical User)

 

[RobotMush]

Ok, I wrote too soon. It was working, now it is letting me find the records but it is not going to them. Here is how the Event program is written

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[strLNCo] = '" & Me![Combo32] & "'and strFN = '" & Me![Combo32].Column(1) & "'"
Me.Bookmark = rs.Bookmark
End Sub

What change(s) do I need to make with the above program?

RobotMush(Technical User)

 

[RoyVidar]

Only thing I see, is the missing space before "and". Any error messages, what's the value of Me![Combo32] and Me![Combo32].column(1) (test it using message boxes). strFN is really a text field?

Roy-Vidar

 

[RobotMush]

I'm realy not sure what is going on, one time it works and the next it doesn't, then it works again. At present it is working, but was not working when I got your last statement. The values of Me![Combo32] is the strLNCo which is column 0 and the Me![Combo32].column(1) is strFN which is a text field.
What do you mean when you wrote
"Test it using message boxes"

Thanks again for your help

RobotMush(Technical User)

 

[RoyVidar]

[tt]Msgbox Me![Combo32] & " : " & Me![Combo32].Column(1)[/tt]

- will tell you what values are in the controls when the code executes

Roy-Vidar

 

[RobotMush]

Ok, being just a newbie techie, I do not know how to do that. But in playing around with the program I find that It works when I first go into the form. If I use the record pointer buttons of which I have made 3
First Record
Next Record
Previous Record
I believe this messes up the record pointer of the program. Because if I go out and come back into the form the combo box works great.
Thanks very much for your help

RobotMush(Technical User)

 

[pokeymonsc]

Roy:

Don't know if you are still watching this post, but .....

I'm using the above code to use a combo box to locate employees and force the form to go to their record. The form itself is based on a query and worked until I decided to sort the query using 'Last_Name'. Once I did this then I started getting Run-Time error 3079: "LastName could refer to more than one table listed in the FROM clause of your SQL statement". What SQL statement? So right now I have the choice of using the combo box or having alphabetised records. Is there a way of doing both?

 

[RobotMush]

I'm not reading this post, but it is on my e-mail notify which is how I got your message and I'm replying to this.

First off, the SQL Statement is accessed in your Query object.
Go into design view for any query you are using, click on the "view" pull down menu and select the SQL view. This will bring up your query in SQL view letting you see what tables.field you are using, from what table or other query, and the sort order you have it in (if any)
If you check your From Clause in the SQL View you might get an idea of what is wrong.
The other item, having a combo box and alphabatized records. This can be done.
First make a query that is sorted the way you want. Remember that the query sorts from left fields to right fields, so if you have first name, last name fields and want to sort it by Last Name then First Name you will need to switch the fields.
Secondly, I have created a combo box that shows my Last Name and First Name fields with the following program in the "After Update" event.

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[strLNCo] = '" & Me![Combo20] & "'And Nz(strFN) ='" & Nz(Me![Combo20].Column(1)) & "'"
Me.Bookmark = rs.Bookmark
End Sub

Your best recourse is to copy and past the above program to the combo box's after update even Making the changes to replace my field and combo box names with yours.

Hope this helps, if you have any more questions just ask.


Sincerly
RobotMusch (Technical User)

 

[pokeymonsc]

Thanks RobotMush:
The first sentence was the key. "First off, the SQL Statement is accessed in your Query object."

I went to SQL view and studied the query statement and The Lord popped the answer into my head. My SQL was

Select dbo_SalaryBenefit05.*, dbo_SalaryBenefit05.LastName
from dbo_salaryBenefit05
Order by dbo_salaryBenefit05.LastName

It was the .* that had LastName in the SQL statement twice and caused the error. Once I pulled down (in designer view) all the columns instead of * everything works fine. Thanks for the mind trigger. :-D

 

[RobotMush]

Glad to help pokeymonsc, I like the "The Lord popped the answer into my head" Sounds a lot better than my usual thoughts of "Dag Nab It ya dolt why didn't ya see that the first time!"

Hope everything else works out, else ya know where to go for the answers "Tek-Tips"

RobotMush (Technical User)