report by combo box and logo

[colkas]

Hi

A couple of questions I hope someone can help me with.

I intend to create a set or reports and depending on what the combo box says on the quoatation survey type status will depend on what report is selected.

So if in the combo box it says "Demolition" I would like it to print the report speicifically created for Demolition Quotes.

I will only ahve one print button and the print out must only relate to the specific quote on the screen...

Becuase there will be many table involved (Quote, Client, Site, report proerties like forn cover information etc.....) I think I will need to use a query......

Any advice on this please.

Second question, how can I store a log in a table so I can attache it to the relevant report. I have a field OLE object and attached the image from my desktop through the table, but it just shows when i add it to a report as a windows icong with logo.jpg name.

Many Thanks

 

[CBrighton]

1. Setup the combobox with 2 columns, something like:

rptReport1   Daily Report
rptReport2   Weekly Report
rptReport3   Monthly Report

You can then set the column widths to something like "0cm;5cm", which will hide the first column from view by the user and display the report description, however the combobox will store the actual report name.

A command button can then run "DoCmd.OpenReport cboComboBox", assuming the name of the combobox is "cboComboBox".

2. I did this the other way around. I would store my "attachment" files in a certain folder (in my case a subfolder within the folder holding the database) and instead of storing the file I store the location.

This means that I can then use that location with a .FollowHyperlink to open it to use it as part of Outlook automation to attach it, etc.

However I don't know how you would include it in a report.

 

[colkas]

Hi

What I was really wanting to ahcieve is basing the reports on 3 exisiting names in an existing combo box

Demolition
Management
Cobination.

So if I Quote number 3 which is a demolition quote then when i click Print it looks at the surveytype = demolition,
Then runs a query called qdreport
The report as all the text and from cover headings etc..... based from the quote and the query....

So I need it to take the type then run the correct query for the correst report...

It also needs to just print currentrecord....

if you could point me on a way to do it that would be great, would the Print button wizard be any good???

Thanks

 

[CBrighton]

How about something like this:

DoCmd.OpenReport [B]ReportName[/B], acPreview, , "surveytype = " & [B]ComboBoxName[/B]

The 4th paramater is a filter, it's like an additional where clause on top of the query / table the report is based on but without the where prefix.

I use it in a more complex way, creating a string in VBA and checking a few dozen controls. It creates a dynamic filter based on exactly what criteria the user selects.

 

[colkas]

Hi

Not 100% what you mean.... If I create a button called Print with the wizard, I then go in and put the line you mentioned will this work

Docmd.openreport demoquote, acpreview, , "surveytype = " Combo38

(badly named combo I know), How does this alos get the current record?
I assume the report activates the query on open...

On awaiting your answer I will give it a shot

Many thanks

 

[CBrighton]

I'm not sure what "current record" you mean.

Rereading this thread you have not mentioned any additional criteria. What I have provided will just add the survey type filter on top of the default criteria for the query behind the report. If you want additional criteria they can be added to the query to to the VBA.

 

[colkas]

Hi

Ok I have a form called formquote. In this form I enter quotes.

If I ahve Quote Number 5 up, this is the current quote I want to print. It as a Surveytype of Demoltion..... Becuase our reportsd have different headings depending what type of survey it is.

Therefore I have a query that brings all the data together and then when I run the report it populates the report (I have only done the from sheet so far)....

So from the quote I need to select the Survey type and run the report based on this critria, so in the example I gave run the report for demolition and for only quote number 5......

I can attach the DB if you would like it......

Thanks alot

 

[CBrighton]

It should be a simple change, of course I don't know your field / control names:

DoCmd.OpenReport [B]ReportName[/B], acPreview, , "surveytype = " & [B]ComboBoxName[/B] & " AND [B]QuoteIDField[/B] = " & [B]QuoteIDControl[/B]

 

[colkas]

Hi

I have added the code and adjusted to my field names etc... but I get an debug on the lione I added... any ideas.....

Private Sub Command98_Click()
On Error GoTo Err_Command98_Click
Dim stDocName As String
stDocName = "testquote"
DoCmd.OpenReport , acPreview, , "surveytype = " & Combo38 & " AND IDquote = " & QuoteIDControl
'DoCmd.OpenReport ReportName, acPreview, , "surveytype = " & ComboBoxName & " AND QuoteIDField = " & QuoteIDControl
Exit_Command98_Click:
Exit Sub
Err_Command98_Click:
MsgBox Err.Description
Resume Exit_Command98_Click

End Sub

 

[CBrighton]

Sorry, the survey type will need 's.

i.e.

