Solved

Setting query parameters in vba in Access

Posted on 2015-01-16
7
244 Views
Last Modified: 2015-01-16
How do you set the parameters for a query you are calling in vba code?


Right now I have this statement:
 sql = "q_PMACWeeklyReport"
 Set db = CurrentDb
 Set Qd = db.QueryDefs("q_PMACWeeklyReport")
 'New
 Qd.Parameters(WeeklyReportID).Value = RptID

Open in new window


This references a query built in Access - q_PMACWeeklyReport
The field I want to sort by - WeeklyReportID
And the public function I use to get the data to sort WeeklyReportID - RptID


This is the code for the function:
Function RptID()
Dim f_Reports As Form

Set f_Reports = Forms!f_Reports

RptID = Forms!f_Reports!cmbReportDate.Column(0)

End Function

Open in new window


The function picks the data up just fine. It is the QD parameters statement that isn't working right at all. Am I using it wrong? Even when I plug the data directly into the statement (using 27 rather than RptID), I get errors.

How do I make this work?
0
Comment
Question by:Megin
  • 3
  • 3
7 Comments
 
LVL 26

Expert Comment

by:Nick67
ID: 40554335
sql = "q_PMACWeeklyReport"
 Set db = CurrentDb
 Set Qd = db.QueryDefs("q_PMACWeeklyReport")
 'New
 Qd.Parameters(WeeklyReportID).Value = RptID


That's very sparse
One VERY important thing to do is have Option Explicit at the top of every code module.
It makes you declare your variables.

Now, I am not seeing most of your declarations and I think that's where your problem lies.

Dim db as Database
Dim qd as QueryDef
Dim qdfName as string
qdfName= "q_PMACWeeklyReport"
 Set db = CurrentDb
 Set Qd = db.QueryDefs("q_PMACWeeklyReport")
 'New
 Qd.Parameters(WeeklyReportID).Value = RptID

Now normally, you'd open a recordset from the querydef and do stuff
But you aren't doing that here, so while your syntax may be ok, the functionality is whacked

Now RptID is a bit of a cipher

Function RptID()
Dim f_Reports As Form

Set f_Reports = Forms!f_Reports

RptID = Forms!f_Reports!cmbReportDate.Column(0)

End Function


Here, too there's no declaration.
Now, to use RptID in a different function, you'll need
Public RptID as Long up at the top of the code module
This second bit of code would set RptID and the first bit would consume it.

When you DON'T declare variables Access makes this assumption
Dim Whatever as Variant
in the sub/function that encounters the undeclared variable

You can see why that would be a problem, yes?
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40554388
Next step would be:

Qd.Parameters(WeeklyReportID).Value = RptID
Set rst = Qd.OpenRecorset()

Or similar.
What are you trying to do?

/gustav
0
 

Author Comment

by:Megin
ID: 40554431
Sorry. I wasn't clear.

The piece of code I posted was only a section of the larger code. I have made all my declarations at the beginning. (Were you helping me with this a month or so ago, Nick?) The only difference I am seeing is that I named my string "strsql" and yours is named "qdfName."  I also added the RptID as Long, but that didn't change anything.

I will post all of the code now. It is just long and I was avoiding it.

Also, when I test RptID in the Immediate Window, it is showing the correct data (right now that is 27). It just isn't passing that data into the query.

I am getting the "Item not found in this collection" error.

I have put a comment right above the trouble spot. It is in all caps.



Option Compare Database
Option Explicit

Private Sub btnExportWklyReport_Click()


 On Error GoTo Error_Handler

 Dim db As Database
 Dim rs As Recordset
 Dim iW As Integer
 Dim iRow As Integer
 Dim oApp As Excel.Application
 Dim oBook As Excel.Workbook
 Dim oSheet As Excel.Worksheet
 Dim oRange As Excel.Range
 Dim i As Integer
 Dim iNumCols As Integer
 Dim TheTO As String
 Dim TheSTOname As String
 Dim TheStaffName As String
 Dim TheActDesc As String
 Dim C As Excel.Range
 Dim Qd As QueryDef
 Dim TheTypeID As Integer
 Dim sql As String
 Dim w As Integer
 'New
 Dim RptID As Long
 
 Set oApp = Excel.Application

 sql = ""
 iW = 0
 iRow = 0
 i = 0
 iNumCols = 0
 TheTO = ""
 TheSTOname = ""
 TheStaffName = ""
 TheActDesc = ""

 
 
 Set oBook = oApp.Workbooks.Add
 Set oSheet = oBook.Worksheets(1)
 With oSheet.PageSetup

  
    .Orientation = xlLandscape
    .LeftMargin = oApp.InchesToPoints(0.25)
    .RightMargin = oApp.InchesToPoints(0.25)
    .TopMargin = oApp.InchesToPoints(0.5)
    .BottomMargin = oApp.InchesToPoints(0.5)
    .HeaderMargin = oApp.InchesToPoints(0.5)
    .FooterMargin = oApp.InchesToPoints(0.16)
    .PaperSize = xlPaperLetter
    .CenterHorizontally = False
    .CenterVertically = False
    .FitToPagesWide = 1
    .FitToPagesTall = False
    .Zoom = False
       

