VBA passing rs! into function

[Ruegen]

I have

filePDF = SavedReportAsPDF("rptReflow", [TeacherID], rs!TeacherID)

the function

Function SavedReportAsPDF(ReportName As String, IDWhere As Variant, IDWhereto As Variant) As String


Dim MyPDFPath As String
Dim str1 As String
Dim str2 As String


str1 = IDWhere
str2 = IDWhereto


Debug.Print str1


MyPDFPath = "C:\PDF Files\" & ReportName & ".pdf"


DoCmd.OpenReport ReportName, acViewPreview, , " str1 = " & str2
DoCmd.OutputTo acOutputReport, "", acFormatPDF, MyPDFPath, False
DoCmd.Close acReport, ReportName


SavedReportAsPDF = MyPDFPath


End Function

I don't know how to pass the value across or what type it should be since it is a recordset clone

I'd like to have it so I can add rs!TeacherID to IDWhereto and [TeacherID] to IDWhere

[Ruegen]

Basically I am trying to get this if anyone can help

Function SavedReportAsPDF(ReportName As String, strIDName As String, rsIDvalue As Variant) As String


Dim MyPDFPath As String
Dim str1 As String




str1 = "'[ " & strIDName & "] = '" & varIDvalue




MyPDFPath = "C:\PDF Files\" & ReportName & ".pdf"


DoCmd.OpenReport ReportName, acViewPreview, , str1
DoCmd.OutputTo acOutputReport, "", acFormatPDF, MyPDFPath, False
DoCmd.Close acReport, ReportName


SavedReportAsPDF = MyPDFPath


End Function

using filePDF = SavedReportAsPDF("rptReflow", "TeacherID", rs!TeacherID)

[June7]

You aren't passing a recordset object, just the value from one field of a record of a recordset.

But you don't show code that opens and sets recordset.

You reference varIDvalue but that variable is not declared or set. Should be rsIDvalue.

You have an apostrophe in wrong place.

str1 = "[" & strIDName & "] = '" & rsIDvalue & "'"

This code assumes the field and parameter are always text type.

Have you run Compact & Repair? Have you step debugged? Do you have Option Explicit in the module header?

[Ruegen]

You aren't passing a recordset object, just the value from one field of a record of a recordset.

But you don't show code that opens and sets recordset.

You reference varIDvalue but that variable is not declared or set. Should be rsIDvalue.

You have an apostrophe in wrong place.

str1 = "[" & strIDName & "] = '" & rsIDvalue & "'"

This code assumes the field and parameter are always text type.

Have you run Compact & Repair? Have you step debugged? Do you have Option Explicit in the module header?

what if I...

Function SavedReportAsPDF(ReportName As String, strIDName As String, rsIDvalue As Variant) As String


Dim MyPDFPath As String
Dim strWhere As String




strWhere = "[" & strIDName & "] = '"




MyPDFPath = "C:\PDF Files\" & ReportName & ".pdf"


DoCmd.OpenReport ReportName, acViewPreview, , strWhere & rsIDvalue

DoCmd.OutputTo acOutputReport, "", acFormatPDF, MyPDFPath, False
DoCmd.Close acReport, ReportName


SavedReportAsPDF = MyPDFPath


End Function

The rs!TeacherID is the id in the records - that works fine.

I assume I want the object not the value of rs!TeacherID to pass it through - that is why I chose variant? Or should I choose integer? I'm not wanting to make a variable first then pass the variable through the function - I just want to pass through rs!TeacherID (else I am always having to stuff rs!TeacherID etc into a variable first)


Because I can't pass through the [TeacherID] for some reason I tried as a string so "TeacherID" then add the [ and ]


The function is in a module so I can use it a across other forms...

