Error no: 6 Overflow

[ankrups]

Hi there,

I am trying to filtered the data by Invoice no. in my one of the field and I get the following overflow error...the coding is

Private Sub cboInvoiceNumber_AfterUpdate()
  
   Dim intInvoiceNumber As Integer
      
   intInvoiceNumber = Nz(Me![cboInvoiceNumber])
    
   strFilter = "[InvoiceNumber] =" & intInvoiceNumber
   Me![FilterString] = strFilter
   DoCmd.RunCommand acCmdSaveRecord
   strRecordSource = "qryMainSearch"
   strQuery = "qryFilteredMainSearch"
   strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
      & strFilter & ";"
   Debug.Print "SQL Statement: " & strSQL
   Debug.Print CreateAndTestQuery(strQuery, strSQL) _
      & " records found"
   Me![subSearchResults].Form.RecordSource = strQuery
   

End Sub

your help is appreciated...


thnks

ankit

 

[SydneyGeek]

Hi ankit,

Integer values overflow around 32K. Try

Dim intInvoiceNumber As Long

Denis

 

[ankrups]

Hi Denis,

Now the error turns into no.13 : Type Mismatch. i declare this variable on start of the database.pls have a look and reply me...

Option Explicit
Option Compare Database


Dim strTitle As String
Dim strPrompt As String
Dim strFilter As String
Dim strQuery As String
Dim strRecordSource As String
Dim strSQL As String

--------------
Private Sub cboInvoiceNumber_AfterUpdate()
   
   Dim intInvoiceNumber As Long
      
   intInvoiceNumber = Nz(Me![cboInvoiceNumber])
     
   strFilter = "[InvoiceNumber] =" & intInvoiceNumber
   Me![FilterString] = strFilter
   DoCmd.RunCommand acCmdSaveRecord
   strRecordSource = "qryMainSearch"
   strQuery = "qryFilteredMainSearch"
   strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
      & strFilter & ";"
   Debug.Print "SQL Statement: " & strSQL
   Debug.Print CreateAndTestQuery(strQuery, strSQL) _
      & " records found"
   Me![subSearchResults].Form.RecordSource = strQuery
  

End Sub

waiting for your reply...


thanks ...ankit

 

[SydneyGeek]

In your table, is InvoiceNumber a Number or Text?

Denis

 

[ankrups]

Hi Denis,

it is Text...

 

[ankrups]

now I changed to number and still it's same error....

 

[SydneyGeek]

OK, try this instead. If the InvoiceNumber field is text, and that is the first field in the combo, you need to declare InvoiceNumber as a String and surround any occurrences with quotes.

Private Sub cboInvoiceNumber_AfterUpdate()
 
   Dim strInvoiceNumber As String
     
   strInvoiceNumber = Me![cboInvoiceNumber]
   
   strFilter = "[InvoiceNumber] ='" & strInvoiceNumber & "'"
   Me![FilterString] = strFilter
   DoCmd.RunCommand acCmdSaveRecord
   strRecordSource = "qryMainSearch"
   strQuery = "qryFilteredMainSearch"
   strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
      & strFilter & ";"
   Debug.Print "SQL Statement: " & strSQL
   Debug.Print CreateAndTestQuery(strQuery, strSQL) _
      & " records found"
   Me![subSearchResults].Form.RecordSource = strQuery
   

End Sub

Denis

 

[ankrups]

Hi Dennis,

Now I get the Error no.13 : Type Mismatch any suggestin...and yest data type in table where invoicenumber stored is Integer...not a text,,,


wating for your reply.

thanks

ankit

 

[SydneyGeek]

Hi Ankit,

maybe you are making this too complex by passing the combo value to a variable first. Why not use it directly?

Dim strTitle As String 
Dim strPrompt As String 
Dim strFilter As String 
Dim strQuery As String 
Dim strRecordSource As String 
Dim strSQL As String 

-------------- 
Private Sub cboInvoiceNumber_AfterUpdate() 
    
   strFilter = "[InvoiceNumber] =" & Me!cboInvoiceNumber 
   Me![FilterString] = strFilter 
   DoCmd.RunCommand acCmdSaveRecord 
   strRecordSource = "qryMainSearch" 
   strQuery = "qryFilteredMainSearch" 
   strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _ 
      & strFilter & ";" 
   Debug.Print "SQL Statement: " & strSQL 
   Debug.Print CreateAndTestQuery(strQuery, strSQL) _ 
      & " records found" 
   Me![subSearchResults].Form.RecordSource = strQuery 
  
End Sub

Denis