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

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

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" wrote in message
...
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

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


"Al Campagna" wrote in message
...
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" wrote in message
...
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

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" wrote in message
...
Thanks Al, ClientID is a text field of 25chrs so I get a data type
mismatch error 3464.


"Al Campagna" wrote in message
...
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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
Thanks Al, ClientID is a text field of 25chrs so I get a data type
mismatch error 3464.


"Al Campagna" wrote in message
...
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" wrote in message
...
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

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."


"Tony" wrote in message
...
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" wrote in message
...
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" wrote in message
...
Thanks Al, ClientID is a text field of 25chrs so I get a data type
mismatch error 3464.


"Al Campagna" wrote in message
...
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" wrote in message
...
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