It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.
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.
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.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Have you tried wrapping DCount() in Nz(), that way if it returns NULL then you still get a number, 0.
»bp
If Nz(DCount("connection", "MasterCellDevices", "[connection]='" & Me.txt_InvConnectionPhone & "'"),0) > 0 Then
»bp
Sorry, I should have posted more code.
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.
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
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.
This is the highlighted line:
Set rsMasterCellList = ("SELECT MasterCellDevices.[KioskID
Here is the entire code from button press:
Set rsMasterCellList = ("SELECT MasterCellDevices.[KioskID
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
Should that line be:
»bp
Set rsMasterCellList = db.OpenRecordset("SELECT MasterCellDevices.[KioskID] FROM MasterCellDevices WHERE MasterCellDevices.[connection] = " & Me.txt_InvConnectionPhone & "")
»bp
Premium Content
You need an Expert Office subscription to comment.Start Free Trial