DLookup with Multiple key field criteria in same file

  • Thread starter Thread starter nopolla
  • Start date Start date
N

nopolla

This is my first question and I would really appreciate anyones help.

I am new to Access 2003. I am attempting to validate a field in a form using
DLookup.

I have:

Private Sub Plan_Exit(Cancel As Integer)
Dim B As String
Dim strWhere As String
strWhere = "(Company = " & Me.Company & ") And (Plan = ""& Me.Plan "")"
B = Nz(DLookup("[gmgplc]", "[grpmst]", strWhere), "zzzz")
MsgBox Company
MsgBox Me.Company
MsgBox Me.Plan
MsgBox Plan
MsgBox B
If B = "zzzz" Then
MsgBox "Invalid Plan"
[Plan] = " "
[Plan].Undo
End If
End Sub

All the MsgBox fields reflect the correct values when I run it, but I keep
getting the "zzzz" s even when both company and Plan values match.

Both are indexed fields with duplicates allowed.

I am thinking it must be how I am structuring the B = line.

Thanks in advance
 
This is my first question and I would really appreciate anyones help.

I am new to Access 2003. I am attempting to validate a field in a form using
DLookup.

I have:

Private Sub Plan_Exit(Cancel As Integer)
Dim B As String
Dim strWhere As String
strWhere = "(Company = " & Me.Company & ") And (Plan = ""& Me.Plan "")"
B = Nz(DLookup("[gmgplc]", "[grpmst]", strWhere), "zzzz")
MsgBox Company
MsgBox Me.Company
MsgBox Me.Plan
MsgBox Plan
MsgBox B
If B = "zzzz" Then
MsgBox "Invalid Plan"
[Plan] = " "
[Plan].Undo
End If
End Sub

All the MsgBox fields reflect the correct values when I run it, but I keep
getting the "zzzz" s even when both company and Plan values match.

Both are indexed fields with duplicates allowed.

I am thinking it must be how I am structuring the B = line.

Thanks in advance
Click to expand...

What's the datatype of Company? What's the value in the control named Company?

I'd use the control's BeforeUpdate event rather than its Exit event (and set
Cancel to true in a mismatch).

For that matter, rather than forcing the user to enter a plan and then
slapping their hand if they make a mistake, you might want to consider giving
the user a Combo Box to select an existing company, and then a dependent combo
box using the company combo as a criterion to select only the valid plans for
that company; would that be a possibility?
 
If Company is a numeric field and Plan is a text field

Replace (Plan = ""& Me.Plan "")" With (Plan = '"& Me.Plan & "')"

Notice the single quotes in place of double quotes and the additional
ampersand at the end

If company is a text field than single quotes will be needed there as well.

Tom
 
Both Company and Plan are Text Fields.

I will have to look into Combo Boxes, but I would like to understand where
my error is also.

Thanks
--
nopolla


John W. Vinson said:
This is my first question and I would really appreciate anyones help.

I am new to Access 2003. I am attempting to validate a field in a form using
DLookup.

I have:

Private Sub Plan_Exit(Cancel As Integer)
Dim B As String
Dim strWhere As String
strWhere = "(Company = " & Me.Company & ") And (Plan = ""& Me.Plan "")"
B = Nz(DLookup("[gmgplc]", "[grpmst]", strWhere), "zzzz")
MsgBox Company
MsgBox Me.Company
MsgBox Me.Plan
MsgBox Plan
MsgBox B
If B = "zzzz" Then
MsgBox "Invalid Plan"
[Plan] = " "
[Plan].Undo
End If
End Sub

All the MsgBox fields reflect the correct values when I run it, but I keep
getting the "zzzz" s even when both company and Plan values match.

Both are indexed fields with duplicates allowed.

I am thinking it must be how I am structuring the B = line.

Thanks in advance
Click to expand...

What's the datatype of Company? What's the value in the control named Company?

I'd use the control's BeforeUpdate event rather than its Exit event (and set
Cancel to true in a mismatch).

For that matter, rather than forcing the user to enter a plan and then
slapping their hand if they make a mistake, you might want to consider giving
the user a Combo Box to select an existing company, and then a dependent combo
box using the company combo as a criterion to select only the valid plans for
that company; would that be a possibility?
Click to expand...
 
So both are text fields
then

strWhere = "(Company = '" & Me.Company & "') And (Plan = '"& Me.Plan & "')"



If Company is a numeric field and Plan is a text field
Replace (Plan = ""& Me.Plan "")" With (Plan = '"& Me.Plan & "')"

Notice the single quotes in place of double quotes and the additional
ampersand at the end

If company is a text field than single quotes will be needed there as well.

Tom
nopolla said:
This is my first question and I would really appreciate anyones help.

I am new to Access 2003. I am attempting to validate a field in a form
using
DLookup.

I have:

Private Sub Plan_Exit(Cancel As Integer)
Dim B As String
Dim strWhere As String
strWhere = "(Company = " & Me.Company & ") And (Plan = ""& Me.Plan "")"
B = Nz(DLookup("[gmgplc]", "[grpmst]", strWhere), "zzzz")
MsgBox Company
MsgBox Me.Company
MsgBox Me.Plan
MsgBox Plan
MsgBox B
If B = "zzzz" Then
MsgBox "Invalid Plan"
[Plan] = " "
[Plan].Undo
End If
End Sub

All the MsgBox fields reflect the correct values when I run it, but I
keep
getting the "zzzz" s even when both company and Plan values match.

Both are indexed fields with duplicates allowed.

I am thinking it must be how I am structuring the B = line.

Thanks in advance
Click to expand...
Click to expand...
 
Back
Top