Referencing Dlookup in Form based on query

[Guest]

Have to do field validations in before update event. Not 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.These codes are being
assigned in Form based on queries.
Basically there r two tables : Flight Trans and Cancellation Flight Trans
Query links both on SubTransNo and Form is based on 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

 

[Steve Schapel]

Sandy,

The syntax of the DLookup is not correct. I think it should be like this...
DLookup("[FltTransDate]","Flight Trans","[SubTransNo]=" &
Me![SubTransNo])

 

[Guest]

Thanks! But i have 2 fields by name of subtrans no in form as its based on
query which is linking invoice and cancellation information and they both
have this field. How would Dlookup identify that i am referencing to Flight
Trans or Cancellation Flight Trans SubTransNo. In Form, SubTransNo for
cancellation is referenced as Cncl Flight Trans.SubTransNo. Please clarify as
how we would reference between 2 of them.

Sandy,

The syntax of the DLookup is not correct. I think it should be like this...
DLookup("[FltTransDate]","Flight Trans","[SubTransNo]=" &
Me![SubTransNo])

--
Steve Schapel, Microsoft Access MVP

Have to do field validations in before update event. Not 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.These codes are being
assigned in Form based on queries.
Basically there r two tables : Flight Trans and Cancellation Flight Trans
Query links both on SubTransNo and Form is based on 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

 

[Steve Schapel]

Sandy,

This is a very unusual arrangement. Is SubTransNo the field (or one of
the fields) that joins/relates the two tables in the query. If so, then
the value in the SubTransNo field in both tables will be the same for
each record, so there is no purpose served in including both of them in
the query. If they are not related, then it means you have two fields
with the same name but a different meaning, which I suppose is an
imprudent design choice. In this second case, it would be a good idea
to alias them in the query. Even if you didn't do that, it would be
good to name the controls on the form bound to these fields with very
distinct names, and then use these names in your code.

But having said all that, I am quite confused about the actual meaning
and purpose of the DLookup expression anyway. You are using the DLookup
function to reference the value of a field in the Flight Trans table,
right? And now you tell me that Flight Trans is one of the tables that
is included in the query that the form is based on. So that means that
the FltTransDate field can be included in the query, and hence directly
to the form, right? So DLookup doesn't really make sense, does it?
Please clarify if I am missing something here.