Use macro to add new linked record ACC07

[Penn]

Q: Is it possible to use a macro to open a new record that will be linked to
a record in another table?

Detail: I have two tables - Customers & Contacts. The Contacts are linked to
the Customer by the CustomerID. I have a Customer Details form which
contains a Contacts Subform. The Contacts Subform lists (in Datasheet view)
all the Contacts associated with that Customer.

When I double-click on an existing Contact, I can bring up the Contact
Details form to edit the full Contact information.

I would like to be able to use the same macro to open a new record in the
Contact Details form which will have the correct CustomerID. I have
CustomerID fields in the Contacts Subform, the Contact Details form and the
Query that provides records for the Customer Details form.


Penn

 

[Clifford Bass]

Hi Penn,

I am a little confused. Are you saying you want to modify the existing
macro so that instead of editing the current information it positions to a
new record? If so, just add a GoToRecord, ... , New item after you open it.
Or simply open it in Add mode instead of Edit mode. If that is not what you
want and it does not help, please clarify.

Clifford Bass

 

[Penn]

Chifford

Thank you for your response. I am not editing an old record, I am adding a
new one. The problem is that I don't know how to guarantee that the correct
CustomerID number gets into the new Contact record. All the Contacts are
(must be) linked to a Customer record (one to many).

Does that make sense?

Penn

 

[Clifford Bass]

Hi Penn,

I think it does. Let me rephrase to see if I understand. A user
double-clicks on any record in the subform and up pops the Contact Details
form into which the user enters a new contact. Then the user does something
which is supposed to return the new contact number/ID to the Contacts
subform, in a new row. Is that right? Some questions: Are you opening the
Contact Details form in add mode or edit mode? Can the user enter more than
one contact in the Contact Details form? If so, how do you know which one
add into the Contacts subform? Or do you want all of them added? What is it
that the user does to tell it to place the new contact into the Contacts
subform? Do you provide for the ability to add a contact and then cancel,
such as with a cancel button? Can the user actually add a contact, but then
cancel, in which case the contact would exist in the table, but not be added
into the Contacts subform?

Clifford Bass

 

[Penn]

A user double-clicks on any record in the subform and up pops the Contact
Not exactly. If the user double clicks on an existing Contact in the
subform, that Contact's record pops up for editing.

If the user double clicks on an empy (new) row in the subform, a new record
pops up. (using Add)

When the user "save and exit"s the record, it goes into the Contacts table.
A subform refresh populates the subform (I hope)

Add, if it's a new Contact. Edit if it's an existing Contact.

No. Only one new Contact at a time. If they want to add another one, they'll
have to double click on the blank (new) row again in the subform. (at least,
unless that becomes a problem - one hurdle at a time...)

Nothing.

Good points. I hadn't gotten there yet.

Penn

 

[Clifford Bass]

Hi Penn,

Based on your answers I would suggest doing an After Update event on
the Contact Details form. In that event you would set the contact ID/number
in the appropriate field in the subform to the newly created contact's ID.
So:

SetValue action
Item something like [Forms]![Contacts Subform]![txtContactID]
Expression something like [txtContactID]

Where txtContactID is the field that holds the contact ID in both of
the forms. Now, if you are using a combo box instead of a text box to hold
the contact ID, you will need to do a requery on the subform's combo box
first before you can set it:

Requery action
Control Name [Forms]![Contacts Subform]![cbContactID]

Alternatively, you probably could put this in the Contact Details On
Close event.

Note that if you use that form in other contexts, or on its own, you
will have trouble because it will be trying to do stuff with that contacts
subform. But that is a bridge to be crossed only if needed.

Hope that helps,

Clifford Bass

 

[Penn]

Very cool. Thank you for stickinw with this. I didn't realize you could get
at events in macros. I'll play with it tomorrow but your ideas should work
fine. Both the ContactID and the CustomerID are in hidden or non-displayed
textboxes. Generally, I don't like the users even knwoing that they exist.

Penn

Based on your answers I would suggest doing an After Update event on
the Contact Details form. In that event you would set the contact
ID/number
in the appropriate field in the subform to the newly created contact's ID.
So:

SetValue action
Item something like [Forms]![Contacts Subform]![txtContactID]
Expression something like [txtContactID]

Where txtContactID is the field that holds the contact ID in both of
the forms. Now, if you are using a combo box instead of a text box to
hold
the contact ID, you will need to do a requery on the subform's combo box
first before you can set it:

Requery action
Control Name [Forms]![Contacts Subform]![cbContactID]

Alternatively, you probably could put this in the Contact Details On
Close event.

Note that if you use that form in other contexts, or on its own, you
will have trouble because it will be trying to do stuff with that contacts
subform. But that is a bridge to be crossed only if needed.

Hope that helps,

Clifford Bass

Not exactly. If the user double clicks on an existing Contact in the
subform, that Contact's record pops up for editing.

If the user double clicks on an empy (new) row in the subform, a new
record
pops up. (using Add)


When the user "save and exit"s the record, it goes into the Contacts
table.
A subform refresh populates the subform (I hope)


Add, if it's a new Contact. Edit if it's an existing Contact.


No. Only one new Contact at a time. If they want to add another one,
they'll
have to double click on the blank (new) row again in the subform. (at
least,
unless that becomes a problem - one hurdle at a time...)


Good points. I hadn't gotten there yet.

Penn

 

[Penn]

OOps. Just had a look at Access and realized how stupid my last comment was.
The events are all there just waiting patiently for me to do something with
them. Oh well. Been a long day...

Penn

 

[Clifford Bass]

Hi Penn,

You are welcome! Go home, relax, clear your brain, and tackle it
tomorrow.

Clifford Bass