DoCmd.RunSQL Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can anyone tell me why this code does not work?

editper = Forms![requests]![Subrequests].Form![StaffID]
Dim lstrSQL As String

lstrSQL = lstrSQL & "Select firstname, lastname "
lstrSQL = lstrSQL & "From Customers "
lstrSQL = lstrSQL & "Where customers.CustID = editper"
DoCmd.RunSQL lstrSQL
custname = firstname & "." & Lastname
 
Can anyone tell me why this code does not work?

editper = Forms![requests]![Subrequests].Form![StaffID]
Dim lstrSQL As String

lstrSQL = lstrSQL & "Select firstname, lastname "
lstrSQL = lstrSQL & "From Customers "
lstrSQL = lstrSQL & "Where customers.CustID = editper"
DoCmd.RunSQL lstrSQL
custname = firstname & "." & Lastname
Click to expand...

You should read Access/VBA help.

You cannot run a Select query using RunSQL (only Action queries, such
as Update, Insert, etc.).
Access has no place to display the query results.
 
Try using ADO to open a recordset then use the data to update your fields on
the form.
Also, you need to make sure your ADO reference is checked.

Dim rs As ADODB.Recordset
Dim sSQL As String

On Error GoTo hell

Set rs = New ADODB.Recordset
editper = Forms![requests]![Subrequests].Form![StaffID]

'Make the SQL select statment.
sSQL = "SELECT firstname, lastname " & _
"FROM Customers " & _
"WHERE customers.CustID = " & editper

'adOpenStatic makes it readonly. Read up on ADO if you want to edit the data
rs.Open sSQL, CurrentProject.Connection, adOpenStatic

If rs.RecordCount > 0 Then
custname = rs!firstname & "." & rs!Lastname
End If
'if u want to leave the recordset open then comment out these two lines of
code
rs.Close
Set rs = Nothing

Exit Sub

hell:
'Error trapping
'You can change the name of hell. i just have a weird sense of humor :)
MsgBox Err.Description

Hope this helps.
Good luck!
 
Sweet! I've been looking for this for a couple of days,

Thank you!

Susan

visdev1 said:
Try using ADO to open a recordset then use the data to update your fields
on
the form.
Also, you need to make sure your ADO reference is checked.

Dim rs As ADODB.Recordset
Dim sSQL As String

On Error GoTo hell

Set rs = New ADODB.Recordset
editper = Forms![requests]![Subrequests].Form![StaffID]

'Make the SQL select statment.
sSQL = "SELECT firstname, lastname " & _
"FROM Customers " & _
"WHERE customers.CustID = " & editper

'adOpenStatic makes it readonly. Read up on ADO if you want to edit the
data
rs.Open sSQL, CurrentProject.Connection, adOpenStatic

If rs.RecordCount > 0 Then
custname = rs!firstname & "." & rs!Lastname
End If
'if u want to leave the recordset open then comment out these two lines of
code
rs.Close
Set rs = Nothing

Exit Sub

hell:
'Error trapping
'You can change the name of hell. i just have a weird sense of humor :)
MsgBox Err.Description

Hope this helps.
Good luck!


TallyJasin said:
Can anyone tell me why this code does not work?

editper = Forms![requests]![Subrequests].Form![StaffID]
Dim lstrSQL As String

lstrSQL = lstrSQL & "Select firstname, lastname "
lstrSQL = lstrSQL & "From Customers "
lstrSQL = lstrSQL & "Where customers.CustID = editper"
DoCmd.RunSQL lstrSQL
custname = firstname & "." & Lastname
Click to expand...
Click to expand...
 
That took care of my problem...thanks alot for your insight

visdev1 said:
Try using ADO to open a recordset then use the data to update your fields on
the form.
Also, you need to make sure your ADO reference is checked.

Dim rs As ADODB.Recordset
Dim sSQL As String

On Error GoTo hell

Set rs = New ADODB.Recordset
editper = Forms![requests]![Subrequests].Form![StaffID]

'Make the SQL select statment.
sSQL = "SELECT firstname, lastname " & _
"FROM Customers " & _
"WHERE customers.CustID = " & editper

