Run time error '3071' on open report vba code

**jeskit** · 06-08-2012, 10:39 AM

Hi,

I have written a macro in Access VBA which executes a DCount on a query and if the DCount is above 1 it opens and prints a report. However when it gets to the line of code which opens the report it stops and debugs producing an error of "run time error 3071: This expression is typed incorrectly, or it is too complex to be evaluated"

The code I have created is below and I have highlighted the line it debugs on in red:

Dim ref As String
Dim ref2 As String
Dim strSQL As String

Set db = CurrentDb()
ref = Me.Text22.Value
strSQL = DCount("ReBoxed", "Files", "ReBoxed = " & ref)
ref2 = strSQL
If ref2 > 1 Then
    DoCmd.OpenReport "Reboxed", acViewPreview
End If

I have tried reducing it because at first I had

DoCmd.OpenReport "Reboxed", acViewPreview,,,acWindowNormal

However it kept producing that error.Does anyone know why it keeps debugging on that line?

Thanks,

jeskit

**JosephP** · 06-09-2012, 04:30 PM

can you open the report normally?

**alansidman** · 06-09-2012, 05:14 PM

I wonder if you have to reference your query in your docmd line.

http://msdn.microsoft.com/en-us/library/ff192676.aspx

Alan

**jeskit** · 06-11-2012, 03:56 AM

Hi,

Thank you for replying....Yes I can open it normally.

alansidma....I don't really want the query referenced with the open report line because I am just using the query to find out how many records there. The query is more a Count than anything as It counts how many records there are and If the count returns more than 0 then I want to open the report and if it returns 0 then show a message saying there are no records to run the report.

**JosephP** · 06-11-2012, 04:02 AM

does it help if you use number variables instead of string?

**jeskit** · 06-11-2012, 04:16 AM

Hi,

I tried changing it to a number variable instead and it still debugs on the open report line and produces the same error

**jeskit** · 06-11-2012, 04:23 AM

Hi
I just tried running the macro with all lines except the open report line commented out and still debugged and produced the error. Would the problem be that I have the report running on a query so when it opens a runs a query to produce the results?

**JosephP** · 06-11-2012, 05:01 AM

it's probably help to see the db if possible. what is the source query for the report? are there any parameters or filters being applied?

**jeskit** · 06-11-2012, 05:06 AM

Hi

Thank you for all your help however I have solved the problem. The report I was trying to open had a filter on the data, so I took that out and in the VBA code I add a where clause to the Open report line and NOW it not only opens without a debuggig it gets the correct reuslts and prints the report.

Thank you for anyone's help i greatly appreciate all of it!!!!