DoCmd.OpenReport , acPreview, , "surveytype = '" & Combo38 & "' AND IDquote = " & QuoteIDControl

I am assuming the ID is a number and therefore is fine.

 

[colkas]

Hi

Sorry I am just not getting it..... not sure what you mean by my survey type need 's

I think you meant add ' around combo38 which I have, but I get a compile error on that line of code any more ideas please and thanks as always....

Private Sub Command98_Click()
On Error GoTo Err_Command98_Click
Dim stDocName As String
stDocName = "testquote"
DoCmd.OpenReport , acPreview, , "surveytype = '" & Combo38 & "' AND IDquote = " & QuoteIDControl
'DoCmd.OpenReport ReportName, acPreview, , "surveytype = " & ComboBoxName & " AND QuoteIDField = " & QuoteIDControl
Exit_Command98_Click:
Exit Sub
Err_Command98_Click:
MsgBox Err.Description
Resume Exit_Command98_Click

End Sub

 

[CBrighton]

Yes, you did what I meant.

What error message does it give now?

 

[colkas]

Hi

It si giving a Microsoft Visusal Bacis Application error message say "Compile Errror... Arguement not Optional"

If I click Yes it goes to the code and highlights

Private Sub Command98_Click() (in Yellow) and .... then highlights in bold the line
DoCmd.OpenReport , acPreview, , "surveytype = '" & Combo38 & "' AND IDquote = " & QuoteIDControl

any ideas........

 

[CBrighton]

The non-optional arguement is the first one, the report you are opening.

I assume for testing you are using stDocName, so your code should read:

DoCmd.OpenReport [B]stDocName[/B], acPreview, , "surveytype = '" & Combo38 & "' AND IDquote = " & QuoteIDControl

:edit:

Also note that QuouteIDControl (at the end of the code) should have been replaced with the name of the control which holds the IDQuote field on that form.

 

[colkas]

Hi I have made the changes as you suggested.... this is my code now

Private Sub Command98_Click()
On Error GoTo Err_Command98_Click
Dim stDocName As String
stDocName = "testquote"
DoCmd.OpenReport stDocName, acPreview, , "surveytype = '" & Combo38 & "' AND IDquote = " & idquote

Exit_Command98_Click:
Exit Sub
Err_Command98_Click:
MsgBox Err.Description
Resume Exit_Command98_Click

End Sub


When I click the button now it is coming up with a Enter Parameter box with heading Surveytype , is this meant to do that. I have tried entering the surveytype id number and the type but I just get a blank report.....

Thanks

 

[CBrighton]

If it's prompting you for "surveytype" then chances are that is not the name of the field you are filtering on.

Double check the name of the field.

 

[colkas]

Hi

Yes that is correct..... ok so now I click the button and the report comes up, but with no data at all...... my code is now as below

Private Sub Command98_Click()
On Error GoTo Err_Command98_Click
Dim stDocName As String
stDocName = "testquote"
DoCmd.OpenReport stDocName, acPreview, , "type = '" & Combo38 & "' AND IDquote = " & idquote

Exit_Command98_Click:
Exit Sub
Err_Command98_Click:
MsgBox Err.Description
Resume Exit_Command98_Click

End Sub

The report is attached to a query, if I run this query I can see the data.....

I will send you my Db as I have it now.... I am trying to resolve it myself but if I send it to you as it is now then perhaps you may see something more obvious then I can see.......

The query is linked and uses a speivic report type for demolition... the link is rptid to the survery type

Anyhow I would be grateful if you could take a look and let me know your view

As Always thanks for your help thus far......

 

[CBrighton]

The query may show records but does it show a record plus the two additional criteria applied by the filter?

I'll have a look but I don't know what Type or IDQuote you used. Please confirm what form you used to launch it (where's the command button for the code we were discussing?) and which record you were looking at when you tested it with no records returned.

 

[colkas]

Hi

Yes it does show all the ones in the query with the filter.

There are about 6 to 7 quotes and any that as demolition I am testing with in the survey type list.

It is run from the form formquote and the button is Preview Report and the event proccedure is behined this of course.

There is only one query so this is the one.....

Thanks

 

[CBrighton]

The bound column of the combobox is the ID rather than the text of the report type.

If you run it as normal then change the report into design mode, the form's filter property in the data tab shows what it got from the VBA, which is "(type = '3' AND IDquote = 3)"

However, in your query type = "Demolition" not "3".

Change the code to:

DoCmd.OpenReport stDocName, acPreview, , "type = '" & Combo38[B].Column(1)[/B] & "' AND IDquote = " & idquote