Ideas For Using VBA To Update An Append Queries' Criteria

[asdaf3814]

Hello,



I've been searching around the forum and the internet for ideas and/or solutions to this problem. Can't find any that work with my particular situation.

I have an append query. It is pulling data from a linked table through ODBC.
I unforunately can not have a local table joined to this linked table, or using another table/form as a control for the criteria.

As of right now, I just need to update the criteria on one field in the append query: Account Numbers.
The source for these account numbers is on a local table: AccountsForForms. This is populated by user selections in a seperate form.

I'm getting errors on both of my attempts at using a command button and VBA to udpate the criteria for account numbers in the append query.

Here is my first attempt:

It will generate the error "Object required" on the highlighted section.

Private Sub Command0_Click()
Dim strSQLQ As String
Dim strACNMBR As String

DoCmd.RunSQL "DELETE * FROM AllData"
DoCmd.RunSQL "DELETE * FROM SummaryType1"
DoCmd.RunSQL "DELETE * FROM SummaryType2"

'AllData is the main table where the append query is sending the data
'SummaryType1 & SummaryType2 are tables being populated based on queries summarized types of data stored in the table: AllData
            
            strACNMBR = Table!AccountsForForms!ACCOUNTS
          strSQLQ = "INSERT INTO MyAppendQuery (AC_NR) SELECT [MyLinkedTable]![AC_NR] FROM [MyLinkedTable] WHERE ([MyLinkedTable]![AC_NR]= " & "'" & strACNMBR & "'" & ")"
            CurrentProject.Connection.Execute strSQL
    
DoCmd.OpenQuery "MyAppendQuery"
Beep
DoCmd.OpenQuery "SummaryType1Query"
DoCmd.OpenQuery "SummaryType2Query"
Beep
[Forms]![DataSelection]![Type1SummarizedData].Requery
[Forms]![DataSelection]![Type2SummarizedData].Requery
Beep
MsgBox "Process Finished ", vbInformation
End Sub

After doing some researching on here and google, I read up on the QueryDefs function and tried to get an understanding of this works.

This is what I came up:

It will generate the error "Object required" on the highlighted section.

Private Sub Command0_Click()
Dim db As DAO.Database
Dim AccountTable As String
Set db = CurrentDb
Dim QueryAll As DAO.QueryDef
Set AccountTable = db.OpenRecordset("SELECT [ACCOUNTS] FROM AccountsForForms")
Set QueryAll = db.QueryDefs("MyAppendQuery")
QueryAll.Parameters![AC_NR] = AccountTable
QueryAll.Execute dbFailOnError
End Sub

Is this due to in proper reference for AccountTable as a string?





Is there a cleaner or easier way to insert data from a field in a table into a queries criteria? The property fields on both the table, and the linked table match.


Thanks for any suggestions or advice. If this has already been answered, then I apologize. I tried searching and couldn't find exactly what I was looking for.

[pbaldy]

Both of those have flaws. I'd stick with the simpler first method, and use a DLookup to get the value:


strACNMBR = DLookup("ACCOUNTS", "AccountsForForms")

[asdaf3814]

Both of those have flaws. I'd stick with the simpler first method, and use a DLookup to get the value:


strACNMBR = DLookup("ACCOUNTS", "AccountsForForms")

Hey pbaldy,

Thanks for the reply. I've used Dlookups in the past, not sure why I didn't think of it for this.

I will give it a try in the morning at work. Do you think there will be any issues with speed if I use this Dlookup?

[pbaldy]

Not how you're using it. A DLookup() within a query field could be horribly slow, but a single one to get a value to be used later should be quite fast.

[asdaf3814]

Not how you're using it. A DLookup() within a query field could be horribly slow, but a single one to get a value to be used later should be quite fast.

Hey Pbaldy,

Only got a few minutes to try some things out today, but kept returning the error: " Can not insert data with action query"

