update query code problem

[belsha]

I am using the following code to try and update a group of queries that all start with qryBB. It works when I use select queries and print out statements to print out any query results, but I just want to run the group of update queries, not show the user any screens or print out. Can anyone see my error and let me know? Thanks.

Private Sub cmdRunUpdates_Click()
Dim PCIUpdate As Integer
Dim mDb As Database
Dim qry As QueryDef
Dim xQryCtr As Integer
Set mDb = CurrentDb

PCIUpdate = MsgBox("Are you sure?", vbExclamation + vbOKCancel, "'OK' will run the BB PCI queries")
If PCIUpdate = 1 Then
With mDb
DoCmd.SetWarnings False
For xQryCtr = 0 To .QueryDefs.Count - 1
If UCase$(Left(.QueryDefs(xQryCtr).Name, 5)) = "qryBB" Then
DoCmd.OpenQuery .QueryDefs(xQryCtr).Name, acViewNormal, acEdit
DoCmd.Close acQuery, .QueryDefs(xQryCtr).Name
End If
Next
End With
DoCmd.SetWarnings True

Else: End If
Set mDb = Nothing

End Sub

 

[modest]

try docmd.RunSQL instead of docmd.openquery


if that doesn't work open a recordset

mdb.OpenRecordset queryName

 

[belsha]

Sorry, neither worked?

 

[Mile-O]

Take off your SetWarnings False just now to see if you are getting any errors.

 

[belsha]

No error messages. Ran some of the queries by themselves and they updated fine. Made a macro with a few of them and they updated fine. Just don't want to have to do this long macro for all these queries if I can make this work.

 

[modest]

Private Sub cmdRunUpdates_Click()
    Dim PCIUpdate As Integer
    Dim mDb As Database
    Dim qry As QueryDef
    Dim xQryCtr As Integer

    Set mDb = CurrentDb

    PCIUpdate = MsgBox("Are you sure?", vbExclamation + vbOKCancel, "'OK' will run the BB PCI queries")

    If PCIUpdate = 1 Then
        With mDb
            DoCmd.SetWarnings False
            For xQryCtr = 0 To .QueryDefs.Count - 1
                If UCase$(Left(.QueryDefs(xQryCtr).Name, 5)) = "qryBB" Then
                    DoCmd.OpenQuery .QueryDefs(xQryCtr).Name, acViewNormal, acEdit
                    DoCmd.Close acQuery, .QueryDefs(xQryCtr).Name
                End If
            Next
        End With
        
        DoCmd.SetWarnings True
    Else: End If             [COLOR=Green]'btw you dont need Else:[/COLOR]

[COLOR=Green]  'should do a mdb.close here[/COLOR]
    Set mDb = Nothing

End Sub

sorry I have to make this easier on the eyes... I didn't change anything, just making it easier to read. Next time use the code tags for formatting.

 

[modest]

I just ran through this code on a test database of mine and everything worked perfectly... except the mdb.querydefs.count took a while to load.

Other than that everything updated as it should. Make sure you have your newer reference to DAO object selected. As a last resort, I would open a new database and copy everything into there. This will reset some properties/values of the database's metadata, which sometimes gets rid of unexplainable errors such as this.

If you haven't already, choose compact and repair from the the main database's tools menu.

 

[belsha]

Tried all your suggestions, compact & repair,checked references, new db with copied stuff, mdb.close added, still no luck. If you run the query by itself or a macro though, it works fine, and the code below I am using for a validation routine and it works fine, so I guess I will just use alot of lines in a macro. I am using Access 2002 (XP), were you? Thanks everyone.

Private Sub Command28_Click()
Dim PCIPrint As Integer
Dim mDb As Database
Dim qry As QueryDef
Dim xQryCtr As Integer, xRs
Set mDb = CurrentDb

PCIPrint = MsgBox("Are you sure?", vbExclamation + vbOKCancel, "'OK' will print the AA PCI queries")
If PCIPrint = 1 Then
With mDb
For xQryCtr = 0 To .QueryDefs.Count - 1
If UCase$(Left(.QueryDefs(xQryCtr).Name, 5)) = "qryAA" Then
Set xRs = .QueryDefs(xQryCtr).OpenRecordset
If xRs.RecordCount > 0 Then
DoCmd.OpenQuery .QueryDefs(xQryCtr).Name, acViewNormal
DoCmd.PrintOut
DoCmd.Close acQuery, .QueryDefs(xQryCtr).Name
End If
Set xRs = Nothing
End If
Next
End With

Else: End If
Set mDb = Nothing

End Sub

 

[belsha]

added zip file

Adding a zipped file in case anyone can figure this out...

 

[modest]

Belsha,

It's not even getting to your query... this is because you're setting the result of your string to uppercase.

It's returning QRYBB and you're comparing that with qryBB.
In order to make this work change qryBB to "QRYBB" or take out the ucase function.

 

[belsha]

Thank you, that was it!

 

[modest]

Not a problem, sometimes we overlook the little things ...like a thorn, it seems small but it'll prick you in the end