Attempting to auto-update a subform field upon update of ID field

[BristolGarry]

Hi Folks -



I have a database of clients who come into the adult learning lab where I work. We have a Daily Visits form where we enter who comes in and what they accomplish. Linked to this form is a subform that shows the last time the client filled out an intake form (which has to be done yearly).

What I am trying to arrange is that when I open the Daily Visits form, the client ID gets transferred to the subform, and the subform then shows the intake date for that client. I also need the form to, when I choose another client to enter information for, update with the intake date of that newly chosen client.

I have tried this code, but it only works for the first two clients, and then does not return any further intake dates.

Private Sub Student_ID_Field_Change()
Me.txt.FirstName.Value = Me.Student_ID_Field.Column(1)
Me.txt.LastName.Value = Me.Student_ID_Field.Column(2)
Me.ClientIntakeDates_subform.Form![ID] = Me.Student_ID_Field.Column(0)
Me.ClientInnakeDates_subform.Form.Requery
End Sub0

The pertinent lines are the two "Me.ClientIntakeDates" lines. The Column(0) is the Client ID field of the client record.

I am sure I am missing something relatively easy and straightforward here, and would appreciate any suggestions.

Thanks in advance.

Garry

[orange]

Garry,

Welcome.
Tell us about your tables --their name, primary keys,etc. and how they relate.

I do see some variation in spelling.

Me.ClientIntakeDates_subform.Form![ID] = Me.Student_ID_Field.Column(0)
Me.ClientInnakeDates_subform.Form.Requery

[ssanfu]

Or maybe you could/would make a copy of the dB, change any sensitive data, do a "Compact and Repair", then zip it and post it.
Only need a couple of records.


Shouldn't need any code.... Did you set the linking fields between the main form and the subform control?

And the after update event would be better than the change event; the change event fires after every key stroke.

[June7]

Should only save the student ID, not the name parts. If combobox is bound to field, the bound column value should save to table without code.

Appears to be a misspelling: Me.ClientInnakeDates

Why a 0 after End Sub? Just a typo in post?

[Micron]

Are you sure you want to use the Change event? This code will fire upon every keyboard press if the cursor is in the control, will it not?
I'm surprised this would work Me.txt.LastName.Value, assuming it does.