Dlookup to match two values in the same record

[Tim Miller]

The following code looks at two separate fields: Month & Branch, in the
same table.
If the data the user is trying enter into the Month field AND the Branch
field both already exist, then it gives the Message. If none, or only one
of the field match to the data that the user is trying to enter, but not
both, then it should allow it to enter into the database.

The problem with my code is that it's not looking to see if the matching
data is in the same record. Therefore, if the data the user is trying to
enter into Branch matches ANY RECORD, and likewise for Month, it will fail
with the Message, even if the two records are not the same. That's not what
I want. They need to both be in the same record.

I'm not sure how to correct this problem. Any help / direction would be
appreciated.

Thanks,
Tim



Private Sub cmdPostAgain_Click()

Dim strDate As String
Dim strBranch As String
Dim Message As Variant
Dim varExDate As Variant
Dim varExBranch As Variant

strDate = [cboYear] & " / " & [cboMonth]
strBranch = [Forms]![Varnet Numbers]![cboBranch]
varExDate = DLookup("[Month]", "Varnet Numbers", "[Month] = #" & strDate &
"#")
varExBranch = DLookup("[Branch]", "Varnet Numbers", "[Branch] = '" &
strBranch & "'")

If Not IsNull(varExDate) And Not IsNull(varExBranch) Then
Message = MsgBox("An entry for " & strBranch & " already exists for " &
strDate & ". Please check your date, or ask Tim for help in checking the
database for accuracy. This record has not been posted.", 0 + 16,
"Somethings screwed up")
Else
[Forms]![Varnet Numbers]![Month] = strDate
DoCmd.RunCommand acCmdSaveRecord
DoCmd.GoToRecord acForm, "Varnet Numbers", acNewRec
End If

End Sub

 

[Rick Brandt]

The following code looks at two separate fields: Month & Branch, in the
same table.
If the data the user is trying enter into the Month field AND the Branch
field both already exist, then it gives the Message. If none, or only one
of the field match to the data that the user is trying to enter, but not
both, then it should allow it to enter into the database.

The problem with my code is that it's not looking to see if the matching
data is in the same record. Therefore, if the data the user is trying to
enter into Branch matches ANY RECORD, and likewise for Month, it will fail
with the Message, even if the two records are not the same. That's not what
I want. They need to both be in the same record.

I'm not sure how to correct this problem. Any help / direction would be
appreciated.

You need a single DLookup using both criteria anded together, not two separate ones.

DLookup("[Month]", "Varnet Numbers", "[Month] = #" & strDate & "# AND [Branch] = '" &
strBranch & "'")