Assistance with Split/ Main, Subform creation please 1

[mearmortal]

It's been 10 years since I worked on Access and now a different version and I've lost some information on creating Main Subform or Split form stuff. Old age I guess.

I've got 6 tables all working, relationships created and everything looks good.
The main table is made up of data from three of the others.
Appart from the unique ID, the first field in the main table will be selected from a drop down list, and will remain the same value for the next say, 30 entries in the main table.
The second and third field will change values based on drop down selection for each of the entries in the main table.

Now I've tried creating Main/sub forms against tables, selecting just the main table, and another selecting the main table and different combinations of multiple tables and I can't get them to work.
I'm thinking I'm going to need to use a query, but not sure as using this method pretty much produces the same results.
I'm thinking it's a layout issue on the form, postions of field etc in different sections, Header, Detail, Footer, or something else.

BTW: I've created a datasheet view, and I can add records using all three drop down selection boxes, but as you can imaging, entering the same value for one field all the time for thirty enteries is a tad anoying.

Can someone assist please.

 

[dhookom]

I typically use single tables as record sources for each form/subform. You stated "can't get them to work" which doesn't provide any details that might assist us in helping you.

You can use a little code to set the default value of a control in that control's after update event.

Duane
Hook'D on Access
MS Access MVP

 

[mearmortal]

Thanks for the response, so here goes.

I'll explain the table structure first, and see what you think.
Table 1 is a list of Systems.
Table 2 is a list of Stations, belonging to a system, so there is a 1-m relationship with several stations in any 1 system.
Table 3 is a list of material types.
Table 4 is a list of materials belonging to a material type, so theres a 1-m relationship with several materials in any 1 material type.
Table 5 is a options with 2 options listed.
Table 6 is the main table, containing, IDs for Stations, Materials and Options.

What I would like is the main form to allow me to select the system based on a drop down selection of Systems, from that Select a station based on a drop down selection of stations, that are contained in the system selected.
From that I want to be able to enter ia a table a list of materials (drop down selection), and assign an option for that material, and I want to be able to enter around 20+ materials against system/station record.

Could you recommend a way this can be done either through Queries or through Tables, and if there are any special fields I need to populate.
I'm using Access 2010.

When I try to create a Main/Subform using the Systems, Stations and Main tables, there are three sections however I have to physically move between records using the navigation keys, I can't add a combo box to select any records individually. the first two sections although the deta is right, the format is wrong, if I could get it just to two sections that would be better.
I have data in the main table already along with all the others and when I get to the right record in the form all the raw data is there, in numbers and it looks ok, so I know the data is right, I just can't get the form to do what I want.

Thanks for reading and taking the time.

 

[dhookom]

Any combo box you want to use for searching should be unbound (nothing in the control source).

There are lots of resources on the web for implementing cascading combo boxes.

Let us know when you have these couple points resolved and let us know if you have questions.

Duane
Hook'D on Access
MS Access MVP

 

[mearmortal]

Finally after several hours of painstakingly trying different methods, I now have two sets of two combo boxes selecting values, Systems against Stations and Material Types against Materials.

I have another combo box 'Options' selecting an option based on tblOptions, Fields OptionsID and Options.

Here's the event info:
Private Sub cboMaterialTypes_AfterUpdate()
cboMaterials.RowSource = "SELECT tblMaterials.tblMaterialID, tblMaterials.tblMaterialName " & _
"From tblMaterials " & _
"Where tblMaterials.MaterialTypeID = " & cboMaterialTypes & _
" ORDER BY tblMaterials.MaterialName"
End Sub

Private Sub cboSystems_AfterUpdate()
cboStations.RowSource = "SELECT tblStations.StationID, tblStations.StationName " & _
"FROM tblStations " & _
"WHERE tblStations.SystemID = " & cboSystems & _
" ORDER BY tblStations.StationName"
End Sub


Now since the form is not related to any table, this information is all well and good, but I don't have a clue now on how to populate the values here, in tblCommodities. tbl Commodoties has fields, CommoditiesID, SystemsID, StationsID, MaterialTypeID, MaterialID and OptionsID.
I did try and associate the form with tblCommodities, but that didn't work.

Can you assist please.

 

[mearmortal]

Hello,
I've read around that this might work: -

INSERT INTO tblCommodities (CommoditiesID, SystemID, StationID, MaterialTypeID, MaterialID,
OptionID) Values (CommoditiesID cboSystems, cboStations, cboMaterialTypes, cboMaterials, cboOptions)


I'm also unsure where to put this code or if I should convert it to this: -


Dim SQL As String

SQL = "INSERT INTO tblCommodities (CommoditiesID, SystemID, StationID, MaterialTypeID,
MaterialID, OptionID) Values (cboSystems, cboStations, cboMaterialTypes, cboMaterials, cboOptions)"

DoCmd.RunSQL SQL


However CommoditiesID is based on auto number, how do I increment this without actually knowing it's value before I write the data?

I would Appreciate any assistance you could offer please.

 

[dhookom]

It's ultra confusing when you attempt to hide table names behind numbers (1-6) and then later expect us to map the numbers to the names. It makes much more sense to provide actual table and field names as well as how they are related. Anything less and my mind begins to shut down.

If you want to have defaults set for data entry, you can either use the link master/child from a main form to a subform or add a little code that uses the after update event of controls on the main form to set the DefaultValue property of controls on the subform. Is this what you want to do?

Duane
Hook'D on Access
MS Access MVP

 

[mearmortal]

Here you go
tblCommodities contains:

CommoditiesID - AutoNumber​

SystemID - Number (1-M tblSystems to tblCommodities)​

