Long versus Integer

[eacollie]

Hi:

I'm running into an overflow problem with the following code (with the variable nCNOS), but get a compile error (AddInvoice rsInvoice, nRNOS, nCNOS) if I change it to Long. Can someone help?

    Dim stDocName As String
    Dim nRetreatNumber As Integer
    Dim nRNOS As Integer
    'Dim nCNOS As Integer
    Dim nCNOS As Long
    
    'check whether there is a customer number
    nRNOS = Forms!frmSpecialEventsEnterDeleteModify.RNOS
    nCNOS = Forms!frmSpecialEventsEnterDeleteModify.Text191
    Dim Msg, Style, Title, Help, Ctxt, Response, MyString

    If IsNull(nCNOS) Or nCNOS = 0 Then
        Msg = "You must select a person to bill before you can create an invoice"
        Style = vbOK + vbCritical
        Title = "Select a person to bill"
        MsgBox Msg, Style, Title
        Exit Sub
    End If
    
    Dim sWhere As String
    Dim rsInvoice As ADODB.Recordset
    Dim rsCount As ADODB.Recordset
        
    nRNOS = Forms!frmSpecialEventsEnterDeleteModify.[RNOS]
    Set rsCount = New ADODB.Recordset
    Set rsInvoice = New ADODB.Recordset
    sWhere = "SELECT * FROM tblInvoice WHERE RNOS = " & nRNOS & ""
    rsCount.Open sWhere, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
        
    'check if an invoice already exists for this event
    If rsCount.EOF Then
        rsInvoice.Open "tblInvoice", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
        AddInvoice rsInvoice, nRNOS, nCNOS
        rsInvoice.Close
        stDocName = "rptInvoiceForSpecialEvents"
        DoCmd.OpenReport stDocName, acPreview
    Else
        stDocName = "rptInvoiceForSpecialEvents"
        DoCmd.OpenReport stDocName, acViewPreview
    End If

[Stewart Ross]

Hi. You don't show your code for AddInvoice, so I am just guessing here - is the second parameter for AddInvoice defined as an integer? If it is, passing a Long when an Integer type is expected will cause an error. You would need to change the Integer definition in AddInvoice to Long for compatibility.

Otherwise, there is no obvious problem with the code posted.

-Stewart

[nico5038]

Assigning text to a number field:
nCNOS = Forms!frmSpecia lEventsEnterDel eteModify.Text1 91
looks odd.
You could use Val() like:
nCNOS = val(Forms!frmSp ecialEventsEnte rDeleteModify.T ext191 )
to turn the textfield into a (long) number, but I would probably keep the field having the same datatype as in the original table.

An alternative I often use is to have a combobox holding a DISTINCT value for the personID so the user can only select existing values...

Nic;o)

[eacollie]

Hi. You don't show your code for AddInvoice, so I am just guessing here - is the second parameter for AddInvoice defined as an integer? If it is, passing a Long when an Integer type is expected will cause an error. You would need to change the Integer definition in AddInvoice to Long for compatibility.

Otherwise, there is no obvious problem with the code posted.

-Stewart

Thanks Stewart! How blind am I? Of course, when I look at the AddInvoice code, CNOS it is defined as an Integer....

All works well now.

Thanks!

[eacollie]

Assigning text to a number field:
nCNOS = Forms!frmSpecia lEventsEnterDel eteModify.Text1 91
looks odd.
You could use Val() like:
nCNOS = val(Forms!frmSp ecialEventsEnte rDeleteModify.T ext191 )
to turn the textfield into a (long) number, but I would probably keep the field having the same datatype as in the original table.

An alternative I often use is to have a combobox holding a DISTINCT value for the personID so the user can only select existing values...

Nic;o)

Thanks! I'll try your suggestion...
Thanks for the quick reply.