Combo box not finding record (sort of)

[Romulus]

My form is based on a query. The query pulls first name and last name from table 1 and the rest of the fields from table 2.

The form has 3 combo boxes at the top: one for the first name, one for the last name, and one for an ID number. The bottom has text boxes with fields from table 2. When I select from any one of these 3 combo boxes, it pulls up all data from table 2 except for the data in the combo boxes.

For example, if I select an id, the bottom text boxes are populated but first name and last name remain blank. If I consequently select a first name, the id remains the same and the last name remains blank while the bottom text boxes are appropriately populated.

I want all boxes to be populated with the appropriate values.

 

[JCross]

Assuming that each person has a first name, last name, and ID number - why would you want to be able to bring them up only by first name? I think you should have ONE combo box with their ID, Lastame, FirstName and then bring up the record based on that choice.

Jennifer (remus?)

[This message has been edited by JCross (edited 03-07-2002).]

 

[Alexandre]

We will check if you update your profile each month, Jennifer

(these are potentially 7793 congratulations in May

)

Alex

[This message has been edited by Alexandre (edited 03-07-2002).]

 

[JCross]

hee. i'm due June 4th........if i can survive his kicking until then!

Jennifer

 

[Romulus]

I ran into a problem with that. I sorted the list by last name, but if the data entry person only knows the person number (for confidentiality purposes) and tries to type in a person number (for more efficient data entry) that doesn't exist, Access does not give any warning messages. Instead the text boxes below remain unchanged and behaves as if the information in the text boxes below belong to the non-existing person number. I want there to be a warning message that this person is not in the combo box list.

 

[JCross]

Would putting a error msgbox in the notinlist event of the combo box solve that problem? (limit to list must be set to 'yes')

Also - in the query that populates the combo box, you should have the customer number, and then the name sorted ascending.....the row source is the query, and the control source is the bound field...

Jennifer

[This message has been edited by JCross (edited 03-11-2002).]

[This message has been edited by JCross (edited 03-11-2002).]

 

[Romulus]

Simply setting 'limit to list' to yes worked. Thanks Jennifer. I guess I should look at all of my properties more carefully. There is one more thing that I can't resolve. I have some VB code written for an "After Update" event for that combo box. I did a little testing. I typed in an invalid number, when I got my message about an incorrect value, I manually erased the invalid number (and left it blank) and "accidentally" tabbed to another field. When this happened it took my directly to my VB code. I also tested this after deselecting all of my menu options in the startup window. It still takes me back to the vb code. I don't want my users having access to the code. Do you know how I can prevent this from happening?

This is my code:
Sub Combo205_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[ID] = " & Me![Combo205]
If Me.RecordsetClone.NoMatch Or Me.RecordsetClone.RecordCount = 0 Then
MsgBox ("WARNING!! This person does not exist. Type a valid number")
Combo205.SetFocus
' want to clear the combo box here
Else
Me.Bookmark = Me.RecordsetClone.Bookmark
Field1.SetFocus
End If
End Sub

[This message has been edited by Romulus (edited 03-11-2002).]

 

[JCross]

May have to wait for someone else to answer this one : ) Where in the code does it take you? Did you try stepping through your code to see where the glitch happens?

As far as your users having access to your code, I've been giving my users MDE files instead of the db frontend just for that reason. Works GREAT, but you have to keep a db copy to work on because you can't go back and work on the MDE.

Jennifer

 

[Romulus]

Jennifer,
It takes me to this part of the code:
Me.RecordsetClone.FindFirst "[ID] = " & Me![Combo205]

I didn't step through the code because honestly I can't remember how to do that. I learned it in class a long while ago. I'll have to rummage through my books at home to find out.

I created an MDE file for someone else's database, but I was going to try and avoid that for this one because it is going to go up on a server that is not ours.

 

[JCross]

Here we are bumbling about and there is probably a simple answer (but i don't know what it is). One easy way to go in and look at code while it is running is to just click on the grey space next to the line you want to look at and a reddish dot will appear. then when the code is running on your event, it will stop there and you can mouse over the different arguments and see what their current values are (remember that they won't have any until the line has already been executed).

Even though you are having some issues with your code now, your basic security should lock down access to your code and db window in the end...

Jennifer