Using Forms and Querys to search for records in a sub-form

[Daff]

I'm having trouble trying to do what I want here: I want to be able to select products to put in a sub-order-form, by using keyword searches.

Eg: In the sub-order-form, I want a text box, where keywords can be entered - eg "multivit 30". Using Split(), I then need to pass this across to another form which will display the results of the search, and enabling a product to be selected using a button, which will transfer the information and close the displayed form. Ie, pick this product...

I'm getting myself a little confused with whether I need to pass the information to a query, or a form based on a query? Input would be appreciated - I'm not used to Vb, SQL, and Access.

 

[Aivars]

Hi!

Try to solve it like following:

private sub txtSearch_AfterUpdate()
dim frm as form
dim rst as recordset

set frm=me.subMySubform.form
set rst=frm.recordsetclone
with rst
.findfirst "MySearchField like '" & _
txtSearch & "*'"
if not .nomatch then
frm!chkCheckBox=true
else
goto Exit_txtSearch_AfterUpdate
endif
do
.findnext "MySearchField like '" & _
txtSearch & "*'"
if not .nomatch then
frm!chkCheckBox=true
else
goto Exit_txtSearch_AfterUpdate
endif
loop
end with

Exit_txtSearch_AfterUpdate:
rst.close
set rst=Nothing
end sub

Or you can select all needed records of subform and set recordsource of subform for each criteria (I think last case is better for form design)

private sub txtSearch_AfterUpdate()
dim strSQL as string
dim frm as form

set frm=me.subMySubform.form
strSQL="Select * From MyTable Where MySearchField like '" & txtSearch & "*';"
frm.RecordSource=strSQL
end sub

Aivars