Populating control values based on each record on the many side

[kheisler6]

I have a one-to-many relationship between tblPatients and tblVisits.

My main form holds information for patients (based on tblPatients), and
the subform shows information for each visit (based on tblVisits). The
subform is in form view.

One field in tblVisits is the date of the visit.

In the form header of the subform, I would like to list the date of
every visit entered for that patient (horizontally - i.e., on one
line), so that the user can simply click on the date of interest and
the subform will jump to that record.

It's safe to assume a patient will never have more than 4 visits (and
a patient will never have two visits on the dame day), so perhaps I can
add four controls (txtVisit1, txtVisit2, etc.). The control source for
each txt field will refer to the date of the first visit record, second
visit record, etc. Each control will have an OnClick event which will
take the user to the 1st visit record, the 2nd visit record, etc.

But I'm stuck on how to code all this. Or maybe there's any easier
way?

Any ideas? Thanks.

Kurt

 

[Marshall Barton]

I have a one-to-many relationship between tblPatients and tblVisits.

My main form holds information for patients (based on tblPatients), and
the subform shows information for each visit (based on tblVisits). The
subform is in form view.

One field in tblVisits is the date of the visit.

In the form header of the subform, I would like to list the date of
every visit entered for that patient (horizontally - i.e., on one
line), so that the user can simply click on the date of interest and
the subform will jump to that record.

It's safe to assume a patient will never have more than 4 visits (and
a patient will never have two visits on the dame day), so perhaps I can
add four controls (txtVisit1, txtVisit2, etc.). The control source for
each txt field will refer to the date of the first visit record, second
visit record, etc. Each control will have an OnClick event which will
take the user to the 1st visit record, the 2nd visit record, etc.

But I'm stuck on how to code all this. Or maybe there's any easier
way?

You can populate the header text boxes using code in the
subform's Load event. Here's some air code (DAO) with the
general idea:

With Me.RecordsetClone
If .RecordCount > 0 Then
.MoveFirst
Do Until .EOF Or .AbsolutePosition >= 4
Me("txtVisit" & .AbsolutePosition +1) = !visitdate
Loop
End If
End With

You will have to run that code from the main form's Current
event procedure:
Me.subformcontrolname.Form.Form_Load

You will probably want to add more code (e.g. make unused
txtVisit text boxes invisible, rearrange it into a separate
Sub that's called from the event rocedure, ...)

The txtVisit text boxes Click event would then be like:

With Me.RecordsetClone
If .RecordCount > 0 Then
.FindFirst "visitdate=" & Format(txtVisit1,
"\#m\/d\/yyyy\#")
If Not .NoMatch Then Me.Bookmark = .Bookmark
End If
End With

Using another subform for just the dates might be easier,
but you would not get the dates to appear on one row.

 

[kheisler6]

Small problem with the On Load code:

You can populate the header text boxes using code in the
subform's Load event. Here's some air code (DAO) with the
general idea:

With Me.RecordsetClone
If .RecordCount > 0 Then
.MoveFirst
Do Until .EOF Or .AbsolutePosition >= 4
Me("txtVisit" & .AbsolutePosition +1) = !visitdate
Loop
End If
End With

Just to make sure we're on the same page, I'm assuming that ...

1) 'visitdate' is the name of my visit date field in tblVisits. (It's
also a control on the subform.)(Which it is!)

2) I have four unbound text fields (txtVisit1, txtVisit2, etc.) in the
subform's header (and that the 'txtVisit' ... +1 code is designed to
populate them accordingly)

However, when the subform (frmReferrals) loads, the loop seems to run
indefinitely and eventually Access stops responding. I can't figure out
what's causing this.

 

[kheisler6]

Okay. Now I'm having trouble with calling the subform's On Load event.

You will have to run that code from the main form's Current
event procedure:
Me.subformcontrolname.Form.Form_Load

The name of the subform control is frmReferrals, so I added this to the
main form's Current event:

Me.frmReferrals.Form.Form_Load

This produces a run time 2455 error (invalid reference to the
property/form report). I've tried a million other variations,
including:

Me!frmReferrals.Form.Form_Load

