Referencing other tables without turning data source into query?

[securitywyrm]

For simplification, I have a table that has names and addresses, and a form that shows that table info. I have a second table that is a list of states and the state bird.

In order to show both address and state bird, I opened up data source and had it select both tables, and linked state in table 1 to state in table 2. This let me put state bird into the form, and it worked... BUT... I then couldn't edit any data or create new records, because the data source had become a query.

So as I understand it, I can only have one table worth of data in a form if I want to be able to edit it. What I'm trying (and failing) is to use a sub-form that has a data source of a query to get the relevant information, and then have a field in the main form reference the data from the sub-form. Is there something I'm missing?

[June7]

Set the query link as "Show all records from [names and addresses table] and only those from [states] that match"

Bind textbox to bird field and set it as Locked Yes and TabStop No. Fields from [names and addresses table] should be editable.

What I don't understand is 'main form reference the data from the sub-form' - reference what data and why?

[securitywyrm]

Set the query link as "Show all records from [names and addresses table] and only those from [states] that match"

Bind textbox to bird field and set it as Locked Yes and TabStop No. Fields from [names and addresses table] should be editable.

What I don't understand is 'main form reference the data from the sub-form' - reference what data and why?

I think I understand. I've created a subform which has a data source of the query linking the address table state to bird table state, and in the subform I have a box showing just the bird, and it works. However, that means the bird box is 'trapped' inside the subform. How can I get the data from the bird box into a box in the main form?

[securitywyrm]

I think I got it! Open a blind box in the main form, open the wizard for data source, go to the subform fields, double click relevant one, and then I can hide the subform from view while having the data in the 'main' form!

[June7]

I don't understand purpose of this subform. What is the RecordSource of main form? Why don't you just use the described query as RecordSource for main form?

By 'blind' box do you mean UNBOUND?

[securitywyrm]

I don't understand purpose of this subform. What is the RecordSource of main form? Why don't you just use the described query as RecordSource for main form?

By 'blind' box do you mean UNBOUND?

The purpose of the subform is to be able to have a query pull that data using data in the main form. If I use a query in the recordsource, it won't let me edit anything because you can't edit data out of a query.

And yes, i meant unbound.

[June7]

Yes, queries can be editable if they are designed properly. My suggestion should work if I correctly understand data relationship.

An alternative method which would not require the joined tables query involves a combobox. Does main form have a combobox for selecting state? Make it a multi-column combobox that has a column for the bird field (can be hidden). Then expression in a textbox references the bird column by index. Index begins with 0 so if bird is in column 2 its index is 1:

=ComboboxName.Column(1)

There is even a third option using DLookup() domain aggregate function.

Really don't need complication of a hidden subform.

[securitywyrm]

Unfortunately I think you're wrong about how queries work. The solid rule I see is that you can't edit data through a query. You can only edit one table's worth of data at a time, and have to have that table as your data source.

[CJ_London]

you can try changing the form recordsource type to dynaset inconsistent updates.

but beware trying to edit fields from more than one table - so ensure the controls for state and bird are locked.

Another option is to not bring through your state and bird table in your recordsource at all. Just use a combobox for state in your main table with a rowsource of your state table to include the bird, then have an unbound textbox with a controlsource something like

=cboState.column(2)

[June7]

No, I am not wrong about editing data through query that includes multiple tables. I built a db that depends on this and it has been performing perfectly for 10 years.

Many developers prefer to use a query as form RecordSource, even when there is no table join, one reason being they may be doing calcs they want displayed and they prefer calcs in query instead of textbox.

I have never had to modify the form's recordsource type. Always left default Dynaset. Here is example of a query I have no problem editing, even adding a new record to the Games table. The Teams fields I would not want to edit in this query.

SELECT Games.StartDate, Games.StartTime, Teams.Division, Teams.PlayLevel, Games.HomeTeam, Games.AwayTeam, Games.Plate, Games.Base
FROM Teams RIGHT JOIN Games ON Teams.[TeamNumber] = Games.HomeTeam
ORDER BY Games.StartDate, Games.StartTime, Teams.Division;


However, that point aside, 2 methods already suggested by 2 replies that don't require query that joins tables and also eliminate hidden subform.