-
Mar 31st, 2007, 10:36 AM
#1
Thread Starter
Member
[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
-
Mar 31st, 2007, 01:02 PM
#2
Frenzied Member
Re: problems with rs.Open
Have you declared rs as a new recordset:
vb Code:
Set rs = New ADODB.Recordset
new at this myself mind, so it might not be this.
-
Mar 31st, 2007, 01:04 PM
#3
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 Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API 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
-
Mar 31st, 2007, 01:32 PM
#4
Thread Starter
Member
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.
-
Mar 31st, 2007, 01:35 PM
#5
Frenzied Member
Re: problems with rs.Open
You haven't declared either the cn or the rs
vb Code:
Dim cn as ADODB.Connection
Dim rs as ADODB.Recordset
-
Mar 31st, 2007, 01:36 PM
#6
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 Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API 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
-
Mar 31st, 2007, 01:49 PM
#7
Thread Starter
Member
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
-
Mar 31st, 2007, 02:24 PM
#8
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 Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API 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
-
Mar 31st, 2007, 02:40 PM
#9
Thread Starter
Member
Re: problems with rs.Open
This is the way I had the code, so I'm still having problems.
-
Mar 31st, 2007, 02:45 PM
#10
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 & "'"
-
Mar 31st, 2007, 03:05 PM
#11
Thread Starter
Member
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.
-
Mar 31st, 2007, 03:15 PM
#12
Re: problems with rs.Open
You need to remove the spaces between the ' marks, eg:
vb Code:
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|