Filling ComboBoxes automatically after typing id for existing record

[locorecto]

Hello Guys,

I am relatively new to Access. But I have a little more experience with Visual Basic. I am creating a form with a set of ComboBoxes which contain students' information. The students will type the last 4 digits of the social security number and if the Student have entered his/her information before, the rest of the ComboBoxes will get filled automatically using that record from the Database, otherwise they will stay blank for the student to fill them.

I would like to know how to search within the database for a particular record using the last 4 digit of the ssn??

Now, there is another issue. Since there are only 10000 combinations of the last 4 digits of the ssn, there could be the case that more than 1 student have the same last 4 digits. In that case I have to populate the comboBox where the student's name goes with only the name that matches the last 4 digit previously typed.

Can someone help me with that???? What should I use!!



I was planning to use VBA code but I don't know what code to use for Access.

Thanks in advance....

By the way, I am using Access 2007

[NTC]

A combobox has a record source - make that record source be a query with the criteria of the last 4 digits.

So to do it - first make the combobox that see all records - then modify that source.

The criteria in the query is 'called' from the 4 digit control so it is dynamic.

Finally you will need to requery that combobox so it refreshes with correct records after the 4 digits have been entered into the 4 digit control.

hope this makes sense.

[locorecto]

Hey thanks for your quick response.

I understand your logic pretty well, in fact that is what I was thinking to do more or less. However, my problem is the implementation/coding part.

I would like to know what code or how would I take the 4 numbers typed in txtSSN (which is a textbox where the user is going to type the last 4 digit of ssn), find all the matching records to that SSN, and fill combobox called cbName with the name field of those matching records.

I have found some example on google that use the FindFirst function. However, that only return the first matching record. I need more than that.

I really appreciate your help.

Thank You!

[NTC]

the record source of the combobox can be a named (regular) query. this allows you to design/test the query all by itself...

so make the query that holds the fields you want in the combobox. when it is showing all records; then add criteria to call the 4 ssn from the form. the form must be open. type in a value... in your query put in the call via syntax:
Forms!FormName.txtSSN

when you run it - it will show records with that 4 ssn values

then make the combobox sourced on this query if it isn't already...

you will need to put a line of vba to refresh the combobox; me.ComboBoxName.requery

you can put that on the OnExit or AfterUpdate event of the txtSSN box - presuming the user will tab forward and trigger the event...

hope this helps.