Alter query criteria based on current object

[XLEAccessGuru]

Hi All,

If anyone has any ideas here for me, I'd really appreciate it.

I have MENU1 and MENU2 that each have 2 user input text boxes - begin and end dates. User inputs begin and end dates and query runs for records between those dates. Here's what I need to do:

1. Determine if MENU1 or MENU2 is the active object at run time (double-click event of list box)
2. Right now, the query criteria is predetermined to read "Between Forms![frmMENU1]![Begin] And Forms![frmMENU1]![End]". I need the double-click event code to change the query criteria at run time to refer to the active form and its controls for Begin and End date.

I'm guessing I need to assign and pass some variables, but I still struggle with that a sometimes. Can anyone help?

Thanks!

 

[edtab]

Youl'll need to test which of the two forms is loaded. To do this,
you need a function in your module. I have pasted here the code
which I found by searching the forum:

'=======================================================
Function IsLoaded(ByVal strFormName As String) As Integer
' Returns True if the specified form is open in Form view or Datasheet view.
Const conObjStateClosed = 0
Const conDesignView = 0
If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If
End Function
'=======================================================

To call this function, you will code:

If IsLoaded("Menu1") then
...do something
else
If is loaded("Menu2") then
...do something else
End If
End If

HTH

 

[gemma-the-husky]

i would do this with variables - store the dates you currently want to use in global variables in a module (not in a form module - use a global module), then you needn't worry which form you have open - it will work anywhere you need it to.

ie - in a module put
public datefrom as date
public dateto as date

now, you cant just read a variable directly in a query, so you need a function to retrieve the values. Cast the value with an nz, then if the dates haven't been initiallized it won't crash the function.

function readdatefrom() as date
readdatefrom = nz(datefrom,0)
end function

and a similar function for readdateto

now in your query instead of

Between Forms![frmMENU1]![Begin] And Forms![frmMENU1]![End]".

you can put

between readdatefrom() and readdateto()

(you need the brackets!)
-----------------------------------
so in your form, all you need to do is somewhere - either in controls afterupdate events, or in a buttonclick event

datefrom = whateverdate1 and
dateto = whateverdate2

and the query will work ok.

Hope this helps

 

[XLEAccessGuru]

This is exactly what I was looking for. Thank you!

Call me stupid, but when you say "whateverdate1" and "whateverdate2" what is this referring to? Shouldn't I be calling the read dates functions on my button click event in my form?

 

[XLEAccessGuru]

Nevermind, I got it to work.

But I do have one more question for you. I also have to create this global variable for a string. What's the equivalent syntax for a string, i.e. for a date, you showed me the syntax "readdatefrom = nz(datefrom,0)". My global string variable is strDiv. If I create a function called ReadDiv(), what goes after:

Public ReadDiv()
ReadstrDiv = ?????

Thanks Husky! I am eternally grateful.
-L

 

[gemma-the-husky]

as you probably understood, i meant set the gloabl dates toi anydate you require - probably the dates in your textboxes in this case.

create another global variable

public gblStrDiv as string

public function readdiv() as string
readdiv=nz(gblstrdiv,"")
'or even readdiv=nz(gblstrdiv,vbnullstring) 'which is the same thing
end function

'again the nz deals with the situation when the string is not initialized and sets it to a blank string instead of null, which would cause an error probably
'the nz s may not be necessary, but they do add a bit of extra robustness

 

[XLEAccessGuru]

Thanks Husky.

I did figure out the string null syntax. But I cannot, for the life of me, get it to work because there is an if.. then situation.

See if you can figure this out.

Global strDiv as string

Public Function ReadDiv()
If strDiv="ALL DIVISIONS" then
ReadDiv="'PAC' OR 'PRF' OR 'FCL' OR 'LON'"
Else
readDiv=strDiv
End Function

I need this if.. then because our data includes business units that are not ours so I have to exclude them. If user selects ALL DIVISIONS (and thus sets the public variable to "ALL DIVISIONS") then I need the Division field in the query to include what's in the string "'PAC' OR 'PRF' OR 'FCL' OR 'LON'". If I paste this string (without quotes) into my query design grid directly, it works, but if I put the ReadDiv() function in there as criteria and select "ALL DIVISIONS" in my menu, it does not work. The text is identical to what I put in the query grid. It just won't work! No data is returned when I use the function as opposed to copying the string into the query grid.

I used a watch to watch the variable, as well as the immediate window. I even pasted the exact text from the IW after calling the value into the query design grid in place of the function and again, the text works, but not the function.



Could it have something to do with the way I wrote the variable to set itself?

Last point of help here. Thanks so much for your time Husky. Once I fix this last issue, I'll be all set!

-L

 

[gemma-the-husky]

the trouble is, the text isnt identical

if you put it directly into the query as
'PAC' OR 'PRF' OR 'FCL' OR 'LON' you dont have any problem.

if you put this string in as a variable you are now saying

where fieldname = " 'PAC' OR 'PRF' OR 'FCL' OR 'LON' "

ie, the whole string, which is clearly not the same thing at all.

-----------------------------------------------
I am not sure of the best way round this. I think you can include a function call in the criteria - something like

testdiv(divfieldname)=true


your function would now need to be a boolean

function testdiv(fieldname as string) as boolean
and in here you would need to test whether the fieldname is in your required list or not, and return true if it is, or false if its not.
end function

i am not sure if you can put the function in a criteria like this. if not just put
testdiv(divfieldname) in a new column, and put true in the criteria column, which will do the same thing.

I hope this all makes sense.

good Luck