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

[dennisb82]

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.

Yes I tried this and it works, until I use the make table again. Than its back to -1 or 0. Any ideas?

[June7]

Well, I wouldn't use MAKE TABLE. I do have some processes that require manipulation of data that can't be done with query calcs. I use 'temp' table - table is permanent and records are temporary. I use VBA to run DELETE and INSERT sql actions. However, these 'temp' tables are not duplicates of the source tables, they are completely different because the data is being manipulated so much.

MAKE TABLE doesn't require any records. If you want records in the table, options:

1. use UNION query as source for the MAKE TABLE - this does require at least 1 record in the original table. Query looks like:

SELECT Query1.Field1, Query1.Field2 INTO tests2
FROM
(SELECT Field1, Field2 FROM Table3
UNION SELECT "0A", "A" FROM Table3
UNION SELECT "0B", "B" FROM Table3
UNION SELECT "0C", "C" FROM Table3) AS Query1;

2. run MAKE TABLE query to create the table then run INSERT actions to add 3 records.

More code could modify the table properties of the new table to set the yes/no field with checkbox.

Is this a multi-user database? Is this a split design (frontend and backend)? Each user will use the duplicate table to manipulate data as they wish? My recommendation is a temp table in the frontend.

[dennisb82]

Hi June7. Thanks for the learnfull information.

I understand both options.

For option 2: Maybe ther is a way to make a macro/module that does:
-insert 3 records (one for each product with a dummy store)
-then run the make tablen
-then modify the table properties of the new table to set the yes/no field with checkbox
-Remove the 3 dummy records.

Can you put me in the right direction?

The database is not yet a multi-user.

[June7]

Add the records to the source table or to the duplicate - whichever. Macro has a RunSQL action. However, AFAIK macro cannot modify table properties - requires VBA using TableDefs. Here is an example of code:

CurrentDb.TableDefs("tablename").Fields("fieldname ").AllowZeroLength = False

For DisplayControl:

CurrentDb.TableDefs("tablename").Fields("fieldname ").Properties("DisplayControl") = acCheckBox

But why is it important to have checkbox in table? Why do users need to mess with data directly in table?