'adOpenStatic makes it readonly. Read up on ADO if you want to edit the data
rs.Open sSQL, CurrentProject.Connection, adOpenStatic

If rs.RecordCount > 0 Then
custname = rs!firstname & "." & rs!Lastname
End If
'if u want to leave the recordset open then comment out these two lines of
code
rs.Close
Set rs = Nothing

Exit Sub

hell:
'Error trapping
'You can change the name of hell. i just have a weird sense of humor :)
MsgBox Err.Description

Hope this helps.
Good luck!


TallyJasin said:
Can anyone tell me why this code does not work?

editper = Forms![requests]![Subrequests].Form![StaffID]
Dim lstrSQL As String

lstrSQL = lstrSQL & "Select firstname, lastname "
lstrSQL = lstrSQL & "From Customers "
lstrSQL = lstrSQL & "Where customers.CustID = editper"
DoCmd.RunSQL lstrSQL
custname = firstname & "." & Lastname
Click to expand...
Click to expand...
 
Sure i enjoyed it.
If either of you have any more questions i will keep tabs on this post.

TallyJasin said:
That took care of my problem...thanks alot for your insight

visdev1 said:
Try using ADO to open a recordset then use the data to update your fields on
the form.
Also, you need to make sure your ADO reference is checked.

Dim rs As ADODB.Recordset
Dim sSQL As String

On Error GoTo hell

Set rs = New ADODB.Recordset
editper = Forms![requests]![Subrequests].Form![StaffID]

'Make the SQL select statment.
sSQL = "SELECT firstname, lastname " & _
"FROM Customers " & _
"WHERE customers.CustID = " & editper

'adOpenStatic makes it readonly. Read up on ADO if you want to edit the data
rs.Open sSQL, CurrentProject.Connection, adOpenStatic

If rs.RecordCount > 0 Then
custname = rs!firstname & "." & rs!Lastname
End If
'if u want to leave the recordset open then comment out these two lines of
code
rs.Close
Set rs = Nothing

Exit Sub

hell:
'Error trapping
'You can change the name of hell. i just have a weird sense of humor :)
MsgBox Err.Description

Hope this helps.
Good luck!


TallyJasin said:
Can anyone tell me why this code does not work?

editper = Forms![requests]![Subrequests].Form![StaffID]
Dim lstrSQL As String

lstrSQL = lstrSQL & "Select firstname, lastname "
lstrSQL = lstrSQL & "From Customers "
lstrSQL = lstrSQL & "Where customers.CustID = editper"
DoCmd.RunSQL lstrSQL
custname = firstname & "." & Lastname
Click to expand...
Click to expand...
Click to expand...
 
I am having a problem executing this twice in an event procedure. I first
check one table for certain values and then I check another table for certain
values. I am still trying to get the code corrected in my procedure but it
either tells me that I have a Duplicate declaration in current scope or no
value given for one or more required parameters.

visdev1 said:
Sure i enjoyed it.
If either of you have any more questions i will keep tabs on this post.

TallyJasin said:
That took care of my problem...thanks alot for your insight

visdev1 said:
Try using ADO to open a recordset then use the data to update your fields on
the form.
Also, you need to make sure your ADO reference is checked.

Dim rs As ADODB.Recordset
Dim sSQL As String

On Error GoTo hell

Set rs = New ADODB.Recordset
editper = Forms![requests]![Subrequests].Form![StaffID]

'Make the SQL select statment.
sSQL = "SELECT firstname, lastname " & _
"FROM Customers " & _
"WHERE customers.CustID = " & editper

'adOpenStatic makes it readonly. Read up on ADO if you want to edit the data
rs.Open sSQL, CurrentProject.Connection, adOpenStatic

If rs.RecordCount > 0 Then
custname = rs!firstname & "." & rs!Lastname
End If
'if u want to leave the recordset open then comment out these two lines of
code
rs.Close
Set rs = Nothing

Exit Sub

hell:
'Error trapping
'You can change the name of hell. i just have a weird sense of humor :)
MsgBox Err.Description

