Link Criteria and Open Form Code Help, Thanks in Advance

  • Thread starter Thread starter Gulf Coast Electric
  • Start date Start date
G

Gulf Coast Electric

I am trying to make this form FPayments open the form Timecards and link the
criteria so that the matching record opens.
It opens the correct record, but when there is no matching record it still
opens the form.
How can I correct This ? Thanks, Dave

Dim stDocName As String
Dim stLinkCriteria As String
Dim VarX As Variant

stDocName = "TimeCards"
stLinkCriteria = "[Repair Order Number]=" & "'" & Me![Order ID] & "'"
VarX = DLookup("[Order ID]", "Orders", "[Order ID] =
Forms![FPayments]![Order ID]")

If IsNull(VarX) Then
MsgBox "No Time Card Found" 'Changed 4/7/03
Else: DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
 
Are you sure you're testing using the same criteria? In your DLookup
function your asking for the "OrderID" field. In the linkCriteria you're
asking for "Repair Order Number". Are they the same? Why not just use
stLinkCriteria in the DLookup function?

Also, order id sounds as if it would be a number. What is it's data type? If
it's a numeric type, you shouldn't surroune it with parentheses.

"[Repair Order Number]=" & me![order id]
 
Gulf Coast Electric said:
I am trying to make this form FPayments open the form Timecards and link the
criteria so that the matching record opens.
It opens the correct record, but when there is no matching record it still
opens the form.
How can I correct This ? Thanks, Dave

Dim stDocName As String
Dim stLinkCriteria As String
Dim VarX As Variant

stDocName = "TimeCards"
stLinkCriteria = "[Repair Order Number]=" & "'" & Me![Order ID] & "'"
VarX = DLookup("[Order ID]", "Orders", "[Order ID] =
Forms![FPayments]![Order ID]")
Click to expand...

You are looking here for a record in the Orders table - and this
record probably always exists, it is probably the very same record as
the current record in the form. I guess you should look up a record in
the table for the repair orders.

Another advice: When putting together criterias in code, you should
concatenate the control values, not the control names, like this:

VarX = DLookup("[Order ID]", "Orders", "[Order ID] =" &
Forms![FPayments]![Order ID])

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de
 
The Controls are correct, Repair Order ID and Order ID
I just need to know how to stop the form from opening if there is not a
matching record.


Tom Stoddard said:
Are you sure you're testing using the same criteria? In your DLookup
function your asking for the "OrderID" field. In the linkCriteria you're
asking for "Repair Order Number". Are they the same? Why not just use
stLinkCriteria in the DLookup function?

Also, order id sounds as if it would be a number. What is it's data type? If
it's a numeric type, you shouldn't surroune it with parentheses.

"[Repair Order Number]=" & me![order id]


Gulf Coast Electric said:
I am trying to make this form FPayments open the form Timecards and link the
criteria so that the matching record opens.
It opens the correct record, but when there is no matching record it still
opens the form.
How can I correct This ? Thanks, Dave

Dim stDocName As String
Dim stLinkCriteria As String
Dim VarX As Variant

stDocName = "TimeCards"
stLinkCriteria = "[Repair Order Number]=" & "'" & Me![Order ID] & "'"
VarX = DLookup("[Order ID]", "Orders", "[Order ID] =
Forms![FPayments]![Order ID]")

If IsNull(VarX) Then
MsgBox "No Time Card Found" 'Changed 4/7/03
Else: DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
Click to expand...
Click to expand...
 
Dave Elliott said:
The Controls are correct, Repair Order ID and Order ID
I just need to know how to stop the form from opening if there is not a
matching record.
Click to expand...
Your code should stop it from opening if VarX evaluates to null. The
references to [Order ID] and [Repair Order ID] should be referencing fields
in a table or query not controls on a form. The DLookup function looks for
information in a table or query. The first argument is an expression that
identifies a field in the recordset you're searching. In the criteria, if
you put the control name inside the parenthesis, Access will not evaluate
it. It will simply use the control name as a literal string. In other words,
when you say "[Order ID]= Me![Order ID]" you're asking Access to find
records where [Order ID] = "Me![Order ID]". I doubt any of your records have
an ID of Me![Order ID]. Have you tried stepping through your code to see
what the value of VarX is while the procedure is running?
 
Repair Order Number & Order ID are the same.
Me! simply means the current value of Order ID.
The code works as it is now, just not when no record is found.
Tom Stoddard said:
Dave Elliott said:
The Controls are correct, Repair Order ID and Order ID
I just need to know how to stop the form from opening if there is not a
matching record.
Click to expand...
Your code should stop it from opening if VarX evaluates to null. The
references to [Order ID] and [Repair Order ID] should be referencing fields
in a table or query not controls on a form. The DLookup function looks for
information in a table or query. The first argument is an expression that
identifies a field in the recordset you're searching. In the criteria, if
you put the control name inside the parenthesis, Access will not evaluate
it. It will simply use the control name as a literal string. In other words,
when you say "[Order ID]= Me![Order ID]" you're asking Access to find
records where [Order ID] = "Me![Order ID]". I doubt any of your records have
an ID of Me![Order ID]. Have you tried stepping through your code to see
what the value of VarX is while the procedure is running?
Click to expand...
 
There are two things that I see that don't make sense to me. I'm sorry I
used the Me![Order ID] example earlier. I should have gone back and reread
your code. What I was really referring to was the criteria in the DLookup
function. Yours reads:

DLookup("[Order ID]", "Orders", "[Order ID] = Forms![FPayments]![Order ID]")

Try this:

DLookup("[Order ID]","Orders","[Order ID] = '" & Forms![FPayments]![Order
ID] & "'")

Or even this:

DLookup("[Order ID]", "Orders", stLinkCriteria)

And the other thing that looks suspicious is:

Else: DoCmd.OpenForm stDocName, , , stLinkCriteria

Why is there a colon after Else? Shouldn't it be:

If IsNull(VarX) Then
MsgBox "No Time Card Found" 'Changed 4/7/03
Else
DoCmd.OpenForm stDocName,,,stLinkCriteria
End If

With the colon, Access will assume that "Else" is just a line label which is
ignored I believe. Basically what's happening is that VarX is alway null
because of the way your criteria argument is written and the line of code
that opens the form is always executed when VarX is null. If you fix your
DLookup function without fixing your else line, your form will only open
when you don't want it to. The rest of the time it won't open because the
else line is just another line of code inside your if then statement. Am I
making sense?


Gulf Coast Electric said:
Repair Order Number & Order ID are the same.
Me! simply means the current value of Order ID.
The code works as it is now, just not when no record is found.
Tom Stoddard said:
Dave Elliott said:
The Controls are correct, Repair Order ID and Order ID
I just need to know how to stop the form from opening if there is not a
matching record.
Click to expand...
Your code should stop it from opening if VarX evaluates to null. The
references to [Order ID] and [Repair Order ID] should be referencing fields
in a table or query not controls on a form. The DLookup function looks for
information in a table or query. The first argument is an expression that
identifies a field in the recordset you're searching. In the criteria, if
you put the control name inside the parenthesis, Access will not evaluate
it. It will simply use the control name as a literal string. In other words,
when you say "[Order ID]= Me![Order ID]" you're asking Access to find
records where [Order ID] = "Me![Order ID]". I doubt any of your records have
an ID of Me![Order ID]. Have you tried stepping through your code to see
what the value of VarX is while the procedure is running?
Click to expand...
Click to expand...
 
Back
Top