checking for a record in another table exists (1 Viewer)

[swarv]

Hi all,

I am after the code along the lines of....

If mainpage.item1.text = the same as any record in table [main query] in fields item1 or item2 or item3 or item4.
Then - msgbox

Is this possible with a line of VBA?

Thanks

Martin

 

[Galaxiom]

Having to seach for a single term in multiple fields suggests your data could be denormalized. Would it make sense to combine the Item fields into a single field in a related table with another field to designate the attribute that originally separated them into different fields?

If appropriate this structure is easier to query. It could be done very simply in one line with a DLookUp.

Otherwise you could generate a recordset with a query and count the records.

Derive a field that concatenates the four item fields with spaces (or some character that will not be found in the values) before and between them. Derive another that uses InStr function to test that field for the search term surrounded by spaces (or special character).

The space or special character is used to prevent the string being found as a substring in longer values.

 

[SpentGeezer]

yOU COULD USE THE dlOOKUP fuNCTION. i AM A NOOB, So I use a loop:

Dim strTheText
Dim RdSet
strTheText = item1.value '(item1 is a textbox or combobox??)

if len(strTheText) > 0 then
    set RdSet = currentdb.openrecordset("main query")
    rdset.movefirst
    do until rdset.EOF
        if rdset!item1 = strTheText then
            msgbox "What ever you want to say"
            exit sub
        elseif rdset!item2 = strTheText then
            msgbox "What ever you want to say"
            exit sub
        elseif rdset!item3 = strTheText then
            msgbox "What ever you want to say"
            exit sub
        elseif rdset!item4 = strTheText then
            msgbox "What ever you want to say"
            exit sub
        end if    
        rdset.movenext
    loop
else
    msgbox "Hey enter an Item1 Please!!"
    exit sub
end if

 

[swarv]

Hi spent,

thats works fine. Thanks for this. Great help. Now got to sort out Macro issue.

Thanks again