Updating second field in form when first field is selected

[novice]

Hi,

I have an MS access 2007 form which is bound to Access Table. The table contains two fields "first" and "second". The same fields are also present in the form as TextBoxes. When a data is entered in the first textbox say "TestInput" then the corresponding second entry (2) should be populated from the table.

Here is the same table entries (which contains thousands of entries)
TestTable
First Second
TestInput 2
TestText 3
TestData 4

For this i'm thinking to write a sql query inside second_onClick routine of VBA code and display the output of the query in the text box. Is this possible to do. Is there any other way. Please help.

 

[RuralGuy]

Are you just trying to locate a particular record. The ComboBox wizard can do that for you if you select option #3.

 

[novice]

Well, I have actually 3 fields (field1-> textbox, field2->combobox, field3->textbox), i said 2 fields to keep it simple.

Yes, I'm trying to locate a particular record. when field1 and field2 are selected, and clicked on field3 the field3 entry should be automatically in form by looking up the record from Access Table.

For this, i thought i should write a sql query inside field3_onclick routine. But i dont know how to write this. Please let me know if i'm not clear. Thanks.

 

[RuralGuy]

If I understand you correctly it sounds like you might have used a LookUp Field. This is not a very good idea as the link point out. If you use the ComboBox wizard to "locate a record", there is code in the AfterUpdate event of the ComboBox that will help you understand how to do what you want.

 

[novice]

Thanks for the reply. I think, I'm not using any lookup field. I'm attaching my code so that you can have a look. In this i need some VBA code for Text6_Click (). Please let me know if i'm not clear.

 

[RuralGuy]

You are correct in that there are no Lookup Fields but your sample is a long way from your first post. The form is *not* bound and so none of the controls are bound either. What are you really trying to accomplish? Is there a real world scenario?

 

[novice]

User doesnot want to enter data in Field3 and should be automatically populated in Field3 by finding the record from the table. I want to accomplish this. Is it possible to do if we bound the form and controls?. Please suggest me on how to do this. Thanks.

 

[RuralGuy]

There are a number of ways to accomplish what you are asking but as I said, is there a real world example of why you need to do this? A query that uses your controls on the form would return a record(s) if one exists. Then what?

 

[novice]

This has got a real time scenario, these three fields will be part of a form which contains many fields (which will be saved as a record in different table). So when the user enters the data, he is not comfortable with remembering the field3 data, so i want to make it to populate data automatically when he clicks on the box. I hope this is what you are asking about!.

I'm able to write a query which returns the record (with matching criteria as values in Field1 and Field2) but i could not proceed with updating the value in Field3 as i dont know how to do this. I have made an unsuccessful attempt with writing MACRO also.

Can you suggest on how to update the field value. Thanks.

 

[RuralGuy]

Well let's see, you could post what you tried with the query so we can see it. You could use a DLookup to populate the third TextBox. There are probably other methods that could be employed as well.

 

[novice]

Regarding query i just wrote a select statement. I tried using DLookup and able to update the values. Following is the code which i kept in Text6_OnClick. When i uncomment the code by removing <'> before And I'm getting type mismatch error which is obvious as the Field2 is of type number in Table and Combo4 is returing a string. So, how can i convert the string to number present in form?.


Text6.Value = DLookup("[FIELD3]", "[Populate_Data]", _
"[FIELD1] = '" & Forms!Populate_Data_Form!Text0 & "'") 'And "[FIELD2] = '" & Forms!Populate_Data_Form!Combo4 & "'")

 

[RuralGuy]

To start, you are executing from the Populate_Data_Form so you do not need to go through the FORMS collection. Try:
Text6.Value = DLookup("[FIELD3]", "[Populate_Data]", _
"[FIELD1] = '" & Me.Text0 & "' And [FIELD2] = " & Me.Combo4)
Here's a good reference for the syntax of the Domain functions:
http://www.mvps.org/access/general/gen0018.htm

 

[novice]

It just worked for me. Thanks a lot.

 

[RuralGuy]

Excellent! Thanks for posting back with your success.