|
Subject:
|
Getting Query parameters from a Form
|
|
Posted By:
|
DrCruces
|
Post Date:
|
12/8/2006 5:39:49 AM
|
Hello all
I'm writing cause I'm kinda stuck on a project I'm working on, here's the details.
I have a database containing all my clients along with the month and the year they last paid (they are named CMONTH and CYEAR in the database).
I want to make a query that will give me all the names and identifiers of the clients who have last paid on a specific month of a specific year but I don't want to have to declare these through pop up windows.
I have made a form with two text boxes in which I will be entering the month and the year and a button that is supposed to (actually that one works) open up a form that uses a query.
the query looks something like this
SELECT IDENTIFIER, NAME
FROM CLIENTS
WHERE (((CMONTH) = [FORMS]![SELECTION].[TEXT1].[TEXT])
AND ((CYEAR) = [FORMS]![SELECTION].[TEXT2].[TEXT]));
I have tried everything, even using only the year argument however whenever I run the report through the form I always get a blank report (by the way , yes I have tried TRIM and it still doesn't work). However when I run the report by itself and it asks me through a pop up window for the values the results are always correct, can someone please help me with this? I thought of making some variables that would be used as arguments but I don't really know how to do this
Thanks in advance
edit: sometimes when I press the button to show the report, instead of the blank report, I get the message "you can't reference a property or method for a control unless the control has the focus"
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
12/8/2006 7:44:15 AM
|
I am not sure why the query is not taking the values from the form. Is the form open when the report runs?
I would use a combo box instead of a text box. Perhaps the text box is passing a string value, and your cMonth and cYear columns do not have the same data types.
Anyway, that being said, you can pass the values using a WHERE clause on the button's On Click event. Please remember that the data types must match. If cMonth and cYear are Numbers, which they shouldn't be, then you need to pass numbers. If they are strings, then you need to pass strings.
Here is how you would do strings (assume form name is frmMyForm, and text boxes are txtMonth, txtYear and the report is rptMyreport):
------------------------------------------
Dim sMonth, sYear, sLink, sDoc As String
sMonth = Me.txtMonth
sYear = Me.txtYear
sLink = "[cMonth] = '" & sMonth & "' AND [cYear] = '" & sYear & "'"
sDoc = rptMyReport
DoCmd.OpenReport sDoc, acPreview, , sLink
-------------------------------------------
Check the DoCmd. syntax.
For number values:
------------------------------------------
Dim iMonth, iYear As Integer
Dim sLink, sDoc As String
iMonth = CInt(Me.txtMonth)
iYear = CInt(Me.txtYear)
sLink = "[cMonth] = " & iMonth & " AND [cYear] = " & iYear
sDoc = rptMyReport
DoCmd.OpenReport sDoc, acPreview, , sLink
-------------------------------------------
Did any of that help?
mmcdonal
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
12/8/2006 7:45:08 AM
|
P.S. Remove the criteria from the query columns
mmcdonal
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
12/8/2006 7:46:09 AM
|
Also, change the query to this:
SELECT IDENTIFIER, NAME, CMONTH, CYEAR
FROM CLIENTS
and then don't put the last two columns on the report.
mmcdonal
|
|
Reply By:
|
DrCruces
|
Reply Date:
|
12/8/2006 9:43:20 AM
|
thank you very much for the help (unfortunately the database was written with both month and year as a string and I do not know how to change that, i.e. replace jan with 1 feb with 2 etc),
just two quick questions though
sMonth = Me.txtMonth
does this make the content of sMonth "Jan" or Jan ? cause in the where clause I have to put "jan" but it seems that when I join the two strings it just keeps Jan
what is the difference between
sMonth = Me.txtMonth
and
sMonth = Me.txtMonth.text
?
thanks again for the help
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
12/8/2006 10:47:27 AM
|
If you want to replace the month and year with numbers, but not make the column a number column (you shouldn't) then just do an update query and replace Jan with 1, Feb with 2, etc.
The content of the Variable sMonth is a String if you declare it as a string. so it would be "Jan" if that is what was being passed to it.
In that case, your SQL string needs to use ' ' to pass the string.
Me.TxtMonth and Me.txtMonth.Text are the same thing since Me.txtMonth defaults to the Text value displayed in the text box.
HTH
mmcdonal
|
|
Reply By:
|
DrCruces
|
Reply Date:
|
12/13/2006 4:42:16 AM
|
Thank you very much again for your assistance
it seems that Me.TxtMonth works better than Me.TxtMonth.text
when I use the second one I get a message that I can't do it because the item does not have focus so I had to resort to some weird tactics in order to get things done, again thanks a lot
|
