Add data to Table using Dlookup from a Form 2

[dmeyring]

Hello,
I am new to Access and am having difficulty with a Dlookup function. I have a form that I wish to autopopulate 10 fields with data based on two criteria which are also fields on the form. The user would enter the two criteria in the form and the ten fields autopopulate. To achieve this, I used a Dlookup function in the Control Source of the fields I wished to autopopulate.

This works perfectly except that it does not populate my table with the information retrieved in the Dlookups. The information that I am retrieving thorugh the dlookups is unique data that I need to add into my existing table, not duplicative in nature.

Is there another way to execute the Dlookup so that the table will be updated, or is there a different method that should be used to achieve this?

Any direction on this would be greatly appreciated!! - Thanks!

 

[IGPCS]

well the easiest way to do this i by using an update query where he takes only the information that you have on the form.

if you need more details please let me know and i will post

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[Zion7]

If you're using DLookup, in the control source of a field,
then that field is no longer vbound to yiour table.

IGPCS's idea will work, or since you already have the dLookup syntax, Bind your fields, on the after update event,
of the criteria fields out.
Me.txtName = DLookup("txtName","tblContact", _
"txtID = " & Me.txtID)

Something like that, not sure if your form is bound or not?

Probably like IGPCS said, use UPDATE or INSERT...

 

[dmeyring]

Thank you both!! I switched the Dlookup formulas from the control Source of the fields to the Got Focus event of the fields. Then reverted the control sources back to being bound to my table. I then added an On Exit event from the second data entry field to Set Focus on each of the controls in a particular order. Works like a charm!

I am curious about the update query approach - If I am thinking of this correctly, the update query would update existing records in Table1 with records from Table2 and then query those results for the desired two criteria that my user inputs. But if my user is entering two brand new criteria, not already in Table1, would this still work?

Thanks for all of the information!!!

 

[Zion7]

If I follow correctly, you would do a check first,
for record to either update or insert.

Dim varKey as Variant
varKey = DLookUp("txtName","tblXXX",_
"txtID =" & Me.txtID)

If Not IsNull(varKey) Then
SQL = "UPDATE tblXXXX SET txtName ='" & Me.txtName....
Else
SQL = "INSERT INTO tblXXX(txtName,txtCity) VALUES('" & _
Me.txtName & "','" & ....
End If

CurrentProject.Connection.Execute SQL,,adExecuteNoRecords