Filtering Subform by Textbox from Parentform

[Kevin]

Hi,

This is supposed to be easy (from what I've read) but I guess I'm just
not experienced enough with Access 2000.

*******************************

Scenario:

I have two forms: frmParent and frmChild

frmParent's source is from tblParent and has a field called Ticket
(made up of automatically incrementing long integers). The textbox
displaying the ticket number is called txtTicket

frmChild's source is from tblChild and has a field called TicketChild.
This field is a direct 1on1 relationship with tblParent's Ticket
field. The textbox displaying this field on the subform is called
txtTicketChild.

What I'm trying to do:

When frmParent is opened (with frmChild as a subform embedded within
it), I want the subform to just show records that match the contents
of txtTicket and filter the rest out.

******************************

When I open frmParent, what I end up getting is all the records from
the tblChild displaying inside frmChild rather than just the filtered
records.

Can someone please help me out? If coding is required (I didn't think
I needed to code anything), a simple sample code would be greatly
appreciated.

Thanks in advance for your time.

-Kevin

 

[Guest]

Kevin,

If you are using tblChild as the recordsource for frmChild, then you need to
change it. Write a query that is based on the fields you need for the form,
for the TicketChild field, you need to put put an entry in the criteria row.
The first thought is to reference the text box with the key in it, but that
is not enough because the query will not know the value of the parameter.

Try putting something like this in AfterUpdate event of txtTicket (might not
be correct syntax, but the idea will work)

dim qdf as Querydef
dim rst as recordset

Set qdf = CurrentDb.QueryDefs("qselChildTicket")
qdf.Parameters(0) = Me.txtTicket

Set rst = qdf.OpenRecordset(dbOpenDynaSet)

Now your record source will have only tickets in the subform that match the
tickets in the parent form.

 

[Kevin]

Kevin,

If you are using tblChild as the recordsource for frmChild, then you need to
change it. Write a query that is based on the fields you need for the form,
for the TicketChild field, you need to put put an entry in the criteria row.
The first thought is to reference the text box with the key in it, but that
is not enough because the query will not know the value of the parameter.

Try putting something like this in AfterUpdate event of txtTicket (might not
be correct syntax, but the idea will work)

dim qdf as Querydef
dim rst as recordset

Set qdf = CurrentDb.QueryDefs("qselChildTicket")
qdf.Parameters(0) = Me.txtTicket

Set rst = qdf.OpenRecordset(dbOpenDynaSet)

Now your record source will have only tickets in the subform that match the
tickets in the parent form.

Klatuu,

Aye it did what I needed...thanks for your time and idea!

-Kevin