E
EyeDoc
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)
LastName
FirstName
DOB
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:
txtPatientID
txtLastName
txtFirstName
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
.Open
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!
Simon
struggling to automate it using VB.
The database contains a table with a list of patients
(tblBasicPatientInformation) . The fields are:
PatientID (unique number)
LastName
FirstName
DOB
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:
txtPatientID
txtLastName
txtFirstName
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
.Open
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!
Simon