Add records to table, run make table query, remove records from table

[killerflappy]

Hi Guys,

I'm still a newbe in Access VBA.

I want to make a macro/module that does the following:
-insert 3 records in a table (one for each product with a dummy store)
-then run a make table query
-then modify the table properties of the new table to set a propertie
-Remove the 3 dummy records.

Can this even be done in the macro maker off Access 2016? This seems a mess in my language.

Can some-one put me in the right direction?

 

[Minty]

This sounds like a very poor design.

Tables shouldn't need to be created on the fly like this, so If you can describe in business / process terms what this is for, I suspect we can steer you to a better solution.

 

[killerflappy]

Hi Minty, Thanks for the reply.

I made simplified an example database. See attachment.
It’s for scanning barcodes and making reports for drivers.

In the tblMututions are the scans results.
I want to make a view of the count of products per store in a table. This table must be modified by human interaction.
The table query qryCountPerSore shows the count op products per store.
The “qryCountPerStore MakeTable” adds a deliver (Yes/No) column a makes the tblCountPerStore table.
This tblCountPerStore is then modified by the user. The reports comes from this table.

Problems.
-I have to have a minimum product A, B and C for the make query (qryCountPerStore MakeTable) to work.
-In the made table (tblqryCountPerStore) the Deliver column must be a checkbox. After making the table it show -1 for yes and 1 for no.

So the steps that I have described for the module does correct this problems.

 

[Minty]

I've only had time for a quick look - and am convinced you don't need the make table part.
I'll try and have a look over the weekend if I get time.

Maybe someone else will have a look in the meantime.

 

[Orthodox Dave]

Hi,

I agree with Minty that there must be a simpler way than continually re-creating a new table. But for the sake of speed I can answer the question about the checkbox column.

The results of a checkbox field are always stored in a table as a 0 (false) or -1 (true). That can't be changed. However, in your report, you can change the column to a checkbox format bound to the Deliver field. It will then display as a checkbox.