Append To A Form?

[Eviscerator]

Hello everyone, I hope you can help me with this!



Basically in the database I'm working on, we have organizations and named individuals within those organizations. The organizations have 2 identifiers, their name and their reference number. I'm trying to create a form for adding new named individuals which will accept either of their organization's identifiers and automatically fill in the other.

E.G:
Dave Smith -named individual
Unlimited Telecoms - Organization.*
123 - Organization Ref*
*When either of these is filled out, the other will automatically complete.

Sadly, it's important we have both identifiers attached to each individual's record.

I've had this problem before and solved it by using a long kludge of if commands in VBS, but this time I'd like to do it properly, so the system can be updated without needing to type in new code.

Ideally, I need something like an append query. I currently have a query set to bring up the organization's name from their ref (query Criteria being [forms]![newnamedcontact]![RefNum]), which brings up the organization's name from their reference number no problem.
The issue is then getting that name back into the Organization name section on the form. Append queries will only allow me to send the data directly to the table, which is fine except I'm not sure how to make sure the data is added to the correct record.


So to summarise, I need a way to search a table using data from a form, and then place different data from that query’s result back into a different section of the form.

Can anyone help?

P.S
Using access 2007 for this one.

[jzwp11]

...2 identifiers, their name and their reference number...

Does the organization have more than 1 reference #/name combination?

Am I correct to assume that only 1 reference #/name combination can be applied to a person within that organization?