Question Performing multiple database action on button

[RetPrgmr]

I am writing an application for my personal use. I have 2 Access databases, one contains the data tables and the other has the forms, queries, reports, etc.

So far, I have been able to use the queries in the Row Source property of my form objects to accomplish what I want to do. Now I want to have a button that appends records to a table, the data in the table comes from several different controls on my form. After doing this the button should requery another control to display the new records added.

The OnClick event for the button allows me to use a Macro builder, Expression builder or Code builder. If I choose to use DAO in VBA code to append the records, do I have to define the database to create a database object to use even though the database is already open? Can I use a Macro to do this? Can a macro reference the data that is in the controls on my form?

I would appreciate it if someone would point me in the right direction.

Thanks,

Mary Jane

 

[MikeLeBen]

I think using SQL would be more efficient and easier to control.
You use the DoCmd.RunSQL method for that, so you can design your query in SQL and just pass it as a string to it.

Dim mySQL As String

mySQL = "UPDATE myTable etc." 'your update query

'DoCmd.SetWarnings False <- uncomment this after testing
DoCmd.RunSQL mySQL
'DoCmd.SetWarnings True

For referencing controls on a form in VBA use the Me. keyword followed by the name of the control. You also have the Controls collection which is useful when you need to repeat the same code on many contorls.

 

[vbaInet]

To avoid having to deal with turning on and off the Warnings, you can use this method:

CurrentDb.Execute "UPDATE myTable etc.", dbFailOnError

 

[MikeLeBen]

I don't mind having the popups with the number of records that will be affected at an initial stage; also, I remember reading somewhere there was a difference between the two on lower-level compilation and execution - although I wouldn't remember what it is (some research will surely bring it up).

But yeah, the final result should be the same

 

[vbaInet]

With the dbFailOnError parameter all changes will be rolled back if an error occurs.

Yep, they do the same thing.

 

[MikeLeBen]

With the dbFailOnError parameter all changes will be rolled back if an error occurs.

Didn't know about that one, thanks!

 

[RetPrgmr]

Thanks for your help.

Mary Jane