Hello
I am a novice to Access/VBA and this is driving me nuts. Any help would be appreciated.
I have to create a little application which can print postcards(4"X6" ) based on following options:
Customer Type
OrderNumber
LoggedDate
Location
There are three cmd buttons Print, Preview and Cancel.
I created one report which has general look and feel of postcard(exmple return address on left hand upper corner and To address in the middle)
I developed four queries based on 4 different options.
qry_CustomerTyp eOption
qry_OrderNumber Option
qryLoggedDtOpti on
qryLocationOpti on
When I assign one query to report's recordsource it works fine
My problem:
I want to change the recordsource of the report to the different queries as per the options selected. I don't know how to do it. I tried with the - Private Sub cmdPreview_Click()
-
Select Case Me.Frame0.Value
-
-
Case 1
-
-
DoCmd.OpenReport "rptPostCard", acViewPreview, "qry_CustomerTypeOption"
-
-
Case 2
-
-
DoCmd.OpenReport "rptPostCard", acViewPreview, "qry_OrderNumberOption"
-
-
End Select
-
End Sub
Please help.
thanks
11 1871 ChipR 1,287
Recognized Expert Top Contributor
You can make a separate report for each case, or you should be able to do:
docmd.openrepor t ...
case 1
Reports![rptPostCard].RecordSource = "qry_CustomerTy peOption"
case 2
Reports![rptPostCard].RecordSource = "qry_OrderNumbe rOption"
I have only used that with forms, not reports, but it should work. No time to try it beforehand sorry :(
NeoPa 32,564
Recognized Expert Moderator MVP
You can change the RecordSource of a report from within the Report_Open() procedure. You may have problems unless the new query returns the same fields as the one it was designed with.
A more elegant solution (generally - depends on exactly what you're after) might be to design the report to handle all types, then simply call the same report with a different filter for each option.
After reading this post I tried this: same report with different record source each time using case statement in Report's On Open event like this -
Private Sub Report_Open(Cancel As Integer)
-
-
Select Case [Forms]![frmPostcardPrinting].Frame0.Value
-
-
-
Case 1
-
Me.RecordSource = "qry_CustomerTypeOption"
-
-
Case 2
-
Me.RecordSource = "qry_OrderNumberOption"
-
-
Case 3
-
Me.RecordSource = "qry_LoggedByOption"
-
-
Case 4
-
Me.RecordSource = "qryLocation"
-
-
End Select
-
-
-
End Sub
Now what happens is it only works for the fisrt option(meaning it nicely displays the postcard (reoport) for CustomerType) For other options it shows #Error at all the textboxes on the report.
I am stuck here again.
NeoPa,
Yes each of my query returns same values like FirstName, LastName, Address, City,State and Zip.
You think that what I have mentioned above will not work? Because I have to have develop the queries .I can not use filters as that is the customer requirement.
thanks again for replying
Here I am again..
I got that working. All of my options are working.Don't know how? I just restarted whole application 3-4 times and it worked.
Thanks
NeoPa 32,564
Recognized Expert Moderator MVP
Very pleased to hear it Jinal :)
Not least because I was struggling with the concept of a customer requesting you not use filters (like they would even know what one was).
...but if it's working that's all fine.
@NeoPa
I also wonder, but they have specified in the req. doc that I have to develop four queries.Don't know why?
I have one more question..
For each query I have one criteria "applicatio n must change SeedlingOrder:P ostcardPrinting date to date postcards printed"
How do I do that?
I know I have to use insert query under cmdPrint_click( ) but how?
If you can give me some example, I would be thankful
Thanks again
NeoPa 32,564
Recognized Expert Moderator MVP @jinalpatel
Without access to all the details it's impossible for me to say why they have done that. It looks like they have tried to work out the implementation details for you too. This can sometimes be helpful, especially when the people specifying have a good understanding of the technology. Unfortunately, there are times when it can just add confusion.
Regardless of why that may be, that is the position you find yourself in so it must be handled, one way or another. @jinalpatel
As I have no access to any of the items you refer to there is very little I can do here to help. Try rephrasing your question so that it makes some better sense. @jinalpatel
If you define an Insert query (QueryDef) (The wizard can help get you started there if you need) called "qryAddRecs ", then you can execute that query using code similar to the following : - Call CurrentDB.QuerDefs("qryAddRecs").Execute
I have following code segment for my report which is same for all options, meaning user can chose any of the option from CustType, OrdNo, LoggedDate and Location. As you know I have developed four queries based on each option criteria. Here is the code for Report - Option Compare Database
-
-
Private Sub Report_Open(Cancel As Integer)
-
-
Select Case [Forms]![frmPostcardPrinting].Frame0.Value
-
-
Case 1
-
Me.RecordSource = "qry_CustomerTypeOption"
-
-
Case 2
-
Me.RecordSource = "qry_OrderNumberOption"
-
-
Case 3
-
Me.RecordSource = "qry_LoggedDateOption"
-
-
Case 4
-
Me.RecordSource = "qry_Location"
-
-
End Select
-
-
-
End Sub
-
Private Sub Report_NoData(Cancel As Integer)
-
-
' Display message to user.
-
MsgBox "There are no records to report", vbExclamation, "No Records"
-
-
' Close the report.
-
Cancel = True
-
-
End Sub
-
Here is my code for main form that has all the options - Option Compare Database
-
-
Private Sub cmdCancel_Click()
-
-
'Clear all the controls in the Form Header section.
-
For Each ctl In Me.Controls
-
Select Case ctl.ControlType
-
Case acTextBox
-
ctl.Value = Null
-
Case acComboBox
-
ctl.Value = Null
-
End Select
-
Next
-
-
' Remove the form's filter.
-
' Me.Filter = "(False)"
-
' Me.FilterOn = True
-
-
End Sub
-
-
Private Sub cmdPreview_Click()
-
On Error GoTo Err_cmdPreview_Click
-
-
Select Case Me.Frame0.Value
-
Case 1
-
DoCmd.OpenReport "rptCustType", acViewPreview
-
-
Case 2
-
DoCmd.OpenReport "rptCustType", acViewPreview
-
-
Case 3
-
DoCmd.OpenReport "rptCustType", acViewPreview
-
-
Case 4
-
DoCmd.OpenReport "rptCustType", acViewPreview
-
-
End Select
-
-
Exit_cmdPreview_Click:
-
Exit Sub
-
-
Err_cmdPreview_Click:
-
-
MsgBox "Cannot print postcards as there are no records"
-
'MsgBox Err.Description
-
Resume Exit_cmdPreview_Click
-
-
-
End Sub
-
-
Private Sub cmdPrint_Click()
-
On Error GoTo Err_cmdPrint_Click
-
Dim rstPrint As ADODB.Recordset
-
Dim strSQL As String
-
Dim conDb As ADODB.Connection
-
Dim response As VbMsgBoxResult
-
-
Dim stDocName As String
-
-
stDocName = "rptCustType"
-
-
-
Select Case Me.Frame0.Value
-
-
Case 1
-
-
MsgBox "Do you want to print the labels?Once you click the OK, you will not be able to cancel the process", vbOKCancel
-
-
If vbOK Then
-
-
'Printing report with the following command
-
DoCmd.OpenReport stDocName, acNormal
-
-
-
'As soon as the report
-
Set conDb = CurrentProject.Connection
-
' Debug.Print
-
strSQL = "Update dbo_SeedlingOrder " & _
-
"set dbo_SeedlingOrder.PostCardPrintDate = Date()" & _
-
"where dbo_seedlingOrder.ShippingChoiceGenId <>3" & _
-
"and dbo_SeedlingOrder.CustomerTypeGenId=" & Me.cmbCustType & _
-
"and dbo_SeedlingOrder.LocationGenId=" & Me.cmbLocationName & _
-
"and dbo_SeedlingOrder.PaidInd = true" & _
-
"and dbo_SeedlingOrder.PostCardPrintDate is null"
-
-
-
MsgBox "PostcardPrintDate is updated to today's date"
-
conDb.Execute strSQL
-
conDb.Close
-
Set conDb = Nothing
-
Else
-
-
DoCmd.CancelEvent
-
-
End If
-
-
-
Case 2
-
-
MsgBox "Do you want to print the labels?Once you click the OK, you will not be able to cancel the process", vbOKCancel
-
-
If vbOK Then
-
-
'Printing report with the following command
-
DoCmd.OpenReport stDocName, acNormal
-
-
-
'As soon as the report
-
Set conDb = CurrentProject.Connection
-
' Debug.Print
-
strSQL = "Update dbo_SeedlingOrder " & _
-
"set dbo_SeedlingOrder.PostCardPrintDate = Date()" & _
-
"where dbo_SeedlingOrder.OrderNumberPostLottery>=" & Me.txtBeginOrdNo & _
-
"and dbo_SeedlingOrder.OrderNumberPostLottery<=" & Me.txtEndingOrdNo & _
-
"and dbo_SeedlingOrder.PaidInd = true" & _
-
"and dbo_SeedlingOrder.PostCardPrintDate is null"
-
-
-
-
MsgBox "PostcardPrintDate is updated to today's date"
-
conDb.Execute strSQL
-
conDb.Close
-
Set conDb = Nothing
-
Else
-
DoCmd.CancelEvent
-
-
End If
-
-
'Loggeddate option
-
Case 3
-
MsgBox "Do you want to print the labels?Once you click the OK, you will not be able to cancel the process", vbOKCancel
-
-
If vbOK Then
-
-
'Printing report with the following command
-
DoCmd.OpenReport stDocName, acNormal
-
-
-
'As soon as the report
-
Set conDb = CurrentProject.Connection
-
' Debug.Print
-
strSQL = "Update dbo_OrderPayment Inner join dbo_SeedlingOrder" & _
-
"on dbo_OrderPayment.OrderGenId = dbo_SeedlingOrder.OrderGenId" & _
-
"inner join dbo_ref_Location on dbo_SeedlingOrder.LocationGenId = dbo_ref_LocationGenId" & _
-
"set dbo_SeedlingOrder.PostCardPrintDate = Date()" & _
-
"where dbo_SeedlingOrder.PostCardPrintDate is null" & _
-
"and dbo_OrderPayment.OrderPaymentDate>=" & Me.txtBeginDate & _
-
"and dbo_OrderPayment.OrderPaymentDate<=" & Me.txtEndDate & _
-
"and dbo_ref_Location.LocationName=" & Me.cmbLocationName3 & _
-
"and dbo_SeedlingOrder.PaidInd = true"
-
-
MsgBox "PostcardPrintDate is updated to today's date"
-
conDb.Execute strSQL
-
conDb.Close
-
Set conDb = Nothing
-
Else
-
DoCmd.CancelEvent
-
End If
-
-
Case 4
-
MsgBox "Do you want to print the labels?Once you click the OK, you will not be able to cancel the process", vbOKCancel
-
-
If vbOK Then
-
-
'Printing report with the following command
-
DoCmd.OpenReport stDocName, acNormal
-
-
-
'As soon as the report
-
Set conDb = CurrentProject.Connection
-
' Debug.Print
-
strSQL = "Update dbo_SeedlingOrder " & _
-
"set dbo_SeedlingOrder.PostCardPrintDate = Date()" & _
-
"where dbo_seedlingOrder.ShippingChoiceGenId <>3" & _
-
"and dbo_SeedlingOrder.CustomerTypeGenId <>1" & _
-
"and dbo_SeedlingOrder.PostCardPrintDate is null" & _
-
"and dbo_SeedlingOrder.LocationGenId=" & Me.cmbLocationName1 & _
-
"and dbo_SeedlingOrder.PaidInd = true"
-
-
MsgBox "PostcardPrintDate is updated to today's date"
-
conDb.Execute strSQL
-
conDb.Close
-
Set conDb = Nothing
-
Else
-
DoCmd.CancelEvent
-
End If
-
-
-
End Select
-
-
Exit_cmdPrint_Click:
-
Exit Sub
-
-
Err_cmdPrint_Click:
-
MsgBox Err.Description
-
Resume Exit_cmdPrint_Click
-
End Sub
-
-
Private Sub Form_Load()
-
cmbCustType.Enabled = True
-
cmbLocationName.Enabled = True
-
txtBeginOrdNo.Enabled = False
-
txtEndingOrdNo.Enabled = False
-
txtBeginDate.Enabled = False
-
txtEndDate.Enabled = False
-
cmbLocationName1.Enabled = False
-
cmbLocationName3.Enabled = False
-
-
End Sub
-
-
-
Private Sub Form_Open(Cancel As Integer)
-
End Sub
-
-
Private Sub Frame0_Afterupdate()
-
Select Case Me.Frame0.Value
-
-
Case 1
-
cmbCustType.Enabled = True
-
cmbLocationName.Enabled = True
-
txtBeginOrdNo.Enabled = False
-
txtEndingOrdNo.Enabled = False
-
txtBeginDate.Enabled = False
-
txtEndDate.Enabled = False
-
cmbLocationName1.Enabled = False
-
cmbLocationName3.Enabled = False
-
-
Case 2
-
cmbCustType.Enabled = False
-
cmbLocationName.Enabled = False
-
txtBeginOrdNo.Enabled = True
-
txtEndingOrdNo.Enabled = True
-
txtBeginDate.Enabled = False
-
txtEndDate.Enabled = False
-
cmbLocationName1.Enabled = False
-
cmbLocationName3.Enabled = False
-
-
Case 3
-
cmbCustType.Enabled = False
-
cmbLocationName.Enabled = False
-
txtBeginOrdNo.Enabled = False
-
txtEndingOrdNo.Enabled = False
-
txtBeginDate.Enabled = True
-
txtEndDate.Enabled = True
-
cmbLocationName1.Enabled = False
-
cmbLocationName3.Enabled = True
-
-
Case 4
-
-
cmbCustType.Enabled = False
-
cmbLocationName.Enabled = False
-
txtBeginOrdNo.Enabled = False
-
txtEndingOrdNo.Enabled = False
-
txtBeginDate.Enabled = False
-
txtEndDate.Enabled = False
-
cmbLocationName1.Enabled = True
-
cmbLocationName3.Enabled = False
-
-
End Select
-
-
End Sub
-
-
-
-
Private Sub cmdQuit_Click()
-
On Error GoTo Err_cmdQuit_Click
-
-
-
DoCmd.Quit
-
-
Exit_cmdQuit_Click:
-
Exit Sub
-
-
Err_cmdQuit_Click:
-
MsgBox Err.Description
-
Resume Exit_cmdQuit_Click
-
-
End Sub
-
Here what I am doing is,
1) sending the appropriate query to the report to get opened.
2)Get the PostcardPrintDa te updated to curerent date by docmd.runsql(So mehow I got that working)
My problem:
for both Print and Priview option same problem(written below)
It only works for first option. For other options it goes to Report_NoData() function and gives me message "There are no records to report" and "Cannot print the Postcards as there are no records"
I crosschecked by running the queries separately and it has data. It shows me number of records.
Also I did F9 at different places and found out that it does go to its correct case but than it goes to Report_NoData() .
Please help me out of it. It is driving me nuts.
also,
I got the PostCardPrintDa te field updated to current date. That means those update queries are right. if I comment docmd.openrepor t under cmdPrint command and than I run the app, I can successfully update the PostCardPrintDa te field to the current date.
Please help me out of this.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Mark Preston |
last post by:
Admission first - I don't actually have a problem here but have noticed
that a lot of people have been asking similar questions and getting very
varied answers. What I've done is to sort of "compile the questions"
into a theoretical problem to see what people think should be done to
solve it.
Maybe it will be a worthwhile discussion, but...
|
by: J. B. Moreno |
last post by:
I read the faq, and it mentions that IE 4 on windows requires setting a
printing option to allow background colors to be printed.
Things change, life goes on, other browsers come into existence....
So, is there a way in CSS to say "Yeah, print these background colors",
without the user having to turn on an option that will then apply to...
|
by: wijaja_anton001 |
last post by:
Electronic postcard just like paper postcard
www.myepostcard.com
|
by: Stefania Scott |
last post by:
I am trying to print a word document from Access. The code I've
written works well in my computer but does not in the one were it is
needed.
Here the piece of code:
'doc path
strObjectPath = "P:\2004worksheets\IIS_WS.doc"
Set oWord = New Word.Application
oWord.Documents.Add (strObjectPath)
oWord.PrintOut
|
by: Jurjen de Groot |
last post by:
I'm about to start a new ASP.NET application, this application will
generate several types of documents (PDF HTML XML-DOC) wich have to be
printed at the client-side... (probably on different printers A3 and A4
type)
At this time I'm trying to figure out what would be the best way to
accomplish this :
1. printing through HTML in a IE...
| |
by: Phil Galey |
last post by:
I'm printing to a DeskJet 5550 printer. From WordPerfect, if I have a color
document and I specify grayscale in the printer properties dialog, it prints
in grayscale as expected.
However, from a VB.NET application, I don't seem to be able to get it to
print in black and white / grayscale. It always prints in color. I have a
3rd party DLL...
|
by: Dreamtime |
last post by:
Hi
I am using Visual Studio 2005 and the bundled Crystal Reports
(previously I used .net 2003 and bundled Crystal Reports for 2 years -
same issues!)
I have a report which is displayed in the crystal reports viewer. This
report needs to be printed. Its that simple!
I would like to use the report.printtoprinter method as this is direct
|
by: postcard.com |
last post by:
</div>
<strong>Hello friend !</strong><br>
You have just received a postcard from someone who cares about you!<br><br>
<strong>This is a part of the message:</strong><br>
"Hy there! It has been a long time since I haven't heared about you!<br>
I've just found out about this service from Claire, a friend of mine who also told me that..."<br>...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |