Permanent filter/lookup for a form - how? Should be suiper simple

[john]

cant seem to find a clear example.

Ive skimmed maybe 20-30 books at Borders and Barnes and Noble.

Heres the basics. Theres a table with name , soc security number ,
etc


Table1
( fields)
Soc sec
Name
etc



Its indexed on Soc sec as that will be the lookup.
I have a contiunous form - header + forms below.

In the header are two text boxes to enter :

1) Soc Sec no

2) Date

After entering both , I want it to lookup /filter
all the records that match that soc sec no , between the date entered
and the next 10 records.

Ive created all the input and output forms and databases.

The first question -- it should be so easy to do its ridiculous. How
do you get it to lookup only those records that match the index - soc
sec no ?

The second --- of course Id like to add the criteria of date and the
next 10 records after that date but Im focused on the simple mechanics
of doing the soc sec match and filtering. Ive seen bits on filter by
selection , filter by form . There are slight mentions of it here and
there but then they say use this button interactively etc. I want it
permanently set up were you enter a soc sec number and it filters the
matches automatically. Do I have to use VBA? Is there no automatic
way to do this? What event do I choose to write the code to :


a) initialize the displays on the forms so they are blank.

b) lookup the soc sec after input - obviously some updating event
would trigger that lookup

Anyone know of an example of this somewhere? This should be so common
and simple but searching the net I cant find it.

 

[MacDermott]

You can solve this by building an unbound main form, with your continuous
form as a subform.
The simple thing would be just to link the SSN field.
You can do this using the LinkMasterFields and LinkChildFields properties of
the subform control. Set LinkMasterFields to the name of your textbox on
the main form, and LinkChildFields to the name of your SSN field on the
subform.
Note: You can't use the builder (3 dots) to build this if the main form is
not bound, but you can type the names directly into the properties. This
works best if they don't contain any spaces or non-standard characters (e.g.
#).
No VBA code required for that.

Now, bringing up the first 10 entries after the date you type in is a bit
more complex.
If you want to do this, you can still set your form up as form/subform, but
you may not want to use the LinkMaster/LinkChild properties.
If your main form is named frmMain, with txtSSN the textbox where you enter
the SSN and txtDate where you enter the date, you could set the RecordSource
of the subform like this:
SELECT TOP 10 * FROM MyTable WHERE SSN= Forms!frmMain!txtSSN and
MyDateField>= Forms!frmMain!txtDate ORDER BY MyDateField

You may need to add
MySubform.Form.Requery
to the AfterUpdate event procedure for both txtSSN and txtDate.
Of course, you'll be using your own names for all the controls and fields...

HTH

 

[John]

You can solve this by building an unbound main form, with your continuous
form as a subform.
The simple thing would be just to link the SSN field.
You can do this using the LinkMasterFields and LinkChildFields properties of
the subform control. Set LinkMasterFields to the name of your textbox on
the main form, and LinkChildFields to the name of your SSN field on the
subform.
Note: You can't use the builder (3 dots) to build this if the main form is
not bound, but you can type the names directly into the properties. This
works best if they don't contain any spaces or non-standard characters (e.g.
#).
No VBA code required for that.

Now, bringing up the first 10 entries after the date you type in is a bit
more complex.
If you want to do this, you can still set your form up as form/subform, but
you may not want to use the LinkMaster/LinkChild properties.
If your main form is named frmMain, with txtSSN the textbox where you enter
the SSN and txtDate where you enter the date, you could set the RecordSource
of the subform like this:
SELECT TOP 10 * FROM MyTable WHERE SSN= Forms!frmMain!txtSSN and
MyDateField>= Forms!frmMain!txtDate ORDER BY MyDateField

You may need to add
MySubform.Form.Requery
to the AfterUpdate event procedure for both txtSSN and txtDate.
Of course, you'll be using your own names for all the controls and fields...

HTH

Thanks. Im reading up on it now. Im on the ADO , SQL chapter of beg
VBA ACCESS programming.

I thought I might be able to breeze through it and use drag and drop
controls or copy some predone code with step by step intructions but
cant find anything.

Its one of those things you learn how to do it once and of course you
know how to do it so its simple but until you do its totally
confusing.