By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,209 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

Print Selected records from DropDownBox

P: n/a
DD
I have a mainform with a subform.
The main form has a dropdown box "chooseMonth", 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


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.
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
--------------------------------------------------------------------------------
Deleted this

Dim ctl As Control
Dim varItm As Variant

Dim strSelected As String

Set ctl = Me.lstTransType
For Each varItm In ctl.ItemsSelected

If Len(strSelected) > 0 Then
strSelected = strSelected & ", " & chr$(39) &
ctl.ItemData(varItm) & chr$(39)
Else
strSelected = chr$(39) & ctl.ItemData(varItm) & chr$(39)
End If

Next varItm

'Debug.Print strSelected
--------------------------------------------------------------------------------
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 (((qryGSTRecieved.PaidDate)In("
MySql = MySql & strSelected
MySql = MySql & "))) "
End If

'Again, test for and add the ORDER BY here.
If Not IsNull(Me.chooseMonth) 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.fsubGSTRecieved.Form.RecordSource = MySql
Set ctl = Nothing

End Sub
============================
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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(qryGSTRecieved.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_AfterUpdate() '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(qryGSTRecieved.PaidDate,"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.fsubGSTRecieved.Form.RecordSource = MySql

End Sub
*************************************************
--
HTH,
Don
=============================
Use My*****@Telus.Net 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" <da**********@bigpond.com.au> wrote in message
news:14**************************@posting.google.c om...
I have a mainform with a subform.
The main form has a dropdown box "chooseMonth", 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
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.

---------------------------------------------------------------
Yes you are right in assuming that that was a ListBox control ... sorry, I
should have mentioned that.
--------------------------------------------------------------- 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
-------------------------------------------------------------------------- ------ Deleted this

Dim ctl As Control
Dim varItm As Variant

Dim strSelected As String

Set ctl = Me.lstTransType
For Each varItm In ctl.ItemsSelected

If Len(strSelected) > 0 Then
strSelected = strSelected & ", " & chr$(39) &
ctl.ItemData(varItm) & chr$(39)
Else
strSelected = chr$(39) & ctl.ItemData(varItm) & chr$(39)
End If

Next varItm

'Debug.Print strSelected
-------------------------------------------------------------------------- ------ 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 (((qryGSTRecieved.PaidDate)In("
MySql = MySql & strSelected
MySql = MySql & "))) "
End If

'Again, test for and add the ORDER BY here.
If Not IsNull(Me.chooseMonth) 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.fsubGSTRecieved.Form.RecordSource = MySql
Set ctl = Nothing

End Sub
============================

Nov 12 '05 #2

P: n/a
Hi Don
thanks for this.
Applogies to Dev Ashish

1. MySql = MySql & "WHERE
(((Format(qryGSTRecieved.PaidDate,"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!
Nov 12 '05 #3

P: n/a
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(qryGSTRecieved.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" <da**********@bigpond.com.au> wrote in message
news:40*********************@news.frii.net...
Hi Don
thanks for this.
Applogies to Dev Ashish

1. MySql = MySql & "WHERE
(((Format(qryGSTRecieved.PaidDate,"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!

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.