Multiple Instances of the same table in one query

[jeffhanner10@gmail.com]

I will give it a try. Thanks again for all your help.
Jeff

[jeffhanner10@gmail.com]

Hi,
I'm still trying to get this to work. When the Query is run (outside of the form) it works and the field data is correct, Contacts.Full Name, Contacts_1.Full Name etc.
It's just that when the form fields are entered then saved, it doesn't save the Record Source info and reverts to whatever the first Contacts field in the query is.
Were you able to see that the Query worked when you got the DB?

I'm just trying to see if that provides any additional clues.
Jeff

[June7]

Yes, query works.

I am at a loss as to why the form fails.

So did you go with alternate suggestion to use comboboxes? Just reference Shelter table in form RecordSource and use comboboxes to select contacts. This is my preference anyway.

[jeffhanner10@gmail.com]

Hi
Thanks again,
I did try the combo boxes and they worked the same way. Maybe I have too much in the record source. I will try again. You've been most helpful.
Jeff

[June7]

The form RecordSource should just be ShelterTable.

Then instead of 8 textboxes for contacts, have 4 comboboxes. Combobox ControlSource will be field from ShelterTable.

RowSource: SELECT ID, [Last Name] & ", " & [First Name] AS FullName, Phone FROM Contacts;
ColumnCount: 3
ColumnWidths: 0";1";1"
BoundColumn: 1

Include Phone column if you want and then textbox can reference that column by index, index begins with 0: =[comboboxname].Column(2)

Advise not to use spaces nor punctuation/special characters in naming convention.

[jeffhanner10@gmail.com]

I'd like to thank you for all your help. I finally figured out that I needed to define my fields different that the expression building was doing. With the new syntax I could use the multiple instances of a table in my query builder for the form.

[So before the control source generated by the expression builder or me was Contact_1.FullName or [Contact_1]![FullName]. Neither of these worked and i kept getting errors.
By chance I stumbled on a video and saw that if you bracket the entire text then it works. So, [Contact_1.FullName] worked and it worked with _2 & _3.

Again, thanks for your help.

[June7]

Ah, thank you for followup. I don't have any db with this sort of requirement, so did not catch that. Odd that Access does not recognize and properly bracket.

I still prefer the combobox approach.