(I'm still new to all this)

[June7]

If you want whatever form/report to open filtered to TeacherID, then pass the value to the rsIDValue argument by reference to rs!TeacherID. Which is what I see you doing and should work. However, still not showing code that opens and sets recordset. Why is a recordset needed?

This structure leaves an unmated apostrophe in the constructed strWhere value. There was nothing wrong with the line as I corrected in previous post.

Is TeacherID a number or text? If it is a number then don't use apostrophe delimiters.

[Ruegen]

If you want whatever form/report to open filtered to TeacherID, then pass the value to the rsIDValue argument by reference to rs!TeacherID. Which is what I see you doing and should work. However, still not showing code that opens and sets recordset. Why is a recordset needed?

This structure leaves an unmated apostrophe in the constructed strWhere value. There was nothing wrong with the line as I corrected in previous post.

Is TeacherID a number or text? If it is a number then don't use apostrophe delimiters.

TeacherID is a number

I get confused when I start using " - to be corrected if I use " I have to add another after correct? so "" will end up being """ to insert a "?

I will return to the line you suggested - fix it at work tomorrow.

rs! is from a form recordset clone which I use to go through each record and validate their email, details and what time of booking to email accordingly through outlook (although lately I have been using CDO for some things, I still need to use outlook for others).

[June7]

Yes, on the quote marks if using instead of apostrophe as delimiter but since TeacherID is a number don't need text delimiter.

[Ruegen]

so to be clear if I break it down

strWhere = "[" & strIDName & "] = '" & rsIDvalue & "'"

The first " is the start of the string, the [" stops the string and allows for the variable string "TeacherID" (and needs to be because I am asking for the field name in the table) in then continues the string with the next "] and end at =" to allow for the number from the rs!TeacherID and to finish adds the final " by putting two ""

Is my understanding correct?

[June7]

You said TeacherID is a number field so don't use text delimiters.

strWhere = "[" & strIDName & "] = " & rsIDvalue

If you are going to use this function from various places and the input can be different field types, this will not work without first determining the type of input and using appropriate delimiter or else passing the delimiter with the argument. Example:

using filePDF = SavedReportAsPDF("report name", "text type fieldname", "'" & rs!textfield & "'")

using filePDF = SavedReportAsPDF("report name", "date/time type fieldname", "#" & rs!datefield & "#")

[Ruegen]

You said TeacherID is a number field so don't use text delimiters.

strWhere = "[" & strIDName & "] = " & rsIDvalue

If you are going to use this function from various places and the input can be different field types, this will not work without first determining the type of input and using appropriate delimiter or else passing the delimiter with the argument. Example:

using filePDF = SavedReportAsPDF("report name", "text type fieldname", "'" & rs!textfield & "'")

using filePDF = SavedReportAsPDF("report name", "date/time type fieldname", "#" & rs!datefield & "#")

yeah that is why I removed the " in post 4

Yes, I wouldn't be using this function for anything other than a number and not a date field. It's just for saving time and giving a clean view of the code I have on the event. Good thing I can't sleep cause when work starts I am going to be stoked when it all works

(It 4:30 am here)

[Ruegen]

curious, how does vba know that if I pass rs!TeacherID it is getting the value and nothing else? rs!TeacherID.value I assume wouldn't work

[June7]

rs!TeacherID is being referenced like a variable. It is not between quote marks so the value of the variable is passed to the called function. If it were between quote marks then the literal text "rs!TeacherID" would be passed.

I really don't understand your confusion. You want to pass the fieldname and the parameter in two arguments then the function call as structured should work. An alternative is to reduce the function to two arguments and construct the strWhere in the function call.

using filePDF = SavedReportAsPDF("rptReflow", "[TeacherID]=" & rs!TeacherID)

I still don't know how rs is declared and set.

[Ruegen]

rs!TeacherID is being referenced like a variable. It is not between quote marks so the value of the variable is passed to the called function. If it were between quote marks then the literal text "rs!TeacherID" would be passed.

I really don't understand your confusion. You want to pass the fieldname and the parameter in two arguments then the function call as structured should work. An alternative is to reduce the function to two arguments and construct the strWhere in the function call.

using filePDF = SavedReportAsPDF("rptReflow", "[TeacherID]=" & rs!TeacherID)

I still don't know how rs is declared and set.

Yeah I may just shorted the method into passed instead of three as that seems to look a lot better and less time consuming

rs is a dao recordset

set as recordset clone

[June7]

Why do you need recordsetclone as opposed to just referencing field/textbox of form?

[Ruegen]

Why do you need recordsetclone as opposed to just referencing field/textbox of form?

I loop through a 1,000 + records - if I make it reference the textbox then I get an animated form because the loop cycles through the records.