Feeding Expressions from Forms to Query 1

[Janice7]

I'm a novice Access user, so pardon me if some of the terms I refer to is not precise...

This should be pretty simple for some of you advanced users - I have a form that contains a combo box which is bound to a table. When a certain record is selected (in the combo box), I want to feed that record to the criteria of another query. For example:

The table (tblDates)contains that dates
6/15/2002
7/1/2002
7/15/2002
8/1/2002
...
...

These records are bounded to a combo box (cmbDates) on a form (frmSwitchBoard). When 7/15/2002 is selected (in the combo box), I want the criteria of a field of another query (qryTemp) to be >7/15/2002...

Does anyone know how to feed this information to there??
Any help is great.

Janice

 

[LittleSmudge]

Yes Janice - all perfectly possible, ( if, as you say the terms are a little out of place. )


You have a form frmSwitchBoard containing a control cmbDates

The Form is bound to a Table Containing Records.
The Control is Bound to a Field

A record being a row in the matrix, a field being a column in the matrix.

You now have a "Query" - but where are you using this query?
In another form?
Or is it the source query for another form ( Which amounts to almost the same thing )

If so, then you can use something along the lines of

"SELECT * FROM tblOtherTableName WHERE datDateField > #" & Format(Forms!frmSwitchBoard!cmbDates,"Medium Date&quot & "#;"


The # marks tell Jet that this value is to be interpreted as a date.

The Format stuff gets round the problem that Access arranges dates according to the International setting on your PC whereas JET ignores this.
So whereas most of the world interprets 7/1/2002 is the logical 7th January , to JET it is July 1st !
The Format() avoids the problem



'ope-that-'elps.

G LS

 

[Janice7]

Thank You LittleSmudge! That was very helpful

Now, I have another similiar problem. On the form, Instead of having the dates in 1 combo box like above, I want them in two combo boxes - as below:

cmbMonth: January cmbYear: 2002
February 2003
March 2004
... 2005
... ...

Also on the form is an "OK" button.

Obviously, this is more user-friendly, because the user can simply select the month followed by the year.

Now I want to feed the information submitted by the user to a query. (ie. <3/1/2004) I constructed the following code for it - this is executed when the &quot;OK&quot; button is clicked. but I don't know how to feed the &quot;Parameter&quot; variable back to a query.


Public Sub SwitchBoardOk_Click()

'Declaring Variables
Dim Month, MonthString, Year, Parameter As String

'Setting variables to that of the switchboard
MonthString = Forms!frmDashBoardSwitchBoard!MonthDropDown
Year = Forms!frmDashBoardSwitchBoard!YearDropDown

'Determine the parameter passed to the query
If MonthString = &quot;January&quot; Then
Month = &quot;1&quot;
ElseIf MonthString = &quot;February&quot; Then
Month = &quot;2&quot;
ElseIf MonthString = &quot;March&quot; Then
Month = &quot;3&quot;
ElseIf MonthString = &quot;April&quot; Then
Month = &quot;4&quot;
ElseIf MonthString = &quot;May&quot; Then
Month = &quot;5&quot;
ElseIf MonthString = &quot;June&quot; Then
Month = &quot;6&quot;
ElseIf MonthString = &quot;July&quot; Then
Month = &quot;7&quot;
ElseIf MonthString = &quot;August&quot; Then
Month = &quot;8&quot;
ElseIf MonthString = &quot;September&quot; Then
Month = &quot;9&quot;
ElseIf MonthString = &quot;October&quot; Then
Month = &quot;10&quot;
ElseIf MonthString = &quot;November&quot; Then
Month = &quot;11&quot;
ElseIf MonthString = &quot;December&quot; Then
Month = &quot;12&quot;
End If
Parameter = Month & &quot;/1/&quot; & Year
Debug.Print Parameter

'Open Main Form
DoCmd.OpenForm &quot;frmMain&quot;, acNormal

End Sub

Any help is appreciated! Thanks

Janice

 

[LittleSmudge]

Janice, A nice piece of code - it's a pity it goes no way towards helping you with your problem !

(By the way, Month and Year are Function names in Access : It is unwise to use system key works, system function names etc as field names or variable names. It makes code maintenance hard work, it can confuse anyone reading the code and it can occasionally confuse the system operation itself )

I think what you need is something more along the lines of :-

Public Sub SwitchBoardOk_Click()
Dim strSQL As String
Dim strTheDate As String
Dim strWhere As String


' This bit checks that valid data is in combo boxes
If IsNull(Forms!frmDashBoardSwitchBoard!MonthDropDown) Then
MsgBox &quot;I cannot proceed without a valid Month&quot;,,&quot;Don't ask me to do the impossible.&quot;
Exit Sub
End If
If IsNull(Year = Forms!frmDashBoardSwitchBoard!YearDropDown) Then
MsgBox &quot;I cannot proceed without a valid Year&quot;,,&quot;Don't ask me to do the impossible.&quot;
Exit Sub
End If

strTheDate = Forms!frmDashBoardSwitchBoard!MonthDropDown & &quot;/1/&quot; & Forms!frmDashBoardSwitchBoard!YearDropDown


strSQL = &quot;SELECT * FROM tblOtherTableName WHERE datDateField < #&quot; & strTheDate & &quot;#;&quot;



' Now, if you want to use this as the WHERE Clause of a Form you are about to open then you don't need strSQL, instead you need:-

strWhere = &quot;WHERE datDateField < #&quot; & strTheDate & &quot;#;&quot;

DoCmd.OpenForm &quot;frmMain&quot;, acNormal, , strWhere

End Sub




'ope-that-'elps.

G LS

 

[Janice7]

Thanks LittleSmudge. I understand your code. However, I have another button that actually execute the query - by running the strSQL statement. It looks like:

Private ExecuteQuery_Click()

DoCmd.RunSQL strSQL
... ...

End Sub

But the compiler does not recognize the variable strSQL. So my question is - how would you pass a string variable from a sub (already declared public) to another sub? Both are bounded to different controls.

Janice

 

[Janice7]

Thanks LittleSmudge. I understand your code. However, I have another button that actually execute the query - by running the strSQL statement. It looks like:

Private ExecuteQuery_Click()

DoCmd.RunSQL strSQL
... ...

End Sub

But the compiler does not recognize the variable strSQL. So my question is - how would you pass a string variable from a sub (already declared public) to another sub? Both are bounded to different controls.

Janice

 

[Janice7]

One more thing - the buttons are on different forms.

Janice

 

[LittleSmudge]

Oh you do enjoy making life complicated don't you.

TWO options spring to mind

1) On the Form that creates the strSQL string, place a text box control and make it hidden ( .Visible = No ).
Then, in the code that populates the strSQL variable - get it to populate the control instead.
So if the Form is called frmMakeSQL and the control is called txtSQL then the code will contain a line like :-

