Solved: Value exists?

[ailyn]

How do you check if a value exists in a table on a form.
I want to check if customerId exists in order to show his info or not.

[geekgirlau]

There's lot's of different methods: perform a Find, filter the form and check if the CustomerID field is null, create a recordset, or do a dlookup


If IsNull("[CustomerID]", "tblCustomer","[CustomerID]=" & MyVariable) then
' No customer record
Else
' Customer exists
End If

[chocobochick]

Geekgirlau forgot to include the DLookup function. I think the corrected version looks like this:


If IsNull(DLookup("[CustomerID]", "tblCustomer","[CustomerID]=" & MyVariable)) Then
' No customer record
Else
' Customer exists
End If

[Norie]

What about the DCount function?

[geekgirlau]

Absolutely right Chocobochick (taking code off the top of my head is always a hit-and-miss affair!).

And yes Norie, Dcount would also work.

[geekgirlau]

Ailyn, did this help?

[ailyn]

Sorry for the delay!

I needed it for this:
Private Sub CustomerIdctrl_BeforeUpdate()
If IsNull(DLookup("[CustomerID]", "tblOrders", "[CustomerID]=" & Me.CustomerId)) Then
Me.CustomerIdctrl.Visible = False
Me.EditCustomers.Visible = False ' No customer record
Else
Me.CustomerIdctrl.Visible = True
Me.EditCustomers.Visible = True ' Customer exists
End If
End Sub

In frmOrders (with tblOrders) I have two fields: fldEditCustomers(link to Customers form) & CustomerIdCtrl (shows [customerId] from tblOrders). I want them to show only if [CustomerId] from tblOrders exists. But it simply hides both fields whether it exists or not. Can anybody tell me why?

This other one doesn't work either:

Private Sub CustomerIdctrl_BeforeUpdate()
If IsNull(DLookup("[CustomerID]", "tblOrders")) Then
Me.CustomerIdctrl.Visible = False
Me.EditCustomers.Visible = False ' No customer record
Else
Me.CustomerIdctrl.Visible = True
Me.EditCustomers.Visible = True ' Customer exists
End If
End Sub

[geekgirlau]

Hi Ailyn,

I think the first thing to check is whether the event is being triggered - either of your samples should work, but it's difficult without seeing the form to tell whether they should be attached to a different event.

A quick way to test this is to put a MsgBox command in at the start of your code - that way you know that the code has actually been run.

PS. I've edited your post to include VBA tags. You can insert these by selecting your code, and clicking on the "VBA" button.

[chocobochick]

If I understand correctly, the form frmOrders uses tblOrders as its recordset, CustomerIdctrl is the name of the control which points to the CustomerID field from this recordset, and you're attempting to hide two controls based on the existence of a value everytime you change to a new record. If that's the case, then:

1. You probably don't even need the DLookup function, because your control is already pointing to the value in the current record. Just use:

If IsNull(CustomerIdctrl) Then

2. If you were going to use the DLookup function, the right side of the expression in the last argument needs to point to your form's control, not to a table's field name. So the code probably needed to read:

If IsNull(DLookup("[CustomerID]", "tblOrders", "[CustomerID]=" & CustomerIdctrl)) Then

3. I believe the BeforeUpdate event only occurs when changes are being saved to your records. If you want to check each time new records are displayed, you're looking for the form's Current event. Try the following instead:


Private Sub Form_Current()
If IsNull(CustomerIdctrl) Then
CustomerIdctrl.Visible = False
EditCustomers.Visible = False ' No customer record
Else
CustomerIdctrl.Visible = True
EditCustomers.Visible = True ' Customer exists
End If
End Sub

I hope that helps. If it doesn't, or if you have any new questions, or if I've misunderstood your situation, then supplying a small attachment would be of great help.

[ailyn]

O.o

I can't believe it, it was the last option. Because I only have one record in orders and I was checking if it worked on display not on changes! that was the problem!

THANKS A LOOOT!