how to use OpenRecordset

[clearwave]

I am trying to write code in Access VBA. I am going to run a select SQL to
read a record from a table then copy it into a variable. I am using
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strSQL As String
Dim strTmp As String
Dim rst As DAO.Recordset
Dim myDb As DAO.Database
Dim intResult As Integer


strSQL = "select FirmID as firmid" & _
"from Firm " & _
"where Firm.[Firm Name] = 'Credit Suisse' "
'"where Firm.[Firm Name]= Me.dealerName and " & _
'"Firm.[Vendor Name] = Me.vendorName"

Set myDb = currentDb()
Set rst = myDb.OpenRecordset(strSQL, DAO.dbOpenSnapshot)


End Sub

But for some reason, it always gives me run time error 91 Object variable or
with block variable not set.
How can I do that? What I want is simple, it is just query a record and copy
it into a variable.

Many thanks

 

[Robert Morley]

In an ADP, you can't use CurrentDB(), nor can you use DAO (at least, not the
way you're trying to). You have to make the jump to ADO in an ADP. The
approximate equivalent of CurrentDB() in an ADP is CurrentProject, so the
(untested) code you would want is:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strSQL As String
Dim strTmp As String
Dim rst As ADODB.Recordset
Dim intResult As Integer


strSQL = "select FirmID as firmid" & _
"from Firm " & _
"where Firm.[Firm Name] = 'Credit Suisse' "
'"where Firm.[Firm Name]= Me.dealerName and " & _
'"Firm.[Vendor Name] = Me.vendorName"

Set rst = New ADODB.Recordset
rst.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly,
adCmdText
'The rest of your code...probably something like
'intResult = rst!firmid.Value
rst.Close
Set rst = Nothing
End Sub

 

[clearwave via AccessMonster.com]

Thanks Robert.

Now I have another issue. the Me.dealerName, which is a text box in the form.
when i input a value, for some reason, the value won't show in the sub
function. I just add a button to show the value of Me.dealerName. after I
input a value, I click the button, the value shows. So I do not why the value
does not show in this sub funtion

In an ADP, you can't use CurrentDB(), nor can you use DAO (at least, not the
way you're trying to). You have to make the jump to ADO in an ADP. The
approximate equivalent of CurrentDB() in an ADP is CurrentProject, so the
(untested) code you would want is:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strSQL As String
Dim strTmp As String
Dim rst As ADODB.Recordset
Dim intResult As Integer

strSQL = "select FirmID as firmid" & _
"from Firm " & _
"where Firm.[Firm Name] = 'Credit Suisse' "
'"where Firm.[Firm Name]= Me.dealerName and " & _
'"Firm.[Vendor Name] = Me.vendorName"

Set rst = New ADODB.Recordset
rst.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly,
adCmdText
'The rest of your code...probably something like
'intResult = rst!firmid.Value
rst.Close
Set rst = Nothing
End Sub

I am trying to write code in Access VBA. I am going to run a select SQL to
read a record from a table then copy it into a variable. I am using

[quoted text clipped - 24 lines]

Many thanks

 

[Robert Morley]

What sub are you trying to read the value from? Access is a little quirky
that way. Try Me.dealerName.Text instead (which only works if dealerName is
the active control currently).


Rob

Thanks Robert.

Now I have another issue. the Me.dealerName, which is a text box in the
form.
when i input a value, for some reason, the value won't show in the sub
function. I just add a button to show the value of Me.dealerName. after I
input a value, I click the button, the value shows. So I do not why the
value
does not show in this sub funtion

In an ADP, you can't use CurrentDB(), nor can you use DAO (at least, not
the
way you're trying to). You have to make the jump to ADO in an ADP. The
approximate equivalent of CurrentDB() in an ADP is CurrentProject, so the
(untested) code you would want is:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strSQL As String
Dim strTmp As String
Dim rst As ADODB.Recordset
Dim intResult As Integer

strSQL = "select FirmID as firmid" & _
"from Firm " & _
"where Firm.[Firm Name] = 'Credit Suisse' "
'"where Firm.[Firm Name]= Me.dealerName and " & _
'"Firm.[Vendor Name] = Me.vendorName"

Set rst = New ADODB.Recordset
rst.Open strSQL, CurrentProject.Connection, adOpenStatic,
adLockReadOnly,
adCmdText
'The rest of your code...probably something like
'intResult = rst!firmid.Value
rst.Close
Set rst = Nothing
End Sub

I am trying to write code in Access VBA. I am going to run a select SQL
to
read a record from a table then copy it into a variable. I am using

[quoted text clipped - 24 lines]

Many thanks