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
============================
