Sabtu, 28 Juni 2014

Re: [MS_AccessPros] VBA Query and loop it

 

Luigi-


Maybe like this:

Private Sub Comando12_Click()
Dim db As DAO.Database, rst As DAO.Recordset

' point to this database
Set db = CurrentDb
' Open the filter table
Set rst = db.OpenRecordset("SELECT Agente FROM Table1")

Do Until rst.EOF
' export procedure in external periferic database chosing the data depending on the region (region now is a value in the form, chosen by the user)

' perform first query taking the paramether from the form --> this is the value I would like to have like a filter in the table1

srtSQL1 = "SELECT [0_Tab_Universita].id_uni_cin, [0_Tab_Universita].sigla_Uni, [0_Tab_Universita].Nome_Uni, [0_Tab_Universita].Indirizzo_Uni, [0_Tab_Universita].localita_uni, [0_Tab_Universita].cap_uni, [0_Tab_Universita].Regione, [0_Tab_Universita].UNI_webSite, [0_Tab_Universita].Tipologia, [0_Tab_Universita].Commento, [0_Tab_Universita].PIVA_Uni, Agente_regione.Agente" & vbCrLf & _
"INTO 0_Tab_Universita_regione" & vbCrLf & _
"FROM 0_Tab_Universita INNER JOIN Agente_regione ON [0_Tab_Universita].Regione = Agente_regione.regione" & vbCrLf & _
"WHERE (((Agente_regione.Agente)=" & rst.Agente & "));"

' in red the filter, that I have in the rtable, but now I get it from the form with manual +1

db.Execute strSQL1

' here I export the new table just created
DoCmd.TransferDatabase acExport, "Microsoft Access", [PercorsoDB], acTable, "0_Tab_Universita_regione", "0_Tab_Universita", False

' here I delete the already exported table
db.TableDefs.Delete "0_Tab_Universita_regione"

' Parla!

MsgBox " Le statistiche per " + [Agente] + " sono state completate ed esportate nella directory " + [Prima_parte] + "\" + [Regione] + " il nome del database è come consueto " + [NomeDB] + " Avverti l'agenzia se lo ritieni necessario e passa al successivo.", vbInformation, "Buon fine. Our competence, your Value!"

rst.MoveNext
Loop

rst.Close
Set rst = Nothing
Set db = Nothing
    
End Sub

Note that you will have to put the real name of "Table1" in the OpenRecordset.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)




On Jun 28, 2014, at 10:00 AM, bondurri.luigi <no_reply@yahoogroups.com> wrote:

Well, it is a mess! :-) but... Here the complete sub
________________________________________________________________________
Private Sub Comando12_Click()
' silenzio!
DoCmd.SetWarnings False

' export procedure in external periferic database chosing the data depending on the region (region now is a value in the form, chosen by the user)

' perform first query taking the paramether from the form --> this is the value I would like to have like a filter in the table1

srtSQL1 = "SELECT [0_Tab_Universita].id_uni_cin, [0_Tab_Universita].sigla_Uni, [0_Tab_Universita].Nome_Uni, [0_Tab_Universita].Indirizzo_Uni, [0_Tab_Universita].localita_uni, [0_Tab_Universita].cap_uni, [0_Tab_Universita].Regione, [0_Tab_Universita].UNI_webSite, [0_Tab_Universita].Tipologia, [0_Tab_Universita].Commento, [0_Tab_Universita].PIVA_Uni, Agente_regione.Agente" & vbCrLf & _
"INTO 0_Tab_Universita_regione" & vbCrLf & _
"FROM 0_Tab_Universita INNER JOIN Agente_regione ON [0_Tab_Universita].Regione = Agente_regione.regione" & vbCrLf & _
"WHERE (((Agente_regione.Agente)=[Forms]![export_regione]![Agente]));"

' in red the filter, that I have in the rtable, but now I get it from the form with manual +1

DoCmd.RunSQL strSQL1

' here I export the new table just created
DoCmd.TransferDatabase acExport, "Microsoft Access", [PercorsoDB], acTable, "0_Tab_Universita_regione", "0_Tab_Universita", False

' here I delete the already exported table
DoCmd.DeleteObject acTable, "0_Tab_Universita_regione"

' Parla!
DoCmd.SetWarnings True
MsgBox " Le statistiche per " + [Agente] + " sono state completate ed esportate nella directory " + [Prima_parte] + "\" + [Regione] + " il nome del database è come consueto " + [NomeDB] + " Avverti l'agenzia se lo ritieni necessario e passa al successivo.", vbInformation, "Buon fine. Our competence, your Value!"
    

    
End Sub
________________________________________________________________________


' --> I do this for many different table, is not working bad now but so dirty!!!! and the filter is alwas the same, 
[export_regione]![Agente]

How to loop this code? thanks




---In MS_Access_Professionals@yahoogroups.com, <no_reply@yahoogroups.com> wrote :

Ji John, my problem is not to add records but to loop the query depending on the values I've on the Table1 (filterValue).
Now my code is working, but I've to repeat the quert depending on the filter value (I wrote a table, I export It in another DB, I delete the table), and again until the values in table1 (the filter) is over.
Assume the table1 contain the CityName, and I export a table filtering for the name in separata table...
So How can I loop the query is my question. I can post all code if you like, but it is only the loop I need.
Thanks


---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Luigi-

This is not clear at all.  How are Table1 and Table2 related?  And the SQL in srtSQL1 is simply an incomplete SELECT query - it won't add any rows to anything.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)




On Jun 28, 2014, at 3:01 AM, bondurri.luigi <no_reply@yahoogroups.com> wrote:

Hi all,I would like to understand this concept, I'm trying to explain in plain code...
I have a table [TABLE1] containing 1 field FILTER_FIELD (it contain eg (1,2,3,4,5,6,7)
I would like to make a VBA query that perform the query (it write more table) depending on the filterfield in the table 1, and loop it until the filterfield values have finished:

srtSQL1= "SELECT [TABLE2].*  bla bla bla
WHERE [TABLE2].[FIELD_to_be_FILTERED])=[TABLE1].[FILTER_FIELD];"

DoCmd.RunSQL srtSQL1


later on, I transfer the created table...

DoCmd.TransferDatabase acExport, "Microsoft Access", [Path_DB], acTable, "TABLECREATED", "TABLECREATEDnewname", False


now I want to loop this procedure until value FILTER_FIELD in TABLE1 are over...
Hope it is clear; do you have a solution? Thanks in advanceLuigi


__._,_.___

Posted by: John Viescas <johnv@msn.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (6)

.

__,_._,___

Tidak ada komentar:

Posting Komentar