and ...

Forms!frmPatients!frmReferrals.Form.Form_Load

All produce the same error.

Hmmm ...

Kurt

 

[Marshall Barton]

Okay. Now I'm having trouble with calling the subform's On Load event.


The name of the subform control is frmReferrals, so I added this to the
main form's Current event:

Me.frmReferrals.Form.Form_Load

This produces a run time 2455 error (invalid reference to the
property/form report).

Are you sure that's the name of the control? The control
name is not necessarily the same as the name of the form
object it is displaying?

You also need to make sure that the subform's Load event
procedure is Public (instead of the automatically generated
Private).

Now that I think about it some more, the code doesn't really
need to be in the Load event. The main form's Current event
is sufficient. To keep the code local to the subform, I
suggest that you put the code in a separate public sub
procedure and call that from the main form's Current event.

 

[kheisler6]

Are you sure that's the name of the control? The control

name is not necessarily the same as the name of the form
object it is displaying?

It's definitely the name of the subform control. I even tried changing
the name of the subform control and referencing the new name, but I
keep getting the same reference error. I can't seem to refer to
anything on the subfom from the main form - not subform properties,
controls, etc. - and I can't figure out why. It's very odd.

I'll keep working at this, but obviously can't test everything out
until I can figure out what's stopping me from properly referencing the
subform.

Thanks again for your assistance.

Kurt

 

[kheisler6]

Got it! I just needed to first set the focus to the subform.

I call the subform's Load event from the main form's current event. (I
went ahead and kept the Load event in the subform.)

frmPatients (the main form)
-----------------------------------------------------
Private Sub Form_Current()

Me.frmReferrals.SetFocus
Me.frmReferrals.Form.Form_Load

End Sub

frmReferrals (the subform)
-----------------------------------------------------
Public Sub Form_Load()

With Me.RecordsetClone
If .RecordCount > 0 Then
.MoveFirst
Do Until .EOF Or .AbsolutePosition >= 4
Me("txtVisit" & .AbsolutePosition + 1) =
!ReferralDate
.MoveNext
Loop
End If
End With

End Sub

###

txtVisit1 is always populated correctly (except when on a new record,
where it defaults to the value of the visit date for the first record
in tblReferrals - but I should be able to fix that).

txtVisit1 and txtVisit 2 are always populated correctly when a record
has two visits.

etc.

But, if I go from a record that has 3 visits (and 3 visit dates), to a
record that has 2 visits (and 2 visit dates), txtVisit1 & txtVisit2 are
updated correctly, but txtVisit3 retains the value from the previous
record's 3rd visit date.

Likewise, if I go from a record that has 3 visits (and 3 visit dates),
to a record that has 1 visit (and 1 visit date), txtVisit1 is updated
correctly, but txt Visit2 and txtVisit3 retain the values from the
previous record's 2nd and 3rd visit date.

This seems to be the only remaining obstacle!

By the way, the On Click code for each text box works great - it takes
me right to the appropriate visit record. This is such a nice feature
for this database, so thanks for getting me this far!

Kurt

That's strange. The only other straw I can grasp at from
here is that the subform reference is not really in the main
form. That, or the form has been corrupted. The latter is
not unusual if you edit the form's code module when the form
is not in design view.
--
Marsh
MVP [MS Access]

It's definitely the name of the subform control. I even tried changing
the name of the subform control and referencing the new name, but I
keep getting the same reference error. I can't seem to refer to
anything on the subfom from the main form - not subform properties,
controls, etc. - and I can't figure out why. It's very odd.

I'll keep working at this, but obviously can't test everything out
until I can figure out what's stopping me from properly referencing the
subform.

 

[Marshall Barton]

Got it! I just needed to first set the focus to the subform.

I call the subform's Load event from the main form's current event. (I
went ahead and kept the Load event in the subform.)

frmPatients (the main form)
-----------------------------------------------------
Private Sub Form_Current()

Me.frmReferrals.SetFocus
Me.frmReferrals.Form.Form_Load

End Sub

frmReferrals (the subform)
-----------------------------------------------------
Public Sub Form_Load()

