Solved Need expert advice regarding the table structure of Subtypes and supertypes (1 Viewer)
- Thread starter wikihow
- Start date
Minty
AWF VIP
- Local time
- Today, 14:09
- Joined
- Jul 26, 2013
- Messages
- 10,366
Not directly available - the two techniques I have seen are :
1. Change the row source of the combo on getting the focus to only have the records you want to see, then after losing focus change it back to all records to maintain the rest of forms data or
2. Have a text box placed on top of the combo that displays the correct data but hides the combo. on getting focus, you set focus to the hidden combo which is filtered correctly and after update / losing focus you hide the combo again and the text box displays the value selected.
Neither is 2 minutes work and can be a bit of a fiddle to make it look right, but will work.
1. Change the row source of the combo on getting the focus to only have the records you want to see, then after losing focus change it back to all records to maintain the rest of forms data or
2. Have a text box placed on top of the combo that displays the correct data but hides the combo. on getting focus, you set focus to the hidden combo which is filtered correctly and after update / losing focus you hide the combo again and the text box displays the value selected.
Neither is 2 minutes work and can be a bit of a fiddle to make it look right, but will work.
MajP
You've got your good things, and you've got mine.
- Local time
- Today, 09:09
- Joined
- May 21, 2018
- Messages
- 8,525
Here is a sample using technique 2. I find this more reliable than technique one. In technique one there are still times when the records will go blank in a continuous form. Works well in datasheet.
MajP
You've got your good things, and you've got mine.
- Local time
- Today, 09:09
- Joined
- May 21, 2018
- Messages
- 8,525
Here discusses both methods
I think I show in this discussion that you still can get "vanishing" in certain settings using technique 1.
Semi Unique Combox Record Source on Continuous Form
I have a main form which tracks Task Information based on tblTask. There is a subform based on junction table tblTaskDetail. sfrmTaskdetail is a continuous form with a comboBox that looks up AssetID/SerialNumber. I found another post from this forum which was close to my issue...
www.access-programmers.co.uk
Update - 22/11/20:
I think, I will be going with method of exporting the query as a new table and work on it. thanks
Removing the attachment.
Hello again everyone,
I have been slow in updating my database but have made some progress this month. I am again stuck on a issue and seek advice for the same. Before I tell my issue, I would clear some info about the database.
The setup we are using is this database is about construction projects where multiple apartments or units are in the project.
Some unit are assumed identical hence grouped Type A or Type B and so on. Each unit have a number or unit name like G01, G02, 101, 102 etc.
The tables and the relation setup is as shown in image (Thanks to MajP, I got a lot of help from him in setting all up)
In this setup we just enter data for a unit type group and in reports etc, same data is displayed for each individual unit. Entering data for a group is a very time saving instead of entering data for each individual unit.
Table "tbl_Items_To_Units" is the location where we add individual product to a room. If we add a wardrobe in a unit type, it will be displayed in each individual unit.
For example if Unit type A have 3 units G01, G02, 101. If we add a wardrobe in Unit type A, it will be shown in all 3 apartment units.
This generates 3 records upon querying:
Type A - G01 - Bed 1 - Wardrobe
Type A - G02 - Bed 1 - Wardrobe
Type A - 101 - Bed 1 - Wardrobe
Now the issue is when we go ahead on the procedure. Now we need to add additional data for each Wardrobe, which is unfortunately not identical. I setup a table as tbl_MatrixData and linked it with "Units to ItemID"
Type A - G01 - Bed 1 - Wardrobe --- measure date 14th --- Installed 18th
Type A - G02 - Bed 1 - Wardrobe --- measure date 15th --- Installed 19th
Type A - 101 - Bed 1 - Wardrobe --- measure date 16th --- Installed 20th
The problem now is that the records are being grouped for the 3 units here and the matrixData is not being linked with a unique ID. Shown below in image. The Master link is grouping the records for all 3 type of units.
I am not a professional and learning. I need suggestions on how I can link "MatrixData" table with a unique ID so that the records are not attached to a group.
What I thought that I can export a query with records to a new table (the top part of form) with an autonumber as primary key.
This actually works, but can cause problem if someone wants to make changes to Previously entered data. It is not convenient to re-export table after any changes.
Please advice.
Thanks in advance.
(Yes, also this is my first database, and it seems complicated for a newbie like me. It is possible that there are many errors or wrong formats in database. Please bear with me for my errors.
I think, I will be going with method of exporting the query as a new table and work on it. thanks
Removing the attachment.
Hello again everyone,
I have been slow in updating my database but have made some progress this month. I am again stuck on a issue and seek advice for the same. Before I tell my issue, I would clear some info about the database.
The setup we are using is this database is about construction projects where multiple apartments or units are in the project.
Some unit are assumed identical hence grouped Type A or Type B and so on. Each unit have a number or unit name like G01, G02, 101, 102 etc.
The tables and the relation setup is as shown in image (Thanks to MajP, I got a lot of help from him in setting all up)
In this setup we just enter data for a unit type group and in reports etc, same data is displayed for each individual unit. Entering data for a group is a very time saving instead of entering data for each individual unit.
Table "tbl_Items_To_Units" is the location where we add individual product to a room. If we add a wardrobe in a unit type, it will be displayed in each individual unit.
For example if Unit type A have 3 units G01, G02, 101. If we add a wardrobe in Unit type A, it will be shown in all 3 apartment units.
This generates 3 records upon querying:
Type A - G01 - Bed 1 - Wardrobe
Type A - G02 - Bed 1 - Wardrobe
Type A - 101 - Bed 1 - Wardrobe
Now the issue is when we go ahead on the procedure. Now we need to add additional data for each Wardrobe, which is unfortunately not identical. I setup a table as tbl_MatrixData and linked it with "Units to ItemID"
Type A - G01 - Bed 1 - Wardrobe --- measure date 14th --- Installed 18th
Type A - G02 - Bed 1 - Wardrobe --- measure date 15th --- Installed 19th
Type A - 101 - Bed 1 - Wardrobe --- measure date 16th --- Installed 20th
The problem now is that the records are being grouped for the 3 units here and the matrixData is not being linked with a unique ID. Shown below in image. The Master link is grouping the records for all 3 type of units.
I am not a professional and learning. I need suggestions on how I can link "MatrixData" table with a unique ID so that the records are not attached to a group.
What I thought that I can export a query with records to a new table (the top part of form) with an autonumber as primary key.
This actually works, but can cause problem if someone wants to make changes to Previously entered data. It is not convenient to re-export table after any changes.
Please advice.
Thanks in advance.
(Yes, also this is my first database, and it seems complicated for a newbie like me. It is possible that there are many errors or wrong formats in database. Please bear with me for my errors.
Last edited:
