Need Help Auto-Filling Data on a Form

[VAST39]

Hey guys,

I have an access form that I created for user entry. The control source of the form is a table where I want to store their data entries.
However, my boss wants me to have the form automatically retrieve the employer's name and populate it on the screen based on the employer number that the user enters on the screen.

I just can't find a way to do this. I know I have to involve a query somehow to take the user's input for employer number, but everything I've tried as failed. I tried creating a make table query which would store the matching record for the employer name and number in a temp table. Then set the control source of my name field to that temp table. Problem is the temp table can not be deleted since it is locked onto the form.

Surely there is a simple way to do this. All I need is for Access to automatically display the employer name based on the number that the user keys in.

 

[PHV]

Did you try a ComboBox ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

So are your values - Employer (or is it Employee) Number and Name in a table?

If so, then as PHV suggests you can use a Combo Box, and depending upon how you want to show it, you can show 2 columns with the combo box, or else have a textbox that grabs the name from the combo box (or other means).

If you want it to definitely be textbox entry and display, then you could do something like this:
[ol 1]
[li]Create an Input textbox, if not already there[/li]
[li]Create an Output textbox, if not already there[/li]
[li]Use some VBA code to enter the value in the Output textbox based on the entry in the input box... and do this when someone adds or changes the value in the input..[/li]
[/ol]

So an example of what I probably would do is:

Private Sub txtMyInput_Update()
   Dim strSQL As String
   Dim db as DAO.Database
   Dim rs as DAO.Recordset

   strSQL = "SELECT EmployeeName FROM EmployeesTable WHERE EmployeeNumber = " & Me.txtMyInput
   Set db = CurrentDb
   Set rs = db.OpenRecordset(strSQL)

   Me.txtMyOutput = rs.Fields("EmployeeName")

   If rs Is Nothing Then Else rs.Close: Set rs = Nothing

End Sub

What I did there was use a DAO Recordset to grab the value from a query. That tends to work faster, at least in my experience, than using something like DLOOKUP. DLOOKUP can be an easier solution, though. If you want to use it, look it up in the help file. Make sure, though, this is a very small database, or at leas ta small recordset/table. The larger the table/recordset, the slower "D" functions get, such as DLOOKUP, DCOUNT, DMAX, etc.


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[missinglinq]

I suspect that you meant for

Private Sub txtMyInput_Update()

to be

Private Sub txtMyInput_AfterUpdate()

The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[dhookom]

If you need to use a text box, you can still add a combo box with the same control source but display the column of the employer's name. Set the combo box to disabled and locked.

Duane
Hook'D on Access
MS Access MVP

 

[kjv1611]

> missinglinq

Yep, that is exactly what I meant. Thanks

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57