Using Public Variables with an Access Query

[t1hodges]

Can anyone tell me how to use a Public variable declared within a VBA module as a criteria within an MS Access query

Thanks

 

[PHV]

You have to play with a public function retrieving the public value.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RoyVidar]

You'd need a public wrapper function:

[tt]public function getmypublic() as <relevant datatype>
getmypublic = mypuplic
end function[/tt]

then use the name of the public function in the query (note - both the variable and function would probably need to be placed in a standard module, not a forms/reports module).

Roy-Vidar

 

[t1hodges]

Thank you gentleman, your tips work great. Here's what I created

Public value as string
option compare database
---------------------------------------------------
Public Function QryFilter1() as string
value = "Simmons, Haston" ' this is the value for which I needed to filter my results
QryFilter = value
End Function


After creating this module I create a select query. I typed in 'Like QryFilter()' in the criteria field and it return the expected rows of data.

Thanks again

 

[t1hodges]

new problem...

I need to get the public variable to change as I iterate through the values of a recordset.

Here's my code
-------------------------------------------
Public value1 As String
Public fldTmp As DAO.Field

Option Compare Database
---------------------------------------------
Public Function QryFilter1() As String

value1 = fldTmp
QryFilter1 = value1

End Function
---------------------------------------------

Public Function PrintBatch()
Dim db As DAO.Database
Dim rstTmp As DAO.Recordset

Set db = CurrentDb()
Set rstTmp = db.OpenRecordset("tbl_Batch_Filter_Source")

rstTmp.MoveFirst
Do Until rstTmp.EOF
For Each fldTmp In rstTmp.Fields
value1 = CStr(fldTmp.value)
DoCmd.OpenReport "rpt_Payments_Cpty", acNormal 'this report's source is a select query that is using the Public Function QryFilter1() as a field criteria

rstTmp.MoveNext
Next fldTmp
Loop
rstTmp.Close

End Function
-------------------------------------------------------
I have a button on a form that simply calls the function

Private Sub Command13_Click()
Call PrintBatch

End Sub

 

[Eupher]

t1hodges,

You haven't said what isn't working so I'm not quite sure how to assist. However, looks like you've got some redundancy with your variables. Why is fltTmp declared as Public? In your PrintBatch function you assign a value to value1 with each loop, then open the query which calls QryFilter1 - which immediately assigns the value again. Why not just declare fldTmp in PrintBatch? Unless it's being called from somewhere else in your code that you havent' posted, seems to me it doesn't need to be public. Then you don't need to refer to it in QryFilter1 since value1 is already being assigned for each field, and for each loop.

HTH,

Ken S.

 

[t1hodges]

Hi Eupher, thanks for your reply. Im using "QryFilter()" as a criteria in an Access query which is the source for a report. What needs to happen is as I loop through each record in "rstTmp", assign that record value to the public variable "value1" so it can be assigned to "QryFilter()" when it is used in my Access query. I also need the query to run and the report to print each time "value1" has been assigned a new value from "rstTmp".

 

[Eupher]

Yup, I got that. What isn't working, why the declaration of fldTmp as Public, and why the redundant assignment to value1?

Ken S.

 

[t1hodges]

Im looking for help on the structure of this code. the declaration of fldTmp as Public isnt necessary, i'll fix that. Regarding the variable "value1", It was explained to me at the beginng of this thread, that in order to use a public variable as a filter in an access query I needed to pass the value through a public function, then use the public function as my query criteria, and it does work. I tested by making "value1" = to a hard coded string value and it passes the value through to the access query, and it works with the "fldTmp" variable, but I just can't get the values to change as I go through the recordset.

 

[Eupher]

Hi, t1hodges,

Okay, not sure what your level of experience is with the VBE, but there are some tricks you can use to help see what's going on and track down a problem like this. You can set a watch on value1 so the code breaks whenever the value changes; you can set a breakpoint in your code and step through it line by line to follow the flow of execution - and as you hover your mouse over the variable names, you can see what they currently hold; you can use Debug.Print to print the value of variables in the Immediate window.

