Looking up tables and populating form fields (1 Viewer)

S

StuartW

Guest
I have a form "Form1" with a combo box looking up values in "Tbale1" from Coloum1. After_update I want "TextBox1" and "TextBox2" to automatically up date with the corrosponding fields from Coloums 2 and 3 in "Table1"

How do I do this using VBA?
 
S

StuartW

Guest
Some code I was trying to use was:


Private Sub Case_Officer_AfterUpdate()

Dim db As Database
Dim recordset
Dim frm As Form_New_Case_Detail_Form
Dim Name As String
Dim Phone As String
Dim Office As String

Set db = CurrentDb()
Set recordset = db.OpenRecordset("tblCaseOfficer")
Set Name = frm.Case_Officer

recordset.Index = "Case Officer"
recordset.Seek "=", Name

If recordset.NoMatch = True Then
MsgBox "We have record of that name " & "with the last name " & Name
Else
MsgBox Matchmsg
End If

Set Phone = recordset.Index = "Phone Number"
Set Office = recordset.Index = "Office"

Let frm.Phone_Number = Phone
Let frm.Officer = Office

End Sub

This may explain better what I am trying to do. The code is obviousley not working correctly. Any ideas?

Cheers
Stuart
 

KDg

Registered User.
Local time
Today, 04:34
Joined
Oct 28, 1999
Messages
181
You could try opening a recordset rather than a table. Set rst=db.openrecordset("SELECT ...FROM...WHERE...") and then use rst.recordcount>0 to check it exists. then populate you boxes with "let text1=rst.fields(0).value" and "let text2=rst.fields(1).value". That's as long as you're sure there will only be one match for your criteria, this method will only drop in the first values it finds. If there are more you will need to cycle through the recordset ( rst.MoveNext). hope it helps
 

KDg

Registered User.
Local time
Today, 04:34
Joined
Oct 28, 1999
Messages
181
or do you mean that the event isn't firing off?

[This message has been edited by KDg (edited 11-26-1999).]
 

Users who are viewing this thread

Top Bottom