Subform Automatically changes every record to first item from the recordsource

[johnv]

I have a bunch of subforms on the main form that pull from independent tables as the recordsource. When I try to add a new record in the 'Casework&Built-inBySpace_Table', I pull the combobox down and select one of the notes and it automatically changes it to the '18" deep countertop' no matter what I type/select. I have attached the database for reference.Police Programming Database.zip

[davegri]

Police Programming Database-davegri-v01.zip
See attached. Added all the space tables to the relationship diagram.
BTW, you don't need all those queries for the rowsource of you combobox lookups.
Instead of SELECT ......FROM the query
Just
Select ..... FROM the table.
Delete the query unless it is used elsewhere.

[johnv]

I am still having the same issue with your copy. The issue is when i try to use the combobox, no matter what I select, it always defaults to the first item in the list and I cannot change it.

As for the queries, the query was only used to keep the "units" value associated with the note. I stored the units value within the lookup of the note. Is there a better way to do this?

[davegri]

I am still having the same issue with your copy.

Maybe we're not looking at the same form. What form is it?

[johnv]

In the "Rooms_Mainform" There are subforms below. On the 2nd tab (Arch Notes) There is a "Casework + Built-in" subform shown as a datasheet. All of the subforms are built the same way but that is the only one I have added notes to the record source. I want to be able to select an option from the combobox here.

[davegri]

OK. I see. Change the bound column from 3 to 1.

[johnv]

So that solved one problem but created another. The reason it was set to 3 was because the "units" column was being pulled as well. I believe I am doing this wrong based on your previous column about getting rid of the queries. How do I take a row in the record source and bring multiple columns into the subform so that when I set the note, it automatically associates it with the same units?

[davegri]

Don't see the problem... works fine for me.

[johnv]

That column works great now. The "Units" column should read "lf" for all 3 records but instead is reading the "ID" field from the record source.

[davegri]

Police Programming Database-davegri-v02.zip
See attached. I have Furniture and Casework subforms working.
There are many changes. Take a close look at relationships. You will have to make the remaining subforms, tables and queries mimic casework and furniture.
Make note of modifications to the queries for the subform recordsources, and the combobox rowsources, as well as the combobox properties. The furniture subform had property DataEntry=Yes. You don't want that in the others.
Some duplicated fields have been removed, and some moved to another table. Table lookup have been removed. Calculated fields in tables have been removed (from the tables that I worked on, there are probably others).

[ssanfu]

I would suggest you consider fixing other major (IMHO) errors in the table designs and field naming.

You are using special characters in object names. Exanple: Casework&Built-in_Table (ampersand and dash)
There are spaces in object names. Example: "Space Name"
-------------
Object names should be letters and numbers.
Do not begin an object name with a number.
NO spaces, punctuation or special characters (exception is the underscore) in object names
-------------
## Objects include fields, tables, queries, forms, reports and Modules. ##



Design errors:

The tables in the RED area are NOT linked correctly.



Look at these tables

They all have the same structure. I would have 1 table with 1 more field for the entry/record type (Areas. Display, Acoustics, Plumbing, etc)

It really is a bad design (and confusing) when all of the tables have "ID" as the name of the PK field and the FK fields in other tables are different names.




This one I am on the fence about. I kind of understand, but it just looks wrong!
1 table linked to itself 3 times.......

[johnv]

I understand your suggestions and they are very good to note when naming tables, I am quite new to Access and database design in general but these changes are easy to standardize in the process so I will make them. As for the adjacency relationship, the goal is to be able to have a combo box of "spaces" that already exist in the project and be able to pick which spaces relate to one another and should be adjacent. I don't want the user to be able to manually type a space that does not already exist.