Lookup a field to see whether or not it is null or has a value

Hi all.  

I currently have the following line of code to see if number entered is a valid one in my MasterCellDevices table:

            If DCount("connection", "MasterCellDevices", "[connection]='" & Me.txt_InvConnectionPhone & "'") > 0 Then

If it is a valid number I want to check to see if there is anything in the MasterCellDevices.ID field.  If there is, I want a msgbox to pop up saying that it is already assigned to a different location.  If it is null or empty I want it to update the field with the current ID.

I am struggling on how to figure out whether or not that field is empty or not.

Sorry if I didn't explain it enough, but I am new to coding and looking for help.

Thank you for your time and help.
Ryan BassAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
Have you tried wrapping DCount() in Nz(), that way if it returns NULL then you still get a number, 0.

If Nz(DCount("connection", "MasterCellDevices", "[connection]='" & Me.txt_InvConnectionPhone & "'"),0) > 0 Then

Open in new window

»bp
Ryan BassAuthor Commented:
Sorry, I should have posted more code.

            If DCount("connection", "MasterCellDevices", "[connection]='" & Me.txt_InvConnectionPhone & "'") > 0 Then
                Set rsMasterCellList = ("SELECT MasterCellDevices.[KioskID] FROM MasterCellDevices WHERE MasterCellDevices.[connection] = " & Me.txt_InvConnectionPhone & "")
                If Not IsNull(rsMasterCellList) Then
                    DupConnection = MsgBox("This number is already assigned to a kiosk.")  'Display if cell number is in KioskCellInstall
                Else
                    rsKioskCellInstall.Edit
                    rsKioskCellInstall!WanConnection = "AT&T CP"
                    rsKioskCellInstall!phonenumber = Phone
                    rsKioskCellInstall.Update
                    Me.txt_ConnectionType = rsKioskCellInstall!WanConnection
                    Me.txt_ConnectionPhone = rsKioskCellInstall!phonenumber
                End If
            Else
                WanConnection = MsgBox("Phone number does not exist in Master Cell Devices. Please double check the number.")
                Me.cmd_UpdateCell.SetFocus
            End If

Open in new window


The rsMasterCellList is where I am trying to grab the KioskID field from the MasterCellDevices table.   When I run it, I get an error saying Object required.  Which I'm assuming means that rsMasterCellList isn't returning a value.

Thanks for your help.
Bill PrewIT / Software Engineering ConsultantCommented:
Which line is highlighted in the Debug session when you get the error?

»bp
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Learn More!
Bill PrewIT / Software Engineering ConsultantCommented:
And what is rsKioskCellInstall and where is it set?

»bp
Ryan BassAuthor Commented:
This is the highlighted line:
                Set rsMasterCellList = ("SELECT MasterCellDevices.[KioskID] FROM MasterCellDevices WHERE MasterCellDevices.[connection] = " & Me.txt_InvConnectionPhone & "")


Here is the entire code from button press:
Private Sub cmd_UpdateCell_Click()

Dim WAN As String
Dim Phone As String
Dim rsKioskCellInstall As DAO.Recordset
Dim db As Database
Dim rsMasterCellList As DAO.Recordset
Dim WanConnection As String
Dim shortphone As String
Dim DupConnection As String

Set db = CurrentDb()
Set rsKioskCellInstall = db.OpenRecordset("SELECT tbl_KioskCellInstall.* FROM tbl_KioskCellInstall WHERE tbl_KioskCellInstall.[KioskID] = " & Me.txt_IDNum & "")


WAN = InputBox("Please enter corresponding number:" & vbCrLf & vbTab & vbCrLf & vbTab & "1 - AT&T CP" & vbCrLf & vbTab & "2 - Default Password" & vbCrLf & vbTab & "3 - Stores Internet" & vbCrLf & vbTab & "4 - Unknown" & vbCrLf & vbTab & "5 - Verizon 760" & vbCrLf & vbTab & "6 - Verizon CP")

If WAN = "" Then
    Me.cmd_UpdateCell.SetFocus
ElseIf WAN = 1 Then
    Phone = InputBox("Please enter the 11 digit phone number:")
        Me.txt_InvConnectionPhone = Phone
        If Len(Me.txt_InvConnectionPhone) <> 11 Then
            shortphone = MsgBox("Please enter an 11 digit phone number.", vbOKOnly)
        ElseIf Len(Me.txt_InvConnectionPhone) = 11 Then
            If DCount("connection", "MasterCellDevices", "[connection]='" & Me.txt_InvConnectionPhone & "'") > 0 Then
                Set rsMasterCellList = ("SELECT MasterCellDevices.[KioskID] FROM MasterCellDevices WHERE MasterCellDevices.[connection] = " & Me.txt_InvConnectionPhone & "")
                If Not IsNull(rsMasterCellList) Then
                    DupConnection = MsgBox("This number is already assigned to a kiosk.")  'Display if cell number is in KioskCellInstall
                Else
                    rsKioskCellInstall.Edit
                    rsKioskCellInstall!WanConnection = "AT&T CP"
                    rsKioskCellInstall!phonenumber = Phone
                    rsKioskCellInstall.Update
                    Me.txt_ConnectionType = rsKioskCellInstall!WanConnection
                    Me.txt_ConnectionPhone = rsKioskCellInstall!phonenumber
                End If
            Else
                WanConnection = MsgBox("Phone number does not exist in Master Cell Devices. Please double check the number.")
                Me.cmd_UpdateCell.SetFocus
            End If
        
        End If
End If

End Sub

Open in new window

Bill PrewIT / Software Engineering ConsultantCommented:
Should that line be:

Set rsMasterCellList = db.OpenRecordset("SELECT MasterCellDevices.[KioskID] FROM MasterCellDevices WHERE MasterCellDevices.[connection] = " & Me.txt_InvConnectionPhone & "")

Open in new window

»bp

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ryan BassAuthor Commented:
Yes it should be...  

I changed it but now I get the following error:

Data type mismatch in criteria expression
Ryan BassAuthor Commented:
It is a text field in the table.
Bill PrewIT / Software Engineering ConsultantCommented:
Glad that helped.

»bp
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.