With Me.RecordsetClone
If .RecordCount > 0 Then
.MoveFirst
Do Until .EOF Or .AbsolutePosition >= 4
Me("txtVisit" & .AbsolutePosition + 1) =
!ReferralDate
.MoveNext
Loop
End If
End With

End Sub

###

txtVisit1 is always populated correctly (except when on a new record,
where it defaults to the value of the visit date for the first record
in tblReferrals - but I should be able to fix that).

txtVisit1 and txtVisit 2 are always populated correctly when a record
has two visits.

etc.

But, if I go from a record that has 3 visits (and 3 visit dates), to a
record that has 2 visits (and 2 visit dates), txtVisit1 & txtVisit2 are
updated correctly, but txtVisit3 retains the value from the previous
record's 3rd visit date. []
This seems to be the only remaining obstacle!

By the way, the On Click code for each text box works great - it takes
me right to the appropriate visit record. This is such a nice feature
for this database, so thanks for getting me this far!

I don't understand the need to set the focus, I didn't see
this issue when I tried it. Maybe there's a race between
loading the subform and the main form's Current event???

I think the new record issue is the same as going from more
dates to fewer dates.

Earlier, I hinted that you would want to something more like
make the unused date text boxes invisible, set them to Null
or ??? I prefer to make them invisible so users can not
click on something that can't do anything useful. If this
is what you decide to do, I think you can do it by adding
this line after the Do Until line:

Me("txtVisit" & .AbsolutePosition + 1).Visible = True

and these lines after the End With lines:

Dim k As Integer
For k = Me.RecordCount + 1 To 4
Me("txtVisit" & k).Visible = False
Next k

The next thing I think you're going to want is to update the
text boxes when you add a new visit. This can be done by
simply calling the sub procedure from the subform's
AfterInsert event.

Alternatively, you might want to call it from the subform's
AfterUpdate event to catch any editied visit dates as well
as new records. This is probably overkill beacuse it would
redo the date text box stuff for all changes whether a date
was changed or not, but you will probably be unable to
notice it.

 

[kheisler6]

Earlier, I hinted that you would want to something more like

make the unused date text boxes invisible, set them to Null
or ??? I prefer to make them invisible so users can not
click on something that can't do anything useful. If this
is what you decide to do, I think you can do it by adding
this line after the Do Until line:

Me("txtVisit" & .AbsolutePosition + 1).Visible = True

Works perfectly!

and these lines after the End With lines:

Dim k As Integer
For k = Me.RecordCount + 1 To 4
Me("txtVisit" & k).Visible = False
Next k

When I open the main form, I get an Invalid Use of Null error, which
points to this line (in the On Load of the subform):

For k = Me.RecordCount + 1 To 4

This is odd, because every person in this database has at least one
visit right now, and the main form isn't set to go to a new record on
open (I turned that off), so I'm not sure what 'Null' it's detecting.

So I changed it to this:

Dim k As Integer
If IsNull(Me.RecordCount) Then
Exit Sub
Else
For k = Me.RecordCount + 1 To 4
Me("txtVisit" & k).Visible = False
Next k
End If

With this change, I no longer get the Invalid Use of Null error when I
open the main form. Instead, I get a Type Mismatch error, which points
to this line (in the On Current of the main form)

Me.frmReferrals.Form.Form_Load

###

I'll keep chipping away at it. Thanks again for your continued help.

Kurt

 

[Marshall Barton]

Stranger and stranger. I can not imagine how the
RecordCount property can be Null. I am beginning to
seriously suspect some kind of corruption and suggest that
you make a backup copy of the mdb file before it gets any
worse. Then create a new, blank mdb, set all the options
appropriately (especially Name Autocorrect off) and then
import everthing from the problem mdb.

 

[kheisler6]

Stranger and stranger. I can not imagine how the

RecordCount property can be Null. I am beginning to
seriously suspect some kind of corruption and suggest that
you make a backup copy of the mdb file before it gets any
worse. Then create a new, blank mdb, set all the options
appropriately (especially Name Autocorrect off) and then
import everthing from the problem mdb.