txtSQL = &quot;SELECT * FROM tblOtherTableName WHERE datDateField < #&quot; & strTheDate & &quot;#;&quot;


On the form containing the button you put:-

Private ExecuteQuery_Click()

DoCmd.RunSQL Forms!frmMakeSQL!txtSQL
... ...

End Sub

This reads the string from the form even though the control is not visible and the form does not have the focus.


2) The other option is to define strSQL as a Global variable. One form can then populate it and another form can use it.
If you use this ( usually considered risky ) option then make sure that strSQL is unique over the entire application - else you run the risk of some strange errors poping up.


'ope-that-'elps.

G LS

 

[Janice7]

Thanks, LS. How would you declare a global variable that can be seen throughtout the entire app? Just place it outside of the subroutines?

Janice

 

[LittleSmudge]

No.

There are THREE levels of Scope. From the smallest these are:-

LOCAL : Only valid inside the Subroutine or Function

MODULE : eg. Dim statements at the top of a Form's code Module. These are valid within the module and any Subroutine or Function in that Form's module can use them

GLOBAL : These are app wide and need to be declared in a Global Module

Go to the Database Window and along with the Table, Query, Form tabs you'll see MODULE
Select the Module tab and click on New to create a new module - if you don't have any already.

Option Compare Database
Option Explicit
Dim gstrSQL As String

(Hungarian Notation standard suggests a lower case g prefix to Global variable to flag the fact they are globals. )



'ope-that-'elps.

G LS

 

[Janice7]

Thank You LS.

Do you mean:

Option Compare Database
Option Explicit
Public gstrSQL As String

(instead of Dim gstrSQL As String)??

Janice

 

[LittleSmudge]

If you like .. ..

Because it is happening in a Global Module then you can use DIM and it is it's location that makes it global.

PUBLIC is a get out option that allows you to make globally scoped variables in other places.


So in a Global Module then, 'Dim' is exactly the same as 'Public'.


G LS