I'm going to set up a little test to see if I can replicate your problem. I'll post back when I have an answer.

Ken S.

 

[t1hodges]

Cool beans Ken, I appreciate any and all help.

 

[Eupher]

t1hodges,

Could you please post the SQL of your query?

Ken S.

 

[t1hodges]

Ok. Here it is

SELECT sSel_Payments_byCpty.product_id, sSel_Payments_byCpty.book_name, sSel_Payments_byCpty.settle_date, sSel_Payments_byCpty.amount_ccy, sSel_Payments_byCpty.amount, sSel_Payments_byCpty.trade_id, sSel_Payments_byCpty.transfer_id, sSel_Payments_byCpty.ext_le_id, sSel_Payments_byCpty.cpty_code, sSel_Payments_byCpty.cpty_name, sSel_Payments_byCpty.start_date, sSel_Payments_byCpty.end_date, IIf(sSel_Payments_byCpty!notional<>qry_cash_flow_notional!quantity,qry_cash_flow_notional!quantity,sSel_Payments_byCpty!notional) AS notional, sSel_Payments_byCpty.not_unit, sSel_Payments_byCpty.transfer_type, sSel_Payments_byCpty.product_family, sSel_Payments_byCpty.pmt, sSel_Payments_byCpty.pmtamt, sSel_Payments_byCpty.ext_name, sSel_Payments_byCpty.ext_agent_account, sSel_Payments_byCpty.ext_agent_le, sSel_Payments_byCpty.ext_agent_contact, sSel_Payments_byCpty.ext_int_account, sSel_Payments_byCpty.ext_agent_name, sSel_Payments_byCpty.ext_agent_sub_acc, sSel_Payments_byCpty.ext_int_sub_acc, sSel_Payments_byCpty.int_name, sSel_Payments_byCpty.int_agent_le, sSel_Payments_byCpty.int_agent_contact, sSel_Payments_byCpty.int_agent_account, sSel_Payments_byCpty.type, sSel_Payments_byCpty.Strike
FROM (sSel_Payments_byCpty INNER JOIN tbl_stats ON sSel_Payments_byCpty.cpty_code = tbl_stats.CDSCPTY) LEFT JOIN qry_cash_flow_notional ON (sSel_Payments_byCpty.product_id = qry_cash_flow_notional.product_id) AND (sSel_Payments_byCpty.settle_date = qry_cash_flow_notional.payment_date)
WHERE (((sSel_Payments_byCpty.settle_date) Between [Forms]![frmPayments]![Text4] And [Forms]![frmPayments]![Text8]) AND ((sSel_Payments_byCpty.cpty_code) Like QryFilter1()))
WITH OWNERACCESS OPTION;

 

[Eupher]

Unfortunately, the SQL didn't help me. I was making a guess as to what was happening, but your SQL didn't match my guess! However, take a look at the order of the statements in blue:

Do Until rstTmp.EOF
For Each fldTmp In rstTmp.Fields
value1 = CStr(fldTmp.value)
DoCmd.OpenReport "rpt_Payments_Cpty", acNormal 'this report's source is a select query that is using the Public Function QryFilter1() as a field criteria

[COLOR=blue][b]rstTmp.MoveNext
Next fldTmp[/b][/color]
Loop

You're moving to the next record *before* you move to the next field. So the progression as you now have it is Record1:Field1, Record2:Field2, Record3:Field3, etc. Is that what you intend? My assumption was you wanted Record1:Field1, Record1:Field2, Record1:Field3, etc., Record2:Field1, Record2:Field2, etc. I set up a test similar to your code but had those two statements reversed and had no problem with value1.

Ken S.

 

[t1hodges]

Cool. I'll make changes and reply back with the results.