Insertion of new record based on old one (1 Viewer)

[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.

 

[Beetle]

I've got a web database with a somewhat similar scenario. I've got a Zip Code table that stores City, State and Delivery Zone data. On the web form the user selects a Zip Code and the other data is populated based on that selection. In my case I'm just displaying this data in unbound controls, but the concept is basically the same. You lookup existing data in a table and use it to fill controls on a form.

Long story short, it takes two macros to accomplish this (if there is a more efficient method for this in a A2010 web database, I haven't figured it out yet ).

First, you need a table level data macro to retrieve the correct data from the table and then set return variables (SetReturnVar) based on that record. Note that this macro has a parameter defined at the top and the parameter is used in the Where condition. The value for the parameter is actually supplied by the second (calling) macro. Also note that it's important that you set your ReturnVars inside the Lookup A Record In block.

Second, you need a regular macro that uses RunDataMacro and supplies the parameter for the first macro (in my case the value comes from a form control). You then use SetProperty to set the value of the form controls based on the ReturnVars supplied by the data macro.

When working with the new web database options it takes a bit of a different mind set, and usually a few extra steps, to accomplish the things that you're used to doing easily with VBA. I suppose, like anything, it will seems simple enough in time, but for now I still find it somewhat frustrating.

 

[zinrey]

Thanks Beetle, that sounds really useful. I cannot see your images / attachments. Can you check they have uploaded correctly?

 

[Beetle]

See if you can open them from these links;

Data Macro

Form Macro

 

[zinrey]

I can see them now thanks. I see that the table data macro replaces the SQL lookup step in my VBA code and the other macro does the control setting function.

Cool - I have been struggling with how to do lookups etc in macros - I see now that you have to do it with a data macro and that this approach may work well in general for a lot of applications.

Great reply!