Dim strSQLQ As String
Dim strACNMBR As String
DoCmd.RunSQL "DELETE * FROM AllData"
DoCmd.RunSQL "DELETE * FROM SummaryType1"
DoCmd.RunSQL "DELETE * FROM SummaryType2"
'AllData is the main table where the append query is sending the data
'SummaryType1 & SummaryType2 are tables being populated based on queries summarized types of data stored in the table: AllData
            
           strACNMBR = DLookup("ACCOUNTS", "AccountsForForms")
          strSQLQ = "INSERT INTO MyAppendQuery (AC_NR) SELECT [MyLinkedTable]![AC_NR] FROM [MyLinkedTable] WHERE ([MyLinkedTable]![AC_NR]= " & "'" & strACNMBR & "'" & ")"
            DoCmd.RunSQL strSQLQ
    
DoCmd.OpenQuery "MyAppendQuery"
Beep
DoCmd.OpenQuery "SummaryType1Query"
DoCmd.OpenQuery "SummaryType2Query"
Beep
[Forms]![DataSelection]![Type1SummarizedData].Requery
[Forms]![DataSelection]![Type2SummarizedData].Requery
Beep
MsgBox "Process Finished ", vbInformation
End Sub

I also went back to the QueryDef approach, and got it to run slightly, but not the intended results. It turned my append query into a select query based on the where criteria. It's not updating the query I'm referencing in the set qdf function. Am I supposed the SQL for the quer that I'm trying to update the criteria?


Is it possible, that I'm not stating the parameters, so it changes the entire query to match the "where" statement?

Dim strSQLQ As String
Dim strACNMBR As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
DoCmd.RunSQL "DELETE * FROM AllData"
DoCmd.RunSQL "DELETE * FROM SummaryType1"
DoCmd.RunSQL "DELETE * FROM SummaryType2"
Set db = Application.CurrentDb
Set qdf = db.QueryDefs("MyAppendQuery")
strACNMBR = DLookup("ACCOUNTS", "AccountsForForms")
strSQLQ = "SELECT [MyLinkedTable]![AC_NR] FROM [MyLinkedTable] WHERE ([MyLinkedTable]![AC_NR]= " & "'" & strACNMBR & "'" & ")"
qdf.Sql = strSQLQ

            
DoCmd.OpenQuery "MyAppendQuery"
Beep
DoCmd.OpenQuery "SummaryType1Query"
DoCmd.OpenQuery "SummaryType2Query"
Beep
[Forms]![DataSelection]![Type1SummarizedData].Requery
[Forms]![DataSelection]![Type1SummarizedData].Requery
Beep
MsgBox "Process Finished ", vbInformation

End Sub

If I change the above from a SELECT into an INSERT INTO, I get a syntax error on the highlighted section.

	
	
Dim strSQLQ As String
Dim strACNMBR As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
DoCmd.RunSQL "DELETE * FROM AllData"
DoCmd.RunSQL "DELETE * FROM SummaryType1"
DoCmd.RunSQL "DELETE * FROM SummaryType2"
Set db = Application.CurrentDb
Set qdf = db.QueryDefs("MyAppendQuery")
strACNMBR = DLookup("ACCOUNTS", "AccountsForForms")
strSQLQ = "INSERT INTO MyAppendQuery (AC_NR) SELECT [MyLinkedTable]![AC_NR] FROM [MyLinkedTable] WHERE ([MyLinkedTable]![AC_NR]= " & "'" & strACNMBR & "'" & ")"
qdf.Sql = strSQLQ

           
DoCmd.OpenQuery "MyAppendQuery"
Beep
DoCmd.OpenQuery "SummaryType1Query"
DoCmd.OpenQuery "SummaryType2Query"
Beep
[Forms]![DataSelection]![Type1SummarizedData].Requery
[Forms]![DataSelection]![Type1SummarizedData].Requery
Beep
MsgBox "Process Finished ", vbInformation

End Sub

[pbaldy]

This is an important tool for debugging SQL in code:

http://www.baldyweb.com/ImmediateWindow.htm

If you don't spot the problem, post the SQL here, along with the data type of the field in the criteria.