dlookup (1 Viewer)

[kitty77]

I have a form based on a table called calc. On that form, I have a fields called idnumber and standard.
I would like to do the following... If the valve in idnumber (after update) equals or exists in table main (a different table)
then the valve on my form for standard is the valve from table main.

Basically, I would like to get the valve from another table if it's equal and paste that valve into my form.

I guessing some kind of dlookup? Hope I explained it well enough...

 

[bob fitz]

You could use DLookup() in some code. Have you tried this yet.

Alternatively, perhaps you could use a combo box to enter the idnumber. If the combo is populated by the data in the "other" table then you could populate the text box from a hidden column in the combo.

 

[kitty77]

No, I have not tried the DLookup. Not sure how to start that.

 

[arnelgp]

Use the BeforeUpdate or AfterUpdate of the ValveID textbox to get the info:

Private sub valveID_afterupdate()
Dim strValveDesc as string
If len(me!valveID & "") > 0 then
StrValveDesc = dlookup("DescField", "valveMasterTable", "valveId='" & me!valveID & "'") & ""
If len(strvalveDesc)>0 then
Me!txtDesc=strvalveDesc
End if
End if
End sub

 

[Pat Hartman]

What you are requesting indicates a design flaw. The others have given you a band-aid but you really should fix the problem.

You are keeping data in two places and it should be kept in only one.

Since I don't know what IDNumber and Standard are, I'll use a more generic example.

In an order entry application, you have a customer table and an order table. The PK of the customer table is CustomerID and the PK of the order table is OrderID. Both are autonumbers. To connect the two tables, you have a column in the Order table to hold CustomerID. The order entry form would normally have a combo that is bound to CustomerID in the order table but the RowSource of the combo is bound to the customer table and so it shows CustomerName even though CustomerID is what is saved. You would NEVER save CustomerName in the order table. If you also want to show customer information in addition to the CustomerName on the invoice report, you would create a query that joins tblOrder to tblCustomer on CustomerID and select the columns from both tables that will appear on the invoice.

People new to Access don't understand how queries are used to pull everything together and so thinking they need to store the Customer information in the Order table is a quite common mistake. In reality the only customer field that should ever be saved in the order table in this example is the foreign key which is CustomerID. All other fields are obtained by using a query when necessary.