Hope this helps.
Good luck!


:

Can anyone tell me why this code does not work?

editper = Forms![requests]![Subrequests].Form![StaffID]
Dim lstrSQL As String

lstrSQL = lstrSQL & "Select firstname, lastname "
lstrSQL = lstrSQL & "From Customers "
lstrSQL = lstrSQL & "Where customers.CustID = editper"
DoCmd.RunSQL lstrSQL
custname = firstname & "." & Lastname
Click to expand...
Click to expand...
Click to expand...
 
Without seeing your code i think your problem is your use of your recordset
variables.

There are a few ways to open multiple recordsets in one sub. One way is to
open the recordset, get your data, close the recordset, and then reopen a new
recordset. Another way is to make two recordsets.
I will show you how to do both of the methods i just talked about.

This is how you close and reopen a recordset:

Dim rs As ADODB.Recordset
Dim sSQL As String
Dim strLastName as String

On Error GoTo hell

Set rs = New ADODB.Recordset
editper = Forms![requests]![Subrequests].Form![StaffID]

sSQL = "SELECT firstname, lastname " & _
"FROM Customers " & _
"WHERE customers.CustID = " & editper

rs.Open sSQL, CurrentProject.Connection, adOpenStatic

If rs.RecordCount > 0 Then
custname = rs!firstname & "." & rs!Lastname
strLastName = rs!Lastname
End If

'close the first recordset so you can reopen and new query useing the same
variable "rs"
rs.Close

sSQL = "SELECT firstname, lastname " & _
"FROM Customers " & _
"WHERE customers.lastname = " & strLastName

rs.Open sSQL, CurrentProject.Connection, adOpenStatic

If rs.RecordCount > 0 Then
custname = rs!firstname & "." & rs!Lastname
End If
'always close the recordset after you are done with it
rs.Close
Set rs = Nothing

Exit Sub

hell:
MsgBox Err.Description

This is how i would do it with two recordsets:

'set up two variables for each recordset
Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim sSQL As String

On Error GoTo hell

Set rs = New ADODB.Recordset
editper = Forms![requests]![Subrequests].Form![StaffID]

sSQL = "SELECT firstname, lastname " & _
"FROM Customers " & _
"WHERE customers.CustID = " & editper
'open the first recordset
rs.Open sSQL, CurrentProject.Connection, adOpenStatic

If rs.RecordCount > 0 Then
custname = rs!firstname & "." & rs!Lastname

'now if you have data then open the second recordset
Set rs2 = New ADODB.Recordset
sSQL = "SELECT firstname, lastname " & _
"FROM Customers " & _
"WHERE customers.lastname = " & rs!Lastname

rs2.Open sSQL, CurrentProject.Connection, adOpenStatic

If rs2.RecordCount > 0 Then
custname = rs2!firstname & "." & rs2!Lastname
End If
'make sure you close both recordsets when you are done.
'HERE
rs2.Close
Set rs2 = Nothing

End If

'and HERE
rs.Close
Set rs = Nothing

Exit Sub

hell:
MsgBox Err.Description

I hope this helps you with your bug.
Good Luck!
 
I am still having a problem. The error message that I get is "No value given
for one or more required parameters.

The code is:
Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim sSQL As String

On Error GoTo Err_Current

Set rs = New ADODB.Recordset
editper = Forms![requests]![Subrequests].Form![StaffID]

'Make the SQL select statment.
sSQL = "SELECT firstname, lastname " & _
"FROM Customers " & _
"WHERE customers.CustID = " & editper

'adOpenStatic makes it readonly. Read up on ADO if you want to edit the data
rs.Open sSQL, CurrentProject.Connection, adOpenStatic

If rs.RecordCount > 0 Then
custname = rs!firstname & "." & rs!Lastname

Set rs2 = New ADODB.Recordset

'Make the SQL select statment.
sSQL = "SELECT currentuser " & _
"FROM editusers " & _
"WHERE editusers.currentuser = " & CurrentUser()

'adOpenStatic makes it readonly. Read up on ADO if you want to edit the data
rs2.Open sSQL, CurrentProject.Connection, adOpenStatic

