DLookup error

[shylock]

Iam trying to insert a value into a textbox from a select query with DLookup. My code is shown below.

        strSQLQry = "SELECT LogicalServerType.ServerType " _
                & "FROM LogicalServer INNER JOIN LogicalServerType ON LogicalServer.LogicalServerTypeID = LogicalServerType.LogicalServerTypeID " _
                & "WHERE LogicalServer.Name = Me.txtServerNm;"
    
    Debug.Print strSQLQry
    
    strType = DLookup("[ServerType]", "strSQLQry")
    Me.txtServType = strType

When I run the program I receive the following error: "The Microsoft database engine cannot find the input table or query 'strSQLQry'. Make sure it exists and that its name is spelled correctly".



Can anyone help me with this?

[June7]

First, concatenate dynamic parameter and text field parameters need apostrophe delimiter.

& "WHERE LogicalServer.Name = '" & Me.txtServerNm & "';"

Next, won't work because domain aggregate function must reference a physical table or query object, not a string built in VBA. What you have is just a string of characters, not a table, query, or recordset and certainly no data to search.

Instead of a textbox for ServerNm, maybe use a multi-column combobox with a RowSource that is an SQL statement joining the two tables. Then the related info will be available by referencing combobox column.

Why save ServerType into data record? Don't you already have the TypeID in record? This is duplicating data to multiple tables. The type descriptor can always be retrieved in query joining tables on LogicalServerTypeID as you already show.

[Micron]

@shylock: your approach, while more complicated was close. You would actually have to create a query definition (query def) from that sql, then reference the qd.
You would have to save the definition as a named query, or create a temporary query def, which would only last for as long as the procedure is "alive".

[davegri]

As Micron suggests, make a named query. Here's a function to do that. It can go behind the form or into a module:

Public Function fcnMakeNamedQry(qName As String, arg As String)
    Dim Qdf As DAO.QueryDef
    Dim db As DAO.Database
    Dim qExists As Boolean
    Set db = CurrentDb
    qExists = False
    For Each Qdf In db.QueryDefs
        If Qdf.Name = qName Then
            qExists = True
            Exit For
        End If
    Next
    Select Case qExists
        Case True
            Set Qdf = CurrentDb.QueryDefs(qName)
            Qdf.SQL = arg
        Case False
            Set Qdf = db.CreateQueryDef(qName, arg)
    End Select
    Set db = Nothing
    Set Qdf = Nothing
    Application.RefreshDatabaseWindow
End Function

Then your code (modified), calling the function:

strSQLQry = "SELECT LogicalServerType.ServerType " _
      & "FROM LogicalServer INNER JOIN LogicalServerType ON LogicalServer.LogicalServerTypeID = LogicalServerType.LogicalServerTypeID " _
     & "WHERE LogicalServer.Name ='" & Me.txtServerNm & "'"
    
    Debug.Print strSQLQry
    call fcnMakeNamedQry("qTempQuery",strSQLQry)
    Me.txtServType = DLookup("ServerType","qTempQuery")

This will make a named query, qTempQuery, assuming your strSQLQry is valid.

[Micron]

As Micron suggests,

It is, but it also isn't. First, I usually expect that in these cases the query is a one off thing or has enough variation that the developer uses code rather than a stored query. Thus I'd probably use a temporary query as the preferred method over the 2 that I suggested, which means after the procedure is complete, it no longer exists. This helps minimize db bloat over continually creating/deleting queries. It also means that no code is needed to test for the existence of the one that's about to be created in machine memory. All that code might boil down to

Public Function MakeQry()
Dim Qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strSql As String

strSql = "sql statement goes here"
Set Qdf = CurrentDb.CreateQueryDef("",strSql)
Set rs = Qdf.OpenRecordset(dbOpenSnapshot)
If Not (rs.BOF And rs.EOF) Then 
    rs.MoveFirst
    Me.txtServType = rs.Fields("ServerType")
End If
    
Set Qdf = Nothing
Set rs = Nothing

End Function

Note that I only concerned myself with the first record in the set, since the posted DLookup has no criteria. Such a lookup returns the value from the first record it finds, which is not necessarily the first record showing in a table or query that the lookup is based on. Thus I presume the sql returns only one record, which might make the rs.MoveFirst redundant. Nor did I worry about error handling.

[shylock]

Thanks to all of you for your replies.