End With
    
oApp.Visible = True
oApp.UserControl = True
 
sql = "q_r_TO_PMACReport"
Set db = CurrentDb
Set Qd = db.QueryDefs("q_r_TO_PMACReport")


Set rs = Qd.OpenRecordset(dbOpenDynaset, dbReadOnly)
 rs.MoveLast
 rs.MoveFirst
If rs.RecordCount = 0 Then
    GoTo Bail
End If
 
iNumCols = IIf(w > 0, w, iNumCols)
 'Format the header row as bold
oSheet.Range("a1").Select
With Selection
    .Columns("A:A").ColumnWidth = 46.2
    .Columns("B:B").ColumnWidth = 32.57
    .Columns("C:C").ColumnWidth = 73.43
End With
    
'Enters information in the report header, merges the cell with the one next to it, and formats

oSheet.Cells(1, 1).Value = "WSDOT AWV Project - Y-9762 - HMM 226862" & vbCrLf & "PMAC Weekly Activity Report"
Set C = oSheet.Cells(1, 1)
Range(C.Cells, C.Cells.Offset(0, 1)).Select
Selection.Merge
Set C = Selection
With C
    Call FormatHeader(C, True, 18, "Ariel", True, xlCenter, xlBottom, 69, xlContinuous, xlThin, xlAutomatic)
End With

oSheet.Cells(1, 3).Value = "No.  "
Set C = oSheet.Cells(1, 3)
With C
    Call FormatHeader(C, True, 12, "Ariel", False, xlCenter, xlBottom, 69, xlContinuous, xlThin, xlAutomatic)
End With

 oSheet.Cells(2, 1).Value = "Task Order/Sub Tasks"
 Set C = oSheet.Cells(2, 1)
 With C
    Call FormatHeader(C, True, 14, "Ariel", False, xlCenter, xlBottom, 29.25, xlContinuous, xlThin, xlAutomatic)
End With

oSheet.Cells(2, 2).Value = "Staff"
Set C = oSheet.Cells(2, 2)
With C
    Call FormatHeader(C, True, 14, "Ariel", False, xlCenter, xlBottom, 29.25, xlContinuous, xlThin, xlAutomatic)
End With


oSheet.Cells(2, 3).Value = "Week Ending" & " " & reportdate
Set C = oSheet.Cells(2, 3)
With C
     Call FormatHeader(C, True, 12, "Ariel", False, xlCenter, xlBottom, 29.25, xlContinuous, xlThin, xlAutomatic)
End With


'Main header is now done. Now it is time to fill in that first gray row.

oSheet.Cells(3, 1).Value = "Summary Report"
Set C = oSheet.Cells(3, 1)
Range(C.Cells, C.Cells.Offset(0, 1)).Select
Selection.Merge
Set C = Selection
With C
    Call TopGrayBar(C, "Ariel", True, 12, 15, xlContinuous, xlThick, xlThin, xlAutomatic, True, 54.75, xlThin, xlThin, xlLeft, xlCenter)
End With

Set C = oSheet.Cells(3, 3)
With C
    Call TopGrayBar(C, "Ariel", True, 12, 15, xlContinuous, xlThick, xlThin, xlAutomatic, True, 54.75, xlThin, xlThin, xlLeft, xlCenter)
End With
    
'Now start entering the full task order section. This next piece of code adds the first task order.


TheTO = rs!TaskOrderDesc
oSheet.Cells(4, 1).Value = TheTO
Set C = oSheet.Cells(4, 1)
Range(C.Cells, C.Cells.Offset(0, 1)).Select
Selection.Merge
Set C = Selection
With C
   
    Call FormatTheCell(C, "Ariel", True, 12, True, 50, xlGeneral, xlCenter, xlAutomatic, xlContinuous, xlThin, xlThin, xlMedium, xlMedium, xlAutomatic, xlMedium, xlMedium)
