DAO Query

[Matt Brown]

DAO Query problem, please help anyone!

Why does Access through up an "item not found in this collection" error when i run this?
When i remove the Max() it works.

I need to get the Max Serial number out of the Quotes table.

Private Sub Command0_Click()
Dim db1 As DAO.Database
Dim rs1 As DAO.Recordset
Dim SerialNo As Integer
Dim SQL As String


Set db1 = CurrentDb()

SQL = _
"SELECT MAX(SerialNo) " & _
"  FROM Quote " & _
" where (CustomerNo) = 'MCT35' "
 
Set rs1 = db1.OpenRecordset(SQL, dbOpenSnapshot)

TEST1 = rs1.Fields("SerialNo")


rs1.Close
End Sub

Any help appreciated.

Matt

 

[dcx693]

When you do this:

"SELECT MAX(SerialNo)"

, Access automatically assigns the new Max column a name other than "SerialNo". I'm guessing it's probably called "MaxSerialNo".

But nevermind....you'll be better off giving the calculated column your own chosen name. Use this syntax:

"SELECT Max(SerialNo) AS MaxSerial "

Then use

TEST1 = rs1.Fields("MaxSerial")

later on in your code.

Or...if your code is basically that simple, and nothing else is happening with that recordset, just use a DMax function.

 

[Matt Brown]

Thanks dcx693 i will give that a try.

Matt

 

[Matt Brown]

Ok, the test bit worked fine, now heres where i am trying to get it to work:

Set db1 = CurrentDb()
SQL = _
"SELECT MAX(Serial) AS maxSerialNo " & _
"  FROM [Quote] " & _
" where customerNo = ' & Forms!main!CustomerNo & ' " & _
"   and MyDate = " & Format(Now(), "yyyymmdd") & ";"

Set rs1 = db1.OpenRecordset(SQL, dbOpenSnapshot)
If rs1.EOF Then
    maxSerialNo = 1
Else
    rs1.MoveFirst
    If IsNull(rs1.Fields(0)) Then
        maxSerialNo = 1
    Else
        maxSerialNo = (rs1.Fields(0)) + 1
    End If
End If
rs1.Close

I cant now seem to get the code to find the maximum serial number from the quotes table and run through the "add 1 part" of the code.

The serial number still stays at 1 regardless if the last number entered is 5 10 or whatever.

Can anyone help please.

matt

 

[dcx693]

There are many problems with your code. Let's correct it to:

Set db1 = CurrentDb()
SQL = _
"SELECT MAX(Serial) AS maxSerialNo " & _
"  FROM    [Quote] " & _
" where customerNo = " & Forms!main!CustomerNo & _
"   and MyDate = " & Format(Now(), "yyyymmdd") & ";"

Set rs1 = db1.OpenRecordset(SQL, dbOpenSnapshot)
If rs1.EOF Then
    maxSerialNo = 1
Else
    rs1.MoveFirst
    If IsNull(rs1.Fields(0)) Then
        maxSerialNo = 1
    Else
        maxSerialNo = (rs1.Fields(0)) + 1
    End If
End If
rs1.Close

I am assuming Quote is the name of a table or query and CustomerNo is a numeric field. Is MyDate really stored in your table as a Formatted string? I'm guessing not. By the way you are delimiting it in your SQL string, you're implying that it's numeric (not text or even a date field).

To properly check for an empty recordset, both rs1.BOF and rs1.EOF have to be true.

Again I must point out: if this is all your code is doing, why not just use a simpler DMax function?

 

[Matt Brown]

Hi dcx693,

I seem to be getting the following errer after trying this out:

Too few parameters. Expected 1

Any ideas?

How would i use the DMax function in this code.

I take it that i use it instead of the SQL to get the max number from the quotes table.

Matt

 

[dcx693]

Which line in your code is producing that error?

To use DMax, something like:

Dim maxSerialNo as Integer

maxSerialNo= DMax("[SerialNo]","Quote","[customerNo]" = & _
    Forms!main!CustomerNo & " AND MyDate = " & Format(Now(), "yyyymmdd"))

    If IsNull(maxSerialNo) Then
        maxSerialNo = 1
    Else
        maxSerialNo=maxSerialNo +1
    End If

 

[Matt Brown]

It highlights this line in debug mode:

Set rs1 = db1.OpenRecordset(SQL, dbOpenSnapshot)

Tried the Dmax function and get the following error:

Improper use of Null

 

[dcx693]

Oh crud. Integer variables cannot take on Null values. Try changing the declaration to Dim maxSerialNo as Variant.

 

[Matt Brown]

Ok will do.
Thanks for your help

Matt