Form with Table and Related Table Combo Box search

[June7]

I was editing my earlier post when you posted. Might review it again.



Whether or not form/subform is needed depends on purpose of form.

Autonumber can be primary key but then it should be the value saved as foreign key. You are not doing that. I have some dbs that use autonumber and some don't. Depends on situation. In your case the MRN would serve as PK just as well, especially since FollowUp is already set to receive that value.

[Leonidsg]

OI-Database-version3.0.accdb.zip For my purposes I am fine using it as a subform in single format I didn't realize that there are other formats besides datasheet view. I should mention that I am a medical student about to start my residency in Orthopaedic Surgery. I don't really have any training in access just read a couple of books. This database will be used for collecting information on a subset of patients. It actually is much larger but it seems easier to only attach the relevant parts. I really appreciate the help.

[June7]

Fix pk/fk.

Change the RecordSource for GeneticsVisitForm to remove FollowUp.

The functionality of comboboxes changes with form/subform structure. The MRN combobox can be used to filter/goto existing FirstVisit record as well as requery the date combobox. However the date combobox no longer has functionality on the GeneticsVisitForm. Either have that combobox in header of FollowUp subform or set subform as Datasheet or Continuous view and eliminate date combobox. Controls can be arranged in Continuous to look like Datasheet.

[Leonidsg]

So I have followed your advice. I am stuck in two places. I don't know know how to write a requery on the cmbMRN based of the cmbDate since cmbDate is located in the subform. Before I wrote it as:
Private Sub cmbMRN_AfterUpdate()
Me.cmbDate.Requery
End Sub

but in this case cmbDate comes from the subform followup.
My second problem is doing a afterupdate script for the cmbDate box since the [MRN] comes from the parent form "firstvisit. Thank you again for the help.

Private Sub cmbDate_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object


Set rs = Me.Recordset.Clone
rs.FindFirst "[MRN] = " & Str(Nz(Me![cmbDate], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

[June7]

Genetics form RecordSource:

SELECT FirstVisit.* FROM FirstVisit ORDER BY MRN;

cmbMRN

RowSource:
SELECT FirstVisit.MRN FROM FirstVisit ORDER BY FirstVisit.MRN;

Code:
Private Sub cmbMRN_AfterUpdate()
With Me.RecordsetClone
.FindFirst "MRN='" & Me.cmbMRN & "'"
If Not .NoMatch Then Me.Bookmark = .Bookmark
Me.FollowUp_subform.Form.cmbDate.Requery
End With
End Sub

cmbDate now placed on subform

RowSource:
SELECT ID, DateExam FROM FollowUp WHERE MRN=Forms!GeneticsVisitForm.[cmbMRN];

Code:
Private Sub cmbDate_AfterUpdate()
'Find the record that matches the control.
With Me.Recordset.Clone
.FindFirst "[RecordID] = " & Me.[cmbDate]
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With
End Sub

[Leonidsg]

So everything is working perfectly except I would like to adjust one thing. When I select a MRN in cmbMRN I would like the FollowUp subform to remain blank unless a date is selected in cmbDate. Thank you again. Lenny

[June7]

Try:

Private Sub cmbMRN_AfterUpdate()
With Me.RecordsetClone
.FindFirst "MRN='" & Me.cmbMRN & "'"
If Not .NoMatch Then Me.Bookmark = .Bookmark
Me.FollowUp_subform.Form.cmbDate.Requery
Me.FollowUp_subform.Form.cmbdate = Null
Me.FollowUp_subform.Form.Filter = "DateExam=#1/1/1900#"
Me.FollowUp_subform.Form.FilterOn = True
End With
End Sub

Private Sub cmbDate_AfterUpdate()
'Find the record that matches the control.
Me.FilterOn = False
With Me.Recordset.Clone
.FindFirst "[ID] = " & Me.[cmbDate]
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With
End Sub

Also, appears I had an error in the cmbDate_AfterUpdate code. Field name is not [RecordID], it is just [ID].

[Leonidsg]

Perfect Thanks again for all your help!

[Leonidsg]

Hey June7 was wondering if you can help me with this form again. So everything is working fine. However I added 2 subforms to my subform. They work but when I use the filter function you wrote below it filters the nested subforms. The 2 nested subforms are really a function of the Parent form related by mrn. I don't need the 2 nested subforms to be filtered like the Followup subform. Am I using the nested subform incorrectly since they are really not a function of the Followup subform. Hope that makes sense. Thanks for your help.

Lenny

Try:

Private Sub cmbMRN_AfterUpdate()
With Me.RecordsetClone
.FindFirst "MRN='" & Me.cmbMRN & "'"
If Not .NoMatch Then Me.Bookmark = .Bookmark
Me.FollowUp_subform.Form.cmbDate.Requery
Me.FollowUp_subform.Form.cmbdate = Null
Me.FollowUp_subform.Form.Filter = "DateExam=#1/1/1900#"
Me.FollowUp_subform.Form.FilterOn = True
End With
End Sub

Private Sub cmbDate_AfterUpdate()
'Find the record that matches the control.
Me.FilterOn = False
With Me.Recordset.Clone
.FindFirst "[ID] = " & Me.[cmbDate]
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With
End Sub

Also, appears I had an error in the cmbDate_AfterUpdate code. Field name is not [RecordID], it is just [ID].

[June7]

So you have a form/subform/subsubform arrangement - 3 levels of forms? Not understanding what's going on. If the subsubforms should not be dependent on the subform then don't place them on the subform. If they should be dependent on the main form then why aren't they directly on the main form?

[Leonidsg]

You are correct about about the order of forms. I have a tab form setup so it looked better when there was only one level of tabs instead of two. Anyway you answered my question. Thanks again for the help.

So you have a form/subform/subsubform arrangement - 3 levels of forms? Not understanding what's going on. If the subsubforms should not be dependent on the subform then don't place them on the subform. If they should be dependent on the main form then why aren't they directly on the main form?