Access Forms and a possible automatic field fill 2

[dfitz92]

I am trying to find out if in Access, when using a form, if the employee types in a SS# is there a way to automatically enter that persons name information. I am new at Access and working on the 97 version. Any help would be greatly appreciated. Thanx.

 

[gcole]

You can do that with a combobox. Attach it to a table/sql with your names and ss#'s.

 

[dfitz92]

Thank you for replying gcole. But im not sure i understand that. I can put a combobox on the form. And link it to the table with the information. But when i put a number in i want it to automatically bring up their name. Could you explain a little more in depth?

 

[rpdf]

Do you have a table with several field's worth of information? And you want to type in a SSN and then get the name? If so... you want to create a second table with just the SSN and names. Link the two tables together in the Relationships window (drag the SSN field from your small table to the SSN field in your large table [a one-to-many relationship], so SSN will need to be the primary key in your second smaller table).

Then make a query, pulling the fields you need from both tables. Then make your form from that query. Combo box for SSN on your form is a good idea. You can then change the properties for your Name field so it is not a Tab Stop, so it is not enabled (someone can view the name, but not change it), etc. (I'm not sure if you need to pull the SSN field from your small table or the large one; you may have to try both...)

I know this is fuzzy, but it's been a while since I've done it. At the very least, I hope it gives you a place to start. Good luck!

 

[dfitz92]

Thank you all for your advice. Finally i used the Dlookup to make this work. I built an expression in the code builder with the help of an Access Microsoft representative.
The code goes like this:

Private Sub CODE_LostFocus()
Dim x As Variant
x= DLookup("[LASTNAME]", "COMPREF", "[CODENUM] =" & Forms![ENTER NEW COMPS]!CODE)
Me.Text24 = x
End Sub

In this instance, LASTNAME is a field name, COMPREF is a table, CODENUM is a field name, ENTER NEW COMPS is a table name, CODE is a field name and Text24 is the label of the text box to be filled.
If i understand this correctly it takes the CODENUM you enter and as soon as that field loses focus it searches the table COMPREF, for the name that matches the codenum, then it puts the name found into the form ENTER NEW COMPS in the CODE field. Specifically into the Text24 text box.
I hope this help anyone trying to do this and if not please e-mail me.
Dfitz92@aol.com

 

[jfhewitt]

Basing a form on a linked query is the neatest method, but you can also enter an expression using dlookup into a text field. Easier than writing the VBA code. Syntax is =dlookup(&quot;[<field name>]&quot;,&quot;<table name>&quot;, &quot;[<ssnumber>]= &quot; ssnumber from form). The syntax of the criteria can be bit tricky...check the Access help if you have problems.