Hi David,
You *might* be able to accomplish what you need by simply "wrapping" your
date in "#" 's... like this:
StrLinkCriteria = "[fsubGSTRecieved].forms![paiddate] = " &
"#" & Me![chooseMonth] & "#"
Here's what *I* do instead in this type of situation, and it seems to work
quite well.
=============== =============== =============== ==========
1.) Using the AfterUpdate event of the combo or list box, build an SQL
statement in code including the WHERE , which is determined by the combo-box
item(s) selected.
2.) Use the resulting SQL statement as the recordsource for the subform.
This in effect "requeries" the subform automatically.
3.) Use that same SQL statement as the recordsource for the report. What you
see in the subform is what you get in the report. (WYSIWYG)
Here's some (commented) sample code that you can modify to suit your needs:
This example uses two different controls to modify the SQL , so I just wrote
one Sub procedure that does the "Requery", and call that from each of the
control's AfterUpdate event.
"lstTransTy pe" is a multi-select list box that is used to include different
transaction types in the results.
"cboSortBy" is a combo-box containing selected field names. Choosing a field
name causes the results to be sorted by that field.
*************** *************** *************** **********
Option Compare Database
Option Explicit
Dim MySQL As String 'This needs to be declared in the form declaration
section so that is in scope for various procedures on this form
=============== =============
Private Sub lstTransType_Af terUpdate()
sRequerySubform
End Sub
=============== =============
Private Sub cboSortBy_After Update()
sRequerySubform
End Sub
=============== =============
Public Sub sRequerySubform ()
'"Add All to List" Courtesy: Dev Ashish
'
http://www.mvps.org/access/forms/frm0043.htm
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
MySQL = "" 'Clear the string variable
'Construct the MAIN SQL statement here
MySQL = MySQL & "SELECT tblTransactions .* FROM tblTransactions "
'Now test for and add the WHERE portion here
If Len(strSelected ) > 0 And InStr(1, strSelected, "All") = 0 Then
MySQL = MySQL & "WHERE (((tblTransacti ons.TransType)I n("
MySQL = MySQL & strSelected
MySQL = MySQL & "))) "
End If
'Again, test for and add the ORDER BY here.
If Not IsNull(Me.cboSo rtBy) Then
MySQL = MySQL & "ORDER BY "
MySQL = MySQL & Me.cboSortBy
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.sbfsupplierT ransactions.For m.RecordSource = MySQL
Set ctl = Nothing
End Sub
=============== =============
Private Sub cmdTransactions Report_Click()
On Error GoTo Err_cmdTransact ionsReport_Clic k
Dim stDocName As String
stDocName = "rptTransaction s"
'Use the same SQL string generated above as the recordsource for the
report. (WYSIWYG!)
DoCmd.OpenRepor t stDocName, acPreview, MySQL
Exit_cmdTransac tionsReport_Cli ck:
Exit Sub
Err_cmdTransact ionsReport_Clic k:
MsgBox Err.Description
Resume Exit_cmdTransac tionsReport_Cli ck
End Sub
=============== =============
--
HTH,
Don
=============== ==============
Use
My*****@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.
=============== ==============
"DD" <da**********@b igpond.com.au> wrote in message
news:14******** *************** ***@posting.goo gle.com...
I have a mainform with a subform.
The main form as a dropdown box "chooseDate ", 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 code to add to the print button
i have tried this
StrLinkCriteria = "[fsubGSTRecieved].forms![paiddate] = " &
Me![chooseMonth]
this does not work
any ideas
regards
DD