I did as you suggested but was still getting the same errors. But, I've
finally got everything working. A better programmer could come up with
a more elegant solution, but this is working without any performance
problems. Here's what I did:

---------------------------------------------------------------------
The Current event of the main form (frmPatients)
---------------------------------------------------------------------
Private Sub Form_Current()

If IsNull(Me.PatientID) Then

' On a new patient, hide all the visit text boxes on the
referral subform.

Me!frmReferrals.Form!txtVisit1.Visible = False
Me!frmReferrals.Form!txtVisit2.Visible = False
Me!frmReferrals.Form!txtVisit3.Visible = False
Me!frmReferrals.Form!txtVisit4.Visible = False

Else

' Otherwise, run the referral subform's Load event

Me.frmReferrals.SetFocus ' this was still needed, even after
reimporting eveything
Me.frmReferrals.Form.Form_Load

End If

End Sub

----------------------------------------------------------------
The Load event of the subform (frmReferrals)
----------------------------------------------------------------
Public Sub Form_Load()

With Me.RecordsetClone
If .RecordCount > 0 Then
.MoveFirst
Do Until .EOF Or .AbsolutePosition >= 4
Me("txtVisit" & .AbsolutePosition + 1).Visible =
True
Me("txtVisit" & .AbsolutePosition + 1) =
!ReferralDate
.MoveNext
Loop
End If
End With

' Show/hide the visit text boxes based on how many referrals the
patient has

If DCount("ReferralID", "tblReferrals", "[PatientID] = " & _
Forms!frmPatients!PatientID) = 1 Then
Me.txtVisit1.Visible = True
Me.txtVisit2.Visible = False
Me.txtVisit3.Visible = False
Me.txtVisit4.Visible = False
ElseIf DCount("ReferralID", "tblReferrals", "[PatientID] = " & _
Forms!frmPatients!PatientID) = 2 Then
Me.txtVisit1.Visible = True
Me.txtVisit2.Visible = True
Me.txtVisit3.Visible = False
Me.txtVisit4.Visible = False

' etc. for each referral up to 4

End If

End Sub

-------------------------------------------------------------------
The Current event of the subform (frmReferrals)
-------------------------------------------------------------------
Private Sub Form_Current()

If IsNull(Me.ReferralID) Then
' If this is a new referral, do nothing
Else
' Otherwise, show/hide the visit text boxes based
' on how many referrals the patient has

If DCount("ReferralID", "tblReferrals", "[PatientID] = " & _
Forms!frmPatients!PatientID) = 1 Then
Me.txtVisit1.Visible = True
Me.txtVisit2.Visible = False
Me.txtVisit3.Visible = False
Me.txtVisit4.Visible = False
ElseIf DCount("ReferralID", "tblReferrals", "[PatientID] = " &
_
Forms!frmPatients!PatientID) = 2 Then
Me.txtVisit1.Visible = True
Me.txtVisit2.Visible = True
Me.txtVisit3.Visible = False
Me.txtVisit4.Visible = False

'etc. for each referral up to 4

End If
End If

I also call the subform's Load event in its AfterUpdate event, as well
as after a user clicks a Delete button to delete the record. This way,
the visit text boxes are updated when a new referral is added or
deleted.

Thanks again for all your help.

Kurt

Stranger and stranger. I can not imagine how the
RecordCount property can be Null. I am beginning to
seriously suspect some kind of corruption and suggest that
you make a backup copy of the mdb file before it gets any
worse. Then create a new, blank mdb, set all the options
appropriately (especially Name Autocorrect off) and then
import everthing from the problem mdb.
--
Marsh
MVP [MS Access]

Works perfectly!


When I open the main form, I get an Invalid Use of Null error, which
points to this line (in the On Load of the subform):

For k = Me.RecordCount + 1 To 4

This is odd, because every person in this database has at least one
visit right now, and the main form isn't set to go to a new record on
open (I turned that off), so I'm not sure what 'Null' it's detecting.

So I changed it to this:

Dim k As Integer
If IsNull(Me.RecordCount) Then
Exit Sub
Else
For k = Me.RecordCount + 1 To 4
Me("txtVisit" & k).Visible = False
Next k
End If

