DoCmd.RunSql Error 2342

[shylock]

I am having trouble Selecting a specific ID from a table. I have tied both DoCmd.RunSQL and CurrentDB.Execute but get Run-time error 2342 "A RunSql action requires an argument consisting of an SQL statement." The VBA code is as follows:

Private Sub cmdOK_Click()

Dim LSID, SVID As Variant, sqlQry, SName As String


Dim mydb As Database, myrs As DAO.Recordset

''' Get the LogicalServerID for the Server named on the "Details" tab
Set mydb = CurrentDb
SName = Forms!frmLogicalServers!txtServerNm
sqlQry = "SELECT LogicalServerID FROM LogicalServer WHERE LogicalServer.Name = " & SName & ";" ''' This is the query I am trying to run

' CurrentDb.Execute sqlQry ''' I get the error on this command
DoCmd.RunSQL sqlQry ''' I get the error on this command

Set myrs = mydb.OpenRecordset("LogicalServer")
LSID = myrs!LogicalServerID
LSID = LogicalServer.LogicalServerID

SVID = Me.lstAssocServices.Column(0)

sqlQry = "INSERT INTO ServiceLogicalServer ([ServiceID], [LogicalServerID]" _
& " VALUES (SVID, LSID);"

DoCmd.RunSQL sqlQry

sqlQry = "INSERT INTO ServertoService ([Name], [Description],[ServiceID], [LogicalServerID]" _
& " VALUES ([forms.frmLogicalServers.txtServerNm], [Me.lstAssocServices.column(1)], SVID, LSID);"

DoCmd.RunSQL sqlQry

End Sub


I think this is probably due to some mistake in my coding. Does anyone have a suggestion?

[davegri]

try
WHERE LogicalServer.Name = '" & SName & "'"

[shylock]

Sorry, I get the same error as before. Coould it be something to do with setting the CurrentDB or RecordSet?

[orange]

You can not Run or Execute a Select query.
You Run or Execute an Action query ---INSERT/UPDATE/DELETE

[shylock]

How do I extract the LogicalServerID from the LogicalServer table? Is there some other command I can use? Perhaps DLookUp?

[orange]

I'd try to comment this line and see what happens. How many servers o you expect from your select query?
'DoCmd.RunSQL sqlQry ''' I get the error on this command

[Micron]

If there can only be 1 record as a result, DLookup should be fine.
Don't see why you're trying to open a recordset on what must be a table: Set myrs = mydb.OpenRecordset("LogicalServer") and then successively assigning a value from an arbitrary record twice in a row:
LSID = myrs!LogicalServerID
LSID = LogicalServer.LogicalServerID - especially when one of the declarations seems to involve the very same table.
Then there is the question of what you'd want from your select sql anyway, because while there is one convoluted way to get it to work, you'd open a datasheet to the user, which I doubt you want.


BTW, which of these are variants?
Dim LSID, SVID As Variant, sqlQry, SName As String
Answer - the first 3
Multi line declarations require that all variables are typed (not typed as in keyboarding) such as
Dim LSID As Variant, SVID As Variant, sqlQry As String, SName As String
however I favour splitting multi lines by type, such as
Dim LSID As Variant, SVID As Variant
Dim sqlQry As String, SName As String

Please use code tags with indentation to make your code easier to read when there's more than a few contiguous lines.

[shylock]

Orange and Micron:

Thanks for you replies. I only expect 1 record as this is part of the build process for a new server. You are correct about the record set stuff, in my frustration I have been grasping at straws, so to speak.

I do use indentations in all my codeing, I don't know why it didn't show up when I copied it to my post?

I tried the DLookup function but it returns "????????" instead of the number expected. That number should be "{01ACAA27-E7FE-E555-72E5-B3D35B92776A}". Here is the code I am using: LSID = DLookup("[LogicalServerID]", "LogicalServer", "[Name] = '" & SName & "'"). I know that SName contains the correct server name. Also attached is the complete Sub code. I took this line directly from an example on "AccessWorld.com". Is the syntax correct?

Private Sub cmdOK_Click()
    
   Dim LSID As Variant
   Dim SVID As Variant
   Dim sqlQry As String, SName As String
   Dim mydb As Database, myrs As DAO.Recordset
   
   ''' Get the LogicalServerID for the Server named on the "Details" tab
   SName = Forms!frmLogicalServers!txtServerNm
    LSID = DLookup("[LogicalServerID]", "LogicalServer", "[Name] = '" & SName & "'")
    SVID = Me.lstAssocServices.Column(0)
   
    sqlQry = "INSERT INTO ServiceLogicalServer ([ServiceID], [LogicalServerID]" _
            & " VALUES (SVID, LSID);"
        
    DoCmd.RunSQL sqlQry
    
    sqlQry = "INSERT INTO ServertoService ([Name], [Description],[ServiceID], [LogicalServerID]" _
            & " VALUES ([forms.frmLogicalServers.txtServerNm], [Me.lstAssocServices.column(1)], SVID, LSID);"
        
    DoCmd.RunSQL sqlQry
    
End Sub

Code tags added.
Shylock: Re Code tags: Highlight your code, then click the hash (#)--this puts "code" tags around the vba. (orange)

[davegri]

Get the LogicalServerID for the Server named on the "Details" tab

If your code is behind the main form and the Details tab contains a subform, have a look at this:
Syntax for subs.doc
Also, NAME is an Access reserved word

[shylock]

Davegri:

I don't see how what your link applies, since I can see, through debugging, that SName is correct. I did change it to comply with what your link says but the result is the same. I know that Name is a reserved word but that is the name of the field in the table I have inherited and I am not permitted to change it.

[orange]

Shylock,

What is the data type of "[LogicalServerID]" in your table?

[Micron]

I don't know why it didn't show up when I copied it to my post?

not sure if you saw the note seemingly added to the end of your post but it's because you didn't add code tags. My next question was also going to be 'what is the table field data type'. In the meantime, once you get past this current issue, your sql concatenation won't be correct if the type is text, nor will it like it when you make a control reference a literal part of the string.

[shylock]

Orange:

The data type is "Number". It comes from SQL Sever 2012. I would attach the table def, but I don't see a way to attach a file here.

[davegri]

You didn't directly answer my question about the subform, but I'll assume the design uses one. With that assumption, see attached for two ways to get the server id from the subform using code on the main form:
Dlookup-davegri-v01.zip

[orange]

@davegri
Shylock is expecting this "number" {01ACAA27-E7FE-E555-72E5-B3D35B92776A}
according to post #8.
I don't use SqlServer, but it seems this is a GUID and may need some other processing???

I found this older GUID reference from Michael Kaplan.