VBA code

John Williams

New member
Local time
Today, 19:39
Joined
Apr 2, 2010
Messages
8
I have created a form that I want to save and print the current form.
Print works ok but I get an error when clicking the save button. The error is:
An expression you entered is the wrong data type for one of the arguments.
This is my code. I have tried looking at various web site and access help but cannot find the answer
;)
Private Sub cmdPrintRecord_Click()
Dim strReportName As String
Dim strCriteria As String

strReportName = "rptPrintRecord"
strCriteria = "[TRAMMS]='" & Me![TRAMMS] & "'"
DoCmd.OpenReport strReportName, acViewNormal, , strCriteria

Exit_rptPrintRecord_Click:
Exit Sub
Err_rptPrintRecord_Click:
MsgBox Err.Description
Resume Exit_rptPrintRecord_Click
End Sub
Private Sub cmdOutputRecord_Click()
On Error GoTo Err_cmdOutputRecord_Click

Dim strRecordName As String
Dim strCriteria As String

strRecordName = "rptSaveReport"
strCriteria = "[TRAMMS]='" & Me![TRAMMS] & "'"


DoCmd.OutputTo acOutputForm, strRecordName, strCriteria, acFormatHTML, "C:\Desktop\General\DOA\DOA.html", True
Exit_cmdOutputRecord_Click:
Exit Sub
Err_cmdOutputRecord_Click:
MsgBox Err.Description
Resume Exit_cmdOutputRecord_Click

End Sub
 
What code line does it highlight when you click DEBUG?
 
I would assume you are having a problem because you have strCriteria in the spot which it is looking for OutputFormat instead. There is NO criteria slot you can use for DoCmd.OutputTo. You would need to do it another way.
 
None. I save and debug ok. No errors. I forced an error on just to see if Debug was working. It's only the Save/output that gets the error. Print report works ok. I can print individual forms in report style ok.
 
I was referring to when you get the error, on the dialog box that pops-up with the error, there's a Debug button. Click that button and it will take you to the error, highlighting the problem code.

Comment out the error handler line if you have one.
 
I have already mentioned one error that would occur. It may not be the only one but it definitely won't work as shown. There is no WHERE clause to use strCriteria in the DoCmd.OutputTo code. So, the fact that it is there where it is expecting something like acFormatTXT, acFormatExcel, etc. is definitely stopping this from working. :)
 
I have already mentioned one error that would occur. It may not be the only one but it definitely won't work as shown. There is no WHERE clause to use strCriteria in the DoCmd.OutputTo code. So, the fact that it is there where it is expecting something like acFormatTXT, acFormatExcel, etc. is definitely stopping this from working. :)
That's true SOS. Maybe the OP should run the report first with the WHERE clause before Outputting.

I did see your comment, I was just explaining what I meant by clicking the Debug button hehe!;)
 
That's true SOS. Maybe the OP should run the report first with the WHERE clause before Outputting.
I don't think that would work. I think the OutputTo will use the existing report not the one that is opened, but I guess they could try it.
 
All.
I am really pleased with the response I am receiving. I have taken the strCriteria out of the DoCmd and using HTML I get an HTML page showing all the records in report format. I now have to understand the replies I am getting and work on this part so that I can get only one record. While I am doing this any suggestions would be useful.
 
Hmmm... I have a hunch it may do.

If that fails, then the OP should be looking to changing the record source in design view, closing & saving, then outputting.
 
Another method would be to base the Report on a Query and write the complete SQL query string and the criteria to the SQL property of that Query. That removes the need to go into design view of the Report which can’t be done in an MDE file.

Added

Something like: -

Code:
    Dim strSQL As String
    
    strSQL = " SELECT Blah1, Blah2, Blah3" & _
             " FROM BlahBlahBlah" & _
             " WHERE [TRAMMS] = " & Chr$(34) & Me![TRAMMS] & Chr$(34)
    
    
    CurrentDb.QueryDefs("ReportQueryName").SQL = strSQL
    
    DoCmd.OutputTo acOutputReport, "ReportName", acFormatHTML, "C:\Desktop\General\DOA\DOA.html", True
 
Last edited:
All
I have created a workaround until I can resolve the criteria issue.
Basically I have added a date and time field to my table. I have created a query with a criteria of date(). I have then created a report using the query. I have then added this report into the expression and removed the strCriteria.
I am almost there when I save the report from the form it picks up lst table row which is does not have any information in it. This is because the time is continuing onto this line. This may be easier to resolve.
Just a note. I only started to learn access 4 weeks ago. Everything to do with command buttons and expressions I picking up off the internet so I could be picking up some bad habits. I never give up and even at the age of 60 I am still learning so my workaround may not be a workaround, more an expedient.
Again, all suggestions are useful to me.
 
John, try using SQL or Code to tell us the problem.
 
Hi
I will look at SQL. I have a basic understanding so will spend some researching this on line. In the meantime my project is still alive.
I didn't mention what the project was.
I am creating a centralised database so that particular task I do as part of my job which is currently done on stanalone excel forms.
I need to be able to run reports/Queries but also be able to save and print the current form. Although the print is not esential I need to be able to upload the form onto another appliation hence the need to save the individual forms to a folder. Each member of my team will have their own database and save their own forms. I will create a query which will collect all the table information into one report.
 
All
I have resloved my issue by using the "Last" record in a query table and then linking to a new query which holds all the information. I then ran a report on query which gives the last or newest record. I can now print, save, e mail or print Preview the last record. I did have to add a save form button on the form because I don't always fill all fields with data. I have also created a switchboard with various links on.
I have not forgotten VBA but I now have time to learn it better.
I will keep logging onto this forum.
Thanks to all
 
How are you getting this "Last" Record? I certainly hope not with the LAST function which really is a misnomer and really doesn't get the last record entered.
 
Also, the comment below caught my attention. What do you mean by this exactly?

I need to be able to run reports/Queries but also be able to save and print the current form. Although the print is not esential I need to be able to upload the form onto another appliation hence the need to save the individual forms to a folder. Each member of my team will have their own database and save their own forms.
 
Another method would be to base the Report on a Query and write the complete SQL query string and the criteria to the SQL property of that Query. That removes the need to go into design view of the Report which can’t be done in an MDE file.

Added

Something like: -

Code:
    Dim strSQL As String
    
    strSQL = " SELECT Blah1, Blah2, Blah3" & _
             " FROM BlahBlahBlah" & _
             " WHERE [TRAMMS] = " & Chr$(34) & Me![TRAMMS] & Chr$(34)
    
    
    CurrentDb.QueryDefs("ReportQueryName").SQL = strSQL
    
    DoCmd.OutputTo acOutputReport, "ReportName", acFormatHTML, "C:\Desktop\General\DOA\DOA.html", True

This what I was thinking to suggest, :)
ChrisO has given you the correct answer, SOS is right that there is no WHERE clause to use strCriteria in the DoCmd.OutputTo code.

Good Luck to learn Ms-Access and VBA.
 
All
I have had a reply saying that the method I am using is not very reliable. I will test this on some real forms I have in my office. These froms are single sheet excel forms. This will tell me whether or not using"last"in a query is acceptable.
My last task is to create a " find box". I have done the first part using the "like" parameter which works very well however if the data I am searching does not exsist I am taken to a blank form. I want to change this by putting a message saying " VRN not in the database. I amm researching this now. As for the rest of my Database. This is working fine.
 

Users who are viewing this thread

Back
Top Bottom