With this change, I no longer get the Invalid Use of Null error when I
open the main form. Instead, I get a Type Mismatch error, which points
to this line (in the On Current of the main form)

Me.frmReferrals.Form.Form_Load

###

I'll keep chipping away at it. Thanks again for your continued help.

 

[Marshall Barton]

Well, I am glad to hear that it's working, even I I don't
understand where the problems came from.

Keep this code in mind as you become more familiar with
using procedures, recordsets, etc. eventually you will come
back to this and say "what was I thinking?". We all do that
and it part of the normal process of learning. Some of the
things I suspect will provoke that reaction are moving the
code out of the Load event, using the recordset clone's
properties instead of the DCount and using a Select Case
statement instead of multiple If statements. These will
make the code easier to read as well as more efficient.
--
Marsh
MVP [MS Access]

I did as you suggested but was still getting the same errors. But, I've
finally got everything working. A better programmer could come up with
a more elegant solution, but this is working without any performance
problems. Here's what I did:

---------------------------------------------------------------------
The Current event of the main form (frmPatients)
---------------------------------------------------------------------
Private Sub Form_Current()

If IsNull(Me.PatientID) Then

' On a new patient, hide all the visit text boxes on the
referral subform.

Me!frmReferrals.Form!txtVisit1.Visible = False
Me!frmReferrals.Form!txtVisit2.Visible = False
Me!frmReferrals.Form!txtVisit3.Visible = False
Me!frmReferrals.Form!txtVisit4.Visible = False

Else

' Otherwise, run the referral subform's Load event

Me.frmReferrals.SetFocus ' this was still needed, even after
reimporting eveything
Me.frmReferrals.Form.Form_Load

End If

End Sub

----------------------------------------------------------------
The Load event of the subform (frmReferrals)
----------------------------------------------------------------
Public Sub Form_Load()

With Me.RecordsetClone
If .RecordCount > 0 Then
.MoveFirst
Do Until .EOF Or .AbsolutePosition >= 4
Me("txtVisit" & .AbsolutePosition + 1).Visible =
True
Me("txtVisit" & .AbsolutePosition + 1) =
!ReferralDate
.MoveNext
Loop
End If
End With

' Show/hide the visit text boxes based on how many referrals the
patient has

If DCount("ReferralID", "tblReferrals", "[PatientID] = " & _
Forms!frmPatients!PatientID) = 1 Then
Me.txtVisit1.Visible = True
Me.txtVisit2.Visible = False
Me.txtVisit3.Visible = False
Me.txtVisit4.Visible = False
ElseIf DCount("ReferralID", "tblReferrals", "[PatientID] = " & _
Forms!frmPatients!PatientID) = 2 Then
Me.txtVisit1.Visible = True
Me.txtVisit2.Visible = True
Me.txtVisit3.Visible = False
Me.txtVisit4.Visible = False

' etc. for each referral up to 4

End If

End Sub

-------------------------------------------------------------------
The Current event of the subform (frmReferrals)
-------------------------------------------------------------------
Private Sub Form_Current()

If IsNull(Me.ReferralID) Then
' If this is a new referral, do nothing
Else
' Otherwise, show/hide the visit text boxes based
' on how many referrals the patient has

If DCount("ReferralID", "tblReferrals", "[PatientID] = " & _
Forms!frmPatients!PatientID) = 1 Then
Me.txtVisit1.Visible = True
Me.txtVisit2.Visible = False
Me.txtVisit3.Visible = False
Me.txtVisit4.Visible = False
ElseIf DCount("ReferralID", "tblReferrals", "[PatientID] = " &
_
Forms!frmPatients!PatientID) = 2 Then
Me.txtVisit1.Visible = True
Me.txtVisit2.Visible = True
Me.txtVisit3.Visible = False
Me.txtVisit4.Visible = False

'etc. for each referral up to 4

End If
End If

I also call the subform's Load event in its AfterUpdate event, as well
as after a user clicks a Delete button to delete the record. This way,
the visit text boxes are updated when a new referral is added or
deleted.