Action QUery??? (1 Viewer)

chrisjames25 · Oct 18, 2017

Hi - hopefully i will explain the below as accuratley as i can.

For the purposes of this issue i have two tables:
1 Tbl_Variety
2 Tbl_PotSize
There are respective forms for each table and within each form i can turn a variety or potsize to inactive using a checkbox.

What i need to do is now populate a Tbl_Product Table.

My plan at present is everytime a new variety is added via the new variety form is to cross reference against all the potsizes in the potsize form and add a new product for each one so if there are 14 pot sizes at present and i add 1 new variety 14 new products will get added to the products table.

Likewise if i add a new potsize to the pot size table and i currently have 200 varieties in variety table it will add 200 new products to the products table.

However it is not perfect as if i add variety "Nelly" i think it should add all the products as potsizes to the table as above but it would be great if it was possible to go through and tick if the potsizes are currently applicable to the new variety.

So my plan is that when i add a new variety and press add variety a new form will populate with the new he product name (variety and potsize) as a continuous form which i can then click a checkbox for active with this particular variety.

I hope that all makes kind of sense.

Just really after some guidance as to whether that sounds like a sensible approach or am i missing something simple.

Minty · Oct 18, 2017

I would not try and automatically create all the options by default, you are effectively creating redundant data for sizes that you will never offer for that variety.

What might work better would be a form with a multiselect list box. You select your variety from a combo, and then select in a list box all the available pot sizes. You select the ones you want to populate, and then click a button and it inserts the records accordingly.

I think you still need your active field on those created records as you may discontinue a size in the future.

I personally think you shouldn't create a record in advance of it's actual need.

chrisjames25 · Oct 18, 2017

Hi Minty

Thanks for your suggestions. As ever they make perfect sense.

Question though. If i used your method to populate new products, how could i stop the form in the future allowing me from deselcting an already created product from the listbox or if i deslected make it inactive rather non existant

chrisjames25 · Oct 18, 2017

WOuld i be better having 2 listboxes.

One that states potsizes that are currently not linked to the variety so that i cant unselect an already created product and the other list box populates potsizes already linked to the variety and allows you to select which ones you want active?

T

Minty · Oct 18, 2017

You could simply only list the sizes that aren't currently there? So on a new variety you get the lot.

The other list box would display all the other existing sizes listed for that variety, and their current activation status. You can then requery it after doing the selection. If you make make that list box single select - then you could 'flip' a sizes activated status on selection.

chrisjames25 · Oct 18, 2017

Hi Minty your idea so far is great and looks great and user friendly.

Much much better than what i was playing with. U must hate me with all the questions but i have 2 more

1. your last sentence "The other list box would display all the other existing sizes listed for that variety, and their current activation status. You can then requery it after doing the selection. If you make make that list box single select - then you could 'flip' a sizes activated status on selection."

I get make it single rather than multiselct, but am confused by the 'flip' a sizes activated status on selection.

ANy chance you could elaborate on that for me.

2. If i was to introduce a new potsize in the future that will be rolled out to numerous varieties could i ineffect just reverse the form and have combobox to select new potsize and then listbox populated by all varieties and then select them all or which ever ones i wanted and do it that way also

Minty · Oct 18, 2017

1. Assuming Active is either yes or no, the you would change that from Yes to No or vice versa if you selected the record in the second list box.

2. Yes you could do either another similar form or use the same form and change the listbox recordsource and insert query code based on a maybe an option group to determine what was being listed in the combo, and what was being updated.

chrisjames25 · Oct 18, 2017

Last question i promise.

DO i save multiselected listboxs to multi-value fields in a table or is it better to add code to cycle through the selections and add independent lines

Minty · Oct 18, 2017

Don't use multivalued fields. Whilst I can see some of the attraction, they intrinsically break normalisation rules.

Just use the list box to loop and create a record per selected item in your target table. You will see the benefits long term to correct data storage.

chrisjames25 · Oct 18, 2017

WAs hoping you would say that as prefer to keep within what i leanrt about normalisation.

Will dig some VBA out for the loop, unless you have it to hand

Minty · Oct 18, 2017

Not directly to hand but our very own Paul does - well should point you in the right direction
http://www.baldyweb.com/multiselect.htm

It's actually for filtering a report, but you'll hopefully get the idea.

chrisjames25 · Oct 18, 2017

Brill. Thanks very much

Pat Hartman · Oct 18, 2017

The relationship between variety and pot size is many-to-many. That means you need a junction table which I assume you already have. It sounds like most varieties are carried in most pot sizes and so you would like to avoid the work of adding records to the junction table for all variations. So the question becomes, is it easier to add useless rows and then delete them or to only add the useful rows. I think most of us would prefer to do the latter and it sounds like that is the path you are pursuing.

The interface I would use would show the multi-select list box only for new records and the code would require at least one item to be selected. The form would handle saving the variety record when you press your save button. Then the AfterInsert event code would loop through the multi-select listbox and insert new records as needed into the junction table.

Future maintenance on the junction table should be performed using a subform that shows what your original multi-select code inserted. You can then delete any rows you created accidentally or add any new rows going forward.

chrisjames25 · Oct 18, 2017

hi. thanks to both of you for continued advice.

if i have a form that i choose variety name and then in afterupdate it populates a lstbox of potsizes that currentyl are not linked to the variety how can that be achieved.

easy to run a query in productstable (junction table) to see what potsizes are linked but cant for life of me figure out how to run query on which ones arent linked.

or is this impossible and i need some fancy loop vba code

Minty · Oct 19, 2017

You need a left join but the target of the join needs to be Null.
So join your pot sizes table to your Products table right click on the join type.

It should all be come clear from there.

chrisjames25 · Oct 19, 2017

Apologies Minty. I am a bit clueless with regards to your last comment.

Any chance you could expand on it for me.

CHeers

Minty · Oct 19, 2017

Try this - A table of Companies, and Studies for those Companies. to list all the companies that have no studies you set the query up like this;

If your query opens up with the two tables already linked - click on the link and change it as shown.

chrisjames25 · Oct 19, 2017

Wow, access literally just gets better and better. From a few weeks ago i am amazed with how much this prgram can do and also how without you guys willing to help, us novices would get nowhere.

THanks so much

chrisjames25 · Oct 19, 2017

Minty got it all working now but had to do a work around that i just wanted to check i needed to do.

Rather than run the left join query against Potsize and product table i ran it against potsizetbl and a query that filtered the product table by the variety name selected on a form. Works like a charm but just wanted to check i could achieve that all from the intial 2 table or whether the additinoal query is required.

Minty · Oct 19, 2017

That sounds about right, If it is giving you the results you need, and you have sanity checked it then job done.

Action QUery??? (1 Viewer)

Registered User.

AWF VIP

Registered User.

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

Super Moderator

Registered User.

AWF VIP

Registered User.

AWF VIP

Attachments

Registered User.

Registered User.

AWF VIP

Similar threads

Users who are viewing this thread