create table rows using combobox (1 Viewer)

[SteveAccess93]

Good afternoon all,

I have an access tracking software which we use at work for a windows manufacturer.

We have a form, and within the form we have a combo box 'Combo128' which allows us to select the product type 'Window', 'Door', 'Commercial Door' and 'Curtain Wall.

We also have a sub-form called 'Ordering' which uses a table 'PurchOrders' which we update with all the purchase orders for that particular product 'Ali', 'Hardware' etc.

Please could someone tell me whether it would be possible if we could automatically populate the table, depending on the product type?

Im a novice with Access but the best way to explain it would be... If Combobox = "Window" then add 5 Rows. Supplier ID 'To Buy', Description 'Ali, 'Hardware', Sealed Units' 'Panels' ' Teleflex'.

Apologies for the lack of information.

 

[MarkK]

Please could someone tell me whether it would be possible if we could automatically populate the table, depending on the product type?

Yes, this is possible.

 

[SteveAccess93]

Yes, this is possible.

Thanks Mark!

Would you mind guiding me in the right direction or explain how this can be done please?

Much appreciated!!!

 

[Gasman]

Combo128?
Why not give it a more meaningful name, perhaps cboProduct?

Anyway I might approach it by storing the required data in a table, then append that data to your table. So if a window has five rows, then those five rows are added to your table with what data you have, leaving the user to complete the rest. That way if it changes to 4 or 6, easy to amend via data. Would need an 'active' field to do that.

 

[SteveAccess93]

Combo128?
Why not give it a more meaningful name, perhaps cboProduct?

Anyway I might approach it by storing the required data in a table, then append that data to your table. So if a window has five rows, then those five rows are added to your table with what data you have, leaving the user to complete the rest. That way if it changes to 4 or 6, easy to amend via data. Would need an 'active' field to do that.

Hi Gasman,

Thank you for the advice.

Would you be able to explain how I would code this?

Thanks,

Steve

 

[MarkK]

Steve, where, exactly, are you stuck? Say you set off to start coding this. Now, what, exactly, do you not know how to do?

 

[Gasman]

Have a table that holds the minimum/standard information that you want in a table.
Then when the user does something (click a button?, up to you), run an append query that selects all that data for that product and appends it to your destination table, then requery the subform.
I would have a field that determines whether that entry is active or not. A date field would suffice.

Then if you end up having another entry for that product, you just add it to the table and it gets appended with the rest.
Same if you drop an entry, then if that field has a date and that date is less than today, it does not get picked up for appending.

This method does rely on the fact that you would *always* need those entries and have no real need to delete any, which is still possible of course.

 

[SteveAccess93]

Hi Gasman,

Thank you for the advice.

Would you be able to explain how I would code this?

Thanks,

Steve

We have a table called PurchOrders which has all the purchase orders for all jobs and phases. This is the table which we will need to append with prepopulated information. All the 'supplierID' will be need to say 'To Buy' and then the Descriptions will be Ali, hardware, sealed units, panels etc.

 

[SteveAccess93]

Steve, where, exactly, are you stuck? Say you set off to start coding this. Now, what, exactly, do you not know how to do?

Sorry Mark, I have very little knowledge of coding.

I know what I want but don't know how exactly to do it.

Table 'PurchOrders'

Append Rows based on Form 'Job Sheet', Product Group Combo 'Combo128'

Window		Door		Commercial Door		Curtain Wall
Supplier ID	Description	Supplier ID	Description	Supplier ID	Description	Supplier ID	Description
**To Buy**	Ali	**To Buy**	Ali	**To Buy**	Ali	**To Buy**	Ali
**To Buy**	Hardware	**To Buy**	Hardware	**To Buy**	Hardware	**To Buy**	Hardware
**To Buy**	Sealed Units	**To Buy**	Sealed Units	**To Buy**	Sealed Units	**To Buy**	Sealed Units
**To Buy**	Panels	**To Buy**	Panels	**To Buy**	Panels	**To Buy**	Panels
**To Buy**	Teleflex
						**To Buy**	Fixings
						**To Buy**	Fixing Shoes
						**To Buy**	Edge Panels
				**To Buy**	Auto
**To Buy**	Maglock	**To Buy**	Maglock	**To Buy**	Maglock
**To Buy**	Panic Bars	**To Buy**	Panic Bars	**To Buy**	Panic Bars

 

[Gasman]

That is a spreadsheet layout.
Take your spreadsheet expertise and put it somewhere where it cannot be referenced when you work on databases.

You would have a table more along the lines of below, I would have thought?

ItemID    ProductIDFK            Item        ItemValue
1        1            SupplierID    **To Buy**    'repeat as needed.
2        1            Description    Ali
3        1            Description    Hardware
4        1            Description    Sealed Units
5        1            Description    Panels   
6        1            Description    Teleflex

where 1 is the ProductID for Window

Damn, even the code tags did not keep the format?

 

[Pat Hartman]

You need to base the subform on a query that selects records using criteria based on the combo. Give the combo a meaningful name. You'll thank us later

Select ...
From ...
Where ProductID = Forms!myform!cboProductID

In the Click event of the combo, requery the subform:

Me.NameOfTheSubformCONTROL.Form.Requery

If you ALWAYS want to restrict the subform, you can just use this method. If you have other situations, then you can replace the name of the RecordSource query in the subform with the query that you want to use. That automatically runs a requery so you don't need to do that also.

In that case, you can use this code in the click event of the combo:
Me.NameOfTheSubformCONTROL.Form.RecordSource = "thenameofthequeryabove"