Problems with Recordset.clone Combo Box

[Fred Urquhart via AccessMonster.com]

Access 2003
Patient database

My main form opens up to a new record so that the form is blank.

I have a combo box on my main form that works off of a query. The query
brings up Patient Name, Patient ID, SSN, Medical Record number, etc., so you
can pick the patient you want.

I want the record selected in the combo box (PatientID) to pull up the
corresponding PatientID from the Patient Table and open it up on the form. So,
in the After Update event of the combo box I have the following code running:

Sub SelectPatient_AfterUpdate()
Dim rst As Recordset
Dim stPatientID As String
Set rst = Me.RecordsetClone
stPatientID = Str(Me!PatientID)
rst.FindFirst "PatientID = " & stPatientID
If rst.NoMatch Then
MsgBox "Record not found"
Else
Me.Bookmark = rst.Bookmark
End If rst.Close
End Sub

I set a watch on PatientID and stPatientID. I'm thinking that the code will
pull in the PatientID from the recordset that is the result of the query.

I think my problem is that I do not know how to grab the PatientID from the
recordset and plug it into a variable, then pick up the variable to pull up
that record from the underlying Patient Table.

My problem is that the above code does not pull in the PatientID from the
record selected in the combo box. It pulls up a completely different
PatientID and then compares it to the PatientID on the form which is blank
(new record) and then I error out on the Null from the blank field on the
form.

I think I have several problems going on but I can't seem to get it. I am not
sure where the problem lies.

Thanks

Fred

 

[Piet Linden]

Access 2003
Patient database

My main form opens up to a new record so that the form is blank.

I have a combo box on my main form that works off of a query. The query
brings up Patient Name, Patient ID, SSN, Medical Record number, etc., so you
can pick the patient you want.

I want the record selected in the combo box (PatientID) to pull up the
corresponding PatientID from the Patient Table and open it up on the form.. So,
in the After Update event of the combo box I have the following code running:

Sub SelectPatient_AfterUpdate()
Dim rst As Recordset
Dim stPatientID As String
Set rst = Me.RecordsetClone
stPatientID = Str(Me!PatientID)
rst.FindFirst "PatientID = " & stPatientID
If rst.NoMatch Then
MsgBox "Record not found"
Else
Me.Bookmark = rst.Bookmark
End If rst.Close
End Sub

I set a watch on PatientID and stPatientID. I'm thinking that the code will
pull in the PatientID from the recordset that is the result of the query.

I think my problem is that I do not know how to grab the PatientID from the
recordset and plug it into a variable, then pick up the variable to pull up
that record from the underlying Patient Table.

My problem is that the above code does not pull in the PatientID from the
record selected in the combo box. It pulls up a completely different
PatientID and then compares it to the PatientID on the form which is blank
(new record) and then I error out on the Null from the blank field on the
form.

I think I have several problems going on but I can't seem to get it. I amnot
sure where the problem lies.

Thanks

Fred

Why not just put the combobox on an unbound form, and then once the
user has selected a record, just open the main form with that SQL
statement as the recordsource? Just set it in the Open event of the
form...

 

[Steve Sanford]

I numbered the lines to make it easier to talk about. I see three problems
with your code....


1 Sub SelectPatient_AfterUpdate()
2 Dim rst As Recordset
3 Dim stPatientID As String

4 Set rst = Me.RecordsetClone
5 stPatientID = Str(Me!PatientID)
6 rst.FindFirst "PatientID = " & stPatientID

7 If rst.NoMatch Then
8 MsgBox "Record not found"
9 Else
10 Me.Bookmark = rst.Bookmark
11 End If rst.Close
12 End Sub


The first thing might be how the code posted. On like 11, "rst.Close" must
be on a separate line.

The second problem is at line 6. Is [PatientID] a number or a string. If it
really is a string, it needs to be delimited.

The third problem is the main problem at line 5. If the combo box name is
"cboSelect", then line 5 should be:

5 stPatientID = Str(Me.cboSelect) ' if cboSelect is a string


Your code is opening the form, opening a recordset, getting the *recordset*
[PatientID] (which can be at ANY PatientID, then searching for that same (the
recordset) PatientID.

Nowhere does the code look at the *combo box* - where the PatientID you
selected is at.


HTH