DLookup with Multiple key field criteria in same file

[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

 

[John W. Vinson]

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

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?

 

[Chegu Tom]

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]

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

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

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?

 

[Chegu Tom]

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

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

 

[John W. Vinson]

Both Company and Plan are Text Fields.

Then you need quote delimiters on both in the DLookUp. See Chegu Tom's
response.