End With

'format cell next to it
Range(C.Cells, C.Cells.Offset(0, 1)).Select
Set C = Selection
With C
    Call FormatTheCell(C, "Ariel", True, 12, True, 50, xlGeneral, xlCenter, xlAutomatic, xlContinuous, xlThin, xlThin, xlMedium, xlMedium, xlAutomatic, xlMedium, xlMedium)
End With


'Start 5 rows down

iRow = 5
Do Until rs.EOF

'If the TO matches what is already there, go to the next TO

    If rs!TaskOrderDesc = TheTO Then
    rs.MoveNext
Else
'If it doesn't match, then start writing them into the cells

oSheet.Cells(iRow, 1).Value = rs!TaskOrderDesc

TheTO = rs!TaskOrderDesc

Set C = oSheet.Cells(iRow, 1)
Range(C.Cells, C.Cells.Offset(0, 1)).Select
Selection.Merge
Set C = Selection
With C
Call FormatTheCell(C, "Ariel", True, 12, True, 50, xlGeneral, xlCenter, xlAutomatic, xlContinuous, xlThin, xlThin, xlMedium, xlMedium, xlAutomatic, xlMedium, xlMedium)
   
End With

'Format cells next door.

Set C = oSheet.Cells(iRow, 1)
Range(C.Cells, C.Cells.Offset(0, 1)).Select
Set C = Selection
With C
Call FormatTheCell(C, "Ariel", True, 12, True, 50, xlGeneral, xlCenter, xlAutomatic, xlContinuous, xlThin, xlThin, xlMedium, xlMedium, xlAutomatic, xlMedium, xlMedium)
    'Call TaskOrderSection(C, "Ariel", True, True, 50, xlContinuous, xlThin, xlAutomatic, xlGeneral, xlCenter, 12)
End With



iRow = iRow + 1
        
End If
skip:
Loop

 sql = "q_PMACWeeklyReport"
 Set db = CurrentDb
 Set Qd = db.QueryDefs("q_PMACWeeklyReport")
 
 
 '******THE TROUBLE IS HERE************************************************************************************
 
 Qd.Parameters("WeeklyReportID").Value = RptID
 

 Set rs = Qd.OpenRecordset(dbOpenDynaset, dbReadOnly)
 rs.MoveLast
 rs.MoveFirst

 If rs.RecordCount = 0 Then
     GoTo Bail
 End If


'These row numbers will have to change whenever a new task order is added

TheTO = rs!TaskOrderDesc
oSheet.Cells(26, 1).Value = TheTO
Set C = oSheet.Cells(26, 1)
Range(C.Cells, C.Cells.Offset(0, 1)).Select
Selection.Merge
Set C = Selection
With C
Call formatItem(C, "Arial", True, 12, 15, xlAutomatic, xlContinuous, xlThin, True, 54.75)
End With



'formats cell next to it. just turns it gray
Set C = oSheet.Cells(26, 3)
With C
Call formatItem(C, "Arial", True, 12, 15, xlAutomatic, xlContinuous, xlThin, True, 54.75)
End With

'STO
TheSTOname = rs!SubTaskDesc
oSheet.Cells(27, 1).Value = TheSTOname
Set C = oSheet.Cells(27, 1)
With C
    Call formatItem(C, "Arial", False, 12, 0, xlAutomatic, xlContinuous, xlThin, True, 54.75)
End With

'Staff
TheStaffName = rs!ReportAs
oSheet.Cells(27, 2).Value = TheStaffName
Set C = oSheet.Cells(27, 2)
With C
    Call formatItem(C, "Arial", False, 12, 0, xlAutomatic, xlContinuous, xlThin, True, 54.75)
End With

