VBA Module with SQL help

[fredalina]

Please help me debug the following. The problem appears to be with my SQL statement, as I get the error:

"Run-time error '2342':

A RunSQL action requires an argument consisting of an SQL statement."


Here is the code (with MsgBox added to help me keep track; both come out correct):

sCurMonth = Right(CStr(10000 + Year(Date)), 4) + Right(CStr(100 + Month(Date)), 2)

MsgBox "sCurMonth is " & sCurMonth

strGeneric = "'" & sCurMonth & Chr(37) & "'"

MsgBox "strGeneric is " & strGeneric

sequel = "SELECT TOP 1 Right([RefNumber], 2) FROM SPRRTable WHERE [RefNumber] LIKE strGeneric"

DoCmd.RunSQL "SELECT TOP 1 Right([RefNumber], 2) FROM SPRRTable WHERE [RefNumber] LIKE strGeneric"


I've tried it with the statement DoCmd.RunSQL sequel and with the longer bit.

Thank you!

 

[KenHigg]

I think your problem is that the RunSQL executes an 'action' type sql statement, i.e. update, append, etc. It kinda makes no sense to try and get it to execute a simple select sql statement.

What are you wanting to do with the rows of data that you sql statement returns?

 

[fredalina]

Hmmm... maybe there's a much simpler way.

The ultimate goal is to provide a RefNumber field that is the year, month, and then counts from one up. 20080701, 20080702, 20080703, ..., 20080801, 20080802, 20080803,...,20080901, etc.

I actually got this code from my brother. I expected the SQL statement to return only the one "TOP" row, not many rows, but i am quite poor at SQL.

 

[KenHigg]

There are several ways to do this. Are you wanting to store the value in a field?

 

[fredalina]

The final result field (really the only one that matters ) is called RefNumber. That is the one with the format 20080701, 20080702, 20080801, etc.

Thanks!

 

[KenHigg]

If a counter goes to something like 100 for a month and then a record is deleted, your last number will be still 200807100 but you will only have 99 records for the month. Is that ok?

 

[fredalina]

Yes, it will be fine. Thanks!

 

[KenHigg]

What are the max number of records for a month? 10, 100, 1000, 10000?

 

[fredalina]

It should definitely not exceed 100 in a month. Realistic max is probably 30 (and that would be rare).

 

[KenHigg]

Give me a few minutes to tinker with this...

 

[fredalina]

I do appreciate it!

 

[KenHigg]

Put this function in a module and set the fields default value to call it:

Public Function f_OrdNum() As String
    Dim strMyFieldName As String
    Dim strMyTableName As String
    
    'Change these
    strMyFieldName = "myOrderNumber"
    strMyTableName = "Table1"
    
    Dim strPeriodMonth As String
    Dim strPeriodYear As String
    Dim intPeriodMaxValue As Integer
    Dim strSQL As String
    Dim strMyDefaultValue As String
    Dim db As Database
    Dim rs As Recordset
    
    Set db = CurrentDb
    
    'This section gets the next number and places it in the int var intPeriodMaxValue
    strSQL = "SELECT Max(CInt(Right([" & strMyFieldName & "],4))) AS LastNumber " & _
                "FROM " & strMyTableName & " " & _
                "WHERE (((DatePart('yyyy',[date]))=DatePart('yyyy',Now())) AND ((DatePart('m',[date]))=DatePart('m',Now())));"
    
    Set rs = db.OpenRecordset(strSQL)
    
    rs.MoveFirst
        If rs.EOF Then
        intPeriodMaxValue = 1
        Else
        intPeriodMaxValue = rs!LastNumber + 1
        End If
    rs.Close
    
    'This section gets the current year and places it in the int var strMyDefaultValue
    strMyDefaultValue = DatePart("yyyy", Now)
     
    'This section gets the current month and concatenates to the end of the var strMyDefaultValue
    If DatePart("m", Now) < 10 Then
        strPeriodMonth = "0" & DatePart("m", Now)
        strMyDefaultValue = strMyDefaultValue & "0" & DatePart("m", Now)
    Else
        strMyDefaultValue = strMyDefaultValue & DatePart("m", Now)
    End If
    
    'This section gets the value in intPeriodMaxValue and concatenates to the end of the var strMyDefaultValue
    Select Case intPeriodMaxValue
    Case Is < 10
        strMyDefaultValue = strMyDefaultValue = "000" & CStr(intPeriodMaxValue)
    Case Is < 100
        strMyDefaultValue = strMyDefaultValue & "00" & CStr(intPeriodMaxValue)
    Case Is < 1000
        strMyDefaultValue = strMyDefaultValue & "0" & CStr(intPeriodMaxValue)
    End Select
    
    'This line returns the value
    f_OrdNum = strMyDefaultValue

End Function

And change the lines in the code where it says 'Change these'.

I'm sure some others can ploish this up -

 

[fredalina]

Thank you again. Unfortunately at the line
Set rs = db.OpenRecordset(strSQL)
I get the error:
Run-time error '3061':
Too few parameters. Expected 1.

The "change these" variables are now:
strMyFieldName = "RefNumber"
strMyTableName = "SPRRTable"

The SQL is now:

strSQL = "SELECT Max(CInt(Right([" & strMyFieldName & "],4))) AS LastNumber " & _
"FROM " & strMyTableName & " " & _
"WHERE (((DatePart('yyyy',[date]))=DatePart('yyyy',Now())) AND ((DatePart('m',[date]))=DatePart('m',Now())));"


Can you help me find the bug?

 

[KenHigg]

Sorry - I forgot to account for using the date field. In the following line:

"WHERE (((DatePart('yyyy',[date]))=DatePart('yyyy',Now())) AND ((DatePart('m',[date]))=DatePart('m',Now())));"

You need to change the '[date]' to you date field in the records.

Something like:

"WHERE (((DatePart('yyyy',[MyDate]))=DatePart('yyyy',Now())) AND ((DatePart('m',[MyDate]))=DatePart('m',Now())));"

Maybe that'll fix it -

 

[fredalina]

Hmmm, now I get "Invalid use of Null" at this line:

intPeriodMaxValue = rs!LastNumber + 1

Should LastNumber be substituted?

Thanks again (sorry to be a pain).

 

[KenHigg]

Hum... Thought the eof would trap that. Give me minute to look at it...

 

[KenHigg]

I need to bug out - I'll give this a look a little later. In the mean time try putting in a record for the current month manually then see if the next new record works....

 

[KenHigg]

Try this:

  Set rs = db.OpenRecordset(strSQL)
    
        If IsNull(rs!lastnumber) Then
            intPeriodMaxValue = 1
        Else
            intPeriodMaxValue = rs!lastnumber + 1
        End If
    
    rs.Close

 

[fredalina]

Ken, I can't tell you how much I appreciate this. Obviously I am in over my head.

I replaced the section from Set rs = ... to rs.close with the new code:

Set rs = db.OpenRecordset(strSQL)

If IsNull(rs!lastnumber) Then
intPeriodMaxValue = 1
Else
intPeriodMaxValue = rs!lastnumber + 1
End If

rs.Close

On opening a new record, the new RefNumber is "False". Again, thanks for your help. Can you, by chance, help me through that part?

 

[fredalina]

P.S. I inserted a MsgBox after that section that tells me the value of intPeriodMaxValue, which in this case is 1. Perhaps this provides a clue.