Checking a feild of a text box in a form against multiple tables

[input1000]

[frmPunchlist] has a text box [tagitem]

[tagitem] must = one of the tags over a multiple of tables eitherwise when I run reports later they will not show valid results.

to use a drop down list of all tags, over the 9 tables would be to large.

what I was would like to do make a function afterupdate to check the feild created against each table.

If it provides a true the box would be green, or another box beside it would have check, if it provides a false the box would turn red or have an x beside it.

[tblLineList], [tblIso], [tblCable], [tblInstrument] are examples of the tbl's I would be using, and the row of data would be called [tag]

Any Ideas of how I could write this up? I'm pretty new at VB and access, but if someone has some code that I could start with I should be able to mould it to what I need.

thankyou!

Jason

 

[MajP]

First build a union query of all 9 tables to return all of the tag IDs. Then use a dlookup function to see if the tag exists. Assume your query is "qryAllTags". Then do something like

public function tagExists(tag as variant) as boolean
if not isnull(dlookup("tagItem","qryAllTags","tagItem = '" & tag & "'" then
tagExists = true
end if
end function

 

[MajP]

look like I forgot some closing parentheses
(dlookup("tagItem","qryAllTags","tagItem = '" & tag & "'" ))

 

[input1000]

Thankyou!

 

[input1000]

Finally had time to test this function. but It's not working.
[Cable No] is the feild in query [UnionTagLists]
[txtTagLine] is the feild person types into
[TagCheck] is a checkbox to show true or false

[Txttagline] mustequal [Cable No]

Private Sub txtTagLine_AfterUpdate()

If Not IsNull(DLookup("[Cable No]", "UnionTagLists", "[txtTagLine] = '" & Tag & "'")) Then
TagCheck = True
Else
TagCheck = False


End If


End Sub

 

[formerTexan]

The 2nd of the 3 arguments in DLookup is the table or query to be searched. The third argument is search criteria (like the WHERE clause in a query). You need to indicate which field to search and what criteria to search for. This field must be included the table or query you provided in the 2nd argument.

In your example you have provided a control name instead of field name.

Cheers, Bill

 

[input1000]

Thankyou. Works now!