Button on form which opens another form based on ID

[RCheesley]

Hi folks,

I thought I'd got this one cracked but it doesn't seem to be working quite right.

What I have is a form where patients are entered, and then I have a button which I want to be able to click, and the following happen:

• If there is an existing record, this is displayed (pref. in continuous form layout - I assume this is done via subform but not sure how!?), above this are the fields for entering a new record
• If there is no existing record, this is reported as such (i.e. No Record Found) and the fields for entering a new record are shown

I have two tables:

tbl_patientdetails - PK=PatientID
tbl_sampleinfo - PK=CaseID

PatientID is contained within tbl_sampleinfo and I have created a relationship between the two.

Database is attached if anyone can help?

Ruth

 

[DCrake]

The PK that I suggest you use between the Patient record and the episode details is the NHS No as this will never change. After you input the NHS No you should code your main form sub form with the NHS No as the parent and child fields in the properties of the sub form. There is plenty of help on relating the two together.

 

[RCheesley]

Thanks for the advice, I've used NHSNo as a primary key on the patient details table, linked to NHS number in the other table.

Subform is set up with parent/child links, button created with the option of displaying results based on the NHS number specified, but still not working..the VBA for the button is as follows:

Private Sub But_Open_Samples_Click()
On Error GoTo Err_But_Open_Samples_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frm_sampleinfo"
    
    stLinkCriteria = "[NHSNo]=" & Me![NHSNo]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_But_Open_Samples_Click:
    Exit Sub

Err_But_Open_Samples_Click:
    MsgBox Err.Description
    Resume Exit_But_Open_Samples_Click
    
End Sub

When I put the sample details form into design view I do see

[NHSNo]=123456789

under Filter in Properties, however whenever I try to add a new sample I get:

You cannot add or change a record because a related record is required in table 'tbl_patientdetails'.

Having searched on this I understand this to be a problem with relationships, particularly parent/child between the form/subform.

My Link Child & Link Master fields are set to NHSNo ..... I used the subform wizard to insert the form .. am I missing something here or just being completely blonde (a combination of both is perfectly plausible!)

Ruth