Solved Duplicate records changing a field with VBA (1 Viewer)

[Cris VS]

Hello all,

I have a table where I register the configuration of different elements for various events. The fields of this table are: "ElementName", "Configuration" and "Event". Field "Event" is a lookup to a table where I also have a date field. I have a form where I show the user the Event and its date among other fields. The configurations for each event change very slightly from one to another, so I wanted to allow the user to load the immediate previous event's configurations by clicking on a button, allowing a faster update. It is important for me to keep a register of all the configurations used for all the events.

I created a parameter query that extracts from the table a set of records using the date of the event opened in a form to get the previous event: the idea is that the query gets the configuration of the previous event to the one I am working on (the one opened in the form).

Now, I would like to "copy" the records from the query and paste them to the same table, but updating the Event field to the one opened in the form.

I have found this code on the Internet that I believe would be useful but I am not sure how to adapt it to VBA so that I can run it when clicking on a button of the form:

CREATE table temp_table AS SELECT * FROM conf_query;
UPDATE temp_table SET Event="NewEvent";
UPDATE temp_table SET ID=NULL
INSERT INTO conf_table SELECT * FROM temp_table;
DROP TABLE temp_table

Thanks a lot

 

[theDBguy]

Hi. I think that's a little convoluted if you have to create and delete a temp table just to update a record. If you can post a sample copy of your db with test data, we might be able to show you a simpler way.

 

[Cris VS]

Thanks!

 

[theDBguy]

Okay, got it. Please describe in detail what you want to happen when the user opens and uses the form.

 

[Cris VS]

Okay, got it. Please describe in detail what you want to happen when the user opens and uses the form.

I want the user to click the button in the form to run a program that copies the previous event's configurations and pastes them with the current event's name in the same table. This way, in the current event would appear the list of configurations and the user can modify them as needed instead of having to type one by one. I hope it was enough detail

 

[arnelgp]

test and check i add code to the command button.

 

[Cris VS]

test and check i add code to the command button.

I clicked in the button but nothing happened - it just went to the first record...

 

[arnelgp]

did you supply the Event and the EventDate on the main form?

 

[Cris VS]

Yes, I created a new event by going to a blank record, made sure it was saved correctly in the table and then clicked the button, but nothing happened

 

[arnelgp]

sorry i can't replicate what you are experiencing.
if you have time you can download
this video of what the program supposed
to be doing.https://www.dropbox.com/s/g6vryaqgohmizen/20211201_184828.mp4?dl=0

 

[Cris VS]

sorry i can't replicate what you are experiencing.
if you have time you can download
this video of what the program supposed
to be doing.https://www.dropbox.com/s/g6vryaqgohmizen/20211201_184828.mp4?dl=0

Hi, I must have done something wrong previously because now it works The only problem is that it takes the configurations from the event with the oldest date, not the immediate previous. How could I fix it? I have seen that in the code you added you don't use the query I used to select the configurations that must be copied, so question out of VBA-ignorance, wouldn't it be better to take the data from that query instead of comparing again the dates? Thanks a lot

 

[arnelgp]

I have seen that in the code you added you don't use the query I used to select the configurations

i checked first your query before building the code.
the code is based on the Criteria on your query:

DatePresented on the table must be Less (the Maximum date that is less) than the date on the form.

so, in essence, both your query and the code is doing exactly the same.

 

[Cris VS]

Solved, thanks!