Run-time error '3075' when using DoCmd.OpenForm in VBA (1 Viewer)

[Birrel88]

Hello there,

For this question I searched alot on the internet. And I stared hours looking at my code and form design to find what was wrong, but I just couldn't find it.
So hopefully you guys can help me.

I have 2 forms.
Form 1: frm200Groep
- It shows a Continuous Form of the table tbl200Groep.
In there there is information about some special customers.
Every row on the continuous form has a Edit button. Pressing this supposed to open a popup form where you can edit the information.

Form 2: frm200GroepWijzigen (this is the edit popup form)
- It's a normal form where all the fields from the table are listed, it is supposed to display the information about the correct customer. The correct customer will be the row where I pressed the edit button on the continuous form "frm200Groep"

Now, the VBA code behind the EDIT button:

Private Sub cmdGebruikerBewerken_Click()
    DoCmd.OpenForm "frm200GroepWijzigen", , , "200GroepPK = " & Me.txt200GroepPK
End Sub

In frm200Groep there is a hidden field named "txt200GroepPK" which holds the PK autonumber field.
"200GroepPK" is the name of the autonumber field from the table which holds all the rows

But, when I press the button, I get the following error:

Run-time error '3075':

Syntax error (missing operator) in query expression '200GroepPK = 10'.

Where 10 will be the rownumber from the PK autonumber field.

The strange thing is, I have the exact same system for my employees forms and tables. The only difference is the name of some fields and the tables.
But, in the employees form it is working.

I already checked a dozen times if there was something I was missing, but just couldn't find it.

Can somesome see what is wrong? If there is more information needed, I would give it.

Thanks in advance

 

[jleach]