If rs2.RecordCount > 0 Then
GoTo Exit_Current
End If
rs2.Close
Set rs2 = Nothing
End If
rs.Close
Set rs = Nothing

visdev1 said:
Without seeing your code i think your problem is your use of your recordset
variables.

There are a few ways to open multiple recordsets in one sub. One way is to
open the recordset, get your data, close the recordset, and then reopen a new
recordset. Another way is to make two recordsets.
I will show you how to do both of the methods i just talked about.

This is how you close and reopen a recordset:

Dim rs As ADODB.Recordset
Dim sSQL As String
Dim strLastName as String

On Error GoTo hell

Set rs = New ADODB.Recordset
editper = Forms![requests]![Subrequests].Form![StaffID]

sSQL = "SELECT firstname, lastname " & _
"FROM Customers " & _
"WHERE customers.CustID = " & editper

rs.Open sSQL, CurrentProject.Connection, adOpenStatic

If rs.RecordCount > 0 Then
custname = rs!firstname & "." & rs!Lastname
strLastName = rs!Lastname
End If

'close the first recordset so you can reopen and new query useing the same
variable "rs"
rs.Close

sSQL = "SELECT firstname, lastname " & _
"FROM Customers " & _
"WHERE customers.lastname = " & strLastName

rs.Open sSQL, CurrentProject.Connection, adOpenStatic

If rs.RecordCount > 0 Then
custname = rs!firstname & "." & rs!Lastname
End If
'always close the recordset after you are done with it
rs.Close
Set rs = Nothing

Exit Sub

hell:
MsgBox Err.Description

This is how i would do it with two recordsets:

'set up two variables for each recordset
Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim sSQL As String

On Error GoTo hell

Set rs = New ADODB.Recordset
editper = Forms![requests]![Subrequests].Form![StaffID]

sSQL = "SELECT firstname, lastname " & _
"FROM Customers " & _
"WHERE customers.CustID = " & editper
'open the first recordset
rs.Open sSQL, CurrentProject.Connection, adOpenStatic

If rs.RecordCount > 0 Then
custname = rs!firstname & "." & rs!Lastname

'now if you have data then open the second recordset
Set rs2 = New ADODB.Recordset
sSQL = "SELECT firstname, lastname " & _
"FROM Customers " & _
"WHERE customers.lastname = " & rs!Lastname

rs2.Open sSQL, CurrentProject.Connection, adOpenStatic

If rs2.RecordCount > 0 Then
custname = rs2!firstname & "." & rs2!Lastname
End If
'make sure you close both recordsets when you are done.
'HERE
rs2.Close
Set rs2 = Nothing

End If

'and HERE
rs.Close
Set rs = Nothing

Exit Sub

hell:
MsgBox Err.Description

I hope this helps you with your bug.
Good Luck!

TallyJasin said:
I am having a problem executing this twice in an event procedure. I first
check one table for certain values and then I check another table for certain
values. I am still trying to get the code corrected in my procedure but it
either tells me that I have a Duplicate declaration in current scope or no
value given for one or more required parameters.
Click to expand...
Click to expand...
 
Hmmm....
When you step threw your code, what line does the error occur on?
It could be CurrentUser(). Is it a function? Does it require you to pass
data to it?

If you still can't find the problem show me then show me the code for
CurrentUser() and try to find what line the error is on.


TallyJasin said:
I am still having a problem. The error message that I get is "No value given
for one or more required parameters.

The code is:
Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim sSQL As String

On Error GoTo Err_Current

Set rs = New ADODB.Recordset
editper = Forms![requests]![Subrequests].Form![StaffID]

'Make the SQL select statment.
sSQL = "SELECT firstname, lastname " & _
"FROM Customers " & _
"WHERE customers.CustID = " & editper

'adOpenStatic makes it readonly. Read up on ADO if you want to edit the data
rs.Open sSQL, CurrentProject.Connection, adOpenStatic

If rs.RecordCount > 0 Then
custname = rs!firstname & "." & rs!Lastname

Set rs2 = New ADODB.Recordset

'Make the SQL select statment.
sSQL = "SELECT currentuser " & _
"FROM editusers " & _
"WHERE editusers.currentuser = " & CurrentUser()

'adOpenStatic makes it readonly. Read up on ADO if you want to edit the data
rs2.Open sSQL, CurrentProject.Connection, adOpenStatic

If rs2.RecordCount > 0 Then
GoTo Exit_Current
End If
rs2.Close
Set rs2 = Nothing
End If
rs.Close
Set rs = Nothing

visdev1 said:
Without seeing your code i think your problem is your use of your recordset
variables.

There are a few ways to open multiple recordsets in one sub. One way is to
open the recordset, get your data, close the recordset, and then reopen a new
recordset. Another way is to make two recordsets.
I will show you how to do both of the methods i just talked about.

This is how you close and reopen a recordset:

Dim rs As ADODB.Recordset
Dim sSQL As String
Dim strLastName as String

On Error GoTo hell

Set rs = New ADODB.Recordset
editper = Forms![requests]![Subrequests].Form![StaffID]

sSQL = "SELECT firstname, lastname " & _
"FROM Customers " & _
"WHERE customers.CustID = " & editper

rs.Open sSQL, CurrentProject.Connection, adOpenStatic

If rs.RecordCount > 0 Then
custname = rs!firstname & "." & rs!Lastname
strLastName = rs!Lastname
End If

'close the first recordset so you can reopen and new query useing the same
variable "rs"
rs.Close

sSQL = "SELECT firstname, lastname " & _
"FROM Customers " & _
"WHERE customers.lastname = " & strLastName

rs.Open sSQL, CurrentProject.Connection, adOpenStatic

If rs.RecordCount > 0 Then
custname = rs!firstname & "." & rs!Lastname
End If
'always close the recordset after you are done with it
rs.Close
Set rs = Nothing

Exit Sub

hell:
MsgBox Err.Description

This is how i would do it with two recordsets:

'set up two variables for each recordset
Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim sSQL As String

On Error GoTo hell

Set rs = New ADODB.Recordset
editper = Forms![requests]![Subrequests].Form![StaffID]

sSQL = "SELECT firstname, lastname " & _
"FROM Customers " & _
"WHERE customers.CustID = " & editper
'open the first recordset
rs.Open sSQL, CurrentProject.Connection, adOpenStatic

If rs.RecordCount > 0 Then
custname = rs!firstname & "." & rs!Lastname

'now if you have data then open the second recordset
Set rs2 = New ADODB.Recordset
sSQL = "SELECT firstname, lastname " & _
"FROM Customers " & _
"WHERE customers.lastname = " & rs!Lastname

rs2.Open sSQL, CurrentProject.Connection, adOpenStatic

If rs2.RecordCount > 0 Then
custname = rs2!firstname & "." & rs2!Lastname
End If
'make sure you close both recordsets when you are done.
'HERE
rs2.Close
Set rs2 = Nothing

End If

'and HERE
rs.Close
Set rs = Nothing

Exit Sub

hell:
MsgBox Err.Description

I hope this helps you with your bug.
Good Luck!

TallyJasin said:
I am having a problem executing this twice in an event procedure. I first
check one table for certain values and then I check another table for certain
values. I am still trying to get the code corrected in my procedure but it
either tells me that I have a Duplicate declaration in current scope or no
value given for one or more required parameters.
Click to expand...
Click to expand...
Click to expand...
 
I just remembered what CurrentUser was and its not that. I think your
problem might be the connection string. I think i remember seeing that error
before and editing the connection string fixed my bug i think. Now i have
been using CurrentProject.Connection but if you are connecting to an external
database then you need to set a connection string. Even if you are
connecting to an internal table i would still suggest setting a connection
string just to make sure that its not the error.



'insert these lines

Dim cnn As ADODB.Connection

Set cnn = New ADODB.Connection

cnn.Open "Provider= Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Test\db1.mdb;"

'modify this line
rs.Open sSQL, cnn, adOpenStatic

Hope this helps :)
Good Luck!

