Need help with Dlookup

[Sketchin]

I have a form and subform. Reservations, and Reservation_Details. Reservation details is where I add my items to be reserved. I want to pop up a message and undo an entry if it already exists in the subform. I am using this code:

'Custom error message if you are creating duplicates
Dim Answer As Variant
 Answer = DLookup("[BOMNumber]", "tblReservation_Details", "[BOMNumber] = '" & Me.cboBOMDescription.Column(0) & "' and [ReservationID] = " & [Forms].[frmreservations].[TxtReservationID] & "'")
 If Not IsNull(Answer) Then
 MsgBox "Duplicate Social Security Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
 
 'Cancel = True
 Me.cboBOMDescription.Undo
  
 Else
 End If

My problem is with the DLookup statement. I need it to select entries where [BOMNumber] = '" & Me.cboBOMDescription.Column(0) & "' and [ReservationID] = " & [Forms].[frmreservations].[TxtReservationID] & "'").

I know that this is written completely wrong and am hoping I can get some help with this statement.

THanks

 

[pr2-eugin]

Is frmReservation the Parent form if so you need to refer it correctly.. Also if the Combo box is bound to the first column there is no need of using the Column property.. Another suggestion is why not use DCount rather than DLookUp? DCount would count instead of looking for the match. and if the count is 0 then you do not have to worry about duplicate.. based on that the code becomes..

'Custom error message if you are creating duplicates
If DCount("*", "tblReservation_Details", "[BOMNumber] = '" & Me.cboBOMDescription & "' AND [ReservationID] = " & Me.Parent![TxtReservationID]) <> 0 Then
    MsgBox "Duplicate Social Security Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
    Me.cboBOMDescription.Undo
    Cancel = True
Else
End If

 

[boblarson]

The other thing that using DCount does, if you can use it, is that it doesn't throw an "invalid use of null" error if no records that match exist. It just returns a 0. If you have to use the DLookup, you need to also encapsulate that in the NZ function or else you will suffer that error if no record is found.

 

[Sketchin]

Thanks pr2! It seems like its almost working. I am getting the message box everytime I try to add an item though.

Is there a way to debug the dcount in the immediate window?

 

[boblarson]

Thanks pr2! It seems like its almost working. I am getting the message box everytime I try to add an item though.

Is there a way to debug the dcount in the immediate window?

Put a breakpoint in just after the DCount is assigned and then you should be able to see what is being returned by hovering over the variable you assigned it to.

Or you can just put

Debug.Print DCount(.....put the function here)

 

[Sketchin]

Figured out the debugging and discovered that dcount is returning a 0 no matter what is selected in the combo. Will have to do some digging...

By the way, in my tblReservation_Details I have ReservationID and BOMNumber set as primary keys so i avoid allowing duplicates. Only problem is if I choose duplicates on the form and close it, I get that "Duplicate values in the primary key, close form and dont save it" message, which is what I'm trying to avoid.

 

[Sketchin]

I actually had to do this to get it to return the result:

Dim test As Integer
test = DCount("*", "tblReservation_Details", "[BOMNumber] = '" & Me.cboBOMDescription & "' AND [ReservationID] = & Me.Parent![TxtReservationID]") <> 0
Debug.Print test

 

[boblarson]

Figured out the debugging and discovered that dcount is returning a 0 no matter what is selected in the combo. .

That usually means your criteria isn't using the right values. For example, you refer to a combo box in your criteria. You need to determine what that is returning for a value. So put a

debug.print Me.cboBOMDescription

into the code to see if what is being returned is what you think should be returned. Most of the time it has to do with the data you want being in the wrong column.

 

[Sketchin]

Debugged cboBOMDescription and Me.Parent![Txtreservationid] and they are returning the correct numbers

 

[boblarson]

You got a quote in the wrong place:


test = DCount("*", "tblReservation_Details", "[BOMNumber] = '" & Me.cboBOMDescription & "' AND [ReservationID] = & Me.Parent![TxtReservationID]") <> 0


Should be


test = DCount("*", "tblReservation_Details", "[BOMNumber] = '" & Me.cboBOMDescription & "' AND [ReservationID] = " & Me.Parent![TxtReservationID]) <> 0

And is TxtReservationID numeric or text? If text, it needs single quotes like you did with BOMNumber (which I would assume, by the name, should be a number and then wouldn't need quotes).

 

[Sketchin]

They are both Numeric. Which quotes can I eliminate?

 

[pr2-eugin]

Change it as..

DCount("*", "tblReservation_Details", "[BOMNumber] = " & Me.cboBOMDescription & " AND [ReservationID] = " & Me.Parent![TxtReservationID]) <> 0

 

[Sketchin]

That did it, thanks alot guys!