Open a record on a form depending on the record of another form

[Tony]

I have 2 forms: A client entry form and a another form with an included
subform.

Both forms have common fields from the client table: clientid name and dob.
The subform has the fields clientid and attendance date.

I have no problem with the 2nd form with its subform as the clientid syncs
without any problem.
As it is opened by an event procedure from the client entry form with the VB
code: DoCmd.OpenForm "frm_client_dates", acNormal I would like it to go to
the current record in the client entry form to save people from doing a
search and from them putting data into the wrong record.

Thank you

 

[Al Campagna]

Tony,
If I understand correctly...
Use the Where argument of the OpenForm method.
(acNormal is assumed in the OpenForm method, so no need to
include it.)
Use your own object names, and the code is all on one line.
Leave the "calling" form open.
Assuming ClientID is numeric
DoCmd.OpenForm "frm_client_dates", , , "ClientID = " & Me.ClientID
--
hth
Al Campagna
Microsoft Access MVP 2006-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Tony]

Thanks Al, ClientID is a text field of 25chrs so I get a data type mismatch
error 3464.

 

[Al Campagna]

Tony,
Try...
DoCmd.OpenForm "frm_client_dates", , , "ClientID = ' " & Me.ClientID & " ' "
(I added spaces in the quotes for clarity - remove them in your code)
DoCmd.OpenForm "frm_client_dates", , , "ClientID = '" & Me.ClientID & "'"

Cut and Paste your code in your reply, if you still have problems.
Hopefully, this should do it...
--
hth
Al Campagna
Microsoft Access MVP 2006-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Tony]

Thank you very much, it works as you said it should.
I am left with one small problem:
The first attendance date is part of the client table while the subsequent
ones end up on the dates table.
I would like all the dates to be recorded on the dates table.

Both tables are linked by ClientID and the "Attend_dates" variable is on
both tables.

 

[Al Campagna]

Tony,
That doesn't seem to make sense.
Give me just the pertinent table fields in each table, with some
sample data.
Given ONE Client vs MANY AttendDates, all aattendance dates
should be in the tblAttendDates.
--
hth
Al Campagna
Microsoft Access MVP 2006-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."