'Description
TheActDesc = rs!ActivityDesc
TheTypeID = rs!ActivityTypeID
Select Case TheTypeID
    Case 1
        oSheet.Cells(27, 3).Value = "     * " & TheActDesc
        oSheet.Cells(27, 3).Font.Name = "Arial"
        oSheet.Cells(27, 3).Font.Size = 10
        oSheet.Cells(27, 3).indentlevel = 1
        oSheet.Cells(27, 3).WrapText = True
        oSheet.Cells(27, 3).indentlevel = 1
        oSheet.Cells(27, 3).Borders(xlEdgeRight).LineStyle = xlContinuous
        oSheet.Cells(27, 3).Borders(xlEdgeRight).weight = xlThin
        oSheet.Cells(27, 3).Borders(xlEdgeRight).ColorIndex = xlAutomatic
        oSheet.Cells(27, 3).Borders(xlEdgeLeft).LineStyle = xlContinuous
        oSheet.Cells(27, 3).Borders(xlEdgeLeft).weight = xlThin
        oSheet.Cells(27, 3).Borders(xlEdgeLeft).ColorIndex = xlAutomatic
    Case 3
        oSheet.Cells(27, 3).Value = "- " & TheActDesc
        oSheet.Cells(27, 3).Font.Name = "Arial"
        oSheet.Cells(27, 3).Font.Size = 10
        oSheet.Cells(27, 3).indentlevel = 1
        oSheet.Cells(27, 3).Borders(xlEdgeRight).weight = xlThin
        oSheet.Cells(27, 3).Borders(xlEdgeRight).ColorIndex = xlAutomatic
        oSheet.Cells(27, 3).Borders(xlEdgeLeft).LineStyle = xlContinuous
        oSheet.Cells(27, 3).Borders(xlEdgeLeft).weight = xlThin
        oSheet.Cells(27, 3).Borders(xlEdgeLeft).ColorIndex = xlAutomatic
    Case Else
        oSheet.Cells(27, 3).Value = TheActDesc
End Select

iRow = 28

rs.MoveNext

iNumCols = 3


Do Until rs.EOF
    If rs!TaskOrderDesc = TheTO Then
        If rs!SubTaskDesc = TheSTOname Then
            If TheStaffName = rs!ReportAs Then
                If TheActDesc = rs!ActivityDesc Then
                    rs.MoveNext
                Else
                    TheTypeID = rs!ActivityTypeID
                    Select Case TheTypeID
                        Case 1
                      
                            oSheet.Cells(iRow, 3).Value = "     * " & rs!ActivityDesc
                            oSheet.Cells(iRow, 3).Font.Size = 10
                            oSheet.Cells(iRow, 3).Font.Name = "Arial"
                            oSheet.Cells(iRow, 3).indentlevel = 1
                            oSheet.Cells(iRow, 3).WrapText = True
                            oSheet.Cells(iRow, 3).Borders(xlEdgeRight).weight = xlThin
                            oSheet.Cells(iRow, 3).Borders(xlEdgeRight).ColorIndex = xlAutomatic
                            oSheet.Cells(iRow, 3).Borders(xlEdgeLeft).LineStyle = xlContinuous
                            oSheet.Cells(iRow, 3).Borders(xlEdgeLeft).weight = xlThin
                            oSheet.Cells(iRow, 3).Borders(xlEdgeLeft).ColorIndex = xlAutomatic
                            
                        Case 3
                            oSheet.Cells(iRow, 3).Value = "- " & rs!ActivityDesc
                            oSheet.Cells(iRow, 3).Font.Size = 10
                            oSheet.Cells(iRow, 3).Font.Name = "Arial"
                            oSheet.Cells(iRow, 3).indentlevel = 1
                            oSheet.Cells(iRow, 3).WrapText = True
                            oSheet.Cells(iRow, 3).Borders(xlEdgeRight).weight = xlThin
                            oSheet.Cells(iRow, 3).Borders(xlEdgeRight).ColorIndex = xlAutomatic
                            oSheet.Cells(iRow, 3).Borders(xlEdgeLeft).LineStyle = xlContinuous
                            oSheet.Cells(iRow, 3).Borders(xlEdgeLeft).weight = xlThin
                            oSheet.Cells(iRow, 3).Borders(xlEdgeLeft).ColorIndex = xlAutomatic
                                
                        Case Else
                            oSheet.Cells(iRow, 3).Value = rs!ActivityDesc
                    End Select
                    iRow = iRow + 1
                    rs.MoveNext
                    TheActDesc = ""
                End If
            Else
                oSheet.Cells(iRow, 2).Value = rs!ReportAs
                Set C = oSheet.Cells(iRow, 2)
                With C
                Call formatItem(C, "Arial", False, 12, 0, xlAutomatic, xlContinuous, xlThin, True, 54.75)
                End With
                TheStaffName = rs!ReportAs
            End If
        Else
            oSheet.Cells(iRow, 1).Value = rs!SubTaskDesc
            Set C = oSheet.Cells(iRow, 1)
            With C
            Call formatItem(C, "Arial", False, 12, 0, xlAutomatic, xlContinuous, xlThin, True, 54.75)
            End With
            TheSTOname = rs!SubTaskDesc
            TheStaffName = ""
        End If
    Else
        
        oSheet.Cells(iRow, 1).Value = rs!TaskOrderDesc
        Set C = oSheet.Cells(iRow, 1)
        Range(C.Cells, C.Cells.Offset(0, 1)).Select

        Selection.Merge

        Set C = Selection
        With C
