Copying a record

[Henley12]

I am trying to copy a record on a form, but when I do I need to put in a new equipment id number. I have some code to check for duplication, but it does not appear to be working quite correctly. Can anyone see anything obvious? I know this should be an easy solution. Thanks.

newID = InputBox("Enter a new Equipment ID Number", "New Equip ID")
flgGo = 0
Do While flgGo = 0
varID = ""
varID = DLookup("[EquipID]", "[tblEquipment]", "[EquipID]='" & newID & "'")
If varID = newID Then
Dialog.Box ("Equipment ID already exists. Please enter a new Equipment ID."), vbOKOnly, "Duplicate Number", , , 0
newID = InputBox("Enter a new Equipment ID Number", "New Equip ID")
Else
EquipID = newID
flgGo = 1
End If
Loop

 

[Scooterbug]

You are putting quotes around the newID. If newID is a datatype Number, then you dont need the quotes in the Dlookup line.

 

[namliam]

The obvious thing I can see is this:
DLookup("[EquipID]", "[tblEquipment]", "[EquipID]='" & newID & "'")

ID usually is a number field... you are using '' around the NewID making a text > Number conversion needed which may cause problems....
If yoru EquipID is a number field try:
DLookup("[EquipID]", "[tblEquipment]", "[EquipID]=" & newID & "")

Edit: Xpost with Scooter

 

[Henley12]

EquipID is not a numeric field. I'm not having a problem with the dlookup, except when it is supposed to loop and check again. When it loops back, it gives me an invalid use of null error. I should have been more specific in my explanation. What I am wanting to know is if this is the proper way to loop through and check the field again.

 

[Scooterbug]

You will get a null value for a dlookup if no record is found. So, when the lookup doesn't find a record, a null value comes back. Try wrapping the dlookup in the Nz() function.

 

[Henley12]

By jove, I think you've got it!! Thanks very much for all the help.