Dynamic query execution option (1 Viewer)

gstylianou · Apr 16, 2019

Hello all,

I'm trying to create a peer-to-peer automated update process and I need some help:

On Form1 there is a Listbox showing the list of queries which includes into my database. By selectring the query I want to run you will see the Textbox to take the name of the selected query.

What i need to do is:
Using the command button I would like to run the selected query. Essentially i'm trying to create a dynamic function so I can avoid creating multiple commands in the vba code for each query.

The second thing I need is:
When the process finished i want to confrim if the data has been appened normally into the related tables. Then, i must fill the Data Listbox "dynamically" having at least some fields of the updated tables (eg. ID, Descr etc) into Data Listbox,just for confirmation. In this case I would like to have your help to create a dynamic fuction for this case.

Thanks in advanced

Ranman256 · Apr 16, 2019

just run the query selected in the list

Code:

sub btnRun_click()
docmd.openQuery lstbox
end sub

gstylianou · Apr 16, 2019

Ranman256 said:
just run the query selected in the list

Code:

sub btnRun_click() docmd.openQuery lstbox end sub

Hi Ramman and thank you very much, but

Why to run this? Actually i mean why i must create a virtual btnRun? To do what exactly?

isladogs · Apr 16, 2019

Also change your listbox row source to exclude 'temp' queries starting with ~sq.
Those are the sql for form and report recordsources and controls such as your listbox.
New listbox sql

Code:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=5) AND ((MSysObjects.Flags)<>3));

If you prefer you can add the code Ranman suggested to the listbox doubleclick event instead of using a button

gstylianou · Apr 16, 2019

Guys,

The first part of the issue it is now OK. I just put the following code about to run each query i want dynamically.

Private Sub cmdRun_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery txtSelect.Value
DoCmd.SetWarnings True

End Sub

Since some hours ago i'm trying to solve the second part of the problem, on haw can i receive the result of the selected queries into DATA LISTBOX.....Any ideas?

isladogs · Apr 16, 2019

Why not just count the records added to your table
Do a DCount before & after running the query then get the difference

Alternatively change your data listbox from a textbox to a listbox (!) and add code to your run button such as

Code:

Select Case Me.List0

Case "Query1"
    Me.DataListbox.RowSource = "SELECT * FROM Table2"

Case "Query2"
    Me.DataListbox.RowSource = "SELECT * FROM Table1"

End Select

Me.Requery

Adapt as appropriate

gstylianou · Apr 16, 2019

isladogs said:
Why not just count the records added to your table
Do a DCount before & after running the query then get the difference

Alternatively change your data listbox from a textbox to a listbox (!) and add code to your run button such as

Code:

Select Case Me.List0 Case Query1 Me.DataListbox.RowSource = "SELECT * FROM Table2" Case Query2 Me.DataListbox.RowSource = "SELECT * FROM Table1" End Select Me.Requery

Adapt as appropriate

Because i'm not running actually only Append Queries my friend, sometime i'm running Update Queries etc...

Gasman · Apr 16, 2019

So check the RecordsAffected property?

https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/database-recordsaffected-property-dao

isladogs · Apr 16, 2019

gstylianou said:
Because i'm not running actually only Append Queries my friend, sometime i'm running Update Queries etc...

OK but perhaps you should look at the updated version I uploaded ....

Dynamic query execution option (1 Viewer)

gstylianou

Registered User.

Attachments

Ranman256

Well-known member

gstylianou

Registered User.

isladogs

MVP / VIP

gstylianou

Registered User.

isladogs

MVP / VIP

Attachments

gstylianou

Registered User.

Gasman

Enthusiastic Amateur

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread