DoCmd.RunSQL

[Guest]

Hello, may some one please tell why this will not work. I keep getting an error message stating that the runSql action needs an argument consisting of a SQL statement.

Private Sub cmd_password_Click()
On Error GoTo Err_cmd_password_Click

Dim Passw As String
Dim MasterP As Integer
Passw = "SELECT tbl_2004_Library.Reference, tbl_2004_Library.rc, tbl_2004_Library.job, " & _
"tbl_2004_Library.prime, tbl_2004_Library.account, tbl_2004_Library.desc, " & _
"tbl_2004_Library.us_amt, tbl_2004_Library.MP FROM tbl_2004_Library;"
DoCmd.RunSQL Passw

Exit_cmd_password_Click:
Exit Sub

Err_cmd_password_Click:
MsgBox Err.DESCRIPTION
Resume Exit_cmd_password_Click

End Sub

 

[Douglas J. Steele]

RunSQL can only work with Action queries (UPDATE, DELETE, INSERT INTO). You
cannot use RunSQL with a SELECT statement.

What were you hoping to do with the SQL statement?

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Hello, may some one please tell why this will not work. I keep getting an

error message stating that the runSql action needs an argument consisting
of a SQL statement.

 

[Douglas J. Steele]

You need to open a recordset, check that there's something in the recordset,
then retrieve the value from that recordset.

Using DAO, this would be something like:

Private Sub cmd_password_Click()
On Error GoTo Err_cmd_password_Click

Dim Passw As String
Dim MasterP As Integer
Dim rs As DAO.Recordset

Passw = "SELECT tbl_2004_Library.Reference, tbl_2004_Library.rc, " & _
"tbl_2004_Library.job, tbl_2004_Library.prime, " & _
"tbl_2004_Library.account, tbl_2004_Library.desc, " & _
"tbl_2004_Library.us_amt, tbl_2004_Library.MP " & _
"FROM tbl_2004_Library " & _
"WHERE tbl_2004_Library.MP=" & MasP

Set rs = CurrentDb().OpenRecordset(Passw)
If Not rs.BOF And Not rs.EOF Then
' You can get the value of the matching fields using
' rs!Reference, rs!rc, rs!job, etc.
Else
' No record matches your criteria
End If

rs.Close
Set rs = Nothing

Exit_cmd_password_Click:
Exit Sub

Err_cmd_password_Click:
MsgBox Err.DESCRIPTION
Resume Exit_cmd_password_Click

End Sub

Note that the WHERE clause that's being set assumes MP is numeric. If it's
text, you need to enclose the value in quotes:

"WHERE tbl_2004_Library.MP=" & Chr$(34) & MasP & Chr$(34)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I would like to show the results of a select query. The way I would it to

work is it to click a button a button and the user is prompted to enter a
number. The number entered will be used in the where portion of the query.

 

[Douglas J. Steele]

You must be using Access 2000 or 2002. By default, they use ADO, and don't
have a reference set to DAO. (Access 2003 at least sets a reference to DAO)

To use my code, you'll need to set a reference to DAO. To do this, open any
code module, then select Tools | References from the menu. Scroll through
the list until you find the reference to Microsoft DAO 3.6 Object Library
and select it. Make sure you put back the DAO. in the declaration for rs:
there's a Recordset object in both models, and you need to ensure that you
use the correct one.

Alternatively, you could try doing the same thing using ADO. Unfortunately,
I don't have an example handy, as I always use DAO against Jet databases
(i.e. MDBs)

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Thanks for the help, Douglas.

It did not allow me to use DAO.Recordset as a data type. So I used

recordset as a data type and kept everything else the same. I get an error
message now which says Type mismatch. What should I do now?

 

[Douglas J. Steele]

It checks to make sure that there's a value in the recordset. Think of BOF
as an imaginary record at the beginning of the recordset, and EOF as an
imaginary record at the end.

It's checking to make sure that we have a real record, not one of the
"imaginary" ones.

You do, of course, have to put code in that If construct: all I've got there
are comments telling you what's what!

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Hello again Douglas,

What does this IF statement do:
Set rs = CurrentDb().OpenRecordset(Passw)
If Not rs.BOF And Not rs.EOF Then
' You can get the value of the matching fields using
' rs!Reference, rs!rc, rs!job, etc.
Else
' No record matches your criteria
End If

How do I get the records to show, given the criteria. I no longer get the

error message but no results appear. I think it is becuase I am not
currently using the If statement, but what code I enter to see the entire
record?

Thanks for all your help I really do appreciate it!!!

 

[Guest]

Hello again Douglas,

What would the code need to be in order for me to see the entire record if there is a match and to move from record to record until the end.

Thanks again for all the help.

It checks to make sure that there's a value in the recordset. Think of BOF
as an imaginary record at the beginning of the recordset, and EOF as an
imaginary record at the end.

It's checking to make sure that we have a real record, not one of the
"imaginary" ones.

You do, of course, have to put code in that If construct: all I've got there
are comments telling you what's what!

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Hello again Douglas,

What does this IF statement do:
Set rs = CurrentDb().OpenRecordset(Passw)
If Not rs.BOF And Not rs.EOF Then
' You can get the value of the matching fields using
' rs!Reference, rs!rc, rs!job, etc.
Else
' No record matches your criteria
End If

How do I get the records to show, given the criteria. I no longer get the

error message but no results appear. I think it is becuase I am not
currently using the If statement, but what code I enter to see the entire
record?

 

[Douglas J. Steele]

A recordset has fields, just like a table. If you want to look at each of
the field values in the current row, you need to address each one, along the
lines of:

Private Sub cmd_password_Click()
On Error GoTo Err_cmd_password_Click

Dim Passw As String
Dim MasterP As Integer
Dim rs As DAO.Recordset
Dim intLoop As Integer
Dim strOutput As String

Passw = "SELECT tbl_2004_Library.Reference, tbl_2004_Library.rc, " & _
"tbl_2004_Library.job, tbl_2004_Library.prime, " & _
"tbl_2004_Library.account, tbl_2004_Library.desc, " & _
"tbl_2004_Library.us_amt, tbl_2004_Library.MP " & _
"FROM tbl_2004_Library " & _
"WHERE tbl_2004_Library.MP=" & MasP

Set rs = CurrentDb().OpenRecordset(Passw)
If Not rs.BOF And Not rs.EOF Then
intLoop = 1
Do While Not rs.EOF
strOutput = "For record " & intLoop & vbCrLf & _
"Reference = " & rs!Reference & vbCrLf & _
"rc = " & rs!rc & vbCrLf & _
"job = " & rs!job & vbCrLf
"prime = " & rs!prime & vbCrLf
"account = " & rs!account & vbCrLf
"desc = " & rs!desc & vbCrLf
"us_amt = " & rs!us_amt & vbCrLf
"MP = " & rs!MP
MsgBox strOutput
intLoop = intLoop + 1
rs.MoveNext
Loop
Else
MsgBox "No records match " & MasP
End If

rs.Close
Set rs = Nothing

Exit_cmd_password_Click:
Exit Sub

Err_cmd_password_Click:
MsgBox Err.DESCRIPTION
Resume Exit_cmd_password_Click

End Sub

However, that's really a cumbersome way of displaying results to users.

What you really should do is have a subform and assign your SQL string to be
the recordsource of the subform.


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Hello again Douglas,

What would the code need to be in order for me to see the entire record if

there is a match and to move from record to record until the end.