Compile Error Expecting Sub, Function or Property

[JulianAustin]

Hi All,

This one has been driving me insane over the past few days. I have searched quite a bit but nothing of concrete leads me to a resolution

I get the Compile Error, expecting Sub, Fucntion or Property. I am relatively new to VBA

I would very much appreciate any help on this.

With thanks.....

Here is my code

Dim dbs As DAO.Database
Dim rst0 As DAO.Recordset
Dim rst2 As DAO.Recordset

Dim Plant1 As String
Dim TotalCOGS As Integer
Dim TotalPlantCOGS As Integer
Dim stDocName As String


DoCmd.SetWarnings False
'DoCmd.SetWarnings True


Plant1 = ""
TotalCOGS = 0
TotalPlantCOGS = 0

'Action First Query
DoCmd.OpenQuery ("MAT COGS - SKU")


Set rst0 = CurrentDb.OpenRecordset("tblMATCOGS-SKU", dbOpenDynaset)
Plant1 = "AU10"
setvalueA (Plant1) <--- I get the error on this.

---Rest of Code....


------ FUNCTION HERE
Option Compare Database

Public strName As String
Public strnameA As String
Public strnameB As String

Function setvalueA(strA As String)
strnameA = strA
End Function
Function SetValueB(strB As String)
strnameB = strB
End Function
Function GetValueA()
GetValueA = strnameA
End Function
Function GetValueB()
GetValueB = strnameB
End Function

 

[MajP]

Try changing

Function setvalueA(strA As String)
 strnameA = strA
 End Function

to

Public Sub SetvalueA(strA As String)
 strnameA = strA
End Sub

Try changing
setvalueA (Plant1)
to
setValueA Plant1

Although how you have it should work, this will help trouble shoot.

 

[JulianAustin]

Many thanks for your response.

Unfortunately I still get the error.

I think it still needs to be a function, since I call some SQL in access that uses the function GetValueB in the filter criteria of the query, it is a way of passing the value of plant AU10 using SetValueA to the SQL query I have already set up.

Any other thoughts?

I have DAO 3.6 reference selected too...

 

[dhookom]

Since the function doesn't return a value, I would try:

Call setvalueA(Plant1)

Duane
Hook'D on Access
MS Access MVP

 

[JulianAustin]

Thank you, unfortunately I get the same error

The module is called AllValues with the code below only.


Function setvalueA(strA As String)
strnameA = strA
End Function
Function SetValueB(strB As String)
strnameB = strB
End Function
Function GetValueA()
GetValueA = strnameA
End Function
Function GetValueB()
GetValueB = strnameB
End Function

 

[dhookom]

You can't use the same name for both a module and a function or sub. That's one of the reasons why most old-timers recommend using a naming convention. It avoids issues like this.

Duane
Hook'D on Access
MS Access MVP

 

[JulianAustin]

Hi,

I am using a function only and have called the module AllValue. I did have a recommendation above to call a sub which I tried but I got the same error.

Any other ideas and could try ?

 

[strongm]

Do you have 'Option Explicit' set?

If not, I suspect that you ought to, as I rather think that may have setvalueA declared more than once.

 

[JulianAustin]

Hi,

A very strange event, I removed all code and deleted the module. I used Options Explicit, re-created the module added the same code and its worked.

Thank you to all of you for your assistance.