D Lookup

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Have got 2 tables. Air Sales and Air Cancellation and along with associated 2
forms.
Both have field ie Product ID. I want that whenever i raise credit note in
cancellation table, i get Product ID automatically in form. Both invoice
table and cancellayion table are linked through transno( auto no in invoice
table).
For Cancellation form, its through Cancellation Query where invoice and
cancellation tables have been linked on Trans No.

When i put Dlookup in either before update event of form or on Exit event of
Product ID field, i dont get results and it comes as blank. Following is the
statements given by me:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim aProductID As Variant
aProductID = DLookup("Forms![Qry Cncl]![ProductID]", "Air Invoice",
"Tables![Air Invoice]![TransNo]" = "Forms![Qry Cncl![TransNo]")
Me![Cncl.ProductID] = aProductID
End Sub

Please advice. I have even put this is in on Exit event but this does'nt sho
results.
 
well, just to analyze the DLookup() function arguments: the first argument
should be the name of a field, or an expression based on the name of a
field, in the domain. AFAIK, a reference to a form's field or control will
not work. the second argument is the name of the table or query that is used
as the domain. as long as "Air Invoice" is the name of either a table or a
query, it should be okay. the third argument is criteria set on one or more
fields in the domain. the full reference "Tables![Air Invoice]![TransNo]" is
not necessary, and AFAIK it will not work. also the syntax of the criteria
argument is wrong. try

aProductID = DLookup("ProductID", "Air Invoice", "TransNo = " & Me!TransNo)

the above assumes that the code is running in the form named [Qry Cncl].
suggest you read up on the DLookup() Function topic in Access Help, so
you'll have a better understanding of how it works.

hth
 
Thanks Tina for your valuable advice. But somehow since am not experienced
user, my codes are not functioning. I have changed my codes as per below.
Would require yours further expertise. Thanks
 
Thanks Tina for your valuable advice. But somehow since am not experienced
user, my codes are not functioning. I have changed my codes as per below.
Would require yours further expertise. Thanks
Click to expand...

If TransNo is a numeric field, do this:

aProductID = DLookup("Forms![Qry Cncl]![ProductID]", "Air Invoice",
"Tables![Air Invoice]![TransNo] = " & Forms![Qry Cncl![TransNo])

If it's a text field, do this

aProductID = DLookup("Forms![Qry Cncl]![ProductID]", "Air Invoice",
"Tables![Air Invoice]![TransNo] = '" & Forms![Qry Cncl![TransNo] & "'")

Tom Lake
 
Thanks Tom for inputs. My Dlookup is working in Forms which is based on
Tables. AM having this form which is based on query : Invoice Table and
Cancellation Table and both have common Trans No field.
So in query Trans No of Cancellation becomes : Cancellation.Trans No. How
should i reference this element in Dlookup as nothing is working? Pls advice.
In my tables trans no is text field.

Tom Lake said:
Thanks Tina for your valuable advice. But somehow since am not experienced
user, my codes are not functioning. I have changed my codes as per below.
Would require yours further expertise. Thanks
Click to expand...

If TransNo is a numeric field, do this:

aProductID = DLookup("Forms![Qry Cncl]![ProductID]", "Air Invoice",
"Tables![Air Invoice]![TransNo] = " & Forms![Qry Cncl![TransNo])

If it's a text field, do this

aProductID = DLookup("Forms![Qry Cncl]![ProductID]", "Air Invoice",
"Tables![Air Invoice]![TransNo] = '" & Forms![Qry Cncl![TransNo] & "'")

Tom Lake
Click to expand...
 
Sandy said:
Thanks Tom for inputs. My Dlookup is working in Forms which is based on
Tables. AM having this form which is based on query : Invoice Table and
Cancellation Table and both have common Trans No field.
So in query Trans No of Cancellation becomes : Cancellation.Trans No. How
should i reference this element in Dlookup as nothing is working? Pls
advice.
Click to expand...

Does your query contain both Invoice table and cancellation table? if so,
you don't need
Dlookup at all, just link the TransNo fields in both tables and include the
fields from the cancellation table
([ProductID] in your case) in the query.

Tom Lake
 
I have changed my codes as per below.

you didn't post your changed code, Sandy.


Sandy said:
Thanks Tina for your valuable advice. But somehow since am not experienced
user, my codes are not functioning. I have changed my codes as per below.
Would require yours further expertise. Thanks

Sandy said:
Have got 2 tables. Air Sales and Air Cancellation and along with associated 2
forms.
Both have field ie Product ID. I want that whenever i raise credit note in
cancellation table, i get Product ID automatically in form. Both invoice
table and cancellayion table are linked through transno( auto no in invoice
table).
For Cancellation form, its through Cancellation Query where invoice and
cancellation tables have been linked on Trans No.

When i put Dlookup in either before update event of form or on Exit event of
Product ID field, i dont get results and it comes as blank. Following is the
statements given by me:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim aProductID As Variant
aProductID = DLookup("Forms![Qry Cncl]![ProductID]", "Air Invoice",
"Tables![Air Invoice]![TransNo]" = "Forms![Qry Cncl![TransNo]")
Me![Cncl.ProductID] = aProductID
End Sub

Please advice. I have even put this is in on Exit event but this does'nt sho
results.
Click to expand...
Click to expand...
 
Thanks Tom! It was such small thought i could'nt visualise..This would work.
But i have to do other field validations in before update event. None of them
are working though same are working in other forms which are based on tables
and not on query. Following are codes which run on form based on table
(ofcourse with relevant field names) but not in query... Would request yrs
expert opinion on same. Is there problem in referencing Trans No in Query as
there are 2 of them one for invoice and other for credit note..Thanks

Dim aCnclDate As Variant

aCnclDate = DLookup("[Flight Trans]![FltTransDate]", "Flight Trans",
"[Flight Trans]![SubTransNo]" = "Me![Cncl Flight Trans.SubTransNo]")
If aCnclDate > Me![FltCnclTransDate] Then
MsgBox "Cncl Date cant be less than Invoice Date", vbOKOnly
Cancel = True
End If
End Sub
 
Back
Top