Try this:
Create a while new project. Under the "Project" menu in the VB IDE, click "References". Find the reference to "Microsoft ActiveX Data Object 2.0" and select it. Close the window, and go back to your project.
Create a module, name it whatever you want, and place the following code in it (modify the database name to what you need):
Public con as ADODB.Connection
Public rs as ADODB.Recordset
Public Sub SetCon()
With con
.ConnectionString=app.path "\databaseName.mdb;"
.Provider="Microsoft.Jet.2.0"
.Open
End With
End Sub
Public Sub SetRS(SQL)
With rs
.ActiveConnection=con
.Source = SQL
.LockType = adLockOptimistic
.CursorType = adOpenDynamic
.Open
End With
End Sub
Now, in your first form's load procedure, add the following code (assuming that the combobox mentioned in your above explanation is on that page, the table name is tblUsers, and the field that you are extracting info from is fldSSN):
Private Sub Form_Load()
SetCon 'This sets your connection object
SetRS "Select * FROM tblUsers"
'This will populate your combo box
Do While Not rs.EOF
cboSSN.addItem rs.Fields("fldSSN"
rs.MoveNext
Loop
End Sub
Now for the rest of the example, we'll assume that you have two text boxes on the form along with the combo box (named cboSSN). One named txtName, and the other txtAddress. We will also assume that the fields in the database have the same name, but are prefixed with "fld" instead of "txt". Add this code to your combobox's OnClick event.
Private Sub cboSSN_OnClick()
rs.MoveFirst
'This selects the requested record
SetRS "SELECT * FROM tblUsers WHERE fldSSN = '" & cboSSN.Text & "'"
'This sets the text fields
txtName.Text = rs.fields("fldName"
txtAddress.Text = rs.fields("fldAddress"
End Sub
The problem with using database connections as you described above is that, when set by static properties, the connection remains static. In other words, if you try to move the program to another machine without setting the connection properties in code, the program will not access the database unless the exact same folders are set up on that machine that were used in the creation of the program.
You can use ADO objects and place them on your form, although I personally find it kind of cluttering, and coding everything seems just a little bit cleaner (that IS just a personal preference though)
I hope this code helps you out. Good luck.
-----------------------------------------------
"The night sky over the planet Krikkit is the least interesting sight in the entire universe."
-Hitch Hiker's Guide To The Galaxy
