Change table with checkboxes, perform actions (send e-mail, delect line, see details of deleted row)

[dennisb82]

Hi guys, I’m a beginner in VBA.

In the Example.zip file I have a table “tblqryCountPerSore”. This table is created by the “qryCountPerStore Maketable”.


The table “tblqryCountPerSore” shows the number of products (A, B and C) per customer and storenumber.

Now. There a a number of reasons to delete a store from the “tblqryCountPerSore”-table (closed, no products, not efficient, not reachable, etc.). This can only be done by human interaction.

What I want to do in “tblqryCountPerSore”:
-For the shops that won't be delivered I want to be able to delete them from the table so I have a clean list of what will be deliverd.
-I want to be able to send an e-mail to the shop that are deleted so the won’t expect any products. Als for the shops that have no products.
-Somehow it must be clear witch product (from tblMutations) are not delivered.

E-mailadresses are stored in tblStores. I necessary (for simplicity) I can put them in the “tblqryCountPerSore”.

Can someone put me in the right direction? Maybe there is a way to use checkboxes in the "tblqryCountPerSore”.

[June7]

Recommend not deleting, just flagging as 'Inactive'. Then apply filter criteria to exclude 'Inactive' records.

If you delete the store, are there related dependent records to also delete?

Why are you using MakeTable query?

Don't work directly with tables and queries, uses forms and reports.

[dennisb82]

Hi June7. Thanks for the information.

If I delete the store from the tblqryCountPerSore-table, there are no dependent records to delete.
When someone makes a mistake in the corrections, the table can be created again.
Therefor i use a Maketable query.

How can I implement the inactive flag?
What are the diffrence by using a form or report?
I don't think the 3 points I want tot implement can be implemented by a report.

[June7]

Right, use form for input and report for output - users should not work directly with tables and queries.

I still don't understand why you would use MakeTable - if the data is deleted, where would you retrieve it from to restore? Routinely programmatically modifying db structure is not advised.

The inactive flag would just be a field - a yes/no or a data/time or a text. This way if relationship with store is renewed, just change the value in field. This is safer than deleting.

[dennisb82]

Thanks June7!

I want this to be a MakeTable so I can change things. If something goed wrong, I can use the MakeTable again.
I çan't mess with the original data this way.

I am going to test with the field.

[dennisb82]

The inactive flag would just be a field - a yes/no or a data/time or a text. This way if relationship with store is renewed, just change the value in field. This is safer than deleting.

Hi June7.

I tried to make a yes/no field (with yes a default value), but I can get it to the “tblqryCountPerSore” by using “qryCountPerStore Maketable".
It should be simple. I tried to make a field yes/no in “tblqryCountPerSore”, but this will be overwritten by “qryCountPerStore Maketable".
Als I tried to put it in the tblMutations, but this is not seems to work with the crosstable query.

Any advise? Maybe you can show it in the example-file?

[Bob Fitz]

If you're still seeking a solution you could try adding a yes/no field to the "tblStores" table. Then add tblStores to the query "qryCountPerStore MakeTable" and join with the "Storenr" field so the flag field can be added to the query and used to set criteria.

See file attached

[June7]

Thought I had responded with the same recommendation to add y/n field to tblStores. Where did you attempt to add and why if not tblStores?

[Bob Fitz]

Thought I had responded with the same recommendation to add y/n field to tblStores. Where did you attempt to add and why if not tblStores?

Yes June7, I believe you did recommend the use of a y/n field in the table "tblStores". However, judging by the comments made by the OP in post #6 the OP was still looking for more advice. Also, the OP had asked me to take a look at this thread. Please accept my sincere apologies if I have caused any offense.

[June7]

No apologies needed by you. I was just trying to convey I had intended to reply to OP's last post and thought I had. My earlier posts did not explicitly say to add field to tblStores but then would not have thought needed to.

[dennisb82]

If you're still seeking a solution you could try adding a yes/no field to the "tblStores" table. Then add tblStores to the query "qryCountPerStore MakeTable" and join with the "Storenr" field so the flag field can be added to the query and used to set criteria.

See file attached

This example is very helpfull! Thanks guys! I'm sorry that I didn't understood you previously June7.

One thing doesn't seems to work right. After putting in the flag at the tblStore and making a new query that combine the cross table query and the flag field I get the tblqryCountPerStore with the flag. But the flag is a -1 (for yes) or a 0 (for no). Is there a way to make this alsso a checkbox?

[June7]

Checkbox is a control on a form.

[dennisb82]

Checkbox is a control on a form.

sorry for this newbe question. Thanks!

One last question: for the make table query there must be at least 1 product for A, B and C. Somethimes there no product A, B or C. Is there some way to workaround this?

[June7]

I don't understand the question. If there must be a product then why would there not be one?

I still don't understand the need for make table query.

Also, correction to earlier statement. It is possible to set yes/no field to display a checkbox in table design and it will carry over into query. In table design set focus on the field and make sure checkbox is set as DisplayControl on the Lookup tab.

[dennisb82]

Sometimes it happens that (for example) product C is not in stock. If thats the case, the make-table can't be created beacause it wants to have a minimum of 1 product A, 1 product B and 1 product C. So how can I create the table if one of the products is missing?

The make table is there to let a human make changes on the table that can't be done by algorithms. Also the original data is still available when something goed wrong. This also is the case when I use a form on this table.