Lookup see if ID exists in another table Yes/No

[jam]

Hi All,

Haven't been on in quite a while whilst I've been working for an MSP and concentrating on their route to market.

I'm trying to setup a DB to track all the customers we have and what they take, so far so good. However we have a policy whereby the customers who have our ISP services get a free domain name. Obviously we still need to renew these domains as per normal.

So I have a form whereby I'm entering a new domain into the DB and I want it to check the internet table to see if the customer exists. If they do not I want the field to show no, if they do I want it to show yes.

My efforts so far have hit nothing but problems. I've tried IF statements but that didn't work.
IF[CustomerID]=([InternetTBL]![CustomerID], Yes,No)
But it just errors out.

I'm not big into VBA so I'm kinda stuck. Any thoughts would be appreciated.

 

[pr2-eugin]

You should use DCount to get this information. If you are using a Bound control, I would say you use this in a Button Click event or some other event.

Private Sub checkCustomerButton_Click()
    Me.yourControlName = IIf(DCount("*", "yourTableName", "[CustomerID] = " & Me.CustomerID) = 0, "No", "Yes")
End Sub

 

[Old Man Devin]

An if statement should work in principle. What you posted: IF[CustomerID]=([InternetTBL]![CustomerID], Yes,No) won't work as it is not set up correctly. Try:

IF([CustomerID]=[InternetTBL]![CustomerID], Yes,No)

I just moved the bracket from after the = to before the if, which is where it should be. The brackets need to surround all the arguments of the IF for it to know what to do. See if the error still occurs when you write it like this.

 

[jam]

Same error as before

Microsoft Access cannot find the object 'IF([CustomerID]=[InternetTBL]![CustomerID], Yes,No).'

IfIF([CustomerID]=[InternetTBL]![CustomerID], Yes,No) is a new macro or macro group, make sure you have saved it and that you have typed its name correctly.

I have checked the InternetTbl and it has the correct CustomerID field. The beginning CustomerID is a box on my form which displays the customer ID for this current domain.

An if statement should work in principle. What you posted: IF[CustomerID]=([InternetTBL]![CustomerID], Yes,No) won't work as it is not set up correctly. Try:

IF([CustomerID]=[InternetTBL]![CustomerID], Yes,No)

I just moved the bracket from after the = to before the if, which is where it should be. The brackets need to surround all the arguments of the IF for it to know what to do. See if the error still occurs when you write it like this.

 

[Old Man Devin]

Ah right, in this case I think it is just being applied incorrectly. Have you literally just written that IF statement into a box on the form? If you haven't already got something scanning the internet table for IDs, I'm afraid you'll need to actually write a program to do it and set it to run whenever the client field is updated.

pr2-eugin's post shows one way of doing it. Replace "yourtablename" with the name of the table containing the internet data. Do you know how to make something on a form call code routines?

 

[jam]

Thanks all,

pr2-eugin this returns every record as yes. Even for dormant accounts who have no internet.

I'm using the on focus event to drive it.

 Internet = IIf(DCount("*", "CustomerTbl", "[CustomerID] = " & CustomerID) = 0, "No", "Yes")

Thanks for your help so far.

You should use DCount to get this information. If you are using a Bound control, I would say you use this in a Button Click event or some other event.

Private Sub checkCustomerButton_Click()
    Me.yourControlName = IIf(DCount("*", "yourTableName", "[CustomerID] = " & Me.CustomerID) = 0, "No", "Yes")
End Sub

 

[pr2-eugin]

Okay explain in plain English, how is your data entered into the table, where/when do you want this check, what are the fields involved in this process as in, which field name should you check for getting the count?

On Focus is not the effective method to achieve this.

 

[jam]

Data is entered into a form.

The user selects the customer from a drop down box. (2 fields in this box Customer ID & Customer name. CustomerID is hidden by virtue of 0cm size. Bound field is Customer ID from customers table).

The rest of the fields come from InternetTbl. As the rest of the fields are entered and you tab down you eventually hit a tick box which is where the Got Focus drives the query I'm trying to put in. This part works as when I get focus it runs the code. However it either fails with IIF commands or the code you gave me defaults everything to yes.

At the very end of this I want it to have a save button which starts a new record.

Thanks
jam