DLookup question

[Larry Hodges]

Hi all,

Running A2002.

I'm trying to prevent the user from entering a duplicate value already in my
table. I'm assuming Dlookup is the best choice for this.

This is what I have:

table: tblShipSheetSummary
field: OrderNumber (Long Integer)
Form: frmShipUnits

So, on the form I have txtOrderNumber as my text box, and the above table as
the Record Source. I also have a print cmd button on my form, and figured I
would put the code there, prior to updating the Recordset with the form's
data. I've also tried it in the Event for the txt box, at both the Before
Update and Lost Focus events. No errors, but it doesn't work.

------------
If (Me.txtOrderNumber) = DLookup("[OrderNumber]",
"[tblShipSheetSummary]") Then
MsgBox "You have entered an Order Number already in use. Please enter
another or press Esc to cancel record.", vbOKOnly, "My Company Name"
DoCmd.CancelEvent
Me.txtOrderNumber.SetFocus
Exit Sub
End If

 

[Tom Lake]

I'm trying to prevent the user from entering a duplicate value already in

my table. I'm assuming Dlookup is the best choice for this.

This is what I have:

table: tblShipSheetSummary
field: OrderNumber (Long Integer)
Form: frmShipUnits

So, on the form I have txtOrderNumber as my text box, and the above table
as the Record Source. I also have a print cmd button on my form, and
figured I would put the code there, prior to updating the Recordset with
the form's data. I've also tried it in the Event for the txt box, at both
the Before Update and Lost Focus events. No errors, but it doesn't work.

The name of the table shouldn't be in square brackets. Try

DLookup("[OrderNumber]", "tblShipSheetSummary")

Tom Lake

 

[Douglas J. Steele]

I'm trying to prevent the user from entering a duplicate value already in
my table. I'm assuming Dlookup is the best choice for this.

This is what I have:

table: tblShipSheetSummary
field: OrderNumber (Long Integer)
Form: frmShipUnits

So, on the form I have txtOrderNumber as my text box, and the above table
as the Record Source. I also have a print cmd button on my form, and
figured I would put the code there, prior to updating the Recordset with
the form's data. I've also tried it in the Event for the txt box, at
both the Before Update and Lost Focus events. No errors, but it doesn't
work.

The name of the table shouldn't be in square brackets. Try

DLookup("[OrderNumber]", "tblShipSheetSummary")

Absolutely nothing wrong with having the name of the table in square
brackets. In actual fact, though, neither require square brackets in this
case: they're only required if there are spaces in the field or table name.

The problem is that the DLookup statement is going to return the OrderNumber
for one row in the table. Which row? You can't be certain.

Rather than

If (Me.txtOrderNumber) = DLookup("[OrderNumber]", _
"[tblShipSheetSummary]") Then
MsgBox "You have entered an Order Number already in use. " & _
"Please enter another or press Esc to cancel record.", _
vbOKOnly, "My Company Name"
DoCmd.CancelEvent
Me.txtOrderNumber.SetFocus
Exit Sub
End If

try

If DCount("*", "tblShipSheetSummary", _
"OrderNumber = " & Me.txtOrderNumber) > 0 Then
MsgBox "You have entered an Order Number already in use. " & _
"Please enter another or press Esc to cancel record.", _
vbOKOnly, "My Company Name"
DoCmd.CancelEvent
Me.txtOrderNumber.SetFocus
Exit Sub
End If

 

[Larry Hodges]

I'm trying to prevent the user from entering a duplicate value already
in my table. I'm assuming Dlookup is the best choice for this.

This is what I have:

table: tblShipSheetSummary
field: OrderNumber (Long Integer)
Form: frmShipUnits

So, on the form I have txtOrderNumber as my text box, and the above
table as the Record Source. I also have a print cmd button on my form,
and figured I would put the code there, prior to updating the Recordset
with the form's data. I've also tried it in the Event for the txt box,
at both the Before Update and Lost Focus events. No errors, but it
doesn't work.

The name of the table shouldn't be in square brackets. Try

DLookup("[OrderNumber]", "tblShipSheetSummary")

Absolutely nothing wrong with having the name of the table in square
brackets. In actual fact, though, neither require square brackets in this
case: they're only required if there are spaces in the field or table
name.

The problem is that the DLookup statement is going to return the
OrderNumber for one row in the table. Which row? You can't be certain.

Rather than

If (Me.txtOrderNumber) = DLookup("[OrderNumber]", _
"[tblShipSheetSummary]") Then
MsgBox "You have entered an Order Number already in use. " & _
"Please enter another or press Esc to cancel record.", _
vbOKOnly, "My Company Name"
DoCmd.CancelEvent
Me.txtOrderNumber.SetFocus
Exit Sub
End If

try

If DCount("*", "tblShipSheetSummary", _
"OrderNumber = " & Me.txtOrderNumber) > 0 Then
MsgBox "You have entered an Order Number already in use. " & _
"Please enter another or press Esc to cancel record.", _
vbOKOnly, "My Company Name"
DoCmd.CancelEvent
Me.txtOrderNumber.SetFocus
Exit Sub
End If

Thanks Doug. Worked great! I really appreciate the help...have a great
day.

-Larry