Print Selected records from DropDownBox

**Don Leverton** · Nov 12 '05, 11:04 PM

Re: Print Selected records from DropDownBox

Hi David,

OK ... try this, I think you're on the right track:
Be sure to have a look at that web link and modify [chooseMonth]'s RowSource
property to include the "(All)" thing.... right?
If all of this still doesn't work, post the RowSource of you combo-box too.

Note that I used -- Format(qryGSTRe cieved.PaidDate ,"yyyymm" -- in the WHERE
section below. If you're going to have the SQL find data, it has to be in
the same format as the combo-box.

*************** *************** *************** ****
Private Sub chooseMonth_Aft erUpdate() 'Right?
' "Add All to List" Courtesy: Dev Ashish
' http://www.mvps.org/access/forms/frm0043.htm
' ALWAYS a good idea to pay homage to those we learn and borrow from :)
' Also makes a great reminder for "How the heck did I do that last time?"

Dim MyMonth
MyMonth = Me![chooseMonth]

'Dim MySql as String ' You have this in the form's declaration, right?
MySql = "" 'Clear the string variable

'Construct the MAIN SQL statement here
MySql = MySql & "SELECT qryGSTRecieved. * FROM qryGSTRecieved "

'Now test for and add the WHERE portion here
If Len(MyMonth) > 0 And InStr(1, MyMonth, "All") = 0 Then
MySql = MySql & "WHERE (((Format(qryGS TRecieved.PaidD ate,"yyyymm"))= ("
MySql = MySql & MyMonth
MySql = MySql & "))) "
End If

' add the ORDER BY here.
' -- I changed this from being conditional. You'll always want it sorted
this way, correct?
MySql = MySql & "ORDER BY "
MySql = MySql & qryGSTRecieved. PaidDate

'Finally, "close out" the SQL string using the semi-colon
MySql = MySql & "; "

'Check the resulting SQL string.
'If an error is displayed, you can copy the SQL string from the debug
window
'into a new query in order to help determine why it isn't working
'Debug.Print MySQL 'Uncomment this line and post the resulting SQL string
if this doesn't work.

'Setting the subform's Recordsource here automatically updates the display.
'No need to requery the subform.
Me.fsubGSTRecie ved.Form.Record Source = MySql

End Sub
*************** *************** *************** ****

--
HTH,
Don
=============== ==============
Use My.Name@Telus.N et for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

=============== =============== ==

"DD" <david.deacon@b igpond.com.au> wrote in message
news:1412655e.0 404242247.7fba5 34c@posting.goo gle.com...[color=blue]
> I have a mainform with a subform.[color=green]
> > The main form has a dropdown box "chooseMont h", in the afterupdate event
> > i requery the subform so all records with the same date are viewed.
> > Now i only want to print the selected records of the selected month
> > Can any one advise on the below[/color]
>
> Below is code i am trying to adapt (Thanks Don)
> 1.This first part is causing me problems so i have deleted it as it
> strikes me that it is looking for a listbox and i have a form with a
> subform please correct me if i am wrong as an error appears with the
> VarItm.[/color]
---------------------------------------------------------------
Yes you are right in assuming that that was a ListBox control ... sorry, I
should have mentioned that.
---------------------------------------------------------------[color=blue]
> 2.
> The other part of the code is what i am working on, my problem is that
> Me.choosemonth is giving me an error 3070 does not recognise 200403 as
> a valid field .
> Where do i get this number from? qryGSTRecieved
> PaidMonth: Format([paidDate],"yyyymm ")
> This is used in the dropdown box Me.ChooseMonth
> --------------------------------------------------------------------------[/color]
------[color=blue]
> Deleted this
>
> Dim ctl As Control
> Dim varItm As Variant
>
> Dim strSelected As String
>
> Set ctl = Me.lstTransType
> For Each varItm In ctl.ItemsSelect ed
>
> If Len(strSelected ) > 0 Then
> strSelected = strSelected & ", " & chr$(39) &
> ctl.ItemData(va rItm) & chr$(39)
> Else
> strSelected = chr$(39) & ctl.ItemData(va rItm) & chr$(39)
> End If
>
> Next varItm
>
> 'Debug.Print strSelected
> --------------------------------------------------------------------------[/color]
------[color=blue]
> Using this
>
> MySql = "" 'Clear the string variable
>
> 'Construct the MAIN SQL statement here
> MySql = MySql & "SELECT qryGSTRecieved. * FROM qryGSTRecieved "
>
>
>
> 'Now test for and add the WHERE portion here
> If Len(strSelected ) > 0 And InStr(1, strSelected, "All") = 0 Then
> MySql = MySql & "WHERE (((qryGSTReciev ed.PaidDate)In( "
> MySql = MySql & strSelected
> MySql = MySql & "))) "
> End If
>
> 'Again, test for and add the ORDER BY here.
> If Not IsNull(Me.choos eMonth) Then
> MySql = MySql & "ORDER BY "
> MySql = MySql & Me.chooseMonth
> End If
>
> 'Finally, "close out" the SQL string using the semi-colon
> MySql = MySql & "; "
>
> 'Check the resulting SQL string.
> 'If an error is displayed, you can copy the SQL string from the debug
> window
> 'into a new query in order to help determine why it isn't working
> 'Debug.Print MySQL
>
> 'Setting the subform's Recordsource here automatically updates the
> display.
> 'No need to requery the subform.
> Me.fsubGSTRecie ved.Form.Record Source = MySql
> Set ctl = Nothing
>
> End Sub
> =============== =============[/color]

**David Deacon** · Nov 12 '05, 11:05 PM

Re: Print Selected records from DropDownBox

Hi Don
thanks for this.
Applogies to Dev Ashish

1. MySql = MySql & "WHERE
(((Format(qryGS TRecieved.PaidD ate,"yyyymm"))= ("
I have remove the " " around the yyyymm as it would not work otherwise.
2.Error says that qryGSTRecieved Varible not defined
MySql = MySql & qryGSTRecieved. PaidDate

3. My recordsource for DropDown box
SELECT DISTINCT qryGSTRecieved. PaidMonth,
Format([paidDate],"mmm yyyy ") AS Expr1 FROM qryGSTRecieved;

I am continuing to try to fix the problems
Thanks for all your help

DD

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

**Don Leverton** · Nov 12 '05, 11:07 PM

Re: Print Selected records from DropDownBox

Hi David,

Re #1:
Your formats for the data in the query MUST match the format of the data in
your combo-box.
You have Format(qryGSTRe cieved.PaidDate ,"yyyymm") in your SQL, and
Format([paidDate],"mmm yyyy ") in your combo box.
Your query will likely return ZERO records because it's trying to find
"200403" in a field that contains "Apr 2003"

Re #2
Ooops -- my fault... I forgot to include the quotation marks... should be:
MySql = MySql & "qryGSTRecieved .PaidDate"

Give those two things a whirl.
If it STILL doesn't work, please post the entire SQL string.

Regards,
Don
=============== =============

"David Deacon" <david.deacon@b igpond.com.au> wrote in message
news:408c5506$0 $203$75868355@n ews.frii.net...[color=blue]
> Hi Don
> thanks for this.
> Applogies to Dev Ashish
>
> 1. MySql = MySql & "WHERE
> (((Format(qryGS TRecieved.PaidD ate,"yyyymm"))= ("
> I have remove the " " around the yyyymm as it would not work otherwise.
> 2.Error says that qryGSTRecieved Varible not defined
> MySql = MySql & qryGSTRecieved. PaidDate
>
> 3. My recordsource for DropDown box
> SELECT DISTINCT qryGSTRecieved. PaidMonth,
> Format([paidDate],"mmm yyyy ") AS Expr1 FROM qryGSTRecieved;
>
> I am continuing to try to fix the problems
> Thanks for all your help
>
> DD
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

Print Selected records from DropDownBox

Print Selected records from DropDownBox

Comment

Comment

Comment