Help with Data into 2 tables

[Ian Baker]

I have 2 tables one for a child and one for parents of that child.
Table Child:
ChildID, ChildName, ContactType, ContactID etc....

Table Contact (the parents table)
ContactID, ContactName, Address, City etc....

I have created a query qryChild of:
SELECT Child.ChildID, Child.ChildName, Child.ContactType, Child.ContactID,
Contact.ContactID AS MainContactID, Contact.ContactName, Contact.Address
FROM Child LEFT JOIN Contact ON Child.ContactID = Contact.ContactID
WITH OWNERACCESS OPTION;

I have a form called Child bound to qryChild where I want to view/enter
children and in a combo box for ContactName either:
1. Enter a new parent name therefor causing a new record in the Contact
table and as I complete the address etc fields this info is going into the
new contact record
2. Or select an existing parent in the ContactName combo box which would
therefor display their details in the address etc fields

The form has to also bind the Child.ContactID to Contact.ContactID

I am having all sorts of trouble trying to do this so your advice on how to
do it is greatly appreciated.

 

[Mingqing Cheng [MSFT]]

Hi Ian,

From your descriptions, I understood that you would like to make an
automatically insert and then display existed information when imputing an
existing id.Have I understood you? If there is anything I misunderstood,
please feel free to let me know

Based on my scope, we could do it with Event AfterUpdate and DLookup for
all fields you want. You need to use IsNull to judge whether it is an
existed one or new one. I would like to make a samll demo to you tomorrow
if this could help you better. However I need your feedback whether I have
fully understood you. It is also highly appreciated if you could make a
sample if I have misunderstood.

Thank you for your patience and cooperation.


Sincerely yours,

Mingqing Cheng
Microsoft Developer Community Support

 

[Ian Baker]

Hi Mingqing
Thankyou for your response. I have taken you up on your offer to send you a
"scaled down" copy of the db and have just now emailed it to you.

Thanks again

 

[Mingqing Cheng [MSFT]]

Hi Ian,

Thanks for you kindness for doing so.

Just a kind reminder that my email is (e-mail address removed)
(remeber to remove the word 'online' as it is only for SPAM, Thanks)

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!


Sincerely yours,

Mingqing Cheng
Microsoft Developer Community Support

 

[Steven Parsons [MSFT]]

Hi Ian -

I have been asked to review you Access 2000 database containing the Child
form within the sample that you provided. I have a question or two, and it
is probably expedient for me to email you directly, so I hope you do not
mind.

In your original email, you state that you want to either select an
existing contact name from a ContactName combo box, or enter a new name.
When looking at the sample that you provided, I do not see a ContactName
combo box on the Child form. I see a ContactID combo box. Maybe you meant
to actually say ContactID combo box, instead of ContactName combo box?

It sounds like you simply want to populate all of the other Contact fields
on the Child form when an existing contact name is chosen. That should be
possible with the use of an AutoLookup query.

Also, I understand that if a name is entered, but it does not match an
existing contact name, then the user would be required to populate the
Contact fields, and these values in turn, would be saved back to the
Contacts table.

If my understanding is correct, please supply me with the name of the
actual combo box on the Child form that you wish to use for achieving this
objective. I see three combo boxes on the Child form. I want to make sure I
know which one you wish to use so that I do not mistakenly use the wrong
one.

My contact information listed is below. Please feel free to call me direct
or reply to this email if you prefer.

Thank you,

- Steven Parsons
- Microsoft Access Product Support Services

I am available Monday - Friday, 7:00 a.m. to 4:00 p.m., Eastern Time (12:00
- 21:00 GMT). I can be reached at 980-776-8056 or
mailto:[email protected].

Satisfied Customers is our top priority. We welcome your comments and
suggestions on how we can improve the support that we provide to you. If
you have suggestions,
mailto:[email protected]?subject=Developer_Support_Feedback:Access

Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

Microsoft Security Announcement: Have you installed the patch for Microsoft
Security Bulletin MS03-026? If not Microsoft strongly advises you to
review the information at the following link regarding Microsoft Security
Bulletin MS03-026
http://www.microsoft.com/security/security_bulletins/ms03-026.asp and/or to
visit Windows Update at http://windowsupdate.microsoft.com to install the
patch. Running the SCAN program from the Windows Update site will help to
insure you are current with all security patches, not just MS03-026.

 

[Ian Baker]

Hi Steven
I emailed a reply to you
Regards
Ian