Question how to find if a value in one field is also in a field in a different table

[stevegee76]

Hi everyone, i have a database where users enter a code for a record into a form which populates a field in one table but I want to now only allow further information about that record to be entered if the code is within a field in another table.

I am setting up a database where users will enter information for a road on which they will conduct maintenance works into a form, which then populates this information into tables. This code is in alpha-numeric format, eg A12345.
I then want to setup a check that either allows the rest of the maintenance works information to be entered into the form if the code is included in a field in a seperate table or to have a message box come up that advises the user that the code is not in the field in the seperate table and no other information is able to be entered into the form.

I can do the checking part in excel with =ISNUMBER(MATCH(lookup_value, lookup_array, [match_type])) where lookup_value is the value in a particular cell, say A1, lookup_array is an array or list of group of cells to look for the value in, say B1:B50 and the match type is usually FALSE.
Ideally I am wanting to know how to do this in access so I do not have to switch between excel and access.

I have spent some time researching DLOOKUP, ISNUMERIC etc but these do not seem to do what i want.

Anyone have any ideas please?

 

[DJkarl]

Let's get something out of the way first...Access is not a spreadsheet tool, it is a database management system. Try not to think of tables as sheets in a workbook, each table should contain its own unique data and you should not be storing the same value accross multiple tables (look into normalization for databases).

That being said using a query or queries to join tables together on the field(s) is probably the preferred way to go, though DLOOKUP should work to check if a value exists in a specified table.

 

[stevegee76]

Thanks for your reply, I am aware of the difference between access and excel as you point out. It seems that I may not have been able to communicate exactly what it is I am trying to do. For us amateurs it seems that you need a degree just to ask a question about something like this!
At least I now know I am looking in the right area with DLOOKUP's. I will spend a bit more time on this.
Thanks again

 

[The_Doc_Man]

Try DCount of the value, though getting it into the right syntax can sometimes be tricky when you are building the selection string on the fly. DCount will susceptible to null issues, so you might want to read up on the NZ function as well as DCount.

If you are searching for a number, you might get away with something similar to this VBA snippet:

A = DCount("NZ([FieldB],0)","theTable","[FieldB]=" & CStr([FieldA]) )

You had better count the parentheses just to double-check me. If A comes back >0 then your value exists in the target table. If A comes back =0, it does not.

 

[stevegee76]

Thanks for your reply and help and sorry for not replying to you sooner.
I have had to shelve the project I was working on that would need this functionality for now. Timelines got brought forward and we have had to go with the clunky old manual way for now. Hopefully I will get to re-visit this in the future. Thanks for your help anyway.

Cheers Steve