Completing a form field based on answers in two other fields.

[Guest]

Access 2003

I have created a form to populate a table with five fields. The user will
enter the first three fields and the fifth field. Howevever, I want the
fourth field to locate an item based on the responses in fields 2 and 3.

I have combo boxes set up for fields 2 & 3, and I want the information to be
verified against equal fields in a table that also has the information to
populate field four.

For example,

If enteredfield 2 = tablefield 2 and enteredfield 3 = tablefield 3 then
currentfield 4 = tablefield specified. I want the result displayed in
currentfield 4 for verification.

I created an SQL statement (query) for the currentfield4, but I've done
something wrong. The system prompts for the values prior to displaying the
form.

Can anyone help?

 

[Guest]

In a situation like this, a DLookup is probably better than using SQL. I
would suggest you write a Sub to return the value you want and to call it in
the After Update events for TextBox2 and Text Box3 (what you are calling
field2 and field 3, but forms don't have fields, they have controls. Only
tables and queries have fields)

Sub GetTextBox4()
Dim varResult as Variant

Me.TextBox4 = DLookup("[FieldSpecified]", "MyTableName", "[Field2] = '"
& _
Me.TextBox2 & "' And [Field3] = '" & Me.TextBox3 & "'")
End Sub