relating fields from one table to a form

[ljmellor]

I am very new to Access. I am using Access 2007 and trying to create a very simple database which tracks inventory, contacts and events. I have first name, last name and company fields in my event form which I would like to automatically populate from my contact form. Is there a way to either; start typing a name in the event form and it would be found in the contacts form or if it isn't found I could add it as a new record to the contact form? Or is it possible to, when in the first name field of the event form, it would list the names in the contact form and if selected it would be filled into the event form (first name, last name and company)?

[June7]

Don't save the contact info in the events table, just the contact table primary key ID. Use a combobox to select contact. If contact not in the dropdown list, use NotInList event to add new contact record.

Review:
http://datapigtechnologies.com/flash...combobox3.html
http://support.microsoft.com/kb/197526

[ljmellor]

Thanks June7 - appreicate the help! Works great, one question tho', in my bound box (created from a query with "first name", Last Name" and Company", is it possible to have all three field show after having been selected?

[June7]

Concatenate the fields in the RowSource sql. The result will be one field instead of the 3 separate so adjust combobox properties accordingly.

SELECT ID, [Last Name] & ", " & [First Name] & " : " & Company AS Customer FROM tablename ORDER BY [Last Name], [First Name];

[ljmellor]

I'm sorry, but does "concatenate the fields in the RowSource sql" mean to put it into Visual Basic?

[ljmellor]

I found where to put the code you sent, however, when I launch the form and click on the "combo box" I get this message "the record source '~sq_cEvent~sq_cCombo55' specified on this form or report does not exist"?

[June7]

My suggestion was to use that SQL statement in the combobox RowSource property, has nothing to do with VBA. I have no idea what you actually did.

[ljmellor]

Yes, I did figure out how to get to the SQL statement in the combo box row source property and put in: SELECT ID, [Last Name] & ", " & [First Name] & " : " & Company AS Customer FROM tablename;
when I went back to the form view and clicked on the box was when I got this message "the record source '~sq_cEvent~sq_cCombo55' specified on this form or report does not exist"?

[June7]

Did you use your real field and table names in the SQL as opposed to the generic references in my example? Is ID name of the unique identifier field? I presume the real table name is not 'tablename'.

[ljmellor]

Got it!. . .. .apparently just a misspelling, appreciate all the help!

[ljmellor]

ok, now I have my list of contacts showing on a record, when I go to select one, that contact is put into all of the records? Any suggestions?

[June7]

Must set combobox ControlSource property to the field that the value is to be saved in. Value selected for an unbound control will be reflected in all instances of the control - it is, after all, only one control.