Call formatItem(C, "Arial", True, 12, 15, xlAutomatic, xlContinuous, xlThin, True, 54.75)
        End With
Set C = oSheet.Cells(iRow, 3)
With C
Call formatItem(C, "Arial", True, 12, 15, xlAutomatic, xlContinuous, xlThin, True, 54.75)
End With
        iRow = iRow + 1
        TheTO = rs!TaskOrderDesc

    End If

skiped:
 Loop


Bail:
     rs.Close
      Set rs = Nothing
      Qd.Close
      Set Qd = Nothing
      db.Close
      Set db = Nothing
      Set C = Nothing
      Set oRange = Nothing
      Set oSheet = Nothing
      Set oBook = Nothing
      Set oApp = Nothing
Exit Sub
     
Error_Handler:
     MsgBox Error$
     Resume Bail
   
 End Sub

Open in new window

0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 26

Expert Comment

by:Nick67
ID: 40554450
Your name is certainly familiar
I am hurt you don't recognize mine ;) [kidding]
I've posted enough to your stuff that my Newsfeed email has your name in it.

Now you use QD early (line 70 in the snippet)
sql = "q_r_TO_PMACReport"
Set db = CurrentDb
Set Qd = db.QueryDefs("q_r_TO_PMACReport")

Why you give sql a value and then don't use it is a mystery to me!
This isn't where your problem is, though
Does this querydef have no parameters?

Now at line 209, you have a new Qd to set, that's ok
sql = "q_PMACWeeklyReport"
 Set db = CurrentDb
 Set Qd = db.QueryDefs("q_PMACWeeklyReport")

This one has a parameter

Only one?
I am getting the "Item not found in this collection" error.

That could be a facepalm moment
That saying that Qd.parameters() doesn't have one named "WeeklyReportID"
If you have only one, you can go
Qd.Parameters(0) = RptID
and that'll do it
0
 

Author Comment

by:Megin
ID: 40554562
I do recognize you from the many, many, many questions I post here. (I just get embarrassed because I am always, always, always asking question!)  Also, I posted the equivalent of this question a while ago and then deleted it because I decided the database needed a new table structure. So...

Okay. The query I am using does not have that parameter set (right now). I wanted to RptID to provide the criteria for the query in the WeeklyReportID field.

So, I just changed it to qd.Parameters(0)=RptID and still got the "Item Not Found In This Collection" error.

When I added a parameter to the query called "WeeklyReportID" with a data type of Integer, and then changed the statement to say "qd.Parameters("WeeklyReportID")=RptID I still got the 'not found in collection' error.


"Why you give sql a value and then don't use it is a mystery to me!" - You are right. I removed it. (I get nervous about removing anything once the code actually works. Though, that is habit I am slowing getting out of)
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 40554600
Okay. The query I am using does not have that parameter set (right now). I wanted to RptID to provide the criteria for the query in the WeeklyReportID field.

If the saved query doesn't have a parameter in it, then you can't bark up the QueryDef.parameters tree.
That's ok.
Instead you get to do something simpler
On line 219 you get to go
Set rs = db.OpenRecordset("select * from q_PMACWeeklyReport where WeeklyReportID = " & rptID, dbOpenDynaset, dbReadOnly)

And then you are off to the races.

And really, unless you need to alter something about a saved query, you don't need the querydef

Up at line 70
sql = "q_r_TO_PMACReport"
Set db = CurrentDb
Set Qd = db.QueryDefs("q_r_TO_PMACReport")


Set rs = Qd.OpenRecordset(dbOpenDynaset, dbReadOnly)


Without a need for a parameter, you just need
sql = "q_r_TO_PMACReport"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenDynaset, dbReadOnly)

or

Set db = CurrentDb
Set rs = db.OpenRecordset("q_r_TO_PMACReport", dbOpenDynaset, dbReadOnly)

QueryDef objects let you screw with the properties of a saved query.  One main use is because there are pre-defined parameters you must supply in code before you can use it in a recordset.
The other is that you can completely rewrite the SQL of the query in code

Qd.SQL = "Some Completely New, Valid SQL Statement I Want To Use Now and Until I change It In Code Again;"
0
 

Author Closing Comment

by:Megin
ID: 40554626
A++++++!!!!!

I wish you could have been here to see me silently scream with joy, because that worked!!!!!!!!

Thank you!!!!!!!!!!!!!!!
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now