Try putting square brackets around the fieldname (I'm wary of fields that start with numbers...)

Private Sub cmdGebruikerBewerken_Click()
    DoCmd.OpenForm "frm200GroepWijzigen", , , "[200GroepPK] = " & Me.txt200GroepPK
End Sub

The square brackets are a band-aid for improper field names: best practice is to use a better naming scheme.

 

[Birrel88]

Try putting square brackets around the fieldname (I'm wary of fields that start with numbers...)

Private Sub cmdGebruikerBewerken_Click()
    DoCmd.OpenForm "frm200GroepWijzigen", , , "[200GroepPK] = " & Me.txt200GroepPK
End Sub

Really, that simple?

Thanks, it is working now.
So, what you are saying is that the problem is that the fieldname start with a number? And with putting the square brackets around it will work (obviously)

So I think a better naming convention for fields will be not starting with numbers?

 

[jleach]

Really, that simple?

Yup.

the problem is that the fieldname start with a number?

Yup.

a better naming convention for fields will be not starting with numbers

Yup.



(I had edit my answer to state that a bit more explicitly right around the time you replied, but yes... in any programming (most any language), be it field name, table name, variable name, etc - best practice is generally to start with a letter of some sort (or in certain cases an underscore, but not in Access/VBA).

Cheers

 

[Buster]

Re: Run-time error '3075' when using DoCmd.OpenReport in VBA

I cannot see why this is giving me a 3075 error. I have exactly the same code with a different date field - works fine. I am obviously staring at it but not seeing it. The form's record source is bound to the Purchase Query and the PurchDate is on the form. Help please.


Private Sub cmdPurchase_Click()

Dim stLinkCriteria As String


stLinkCriteria = "Me.[PurchDate] BETWEEN " & Format$(DateFrom, "\#mm\/dd\/yyyy\#") & "And" & Format$(DateTo, "\#mm\/dd\/yyyy\#")

'Check values are entered into Date From and Date To text boxes
'If so run report or cancel request

If Len(Me.txtDateFrom & vbNullString) = 0 Or Len(Me.txtDateTo & vbNullString) = 0 Then
MsgBox "Please ensure that a report date range is entered into the form", _
vbInformation, "Required Data..."

Exit Sub
Else
DoCmd.OpenQuery "Purchase Query"
DoCmd.OpenReport "Purchase Report", acViewReport, , stLinkCriteria
DoCmd.Close acQuery, "Purchase Query"

End If


End Sub

 

[pbaldy]

For starters drop the "Me." from:

stLinkCriteria = "Me.[PurchDate] BETWEEN..."

It should just be the field name. If you still have trouble use this to see what stLinkCriteria is resolving to:

http://www.baldyweb.com/ImmediateWindow.htm

Also, verify that PurchDate is a date/time field in the table.

 

[Gasman]

Debug.Print your stLinkCriteria

Try Me. with the date controls and no Me. for the Purchdate ?

Would have also been better starting your own thread rather than adding to a thread marked [Solved]

 

[Buster]

Still no joy. 3075 still comes up. tried PurchDate, WHPurchase.PurchDate,
[PurchDate], [WHPurchase.PurchDate]. Adding Me. to the date fields brings up compile error.
Yes PurchDate is a Date field in WHPurchase table

I have a similar situation reporting from a different table. The code below works and gives me my reports. I can't see the difference between the two.

Private Sub cmdProdSum_Click()

Call RunReport("ShopSalesByProductSummary", [ShopSales.SaleDate], Me.txtDateFrom, Me.txtDateTo)

End Sub

'This is the Subroutine to run the requested report

Private Sub RunReport(RepName, SaleDate, DateFrom, DateTo)

Dim stLinkCriteria As String

stLinkCriteria = "[SaleDate] BETWEEN " & Format$(DateFrom, "\#mm\/dd\/yyyy\#") & "And" & Format$(DateTo, "\#mm\/dd\/yyyy\#")

'Check values are entered into Date From and Date To text boxes
'If so run report or cancel request

If Len(Me.txtDateFrom & vbNullString) = 0 Or Len(Me.txtDateTo & vbNullString) = 0 Then
MsgBox "Please ensure that a report date range is entered into the form", _
vbInformation, "Required Data..."
Exit Sub
Else
DoCmd.OpenQuery "ShopSales Query"
DoCmd.OpenReport RepName, acViewReport, , stLinkCriteria
DoCmd.Close acQuery, "ShopSales Query"

End If

End Sub

 

[Buster]

Problem solved, but have no idea why. My structure for sales calling a sub was because I have many reports running of the Shop Sales Query, whereas the Purchase is only one report. However changing the Purchase to call a sub as for Sales has worked. Still can't see why the original code did not work. Working code below.. Thanks for advice and input

Private Sub cmdPurchase_Click()

Call RunPurch("Purchase Report", [WHPurchase.PurchDate], Me.txtDateFrom, Me.txtDateTo)

End Sub

Private Sub RunPurch(RepName, PurchDate, DateFrom, DateTo)

Dim stLinkCriteria As String

stLinkCriteria = "[PurchDate] BETWEEN " & Format$(DateFrom, "\#mm\/dd\/yyyy\#") & "And" & Format$(DateTo, "\#mm\/dd\/yyyy\#")

'Check values are entered into Date From and Date To text boxes
'If so run report or cancel request

If Len(Me.txtDateFrom & vbNullString) = 0 Or Len(Me.txtDateTo & vbNullString) = 0 Then
MsgBox "Please ensure that a report date range is entered into the form", _
vbInformation, "Required Data..."

Exit Sub
Else
DoCmd.OpenQuery "Purchase Query"
DoCmd.OpenReport RepName, acViewReport, , stLinkCriteria
DoCmd.Close acQuery, "Purchase Query"

End If


End Sub

 

[pbaldy]

Glad you got it sorted. There should be no reason to open and close the query, presuming it's the one the report is based on.

 

[Buster]

Thanks again. Had the Query open/close in for debug purposes. Now removed.

 

[Gasman]

I don't understand?
You are using txtDateFrom and txtDateTo, so I would be using those as Me.Controlname in the strWhere ?

 

[Buster]

Me.txtDate... does not work in str...

 

[Minty]

You have to concatenate it into the string;

Dim stLinkCriteria As String

stLinkCriteria = "[PurchDate] BETWEEN " & Format(Me.txtDateFrom, "\#mm\/dd\/yyyy\#") & " And " & Format(Me.txtDateTo, "\#mm\/dd\/yyyy\#")

 

[Buster]

Tried that but error could not find field. I will check my syntax again. However it is now working, so if it aint broke, don't fix it. Thanks for the input.

 

[Minty]

That code would need to be stored in the Forms Module, not in a separate code module?

The Me. predicate is referring to the current form which isn't available to an external module.

 

[Buster]

Yes I figured that out. Thats why I passed the Me.txt... items thru the call parameter list

 

[pbaldy]

I passed the Me.txt... items thru the call parameter list

To be clear, you did do that but also used it within the sub:

If Len(Me.txtDateFrom & vbNullString) = 0 Or Len(Me.txtDateTo & vbNullString) = 0 Then

 

[Buster]

Yes, and I could use DateFrom instead of Me.txtDateFrom in the If - more consistent coding I think. However still could not use Me.txtDate.. in stLinkCriteria string.