Use a Form to lookup data in one table and populate fields in another

[mike67]

I'm fairly new at using forms in access and this seems like it should be a simple issue. I want to use a form to populate an underlying table after the user keys in a code number. I want to use this code to look up certain information from a lookup table containing info on this code. I only want this lookup to happen the first time the code is entered (no need to look up again after the data is added to the underlying table).

I've tried a number of things including a macro that opens a query using the code number from the form ([forms]![form_name]![code_num]) in the OnExit Event of that field and then using SQL statements to select from that query for each field I want to look up. This has been ineffective.

It looks like writing a procedure would be the best way around this, especially to keep from updating the fields each time an existing record is accessed. Any suggustions????

 

[DougP]

This is the start to finding data in Access 2000 and 2002.
We are going to use ADO "Active-X Data Objects" to open a recordset and find the data.
First open your form in design view.
Click the "View" menu then "Code"
This will bring you into the VBA editor program.
In here click the "Tools" menu then "References"
Find "Microsoft ActiveX Data Objects 2.5 Library" it will be way down the list. Put a check next to on the left and click OK. We just told the VBA editor we are going to use ADO. If you don’t do this you will get errors.
This has to be done each time you create a new database .mdb. If you want to use ADO that is.
Now then go back to your form (look at the bottom in the task bard for the form name. When you are working in Access and using VBA you will see at least 3 icons in the task bar. The first one on the left is the Database the second on is the Form the third one is VBA editor. It starts with the word Microsoft and has a funny white icon with red and yellow poker chips.

On the main form add a command button.
Double click the button to bring up its properties. Click the “All” Tab at the top of the properties box.
Look down the list to find the “On-Click” event. When you click the button it will fire this event and run any code listed in it.

You want to find some data. So you need to know the exact spelling of the table to search in and the exact spelling of the field or fields you want to search on in that table. Copy and paste is your friend here!!!

After you have the table name and field(s) then you need to paste this code in the On_Click event
Like so the code in blue is want you want the black code should already be there.

Private Sub Command6_Click()
On Error GoTo Err_Command6_Click

Dim Conn2 As ADODB.Connection
Dim Rs1 As ADODB.Recordset
Dim SQLCode As String
Set Conn2 = CurrentProject.Connection
Set Rs1 = New ADODB.Recordset
SQLCode = "SELECT * FROM [yourtable] WHERE yourfield = '" & Me![yourSearchtextbox] & "';"
Rs1.Open SQLCode, Conn2, adOpenStatic, adLockOptimistic
Me![results1] = Rs1![field-to-put-into-1]
Me![results2] = Rs1![field-to-put-into-2]
Me![results3] = Rs1![field-to-put-into-3]
Me![results4] = Rs1![field-to-put-into-4]
Set Rs1 = Nothing
Set Conn2 = Nothing

Exit_Command6_Click:
Exit Sub

Err_Command6_Click:
MsgBox Err.Description
Resume Exit_Command6_Click

End Sub


DougP, MCP

 

[mike67]

DougP,

Thanks for your help on this post, it certainly sent me in the right direction. Sorry it's taken so long to get back.

I came up with the following because I am actually looking up values from an Oracle database.

'declare global Object variable
Dim OracleDB As Object


'I established a connection with Oracle when the user opened
' the form
Private Sub Form_Open(Cancel As Integer)

'display something in status bar
SysCmd acSysCmdSetStatus, "Connecting to Oracle"
DoEvents
Set OracleDB = CreateObject("ADODB.Connection&quot
OracleDB.Open "ODBC;UID=USERID;PWD=PASSWORD;DSN=vic oracle"

'display something on status bar
SysCmd acSysCmdSetStatus, "Completed connecting to Oracle"
DoEvents

End Sub


'When user exits data entry field, look up value in Oracle
Private Sub CODE_NUM_Exit(Cancel As Integer)

On Error GoTo Err_CODE_Exit

Dim SQLNUM As String
Dim rsOracleData As ADODB.Recordset

Set rsOracleData = New ADODB.Recordset

SQLNUM = "SELECT * FROM TABLE WHERE CODE_NUM = " & Me![CODE_NUM] & ";"
rsOracleData.Open SQLNUM, OracleDB
Me![NAME_STR] = rsOracleData![NAME_STR]
Me![ADDR1_STR] = rsOracleData![ADDR1_STR]
Me![CITY_STR] = rsOracleData![CITY_STR]
Me![ST_STR] = rsOracleData![ST_STR]
Me![ZIP_STR] = rsOracleData![ZIP_STR]
Me![CONTACT_NAME_STR] = rsOracleData![CONTACT_NAME_STR]
Me![CONTACT_TITLE_STR] = rsOracleData![CONTACT_TITLE_STR]
Set rsOracleData = Nothing

Exit_CODE_NUM_EXIT:
Exit Sub

Err_CODE_Exit:
Me.Repaint
Me!CODE_NUM.SetFocus
MsgBox Err.Description
Resume Exit_CODE_NUM_EXIT

End Sub

'Set object to nothing when the form is closed
Private Sub Form_Close()
Set OracleDB = Nothing
End Sub

I'm having a problem however, when a user keys in an invalid code. I figured I would just use the error handler to display a message for the user to make sure they were keying a valid value and then return the focus on the form to that field. The code in RED should take the focus back to the proper field, but I can't make it happen. Am I using the .SetFocus method in the wrong way?

Thanks again for your help!!