I do have a query that I could use, unfortunaterly it returns 637 records. I want the query to return just 1 record, the ServerType corresponding to the Server Name that appears in the forms txtServerNm text box i.e.: "WHERE LogicalServer.Name = '"Me.txtServerNm&"'";". I just don't know how to pass the server name to the query. Any suggestions?

PS: June7:
The TypeID is a guid and the ServerType is something like Domain Controller, Web Server, Print Server, etc.

[Micron]

1) If you put the server name in the query sql instead of the form's control reference do you get only one record that you seem to want? That would seem to be the starting point.
2) what is the data type of the server name? GUID? Text?
3) this textbox isn't on a subform, is it?

I just don't know how to pass the server name to the query.

Your thread seems to be morphing. First you wanted to get the value into a text box. Now you want to know how to pass the server name from it? Does that mean there has been some progress? A lot of info has been provided by us, but there's been no feedback on what was proffered.

[shylock]

Micron:

1) When I put the server name in the criteria for the query sql it returns only one record corresponding to the server name.
2) Data type of the server name is text. The Server ID is a guid, which has it's own issues, but I am not using it in this context.
3) The textbox is NOT on a subform.

I have gotten around the problem by changing the textbox to a list box and setting the row source to my sql text. However, this seems like kind of a waste of resources to me. Why waste a list box on just one row? I'd still like to know if a text box is feasible in this circumstance.

Still I want to thank you and everyone else for your patience and help.

[Micron]

I'd still like to know if a text box is feasible in this circumstance.

I don't see why not, but possibilities that could affect the approach had to be dealt with first. F'rinstance, if an involved control was on a subform, that requires a special syntax to drill down into subforms. Your example from post 6
"WHERE LogicalServer.Name = '"Me.txtServerNm&"'";"
is not what you were shown in post 2
"WHERE LogicalServer.Name = '" & Me.txtServerNm & "';"

You are passing the literal string Me.txtServerNm&
Maybe that's all there is to it. However, hard to know as still don't know what, if anything, you used from what you were shown. The difficulty I'm having here is that (again)

there's been no feedback on what was proffered.

Thanks are appreciated, but thanks alone doesn't tell us where you are approach-wise. Impossible to know if the Me reference was the only problem, or you went the code way from post 4 or 5. We can't see your db or read your mind as no doubt you're aware.

Hopefully if you correct the reference it will work. If not, time to update us with your code & sql.

[shylock]

June7 suggested using a combo box in post #2, but if I use a combo box the box appears empty until I scroll down. A list box seems to work better.

My post #6 was a typo I did try "WHERE LogicalServer.Name = '" & Me.txtServerNm & "';" but it did not work. Sorry, I can't remember what error I got.

I did not try the code in either post #4 or #5, but both look to be very useful. I have stored both in a file for use later on.

[Micron]

OK. But I'm done asking to see the code you tried, or code that shows what you've got that works with a list box. Still can't see where you are in the project.
Good luck with your project from here.

[shylock]

Micron:

Sorry, I didn't understand that you wanted to see my final code that worked. Here it is:

 Me.lstSType.RowSource = "SELECT LogicalServerType.ServerType " _
                         & "FROM LogicalServer INNER JOIN LogicalServerType ON LogicalServer.LogicalServerTypeID = LogicalServerType.LogicalServerTypeID " _
                         & "WHERE LogicalServer.Name = '" & Me.txtServerNm & "';"

I hope this is what you wanted. I am just about finished with this portion of the project. Just need to add a few bells and whistles.

Thanks , again.

[Micron]

What might have seemed persistence on my part was because you wrote

I'd still like to know if a text box is feasible in this circumstance.

That sql can't be used in a query because the query side of things doesn't recognize "Me" at all. It could work in a query if you used the complete reference as in
Forms!frmNameOfYourForm.txtServerNm in the query design grid. Then you can set the textbox control source to the name of the query. I don't see why you couldn't make
Me.txtServerNm.ControlSource = "Your sql statement here"

If you want to pursue this further, post a copy of your db as long as it contains the necessary tables, form(s) etc.

[shylock]

Micron:

Thank you for the repl, this explains things for me. What you suggest makes sense. I will use the full reference in future. You are also correct when you suggest setting Me.txtServerNm.ControlSource = SQL statement. That is what I ultimately did. I am slowly gaining more knowledge of Access, thanks to all of you on this forum.