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

Print from date selection

P: n/a
DD
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
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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.

"lstTransType" 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_AfterUpdate()
sRequerySubform
End Sub
============================
Private Sub cboSortBy_AfterUpdate()
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.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

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 (((tblTransactions.TransType)In("
MySQL = MySQL & strSelected
MySQL = MySQL & "))) "
End If

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

End Sub
============================
Private Sub cmdTransactionsReport_Click()
On Error GoTo Err_cmdTransactionsReport_Click

Dim stDocName As String

stDocName = "rptTransactions"
'Use the same SQL string generated above as the recordsource for the
report. (WYSIWYG!)
DoCmd.OpenReport stDocName, acPreview, MySQL

Exit_cmdTransactionsReport_Click:
Exit Sub

Err_cmdTransactionsReport_Click:
MsgBox Err.Description
Resume Exit_cmdTransactionsReport_Click

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.
=============================
"DD" <da**********@bigpond.com.au> wrote in message
news:14**************************@posting.google.c om...
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

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.