Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 8th, 2006, 06:39 AM
Authorized User
 
Join Date: Dec 2006
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default Getting Query parameters from a Form

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"
 
Old December 8th, 2006, 08:44 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old December 8th, 2006, 08:45 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

P.S. Remove the criteria from the query columns

mmcdonal
 
Old December 8th, 2006, 08:46 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old December 8th, 2006, 10:43 AM
Authorized User
 
Join Date: Dec 2006
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


 
Old December 8th, 2006, 11:47 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old December 13th, 2006, 05:42 AM
Authorized User
 
Join Date: Dec 2006
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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






Similar Threads
Thread Thread Starter Forum Replies Last Post
run query via vba with parameters The_Vicar VBScript 0 April 21st, 2008 06:38 AM
Passing parameters to Access query pcassistnw Access 0 March 2nd, 2007 11:07 PM
Form Input, Parameters, Report Based on Query DavidWE Access VBA 2 November 6th, 2006 10:36 AM
Passing Multiple Parameters into Access Query rit01 Classic ASP Databases 1 October 26th, 2005 04:00 PM
stored query with adodc looking for parameters typhun VB Databases Basics 0 February 12th, 2004 07:51 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.