TallyJasin said:
I am still having a problem. The error message that I get is "No value given
for one or more required parameters.

The code is:
Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim sSQL As String

On Error GoTo Err_Current

Set rs = New ADODB.Recordset
editper = Forms![requests]![Subrequests].Form![StaffID]

'Make the SQL select statment.
sSQL = "SELECT firstname, lastname " & _
"FROM Customers " & _
"WHERE customers.CustID = " & editper

'adOpenStatic makes it readonly. Read up on ADO if you want to edit the data
rs.Open sSQL, CurrentProject.Connection, adOpenStatic

If rs.RecordCount > 0 Then
custname = rs!firstname & "." & rs!Lastname

Set rs2 = New ADODB.Recordset

'Make the SQL select statment.
sSQL = "SELECT currentuser " & _
"FROM editusers " & _
"WHERE editusers.currentuser = " & CurrentUser()

'adOpenStatic makes it readonly. Read up on ADO if you want to edit the data
rs2.Open sSQL, CurrentProject.Connection, adOpenStatic

If rs2.RecordCount > 0 Then
GoTo Exit_Current
End If
rs2.Close
Set rs2 = Nothing
End If
rs.Close
Set rs = Nothing

visdev1 said:
Without seeing your code i think your problem is your use of your recordset
variables.

There are a few ways to open multiple recordsets in one sub. One way is to
open the recordset, get your data, close the recordset, and then reopen a new
recordset. Another way is to make two recordsets.
I will show you how to do both of the methods i just talked about.

This is how you close and reopen a recordset:

Dim rs As ADODB.Recordset
Dim sSQL As String
Dim strLastName as String

On Error GoTo hell

Set rs = New ADODB.Recordset
editper = Forms![requests]![Subrequests].Form![StaffID]

sSQL = "SELECT firstname, lastname " & _
"FROM Customers " & _
"WHERE customers.CustID = " & editper

rs.Open sSQL, CurrentProject.Connection, adOpenStatic

If rs.RecordCount > 0 Then
custname = rs!firstname & "." & rs!Lastname
strLastName = rs!Lastname
End If

'close the first recordset so you can reopen and new query useing the same
variable "rs"
rs.Close

sSQL = "SELECT firstname, lastname " & _
"FROM Customers " & _
"WHERE customers.lastname = " & strLastName

rs.Open sSQL, CurrentProject.Connection, adOpenStatic

If rs.RecordCount > 0 Then
custname = rs!firstname & "." & rs!Lastname
End If
'always close the recordset after you are done with it
rs.Close
Set rs = Nothing

Exit Sub

hell:
MsgBox Err.Description

This is how i would do it with two recordsets:

'set up two variables for each recordset
Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim sSQL As String

On Error GoTo hell

Set rs = New ADODB.Recordset
editper = Forms![requests]![Subrequests].Form![StaffID]

sSQL = "SELECT firstname, lastname " & _
"FROM Customers " & _
"WHERE customers.CustID = " & editper
'open the first recordset
rs.Open sSQL, CurrentProject.Connection, adOpenStatic

If rs.RecordCount > 0 Then
custname = rs!firstname & "." & rs!Lastname

'now if you have data then open the second recordset
Set rs2 = New ADODB.Recordset
sSQL = "SELECT firstname, lastname " & _
"FROM Customers " & _
"WHERE customers.lastname = " & rs!Lastname

rs2.Open sSQL, CurrentProject.Connection, adOpenStatic

If rs2.RecordCount > 0 Then
custname = rs2!firstname & "." & rs2!Lastname
End If
'make sure you close both recordsets when you are done.
'HERE
rs2.Close
Set rs2 = Nothing

End If

'and HERE
rs.Close
Set rs = Nothing

Exit Sub

hell:
MsgBox Err.Description

I hope this helps you with your bug.
Good Luck!

TallyJasin said:
I am having a problem executing this twice in an event procedure. I first
check one table for certain values and then I check another table for certain
values. I am still trying to get the code corrected in my procedure but it
either tells me that I have a Duplicate declaration in current scope or no
value given for one or more required parameters.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top