StationID - Number (1-M tblStaions to tblCommodities)​

MaterialTypeID - Number (1-M tblMaterialTypes to tblCommodities)​

MaterialID - Number (1-M tblMaterials to tblCommodities)​

OptionID - Number (1-M tblOptions to tblCommodities)​

tblSystems contains:

SystemID - AutoNumber​

SystemName - Text​

tblStations contains:

StationID - AutoNumber​

StationName - Text​

SystemID - Number (1-M tblSystems to tblStations)​

tblMaterialtypes contains:

MaterialTypeID - AutoNumber​

MaterialType - Text​

tblMaterials contains:

MaterialID - AutoNumber​

MaterialName - Text​

MaterialTypeID - Number (1-M tblMaterialType to tblMaterials)​

tblOptions contains:

OptionsID - AutoNumber​

Options - Text​

First thing to do was to be able to use combo boxes to form the imput, but due to the sheer number of Statios, and Materials, I had to find a way to reduce the list, so by relating stations to systems and materials to Material types, I could reduce the available options in the combo boxes significantly.
However the use of cascading combo boxes, presented the issue of the unbound form for data entry.
Here's the two combo box Statements.

CboMaterialsTypes has row source: tblMaterialTypes
with Event Procedure:
Private Sub cboMaterialTypes_AfterUpdate()
cboMaterials.RowSource = "SELECT tblMaterials.MaterialID, tblMaterials.MaterialName " & _
"From tblMaterials " & _
"Where tblMaterials.MaterialTypeID = " & cboMaterialTypes & _
" ORDER BY tblMaterials.MaterialName"
End Sub

CboSystems has row source: tblSystems
with Event Procedure:
Private Sub cboSystems_AfterUpdate()
cboStations.RowSource = "SELECT tblStations.StationID, tblStations.StationName " & _
"FROM tblStations " & _
"WHERE tblStations.SystemID = " & cboSystems & _
" ORDER BY tblStations.StationName"
End Sub

cboOptions has row source:
SELECT [tblOptions].[Options ID], [tblOptions].[Options] FROM tblOptions ORDER BY [Options];

cboStations & cboMaterials are both filled in using the event procedures.

Now with this in mind, I now have a form with a list of selected options albeit the numbers hidden behind the names shown (column count 2, width 0,5 where column 1 is the ID number)

So what now need to know is how to take that information off the form and into tblCommodities.
I'm thinking of a buttom to save it, but I'm struggling with the 'how'?
the tblCommodities has the Primary Key CommoditiesID, and I'll need to know how to start populating the data, with 1) empty table, and 2) going back time and time again to add more.

I wanted to populate this table with a list of records I could query later by Material, hope that makes it clearer and is not information overload.

 

[dhookom]

[pre]tblCommodities contains:
CommoditiesID - AutoNumber
SystemID - Number (1-M tblSystems to tblCommodities)
StationID - Number (1-M tblStaions to tblCommodities)
MaterialTypeID - Number (1-M tblMaterialTypes to tblCommodities)
MaterialID - Number (1-M tblMaterials to tblCommodities)
OptionID - Number (1-M tblOptions to tblCommodities)[/pre]

If a station is always related to a system, why do you need to store SystemID in tblCommodities? Can't you determine the SystemID based on the StationID?
The same is true for MaterialTypeID which should be determined from tblMaterials.

I would have combo boxes on the main form that allow drilling down to StationID and MaterialID. The subform would have a record source of tblCommodities. Use the StationID and MaterialID combo boxes as the Link Master properties of the subform. Every record you add to the subform will have default StationID and MaterialID values from the main form.

Duane
Hook'D on Access
MS Access MVP

 

[mearmortal]

OK, Can I keep the combo boxes I have with the dropdown's reducing the selection critera, and use then the StationID & MaterialID from cboStations and cboMaterials.
Then using(as you say) link Master properties, of the subform, use these values for two of the entries, and then just the options to then be selected?
I should be able to reduce the tblCommodities to

tblCommodities contains:

CommoditiesID - AutoNumber​

StationID - Number (1-M tblStaions to tblCommodities)​

MaterialID - Number (1-M tblMaterials to tblCommodities)​

OptionID - Number (1-M tblOptions to tblCommodities)​

If you agree, I'll have a go and update later.

 

[dhookom]

Mearmortal,
I think you have the correct idea. I don't see any reason for additional fields in tblCommodities.

Duane
Hook'D on Access
MS Access MVP

 

[mearmortal]

tblCommodities now reduced, easy.
tblCommodities subform created with record source:
SELECT [tblCommodities].[StationID], [tblCommodities].[MaterialID], [tblCommodities].[OptionID] FROM tblCommodities;
I think this is wrong, as when I look at the first two records, there's nothing against StationID, MaterialID.

I was having trouble creating the sub form, because the main form was not bound.

The property sheet for the subform from within the main form reads:
Source object: Table.tblCommodities
Link Master Fields:StationID, MaterialID
Link Child Fields:StationID, MaterialID.

However I think this is wrong too, as I don't think it links to the field values on the form frmCommodities
I couldn't get it to link to the form.

Could you explain how this is done?
Is it something like frm.Commodities.stationID, frm.Commodities.materialID
I was hoping the changes made in the main form would auto populate the sub form fields, but as yet this doesn't happen.

 

[dhookom]

As stated earlier "Use the StationID and MaterialID combo boxes as the Link Master properties of the subform". This means the name of the combo boxes. I assume the names of the combo boxes are something like "cbo..."

Duane
Hook'D on Access
MS Access MVP

 

[mearmortal]

Great, now working as expected with some dummy data in the table, Thanks for all your efforts and persistance in assisting me to get this right.