Results 1 to 12 of 12

Thread: [RESOLVED] problems with rs.Open

  1. #1

    Thread Starter
    Member
    Join Date
    Feb 2006
    Posts
    60

    Resolved [RESOLVED] problems with rs.Open

    I'm trying to look in an Access DB table (tblSizes), retrieve the record matching the value of "Size" that is selected in "cboToolSizeFrom.Text", and place them in fields in the form. I am getting an error on the rs.Open line of my code. The error is: "Method 'Open' of object '_Recordset' failed. All the item in that line have valid values assigned to them. My code for this section is as follows:
    Code:
    Private Sub cmdCalculateConversion_Click()
    '+++++++++Size,CupDepth,CupVol,DieArea,CupArea,Shape,Hob#
    'Search for the selected tool data
    Dim strSQL As String 'build the SQL statement based on what the user typed in txtSearch
      strSQL = "SELECT * FROM tblSizes"
      If cboToolSizeFrom.Text <> "" Then
        strSQL = strSQL & " WHERE Size = " & cboToolSizeFrom.Text
      End If
      txtCupDepthFrom.Text = rs.Fields("CupDepth")
      txtCupVolumeFrom.Text = rs.Fields("CupVol")
      txtDieAreaFrom.Text = rs.Fields("DieArea")
         'close the recordset (required before reloading it)
      rs.Close 'load the new data
      rs.Open strSQL, cn, adOpenKeyset, adLockPessimistic, adCmdText 'show the data
      fillfields
    'strFromToolDimensions = Mid(strFromTool, 1, 15)
    sngH_from = txtCupDepthFrom.Text 'Cup depth
    sngT_from = txtThicknessFrom.Text 'Tablet thickness
    txtThicknessFrom.Text = FormatNumber(Round(sngT_from, 4), 4)
    sngCV_from = txtCupVolumeFrom.Text 'Cup volume
    sngDA_from = txtDieAreaFrom.Text 'Die area
    sngL_from = sngT_from - (2 * sngH_from) 'Calculate Land of tablet
    txtInitialLand = FormatNumber(Round(sngL_from, 4), 4)
    sngVolume = (2 * sngCV_from) + (sngDA_from * sngL_from) 'Calculate volume of tablet
    'Prepare to calculate target
    strToToolDimensions = cboToolSizeTo.Text
    'Cup depth is in position 2
    sngH_to = txtCupDepthTo.Text
    'Cup volume is in position 4
    sngCV_to = txtCupVolumeTo.Text
    'Die area is in position 5
    sngDA_to = txtDieAreaTo.Text
    sngL_to = (sngVolume - (2 * sngCV_to)) / sngDA_to
    sngT_to = sngL_to + (2 * sngH_to)
    txtTargetLand = FormatNumber(Round(sngL_to, 4), 4)
    txtThicknessTo.Text = FormatNumber(Round(sngT_to, 4), 4)
    End Sub

  2. #2
    Frenzied Member aikidokid's Avatar
    Join Date
    Aug 2002
    Location
    Bristol, UK
    Posts
    1,968

    Re: problems with rs.Open

    Have you declared rs as a new recordset:

    vb Code:
    1. Set rs = New ADODB.Recordset

    new at this myself mind, so it might not be this.
    If somebody helps you, take time to RATE the post. I do.

    "FAILURE IS NOT AN OPTION. It comes bundled with the software."

    Below are some of the threads that have helped me along the way:

    CodeBank submission:
    Listview Backcolor (without subclassing)

    Loading Treeview Nodes From A Database, Creating Registry Keys, Count Number of Lines in TextBox , Excellent RichTextBox Tricks & Tips
    Ideas & Screen Shots For A Code Library App
    How to do Data validation in Excel, Conditional Formating in Excel

  3. #3
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: problems with rs.Open

    Where is rs declared? You are using rs.Fields(... even before any rs.Open call. you need to open the recordset object first before you can use it.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  4. #4

    Thread Starter
    Member
    Join Date
    Feb 2006
    Posts
    60

    Re: problems with rs.Open

    I have the following statements the private sub form load section.
    Code:
    Set cn = New ADODB.Connection 'we've declared it as a ADODB connection lets set it.
        cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=tooling.mdb"
        cn.Open
        Set rs = New ADODB.Recordset 'as we did with the connection
    However, I tried to insert that in this sub before the rs.Open and it still didn't work.

  5. #5
    Frenzied Member aikidokid's Avatar
    Join Date
    Aug 2002
    Location
    Bristol, UK
    Posts
    1,968

    Re: problems with rs.Open

    You haven't declared either the cn or the rs
    vb Code:
    1. Dim cn as ADODB.Connection
    2. Dim rs as ADODB.Recordset
    If somebody helps you, take time to RATE the post. I do.

    "FAILURE IS NOT AN OPTION. It comes bundled with the software."

    Below are some of the threads that have helped me along the way:

    CodeBank submission:
    Listview Backcolor (without subclassing)

    Loading Treeview Nodes From A Database, Creating Registry Keys, Count Number of Lines in TextBox , Excellent RichTextBox Tricks & Tips
    Ideas & Screen Shots For A Code Library App
    How to do Data validation in Excel, Conditional Formating in Excel

  6. #6
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: problems with rs.Open

    So then rs is declared in some module as Public? Also, its still not being opened anywhere. You need ...

    rs.Open SQL Source, cn, adOpenKeyset, adLockOptimistic, adCmdText
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  7. #7

    Thread Starter
    Member
    Join Date
    Feb 2006
    Posts
    60

    Re: problems with rs.Open

    Thanks for patience guys. ADO is new to me. It may be a problem with Public v. Private declarations. I have declared the connection ansd record set, however, as Private. I don't know if that makes a diff. Here is the total code.
    Code:
    Option Explicit
    Private booIsAdding As Boolean
    Private cn As ADODB.Connection
    Private rs As ADODB.Recordset
    Dim strSQL As String
    Dim sngT_from As Single
    Dim sngT_to As Single
    Dim sngH_from As Single
    Dim sngH_to As Single
    Dim sngCV_from As Single
    Dim sngCV_to As Single
    Dim sngDA_from As Single
    Dim sngDA_to As Single
    Dim sngL_from As Single
    Dim sngL_to As Single
    Dim sngVolume As Single
    Dim strFromTool As String
    Dim strFromToolDimensions As String
    Dim strToToolDimensions As String
    Dim strToolSizeTo As String
    'Test portion*************
    Dim txtTestSize As String
    Dim sngTestArea As Single
    Dim sngTestLength As Single
    Dim strToolInitial As String
    Dim strToolTarget As String
    Private Sub form_load()
    strSQL = "SELECT * FROM tblSizes"
    Me.MousePointer = 11 'this makes the mouse pointer the hourglass
        Set cn = New ADODB.Connection 'we've declared it as a ADODB connection lets set it.
        cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=tooling.mdb"
        cn.Open
        Set rs = New ADODB.Recordset 'as we did with the connection
        rs.Open strSQL, cn, adOpenKeyset, adLockPessimistic, adCmdText
    rs.MoveFirst 'moves to the first record
    Do Until rs.EOF = True 'this is the Loop to add items to the combo box
    cboToolSizeFrom.AddItem rs.Fields("Size") 'this adds items from field1 into the combo box
    cboToolSizeTo.AddItem rs.Fields("Size") 'this adds items from field1 into the combo box
    rs.MoveNext 'moves next record
    Loop
    rs.MoveFirst
    fillfields 'i'll explain this later on.
    Me.MousePointer = 0 'sets the mouse pointer to the normal arrow
    End Sub
    Private Sub cmdCalculateConversion_Click()
    '+++++++++Size,CupDepth,CupVol,DieArea,CupArea,Shape,Hob#
    'Search for the selected tool data
    Dim strSQL As String 'build the SQL statement based on what the user typed in txtSearch
      strSQL = "SELECT * FROM tblSizes"
      If cboToolSizeFrom.Text <> "" Then
        strSQL = strSQL & " WHERE Size = " & cboToolSizeFrom.Text
      End If
      txtCupDepthFrom.Text = rs.Fields("CupDepth")
      txtCupVolumeFrom.Text = rs.Fields("CupVol")
      txtDieAreaFrom.Text = rs.Fields("DieArea")
         'close the recordset (required before reloading it)
      rs.Close 'load the new data
      
      rs.Open strSQL, cn, adOpenKeyset, adLockPessimistic, adCmdText 'show the data
      fillfields
    'strFromToolDimensions = Mid(strFromTool, 1, 15)
    sngH_from = txtCupDepthFrom.Text 'Cup depth
    sngT_from = txtThicknessFrom.Text 'Tablet thickness
    txtThicknessFrom.Text = FormatNumber(Round(sngT_from, 4), 4)
    sngCV_from = txtCupVolumeFrom.Text 'Cup volume
    sngDA_from = txtDieAreaFrom.Text 'Die area
    sngL_from = sngT_from - (2 * sngH_from) 'Calculate Land of tablet
    txtInitialLand = FormatNumber(Round(sngL_from, 4), 4)
    sngVolume = (2 * sngCV_from) + (sngDA_from * sngL_from) 'Calculate volume of tablet
    'Prepare to calculate target
    strToToolDimensions = cboToolSizeTo.Text
    'Cup depth is in position 2
    sngH_to = txtCupDepthTo.Text
    'Cup volume is in position 4
    sngCV_to = txtCupVolumeTo.Text
    'Die area is in position 5
    sngDA_to = txtDieAreaTo.Text
    sngL_to = (sngVolume - (2 * sngCV_to)) / sngDA_to
    sngT_to = sngL_to + (2 * sngH_to)
    txtTargetLand = FormatNumber(Round(sngL_to, 4), 4)
    txtThicknessTo.Text = FormatNumber(Round(sngT_to, 4), 4)
    End Sub
    Public Sub fillfields()
        If Not (rs.BOF = True Or rs.EOF = True) Then 'Checks if we are at the first or last record. This is use a lot.
        txtCupDepthFrom.Text = rs.Fields("CupDepth") 'text1 = field2 and display that data
        txtCupVolumeFrom.Text = rs.Fields("CupVol") 'as above
        txtDieAreaFrom.Text = rs.Fields("DieArea")
        cboToolSizeFrom.Text = rs.Fields("Size") 'as above
        txtCupDepthTo.Text = rs.Fields("CupDepth") 'text1 = field2 and display that data
        txtCupVolumeTo.Text = rs.Fields("CupVol") 'as above
        txtDieAreaTo.Text = rs.Fields("DieArea")
        cboToolSizeTo.Text = rs.Fields("Size") 'as above
                 Else
                 MsgBox "Either you are at the first record or the last record.", vbExclamation, "Cannot Move"
        End If
    End Sub
          Private Sub cmdPrev_Click()
          If Not (rs.BOF = True) Then
    rs.MovePrevious 'move previous record
           fillfields 'fill the controls
          End If
          End Sub
          Private Sub cmdNext_Click()
          If Not (rs.EOF = True) Then
          rs.MoveNext 'move to next record
           fillfields 'fill the controls
          End If
          End Sub
    Private Sub cmdSave_Click()
           With rs
            If booIsAdding Then .AddNew
                .Fields("CupDepth") = txtCupDepthFrom.Text
                .Fields("CupVol") = txtCupVolumeFrom.Text
                .Fields("DieArea") = txtDieAreaFrom.Text
                .Fields("Size") = cboToolSizeFrom.Text
                .Update 'this updates the recordset etc.
          End With
          booIsAdding = False 'revert back to "edit" mode
    End Sub
    
    Private Sub cmdAdd_Click()
    
           booIsAdding = True
              cboToolSizeFrom.Text = ""
              txtCupDepthFrom.Text = ""
              txtCupVolumeFrom.Text = ""
              txtDieAreaFrom.Text = ""
              'cboToolSizeTo.Text = "" _
              txtCupDepthTo.Text = "" _
              txtCupVolumeTo.Text = "" _
              txtDieAreaTo.Text = ""'
          End Sub
         Private Sub cmdDelete_Click()
         If MsgBox("Are you sure you want to delete this record?", vbYesNo + vbQuestion, "Delete?") = vbNo Then      'check if you really want to delete this record
         Exit Sub 'exit the command
         Else
            If Not (rs.BOF = True Or rs.EOF = True) Then
                    rs.Delete 'delete the current record
                If Not (rs.BOF = True Or rs.EOF = True) Then
                     rs.MoveNext 'move next
                    If rs.EOF Then rs.MoveLast
                    fillfields
            End If
          End If
        End If
        End Sub
    Private Sub Form_Unload(Cancel As Integer)
    rs.Close 'close the recordset
    cn.Close 'close the connection
    Set rs = Nothing  'set them to nothing
    Set cn = Nothing 'as above
    End Sub

  8. #8
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: problems with rs.Open

    Since its all in the same form, Private is fine unless you need to use the same rs in other forms or modules.

    so its working for you now? or do you get any errors?
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  9. #9

    Thread Starter
    Member
    Join Date
    Feb 2006
    Posts
    60

    Re: problems with rs.Open

    This is the way I had the code, so I'm still having problems.

  10. #10
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: problems with rs.Open

    The problem is that the field name Size is not valid - it is a reserved word. You should change the field name (in the database, and in your code).

    If the data type of the field is text/char you also need to put ' around the value in your SQL statement, eg: = '" & cboToolSizeFrom.Text & "'"

  11. #11

    Thread Starter
    Member
    Join Date
    Feb 2006
    Posts
    60

    Re: problems with rs.Open

    Thanks geek. That fixed that problem. However, I don't understand the bracketing of the apostrophes.

    strSQL = strSQL & " WHERE Dimensions = ' " & cboToolSizeFrom.Text & " ' "

    It seems like I should be bracketting around the cboToolSizeFrom.Text.

  12. #12
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: problems with rs.Open

    You need to remove the spaces between the ' marks, eg:
    vb Code:
    1. strSQL = strSQL & " WHERE Dimensions = '" & cboToolSizeFrom.Text & "'"
    The reason it is like this, is that the bits inside the " marks are strings (as highlighted by the forum tags), and cboToolSizeFrom.Text is also a string. Assuming that cboToolSizeFrom.Text contains "A", the whole string will be: " WHERE Dimensions = 'A'"

Posting Permissions

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



Click Here to Expand Forum to Full Width