How do I automatically open a form linked to the current record?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created an Access 2003 database with several one-to-many
relationships. I need to create a command button that would pull up a form
which is related to only a specific record in the form (the record which is
currently highlighted or has the focus). I've created command buttons before
and attached code to them but I'm not sure what the code should look like for
this situation. Thanks for any help in advance! --justin
 
Hi, Justin.

This is easy, as long as the table has a primary key and you haven't dirtied
the record first.

In the button's OnClick( ) event, try:

DoCmd.OpenForm "frmName", , , "ID = " & Me!txtID.Value


.... where frmName is the name of the form to be opened, ID is the primary
key, and txtID is the text box holding the primary key.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)

- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Gunny - thanks a lot!

The form that will be called up is actually based on a separate table
although that table on the one from which it is being called up are linked
through common fields. Would your line of code still work? If so, do the
primary key and text box need to come from the table ON which the form being
called up is based or from the table FROM which the form is being called up?
Thanks again!

justin
 
Hi, Justin.
The form that will be called up is actually based on a separate table
although that table on the one from which it is being called up are linked
through common fields.
Click to expand...

The primary key you are interested in is the primary key of the RecordSource
of the form you want to open. "Linked through common fields" is called a
foreign key. Your code needs to reference the foreign key on the present
form to open the other form at the correct record. For example, if the
other form were frmClients, bound to the qryClients query (based upon the
tblClients table), and if the primary key for the table were ClientID, and
if the current form's text box with this foreign key (the common field) were
called txtClientID, then the following code would work:

DoCmd.OpenForm "frmClients", , , "ClientID = " & Me!txtClientID.Value

In this case, the primary key for the RecordSource of the current form is
JobID, bound to qryJobs, so even though they now use different data sources,
it's still the primary key of the RecordSource of the _other_ form that you
need to use to identify a single record on that form.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
 
Gunny - That fixed it! Thanks a lot!

justin

'69 Camaro said:
Hi, Justin.


The primary key you are interested in is the primary key of the RecordSource
of the form you want to open. "Linked through common fields" is called a
foreign key. Your code needs to reference the foreign key on the present
form to open the other form at the correct record. For example, if the
other form were frmClients, bound to the qryClients query (based upon the
tblClients table), and if the primary key for the table were ClientID, and
if the current form's text box with this foreign key (the common field) were
called txtClientID, then the following code would work:

DoCmd.OpenForm "frmClients", , , "ClientID = " & Me!txtClientID.Value

In this case, the primary key for the RecordSource of the current form is
JobID, bound to qryJobs, so even though they now use different data sources,
it's still the primary key of the RecordSource of the _other_ form that you
need to use to identify a single record on that form.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
Click to expand...
 
Back
Top