none
VBA Compile error: Expected function or variable & SQL code help needed RRS feed

  • Question


  • Hi, 
    Im new to this forum and VBA but have experience in many other languages.
    I have a form with the following fields: Week, Day, Work_Order, Operator, Reading_Number, Disc_Size, USL, LSL, Centre, Outer_Position_1, Outer_Position_2, Outer_Position_3, Outer_Position_4, Outer_Average.

    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.

    Wednesday, July 11, 2012 2:52 PM

Answers

  • DoCmd.RunSQL is intended for action queries, not for select queries. It doesn't return a value.

    Try this:

    Me!Reading_Number = Nz(DMax("Reading_Number", "Grinding_Control", "Disc_Size=" & Me!Disc_Size), 0) + 1
    


    Regards, Hans Vogelaar

    Wednesday, July 11, 2012 3:23 PM

All replies

  • DoCmd.RunSQL is intended for action queries, not for select queries. It doesn't return a value.

    Try this:

    Me!Reading_Number = Nz(DMax("Reading_Number", "Grinding_Control", "Disc_Size=" & Me!Disc_Size), 0) + 1
    


    Regards, Hans Vogelaar

    Wednesday, July 11, 2012 3:23 PM
  • Thanks, That is perfect!

    You are a God among men =]

    Wednesday, July 11, 2012 3:28 PM