Data validation on entry using table of valid entries

[Fishman]

Hi all,

I have a problem I hope I can get some help on. I want to be able to validate data when entered using a list of valid entries stored in a table, but can't seem to work out how.

The main tables in my (simplified) example database are:

Tissue Samples
Sample ID/River (and lots of other fields in the read db)
eg data
A01/Dee
A02/Don
A03/
A04/Esk
A05/Don

River List
ID/River
eg data
1/Dee
2/Don
3/Esk
4/Clyde

When I enter records into my Tissue Samples table I want to run a check on the river name using a list of valid entries from the River List table. It seems such a simple thing to want to do, but I just can't work out how.

Data is entered into the table using a form which is linked to a third table:

Tissue Samples new data
Sample ID/River

Users open this new data form/table and paste the data in (often hundreds of records). Then run a macro that will:
1) Append new records to the Tissue Samples table if the ID on these records is not already in this table (using an append query)
2) Update records which have their ID already in the Tissue Samples table with new data in their various fields (overwriting what is already in there if necessary, or just filling in missing data in empty fields) (using an update query)
3) Clear the new data table for further use later (using a delete query)

What I want to do is that when I run the append/update/clear macro the river names are checked and if the river name is not in the Rivers List a message appears telling the user that there are records that do not match this validation. I would then like the rest of the correct data to be added to the Tissue Samples table, and the problem records stored in a 'Paste Errors' table so the user can go through and make corrections.

Although I am a bit of a noob, I have been able to get simple validation rules working, but can't seem to work out how to validate against fields in a separate table. I thought it would be a common requirement, but lots of searching has led me no-where (although it did lead me to this very useful forum from which I have already learnt a lot!).

I have attached the example db, and I hope I have given enough info, and that some kind soul can help me.
Thanks in advance

 

[gemma-the-husky]

you would have to write a function that

a) strips out the river name after the / character
b) checks whether that name existis in the river table
c) returns true or false


so in a query based on the new sample data, have another column

=riverexists(sampletext)

and in a module decalre a function

function riverexists(sample as string) as boolean
dim river as string
 'now in this function you need to put the details of this lookup
 'i havent got time to do the exact code but

river = "split out the bit after the slash"
 'make sure you HAVE got some text for river then
riverexists = "do a dlookup on your rivers table, based on the river just extracted"

end function

 

[Fishman]

Thanks for the help.

I'm sorry, but I think I may have not been clear in the OP.

There is no forwardslash in my data, I just used this as a way to show two fields.
The data is:

A01 Dee
A02 Don
A03
A04 Esk
A05 Don

with the A0x ID codes in one primary key field [Sample ID] and the river names in the other field [River].

And it is this 'lookup' that I do not know how to do. I have river in my 'new data' table and need to lookup when updating/appending the new data in the 'Rivers List' table if this value is in there. ie I need to validate the new entries but can find no way to do it.

I will research more the lookup function, thanks again.

 

[fkegley]

Couldn't you just develop a combo box for the form that contained the names of the rivers? You could also put an Add New button to allow them to add a new river if necessary.

 

[Fishman]

The reason I have not gone with a combo box is that I want to enter hundreds of records at a time by pasting into the 'new data' form in datasheet view. As I said am an a novice with access, but as I understand it the combo box would have to be selected for each record entered on the input form.

And new rivers can only be entered by me. I want to check spellings and also add a lat long for the river mouth in the rivers list table.

 

[gemma-the-husky]

use my original idea with this function

function riverexists(RIVERNAME) as boolean
dim riverid as long

if nz(rivername,vbnullstring)=vbnullstring then
 riverexists=false
 exit function
end if

'replace these bits with correct names in your river table
riverid = nz(dlookup("riverid","tblrivers","rivername = " & chr(34) & rivername & chr(34)),0)

riverexists = riverid>0

end function

but if you have a river table, and a sampledata table with rivername in a single column, you can probably also just use a find unmatched query, with the query wizard, which will be much quicker

 

[Fishman]

Thank you very much gemma-the-husky!

That find unmatched query did just what I needed. I just wanted to be able to spot these unmatched records and now I can. Never used one of those before, as I said, bit of a novice (the name of the query type should have given me a hint I think )

I would like to try your function though.
In a new query based on the new data table i have two columns. One is [NASCO River] which is the name of the river. This is from the Tissue samples new Table and has show ticked.
I have a second column, in which I pasted the code you suggested in the 'Field:'. Nothing is filled in for Table and the Show box is ticked. The code now reads

Expr1: riverexists([NASCO River])

I then tried to add the definition of the function to a module (Insert/Module) (never used modules before either!). The total code in this module is now:

Option Compare Database

Function riverexists(RIVERNAME) As Boolean
Dim riverid As Long

If Nz(RIVERNAME, vbNullString) = vbNullString Then
riverexists = False
Exit Function
End If

'replace these bits with correct names in your river table
riverid = Nz(DLookup("ID", "NASCO River List", "[NASCO River] = " & Chr(34) & RIVERNAME & Chr(34)), 0)

riverexists = riverid > 0

End Function

The module is saved as a General module with the name 'riverexists'.

The problem is that when I run the query it tells me [Undefined function 'riverexists' in expression]. How do I define this function/module such that the query knows how to access it? Reading the help files, I thought code in a general module is able to be accessed from anywhere?

 

[gemma-the-husky]

it might be that the module name is the same as the function, and that is causing a problem

change the modulename to something else (eg mymodule)

The module is definitely showing in your modules tab isnt it?


it shouldn't really make any difference, but just in case, try changing

Function riverexists(RIVERNAME) As Boolean to

public Function riverexists(RIVERNAME) As Boolean

--------

as an aside

note that normally you would declare the rivername argument to be a string

public Function riverexists(RIVERNAME as string) As Boolean

but if the rivername is blank, this will cause an error. declaring it just as rivername declares it as a Variant, which CAN be a blank (technically a NULL). In general I feel it is better to type variables properly, rather than rely on variants.

 

[Fishman]

That did the trick, renamed and changed function as you suggested. now I run the query and get results that have a 0 in the Expr1 column against records with no match to the rivers list, and a -1 against records with which do match.

So I now have two ways to identify my non-matching records. I can see that all I need to do now is move these records to a new table before updating/appending the rest to my main table.

I think I will do it by
1) use a Build Table query to build the 'Paste Errors' table with the non-matching records.
2) use a Delete query to delete these from my new data table
3) update main table with what is left in the new data table
4) if there are records in the paste errors table open this for the user to go through.

So again, thanks very much for your help. You have solved my problem, and I have learned a lot.