Looking up tables and populating form fields

[StuartW]

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?

 

[StuartW]

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]

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]

or do you mean that the event isn't firing off?

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