How to search 2 unrelated tables

[GA]

Thanks in advance for any help.

I have 2 tables containing vehicle details.

The first is for departmental staff and it is related to our main
staff tables via the staffID.

The second stands alone with all the available vehicle details for
staff from other departments.

We regularly have to find an owner e.g. when lights have been left on
etc. and at the moment I have a form with two sub-forms each of which
has to be searched independantly. Ideally I'd like an input on the
main form with perhaps a command button to either search or filter
each of the subforms based on the content of the input.

I've looked at this until my brain hurts and can't get any further.
Any pointers please?

GA

 

[Scott Lichtenberg]

You're on the right track. Put a field on your main form, then use the
AfterUpdate event or a command button to search your subforms.

The trick to searching the subform is to open a recordsetclone of the
subform. Try something like this.

Dim rs as recordset
Set rs = Me.SubformName.Form.RecordsetClone
rs.FindFirst "LicensePlate = " & Me!LicensePlate 'Or some other
criteria
If Not rs.NoMatch Then
'We have a hit, do something
Else
'Repeat the process searching the second form
End If

Note the .Form property when opening the recordset. This is what tells
Access to "go into" your subform, rather than just looking at the subform
control on your main form.

Hope this helps.

 

[John W. Vinson]

Thanks in advance for any help.

I have 2 tables containing vehicle details.

The first is for departmental staff and it is related to our main
staff tables via the staffID.

The second stands alone with all the available vehicle details for
staff from other departments.

We regularly have to find an owner e.g. when lights have been left on
etc. and at the moment I have a form with two sub-forms each of which
has to be searched independantly. Ideally I'd like an input on the
main form with perhaps a command button to either search or filter
each of the subforms based on the content of the input.

I've looked at this until my brain hurts and can't get any further.
Any pointers please?

GA

If the two tables have the same structure (or a set of fields in common) you
can use a UNION query. See UNION in the Help, it's pretty clear - post back if
you have trouble.

 

[GA]

If the two tables have the same structure (or a set of fields in common) you
can use a UNION query. See UNION in the Help, it's pretty clear - post back if
you have trouble.

Many thanks for the replies. I went with the Union Query as it gave me
the option of having a simple single form.

It was a bit of a milestone for me - the very first SQL query that
I've 'hand written' as it were albeit with the helpfile open at the
side of my screen 8^) - worked a treat anyway.

I really appreciate the kind help provided by the regular contributors
- thanks again.

GA

 

[Koye]

MO KAN TESTI KINNI SHA BOYA O MA LA, NETIWOKI YI O DA PAA.

 

[Koye]

TESTING