Error in Count of records
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Error in Count of records

  1. #1
    Join Date
    Sep 2010
    Posts
    38

    Error in Count of records

    Code:
    Option Explicit
    Dim tr_no As String
    Private rs As New ADODB.Recordset
    Private ForeColors(0 To 2, 0 To 1) As Long
    Private BackColors(0 To 2, 0 To 1) As Long
    Private Sub Form_load()
    
    Combo3.AddItem "1"
    Combo3.AddItem "2"
    Combo3.AddItem "3"
    Combo3.AddItem "4"
    Combo3.AddItem "5"
    Combo3.AddItem "6"
    Combo3.AddItem "7"
    Combo3.AddItem "8"
    Combo3.AddItem "9"
    Combo3.AddItem "10"
    Combo3.AddItem "11"
    Combo3.AddItem "12"
    Combo3.AddItem "13"
    Combo3.AddItem "14"
    Combo3.AddItem "15"
    Combo3.AddItem "16"
    Combo3.AddItem "17"
    Combo3.AddItem "18"
    Combo3.AddItem "19"
    Combo3.AddItem "20  "
    
       ForeColors(1, 0) = vbBlue
       ForeColors(1, 1) = BackColor
       BackColors(1, 0) = BackColor
       BackColors(1, 1) = BackColor
       tmrBlink_Timer
       tmrBlink.Interval = 500
       tmrBlink.Enabled = True
    End Sub
    Private Sub cmdNew_Click()
    tr_no = autogen
    frmCommission_Entry.txtSeno = tr_no
    frmCommission_Entry.txtEdate = Date
    txtShaha.SetFocus
    End Sub
    Private Sub cmdSave_Click()
    
    Dim cnuser As New ADODB.connection
    Dim rsuser As New ADODB.Recordset
    
    If sempty(txtFulNa.Text) = True Then Exit Sub
    If sempty(txtFan.Text) = True Then Exit Sub
    If sempty(txtEdate.Text) = True Then Exit Sub
    If sempty(txtShaha.Text) = True Then Exit Sub
    If sempty(txtSeno.Text) = True Then Exit Sub
    If sempty(txtFisNa.Text) = True Then Exit Sub
    If sempty(txtSecNa.Text) = True Then Exit Sub
    If sempty(txtLasNa.Text) = True Then Exit Sub
    If sempty(txt1stID.Text) = True Then Exit Sub
    If sempty(txtExpDate.Text) = True Then Exit Sub
    If sempty(txtMob.Text) = True Then Exit Sub
    If sempty(txtChq.Text) = True Then Exit Sub
    If sempty(txtBank.Text) = True Then Exit Sub
    If sempty(Combo3.Text) = True Then Exit Sub
    If sempty(txtPrn.Text) = True Then Exit Sub
    
    Call connection(cnuser, App.Path & "\Commissions.mdb", "endromida")
    'Call connection(cnuser, "\\mika\medrar\Commissions.mdb", "endromida")
    Call Recordset(rsuser, cnuser, "SELECT * FROM commi_cus")
    
    
    With rsuser
    .AddNew
    Call shasta
    .Fields!funa = txtFulNa.Text
    .Fields!Sha_no = txtShaha.Text
    .Fields!Edate = txtEdate.Text
    .Fields!tr_no = txtSeno.Text
    .Fields!fami_na = txtFan.Text
    .Fields!first_na = txtFisNa.Text
    .Fields!sec_na = txtSecNa.Text
    .Fields!last_na = txtLasNa.Text
    .Fields!cus_id = txt1stID.Text
    .Fields!cus_mo = txtMob.Text
    .Fields!exp_date = txtExpDate.Text
    .Fields!cus_mo = txtMob.Text
    .Fields!ch_no = txtChq.Text
    .Fields!ba = txtBank.Text
    .Fields!tran_co = Combo3.Text
    .Fields!pro_na = txtPrn.Text
    .Update
    End With
    
    MsgBox "Successfully Saved.", vbInformation
    Call cmdRePrint_Click
    
    Call clear
    
    Set cnuser = Nothing
    Set rsuser = Nothing
    
    End Sub
    Private Sub lvButtons_H2_Click()
    Unload Me
    End Sub
    Function autogen() As String
    
       Dim cnview As New ADODB.connection
       Dim rsview As New ADODB.Recordset
    
    Call connection(cnview, App.Path & "\Commissions.mdb", "endromida")
    Call Recordset(rsview, cnview, "SELECT max(tr_no)FROM Commi_Cus")
        
        If IsNull(rsview(0)) = True Then
            autogen = "T-0000"
            Else
            autogen = "T-" & Format(Right$(Trim$(rsview(0)), 4) + 1, "0000")
            End If
            
    End Function
    Public Sub clear()
    
    txtSeno.Text = ""
    txtShaha.Text = ""
    txtEdate = Format(Date, "dd-mm-yyyy")
    txtFan.Text = ""
    txtFisNa.Text = ""
    txtSecNa.Text = ""
    txtLasNa.Text = ""
    txt1stID.Text = ""
    txtExpDate = Format(Date, "dd-mm-yyyy")
    txtChq.Text = ""
    txtBank.Text = ""
    txtMob.Text = ""
    txtFulNa.Text = ""
    txtPrn.Text = ""
    
    End Sub
    Private Sub lvButtons_H5_Click()
    Call cmdRePrint_Click
    End Sub
    Private Sub txtPrn_Change()
    IntelliSense txtPrn, "commi_cus", "pro_na"
    End Sub
    Private Sub txtshaha_KeyPress(KeyAscii As Integer)
    If KeyAscii = 13 Then
    txtPrn.SetFocus
    End If
    End Sub
    Private Sub txtprn_KeyPress(KeyAscii As Integer)
    If KeyAscii = 13 Then
    Combo3.SetFocus
    End If
    End Sub
    Private Sub combo3_KeyPress(KeyAscii As Integer)
    If KeyAscii = 13 Then
    txt1stID.SetFocus
    End If
    End Sub
    Private Sub txt1stid_KeyPress(KeyAscii As Integer)
    If KeyAscii = 13 Then
    txtChq.SetFocus
    End If
    End Sub
    Private Sub txtchq_KeyPress(KeyAscii As Integer)
    If KeyAscii = 13 Then
    txtBank.SetFocus
    End If
    End Sub
    Private Sub txtbank_KeyPress(KeyAscii As Integer)
    If KeyAscii = 13 Then
    cmdSave.SetFocus
    End If
    End Sub
    Private Sub cmdRePrint_Click()
    Call ConnectMe
    Set rs = New ADODB.Recordset
    
    rs.Open "select * from commi_cus where tr_no = '" & txtSeno.Text & "'", con, 1, 3
    
    If rs.RecordCount = 0 Then
        MsgBox "No Record Found On Query.", vbCritical, "Medrar"
       
    Else
        'Set Sales_Agreement.DataSource = rs
        Set DataReport1.DataSource = rs
        Unload Me
        'ChngPrinterOrientationPortrait Me
        'Sales_Agreement.Show
        DataReport1.Show
    End If
    
    Set rs = Nothing
    End Sub
    Private Sub txtshaha_Change()
    IntelliSense txtShaha, "Commi_Cus", "sha_no"
    Call pre
    End Sub
    Private Sub tmrBlink_Timer()
    Static State As Integer
    
    Dim fore_color As Long
    Dim i As Integer
    
        ' Toggle the state.
        State = 1 - State
    
        ' Set the colors.
        For i = 1 To 1
    '        lblBlink(i).ForeColor = ForeColors(i, State)
            
        Next i
    End Sub
    Function Cant()
    
      Dim cnview As New ADODB.connection
       Dim rsview As New ADODB.Recordset
    Call connection(cnview, App.Path & "\Commissions.mdb", "endromida")
    Call Recordset(rsview, cnview, "SELECT COUNT(*) AS numRecords FROM Commi_Cus")
    
          txtShaha = .Fields!Sha_no
         txtCou.Text = rec("numRecords").Value
       End If
             
    End Function
    Private Sub cus()
    
    Dim cnview As New ADODB.connection
    Dim rsemployee As New ADODB.Recordset
    
    Call connection(cnview, App.Path & "\Commissions.mdb", "endromida")
    Call Recordset(rsemployee, cnview, "SELECT * FROM customer WHERE idcn =" & txt1stID.Text & "")
    
    If rsemployee.RecordCount = 0 Then
    MsgBox "The record you requested could not be found.", vbExclamation, "Medrar"
    Exit Sub
    End If
    
    
    With rsemployee
    txtFan.Text = .Fields!fname
    txtFisNa.Text = .Fields!fsna
    txtSecNa.Text = .Fields!sena
    txtLasNa.Text = .Fields!trna
    txtFulNa.Text = .Fields!funame
    txtMob.Text = .Fields!mobie
    txtExpDate.Text = .Fields!exd
    End With
    
    Set cnview = Nothing
    Set rsemployee = Nothing
    End Sub
    Private Sub txt1stid_Change()
    IntelliSense txt1stID, "customer", "idcn"
    Call cus
    End Sub
    Private Sub pre()
    Dim cnview As New ADODB.connection
    Dim rsemployee As New ADODB.Recordset
    
    Call connection(cnview, App.Path & "\Commissions.mdb", "endromida")
    Call Recordset(rsemployee, cnview, "SELECT * FROM commi_cus WHERE sha_no =" & txtShaha.Text & "")
    
    If recexist("commi_cus", "sha_no", txtShaha.Text, cnview) = True Then Call hlfocus(txtPrt): Exit Sub
    
    With rsemployee
    
    txtPrt.Text = .Fields!funa
    
    End With
    End Sub
    Function shasta()
        
        Dim cnview As New ADODB.connection
        Dim rsview As New ADODB.Recordset
    
    Call connection(cnview, App.Path & "\Commissions.mdb", "endromida")
    Call Recordset(rsview, cnview, "SELECT * FROM Commi_Cus")
    
        If recexist("commi_cus", "sha_no", txtShaha.Text, cnview) = False Then
            Call st
                 Else
            Call stay
        End If
       End
    End Function
    Private Sub st()
    txtstat.Text = " Present"
    End Sub
    Private Sub stay()
    txtstat.Text = " Transfred"
    End Sub
    Errors in Pre() and Function Cant()

  2. #2
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,838

    Re: Error in Count of records

    what is the error(s)
    What lines do they occur on?

    Why show all that code that apparently has nothing to do with the error but not show the code that is called from within the problem subs?
    For that matter why have a separate sub/function to create a recordset or open a connection. I see a lot of people do this and I can't for the life of me figure out why. It just seems to make the code harder to follow.

    Also note that the Call keyword has not been needed in over 20 years, no idea why I see so many posts online still using it.

    On yet another note Recordset is a bad choice for a function or subroutine name
    Always use [code][/code] tags when posting code.

  3. #3
    Join Date
    Sep 2010
    Posts
    38

    Re: Error in Count of records

    Code:
    Function Cant()
    
      Dim cnview As New ADODB.connection
       Dim rsview As New ADODB.Recordset
    Call connection(cnview, App.Path & "\Commissions.mdb", "endromida")
    Call Recordset(rsview, cnview, "SELECT COUNT(*) AS numRecords FROM Commi_Cus")
    
          txtShaha = .Fields!Sha_no
         txtCou.Text = rec("numRecords").Value
       End If
             
    End Function
    - in this code i get error
    - what is replacement for record set and call

  4. #4
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,838

    Re: Error in Count of records

    What error? Always say what the error message is and which line it happens on.

    Call is an old keyword that has not been needed for many years. It is not needed at all.

    In this case Recordset is a poorly named sub routine.

    You really should not be using a sub routine at all. You should be opening the recordset in line with your code
    you also should not be using * for you selection instead you should specify a field name from your table
    Code:
    rsView.Open "SELECT COUNT(fieldname) AS numRecords FROM Commi_Cus",cnview
    The same is true for your connection sub, poorly named given that both Connection and Recordset are objects and using these as sub routine names causes confusion.

    As written that little piece of code calls two other pieces of code and whatever error you are getting could be happening in either one of those

    The other issues are
    1: You are not checking to see if anything was returned
    2: You are trying to pull a value from a field named Sha_no but that is not part of your select statement
    Always use [code][/code] tags when posting code.

  5. #5
    Join Date
    Sep 2010
    Posts
    38

    Re: Error in Count of records

    Please provide a sample of code like what i am trying to get.

  6. #6
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,838

    Re: Error in Count of records

    I sorta did but then you still have not bothered to tell us what the error is that you are getting. You have put very little effort into your posts and as I said you did not say what the error was nor did you explain what you are trying to do so not sure what you expect to get in the way of sample code.
    Always use [code][/code] tags when posting code.

  7. #7
    Join Date
    Sep 2010
    Posts
    38

    Re: Error in Count of records

    Code:
    Function sus()
    
    Dim cnview As New ADODB.connection
    Dim rsemployee As New ADODB.Recordset
    
    Call connection(cnview, App.Path & "\Commissions.mdb", "endromida")
    Call Recordset(rsemployee, cnview, "SELECT * FROM commi_cus WHERE sha_no =" & txtShaha.Text & "")
    
        If rsemployee.RecordCount > 0 = True Then
             txtstat.Text = .Fields!sta -------------------------> here Error
             txtPrn.Text = .Fields!pro_na
        Else
             txtstat.Text = ""
             txtPrn.Text = ""
    
    End Function
    Error is Invalid or unqualified reference

  8. #8
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,838

    Re: Error in Count of records

    That is a different query than what was in your other posts.
    In fact that is not even the same routine you said was giving you the error.

    That is a not an error related to count of records

    So does that mean your other error has been resolved or have you been giving us wrong information?

    Is there a field in that table named "sta" ?
    Always use [code][/code] tags when posting code.

  9. #9
    Join Date
    Sep 2010
    Posts
    38

    Re: Error in Count of records

    yes Field is there in table

  10. #10
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,838

    Re: Error in Count of records

    wow, Don't you think that just maybe you should put a little more effort into your posts? I asked more than one question and made a few comments and you respond with one short sentence which answers only one question.

    If you can't put in the effort to post answers to questions and give proper info then I will not bother to continue
    Always use [code][/code] tags when posting code.

  11. #11
    Join Date
    Sep 2010
    Posts
    38

    Re: Error in Count of records

    My Previous question was not resolved...still exist same. and this one new which i posted

  12. #12
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,838

    Re: Error in Count of records

    If you want help then post some details, answer all questions and help us help you.

    If you continue to just post 1 sentence and leave out important info then there is not much point here.

    I do not know where your error is at, what the error is, what you have tried nor much of anything due to your lack of info.
    Always use [code][/code] tags when posting code.

  13. #13
    Join Date
    Jul 2005
    Posts
    1,074

    Re: Error in Count of records

    Quote Originally Posted by dongodu View Post
    Code:
    Function sus()
    
    Dim cnview As New ADODB.connection
    Dim rsemployee As New ADODB.Recordset
    
    Call connection(cnview, App.Path & "\Commissions.mdb", "endromida")
    Call Recordset(rsemployee, cnview, "SELECT * FROM commi_cus WHERE sha_no =" & txtShaha.Text & "")
    
        If rsemployee.RecordCount > 0 = True Then
             txtstat.Text = .Fields!sta -------------------------> here Error
             txtPrn.Text = .Fields!pro_na
        Else
             txtstat.Text = ""
             txtPrn.Text = ""
    
    End Function
    Error is Invalid or unqualified reference

    Add the next red code to yours
    and remove blue code
    Code:
        If rsemployee.RecordCount > 0 = True Then
             txtstat.Text = rsemployee.Fields!sta -------------------------> here Error
             txtPrn.Text = rsemployee.Fields!pro_na
        Else
             txtstat.Text = ""
             txtPrn.Text = ""
        End If
    
    Last edited by jggtz; March 24th, 2014 at 02:32 PM.
    JG


    ... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...

  14. #14
    Join Date
    Sep 2010
    Posts
    38

    Re: Error in Count of records

    Name:  untitled.jpg
Views: 19
Size:  27.3 KBAs per your advise code edited, but error is attached
    Last edited by dongodu; March 25th, 2014 at 09:42 AM.

  15. #15
    VictorN's Avatar
    VictorN is online now Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Wallisellen (ZH), Switzerland
    Posts
    17,420

    Re: Error in Count of records

    It is very hard to understand what is written in the errow messagebox.
    However, if it is a run-time error then you have to debug your code to see whet and where goes wrong.
    Victor Nijegorodov

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center