Verifying Data exists in DB

[vandy05]

I need some help.
table 1 - site name and info
table 2 - site personnel info (each site can have more than one personnel so it is tied to table 1 with an ID)
table 3 - phone contact details (in the form, everything fills out automatically for the site info but i need to be able to enter the person contacted manually)

I want to be able to have this person contacted field verify that the name exists in the database in table 2 under name otherwise it needs to prompt the user to enter the data.

How do i do this?

 

[RuralGuy]

Just do a DCount or DLookup in the BeforeUpdate event of the control where the data is entered or use a ComboBox and take advantage of the NotInList event.

 

[vandy05]

I am not sure how those two functions works.
i am not sure what they mean by doman and criteria in the definition of the functions.
Could you by chance explain really quickly?

 

[RuralGuy]

Maybe this link will get you off the start line.

 

[vandy05]

I am missing something.
Please let me know if I am completely off track - I am fairly new to Access and am trying to teach myself.

I'm comparing Person Contacted (Table 3) to Name (Table 2)

Dlookup("[Person Contacted]", "Table 2, "[Person Contacted]=[Name]")

Either way, what error message would appear and how could I change it if I want to?

 

[RuralGuy]

Have you looked up DLookup in VBA help? I believe it returns either a Null or a ZLS ("") if not found, otherwise it returns the field you requested. You validate in the BeforeUpdate event of the control and issue your own message if a duplicate and set Cancel = True to stop the update and hold the focus in that control.

 

[boblarson]

DLookup will return a NULL if nothing found and it will generate an error - "You canceled the previous operation." So, a DCount actually is better as it will handle nulls by counting it as a 0 when it finds nothing matching the criteria.

 

[RuralGuy]

DLookup will return a NULL if nothing found and it will generate an error - "You canceled the previous operation." So, a DCount actually is better as it will handle nulls by counting it as a 0 when it finds nothing matching the criteria.

Correct me if I'm wrong Bob, but you only get an error if you blow the syntax for the DLookup() right?

 

[boblarson]

Correct me if I'm wrong Bob, but you only get an error if you blow the syntax for the DLookup() right?

Not sure what you mean. You will get an error if you mess up the syntax on either but a DLookup will generate an error also if it can't return anything.

 

[RuralGuy]

Only if either the "FieldName" in non existant or the "Domain" in non existant. Otherwise you get a Null as you stated. I believe you will get an error from DCount() under the same conditions.

 

[boblarson]

Only if either the "FieldName" in non existant or the "Domain" in non existant. Otherwise you get a Null as you stated. I believe you will get an error from DCount() under the same conditions.

You will get a null, but if you try to do anything with it, it will generate the "You canceled the previous operation" error. You would have to use an NZ function to avoid that (or just use DCount, which is what I've gone to using).

 

[RuralGuy]

Wouldn't something like:
If Not IsNull(DLookup...) Then
...work?

 

[boblarson]

Wouldn't something like:
If Not IsNull(DLookup...) Then
...work?

I don't think you can use the IsNull(DLookup...) part. I could be wrong but I think you would need

NZ(Dlookup...),"Whatever)

 

[RuralGuy]

I'm pretty sure I've used it that way in the past. My memory could be defective in this area however considering my age.

 

[boblarson]

I'm pretty sure I've used it that way in the past. My memory could be defective in this area however considering my age.

I agree, it may be possible, but I seem to remember having a problem with the "You canceled the previous operation" error when trying to do that without using the NZ function. In any case, I found it to be too much trouble when the DCount seems to work more reliably without coercing things.

 

[RuralGuy]

Sorry for hyjacking the thread. Theoretically, if the lookup field is indexed than I would believe the DLookup would run faster. Just my $0.02. I understand the DCount argument.

 

[vandy05]

Wow I am really confused. I have no clue how to make this work!

 

[RuralGuy]

Does anything I said in post #2 make any sense?

 

[vandy05]

sadly no. everything is basically way over my head.

 

[RuralGuy]

So in the phone contact details form, have the ComboBox wizard create a control that will "Look up a value" for you in the site personnel table. See if you can get that to function correctly and then we will deal with when it is not in that table yet.