One to Many Issue

[JonnyRotten]

I have a database I am creating that has two tables with a one to many
structure with Ref Int Forced.
For ease of typeing I will call them TBLA and TBLB
TBLA has:
ID (key field autonumber)
Name
Date
Time

TBLB has:
ID (Key field number, linked to ID in TBLA)
Activity (the important data I need to collect goes here)
ValueAdded
Comment

Every hour I have a form that they will fill out. The main form is from
TBLA, the subform is from TBLB and each hour they can have up to 4
enteries in the subform tied to the timed entry in TBLA

If nothing is entered after 50 minutes I have it fill out the Activity
field with "away from desk"

Now I need to make it so that they can go back at the end of their day
and edit the Away from desk enteries and only see those.
I have tried doing this two ways, Making two queries that are linked
with the ID field, where QueryB pulls all the enteries with "away from
desk" in them and QueryA (at least in theory) had the ID field with a
like statement that pointed to the ID field in QueryB. This approach
just generates a error every time I run it asking me what ID field I
would like to query for. If I leave out the Like statement in QueryA
then I get QueryB pefect, and QueryA pulls every entry out of TBLA.
this is a problem because when I make a form from this query I get
enteries in it that are blank and will confuse the user.

Method 2. Made 1 query which included both tables. The query works
great. I get all the enteries with "away from desk" in them. But when I
make the form for them to edit the enteries, I can only replace the ONE
"away from desk" activity field, and I cannot add more activities to
the current time stamp.

Is there any way I can filter a basic form so it only shows certain
enteries. I would need to filter both the main form and the subform
since there is data from both tables I would need to not include (all
other Names from the TBLA, and all non-"away from desk" enteries from
TBLB)

 

[Guest]

You don't need to do anything with the form's recordset. Use the form's
Filter and FilterOn properties. You can do the same for the sub form. VBA
help has all the details.

 

[JonnyRotten]

Do you happen to have a link to a example of implementing this? I am
new to VBA and just learning it.

Thanks!

 

[Guest]

This will filter your form's recordset on a field in your table to a control
on your form. The Names are bogus, so you will have to use yours.

Me.Filter = "[SomeTableField] = '" & Me.SomeControl & "'"
Me.Filteron = True

To remove a filter:
Me.FiterOn = False
Me.Requery