Dynamically changing a subform's recordset using txt box



I'm trying to write my first access project using Access 2003 and am really
struggling to automate it using VB.

The database contains a table with a list of patients
(tblBasicPatientInformation) . The fields are:

PatientID (unique number)

PatientID then links to a number of other tables with information about
clinic visits, etc.

The form I am trying to create allows the user to view and search for
patients in the database.

I have a main form with the following textboxes:

DOB (to be added when I can get the others to work)

Initially these are blank. A subform (continuous form with scroll bar)
embedded in the main form initially shows all the patients in the database
(as no criteria are entered in the text boxes)

I then want the user to be able to filter the subform by entering values
into the relevant boxes, so they can search the scrollable list by last name
or patient ID.

I figure the best way to acheive this is to get the main form to load an
ADODB recordset generated from tblBasicPatientInformation according to the
entries in the boxes (and return all results if the boxes are blank, as when
the form initially loads). I would then want to set the subform's recordset
to this ADODB recordset and refresh the subform.

The problem is that I cannot even reference the subform in VB without
getting syntax errors or object not found errors and I can't find the correct
syntax anywhere.

I am creating the unfiltered recordset using the following code (this seems
to work):

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim queryFilterPatients As String

Set cn = CurrentProject.AccessConnection

queryFilterPatients = "SELECT PatientID, LastName, FirstName, DOB FROM
dbo.[tblBasicPatientInformation] ORDER BY LastName, FirstName"

Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = queryFilterPatients
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
End With

Now, how do I set the subform's recordset to 'rs' from the main form?
(within the subform is easy as it's just Set Me.Recordset = rs, but I don't
know how to reference the subform from the main form) And how do I get it to
refresh if I set it to a different recordset after the user selects a
different query?

I hope that this makes sense and that someone can help!




Jeanette Cunningham

Hi EyeDoc
there is a great sample of a search form which does this on this website.


It uses a much easier process than the one you just described.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question