Solved: VBA Compile error: Expected function or variable & SQL code help needed

[davetherave]

Hi,
Im new to this forum and VBA but have experience in many other languages.

I want the SQL code to get the last Reading_Number of a certain Disc_Size when the Disc_Size field is changed.

eg The last Reading_Number of a Disc_Size 2 is 33
The Disc_Size field is changed from 1.6 to 2 so now the value 34 will be automatically entered into the Reading_Number field.

I've think I've attached a picture of the form and below is the code I'm trying to use.

I'm getting the following error at Private Sub Disc_Size_Change() :

"Compile error: Expected function or variable"
Private Sub Disc_Size_Change()
Dim vDisc As Variant
vDisc = Me![Disc_Size]
'Get last reading number of disc size and put it (+ 1) in current record
Me![Reading_Number] = DoCmd.RunSQL("SELECT MAX([Reading_Number]) FROM Grinding_Control WHERE [Disc_Size]=vDisc+1;")

End Sub

Thanks, I hope I was clear? I have other VBA code that runs under certain conditions which I can post if it might be involved.

[hansup]

RunSQL is intended for use with action queries (INSERT, UPDATE, DELETE, SELECT INTO, and DML statements such as CREATE TABLE) according the to Access help topic. It looks like you want to retrieve a maximum value from your table.

Consider the DMax() function with a Criteria parameter (see the help topic) which mimics the WHERE clause you used in your query.

Private Sub Disc_Size_Change()
Dim strCriteria As String
strCriteria = "[Disc_Size]=" & Me![Disc_Size] + 1
Debug.Print strCriteria
Me![Reading_Number] = DMax("Reading_Number", "Grinding_Control", strCriteria)
End Sub

Make adjustments to accommodate Null values if you will be dealing with any, but that code outline should get you started.

[davetherave]

That is perfect.
Thanks very much.

[davetherave]

I now have a slightly more complicated issue.

I want it to find the Max value where Week equals the current week (the ISOWEEK gets this) and Day = todays day (Because I don't want it grabbing reading numbers from previous days)

Here is the code:
Private Sub Disc_Size_Change()
Dim strCriteria As String
Dim vCurrent_Day As String

vCurrent_Day = WeekdayName(Weekday(Date), False, 1)
strCriteria = "[Disc_Size]=" & Me![Disc_Size] & " AND " & "[Week]=" & ISOWEEK(Now(), 1) & " AND " & "[Day]=" & vCurrent_Day

Me![Reading_Number] = Nz(DMax("Reading_Number", "Grinding_Control", strCriteria), 0) + 1

I'm getting the following error:
Run-time error '2471':
The expression you entered as a query parameter produced this error: 'Thursday'

At this line:
Me![Reading_Number] = Nz(DMax("Reading_Number", "Grinding_Control", strCriteria), 0) + 1

[davetherave]

Solved it.

I left out the quote marks around the vCurrent_Day string.