Command button to open form at a specific record

[peted]

I’m working on a database which stores information on hospital patients with neurological disorders. There are currently two tables: 1. Audit_Proforma (which stores info on the patients and their medical history), and 2. AED (which stores info on their drug regimes).

What I would like is a main form which has a combo box listing the patients names and Hospital ID number. When you select a patient with that combo box I would then like to have a command button which opens the Audit_Proforma form at that particular record.

I have succeeded (to a point) in populating the combo box with the patients. I have at present an unbound combo box and its row source in the properties I have entered the following text:
SELECT Audit_Proforma.PatientName, Audit_Proforma.HospNumber FROM Audit_Proforma ORDER BY Audit_Proforma.PatientName;
This is only showing the patient name, whereas I hoped it would also show the Hospital ID. So that’s the first problem.

The second is that I can’t work out how to add a button that will open the form at the chosen record. I’ve tried putting in some code for the on click event which I butchered from another database (involving Dim stDocname and Dim stLinkcriteria), but I haven’t got anywhere. Would anyone be able to point me in the right direction?

Thanks

 

[CEH]

ON your "column width" in the unbound dropdown it probably shows 0";1" if your change that to 1";1" it will show both.
If your form is based on a query make the criteria for your ID the path to your combo box.

 

[peted]

Thanks, Curtis. That didn't seem to work, but I did change column count in the properties, and I can now see both name and ID when I click on the combo box. So that's problem number 1 sorted. Anyone got any ideas for the second issue?

 

[CEH]

ON the second issue you will need a Docmd.requery on the combo box.... you might post the code you are trying to use.... others can see where the error is..

 

[RexesOperator]

Try this for the second issue - it actually opens a popup form, but it works.

http://www.access-programmers.co.uk/forums/showthread.php?t=113874&highlight=rexesoperator

 

[peted]

I'll have a look at that. In the meantime, here's the code that I've been using (please bear in mind I'm new to this and have butchered it form another dbase):
Private Sub Open_Audit_Proforma_Click()
On Error GoTo Err_Open_Audit_Proforma_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "AuditProforma_Form"

stLinkCriteria = "[HospNumber]=" & "'" & Me![HospNumber] & "'"
If IsNull(HospNumber) Then
MsgBox "Hospital Number cannot be blank, please select patient"
Else

DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "Menu", acSaveYes
End If
Exit_Open_Audit_Proforma_Click:
Exit Sub
Err_Open_Audit_Proforma_Click:
MsgBox Err.Description
Resume Exit_Open_Audit_Proforma_Click

End Sub


I get an error message saying that Access can't find the field 'HospNumber' referred to in the expression. Any idea where I'm going wrong?

 

[peted]

I had a look at the thread that RexesOperator pointed me in the direction of, and I'm afraid to say I couldn't undertsand it. Has anyone got an idea of where I'm going wrong with the code I've posted?

Thanks

 

[peted]

Actually, I've just worked it out. The term Me![HospNumber] in the code was wrong, and should have been Me![combo0]. As I said I'm new to this and had tried to work the code out form another database that someone else had set up. So now it works and I think I understand why.