DLookup - check if an ID matches the textbox it works, however if form is null it doesnt work.

[princess12]

Hi basically am creating a booking system, i have a add a room form. my form should check whether i already have a room number in my table, which works when the form is filled in. however when my form is null, then i press add new room button, i get this error rather than " please fill your form in"

Error: runtime error '3075' syntax error (missing operator) in query expression 'Room Number ='.


room number is a number field, integer but has primary key. i cant keep autonumber, as my requirement is to add new room number, but the roomnumber has to be unique.

here is the dlookup;
If DLookup("RoomNumber", "tblRooms", "RoomNumber = " & Forms!RoomPackages!txtRoomNumber) > 0 Then
MsgBox "This number already exists."
Else

p.s it unbound form
........
please help, thank you in advance

[princess12]

btw i also tried sql - same issue

Dim strSQL As String
Set rst = db.OpenRecordset("SELECT RoomNumber FROM tblRooms WHERE RoomNumber =" & Me.txtRoomNumber)
CurrentDb.Execute strSQL
If strSQL = Me.txtRoomNumber Then
MsgBox "This number already exists."

[ssanfu]

Check spelling of field names.
The DLookup looks correct. You are sure the room number has been entered into the table?

Add these lines to your code as a test:

Msgbox "Forms!RoomPackages!txtRoomNumber = " & Forms!RoomPackages!txtRoomNumber
Msgbox "Me.txtRoomNumber = " & Me.txtRoomNumber

Msgbox "DLookup using Forms = " & DLookup("RoomNumber", "tblRooms", "RoomNumber = " & Forms!RoomPackages!txtRoomNumber)
Msgbox "DLookup using Me. = " & DLookup("RoomNumber", "tblRooms", "RoomNumber = " & Me.txtRoomNumber)

Msgbox "DLookup fixed number= " & DLookup("RoomNumber", "tblRooms", "RoomNumber = " & 111)  'change 111 to a number that you know is in the table "tblRooms"

----------------------------------
From Post#2
Dim strSQL As String
Set rst = db.OpenRecordset("SELECT RoomNumber FROM tblRooms WHERE RoomNumber =" & Me.txtRoomNumber) ' This opens a recordset
CurrentDb.Execute strSQL 'EXECUTE is only used for Action queries. strSQL has been declared, but at this point, the value is ""
If strSQL = Me.txtRoomNumber Then ' you are comparing a Null (strSQL) to the roomnumber. strSQL still does not have a value assigned to it.
MsgBox "This number already exists."


------------------------------------
If you want, post your dB for analysis. Do a "Compact and Repair", then Zip it before attaching to a post.

[princess12]

thanks for your help but i fixed it already, thanks a lot though for your time