Autopopulate insert form with previous values using Access 2010 macro editor

[zinrey]

Hi all,



I am creating a Web Database and need to be able to present a form that will autopopulate with values from a previous record but that will insert a new record rather than editing the existing one once any edits have been made. For auditing purposes, the existing record cannot be edited and instead a new entry in the database needs to be made with the current timestamp.

I have done this type of thing before using VBA and it is pretty easy. The steps are:

1. Create a form and open it in 'insert' mode
2. Run an SQL query that finds the last related record
3. Set the values (default values) of each control on the form to the values retrieved in the SQL query (apart from primary key and timestamp of course!)

This does just what is required. Problem is that this is a web project and so I am forced to stay away from VBA and to use the Access 2010 Macro Editor instead. This is proving problematic.

Anyone have any suggestions on how I can do this with a macro? If not, are there other ways e.g. using a data macro to insert rather than update, that you can think of?

Thanks for your help.

[June7]

Review https://www.accessforums.net/showthr...-Entire-Record

Instead of the SQL probably have to use DLookup with a nested DMax.

[zinrey]

Review https://www.accessforums.net/showthr...-Entire-Record

Instead of the SQL probably have to use DLookup with a nested DMax.

Actually, as referred to in the thread you linked to above - the answer is to use a Data Macro. I have been learning a lot about data macros in the last couple of days and they are amazingly powerful - whenever you want to look up, delete, edit, add, manipulate data in your database you can use a (very well named ) 'Data' macro. In this case you call a macro from the On Load event which:

i) reads the ID of the record that needs to be copied from the form
ii) calls a data macro to retrieve all the fields - putting them into returnvars
iii) uses the SetProperty Value function to set the default values of each field on the form.

It actually is very easy. Here is a snippet of my code in case it helps others (the top half of the image shows the normal macro and the bottom half the data macro which is called:

[accessking]

hello! do you mind to send me your working file? I am still very confused over how to use data macro in such a case.

I am facing the same problems as yours actually!

[zinrey]

hello! do you mind to send me your working file? I am still very confused over how to use data macro in such a case.

I am facing the same problems as yours actually!

Hi Accessking, I am happy to help but this file has developed into a massive database now and I am pretty busy at the moment. What are you having problems with exactly??? Think of it like this, whenever you would use an SQL command in VBA, write a Data Macro instead. Data macros essentially return or modify data in your database that your normal macro can manipulate.

You essentially write the SQL command in a data macro and then call it from your normal macro. When you get used to it, it is easier even than using the VBA. Personally I had a eureka moment when the penny dropped and all the things I couldn't do with macros and only with VBA became possible. I think it was when I realised that if I needed an SQL command, I needed to write a data macro.


Did you manage to download the image from my post above?

Good luck! Post back here if this has helped or let me know if you need more help.

Zinrey

[June7]

I think Zinrey is correct for the most part. The exception would be sql SELECT statements. Data macros work with sql actions (INSERT, UPDATE, DELETE). A SELECT can be nested as a subquery in an sql action, otherwise, I don't think SELECT is relevant to data macros.

[zinrey]

I think Zinrey is correct for the most part. The exception would be sql SELECT statements. Data macros work with sql actions (INSERT, UPDATE, DELETE). A SELECT can be nested as a subquery in an sql action, otherwise, I don't think SELECT is relevant to data macros.

The example in this thread where a datamacro had this this structure:

LOOK UP A RECORD IN qirCIRbyDate WHERE [qryCIR].[ProductID] = varProductID ALIAS qryCIR

SETRETURNVAR (varVersion, [qryCIR].[Version])

is equivalent to the SQL statement

SELECT Version AS varVersion FROM qirCIRbyDate WHERE [ProductID] = varProductID LIMIT 1

zinrey

[June7]

Thanks for the clarification zinrey. Sounds like data macros can do more than I thought. I have never used them because web database design has not been a concern. Never heard of LOOK UP A RECORD, probably because I don't use macros.

[accessking]

Hey all! Thanks sincerely for the help! It's my first time posting an question on the forum, and didn't expect such a prompt reply!

Okay, i think my key challenge is that i am not familiar with SQL. Yes, I manage to

My challenge is that..

1. I have a form (as attached) and i wanted the user to fill up the form, however; when they pressed next, i wanted the portion on top (referring to supplier name, collection name etc) , to remain (or same as the previous record) while the bottom of the form to turn empty.

2. the outcome should be such that i have duplicate entries (different collections) match with different design name - in my table.

Thus, i am thinking of using the method of having the "blanks" on the top of the form, to be set as "default value from previous record" - or in short, same as the previous record.

And I have been reading from google's search that data macro can do the magic. Am I on the right track?