VBA Record Set - Read Only

[Scyclone]

Hello Everyone,

I am having no issue retrieving the record set, however, I am unable to get the record set to display and lock as a read-only table. I am essentially creating a lookup function where someone can enter in a name, find the customer information associated with it and use it for referencing, I do not want them to be able to change any of this information though.

======

Function SearchName()
Dim ivalue As String


Dim strSQL As String
Dim dbs As Database
Dim qdf As QueryDef
Set dbs = CurrentDb()

ivalue = InputBox("Please enter the name or portion of the name that you wish to search for.", "Customer Name Lookup")
strSQL = "SELECT * FROM CustList WHERE CustName LIKE '*" & ivalue & "*'"
rs = dbs.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
With dbs
Set qdf = .CreateQueryDef("Search Results", strSQL)
DoCmd.OpenQuery "Search Results", acViewNormal, acReadOnly
.QueryDefs.Delete "Search Results"
End With
dbs.Close
qdf.Close

End Function
=======

Thoughts?

[boblarson]

Go into the saved query and change the recordset type from Dynaset to Snapshot. You do that by going into the QBE grid of the query and click on the gray part where the table is located in the diagram and in the query properties you should see Recordset Type.

[Scyclone]

Hmm not sure I follow you.

When you say saved query, are you referring to the table "CustList" or the query thats created and stored as the variable strSQL?

I am not sure where to find the portion that you are refering to.

=======
Edit

Ahh... I think I understand what you mean now, though this would only apply if the query already exists in Access and isn't created through VBA, correct?

[boblarson]

Sorry, I didn't see that you were using a temporary query.

I would just create a base query as a saved query. So instead of using CreateQueryDef you would just open the saved query and modify it and then open it.

[Scyclone]

Hmm, so to call it through VBA, would I just use the

Docmd.OpenQuery "QueryName"

Also, when making a base query, how do I set an input box as a parameter *refer to the code above*?

[boblarson]

You would still use your querydef object code to change the SQL including the parameter values just like you have. Just delete this line:

.QueryDefs.Delete "Search Results"

and revise this line:
Set qdf = .CreateQueryDef("Search Results", strSQL)

to this

Set qdf = .QueryDefs("Search Results")


and you should be good to go.

[Scyclone]

Okee, so if I understand correctly.

SQL Query is just Select * From CustList

Then I just use this function?

===

Function SearchName()
Dim ivalue As String
Dim strSQL As String
Dim dbs As Database
Dim qdf As QueryDef
Set dbs = CurrentDb()

ivalue = InputBox("Please enter the name or portion of the name that you wish to search for.", "Customer Name Lookup")
strSQL = "SELECT * FROM CustList WHERE CustName LIKE '*" & ivalue & "*'"
rs = dbs.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
With dbs
Set qdf = .QueryDefs("Search Results")
DoCmd.OpenQuery "Search Results", acViewNormal, acReadOnly
End With
dbs.Close
qdf.Close

End Function
====

[boblarson]

Well, with a slight modification from what you have.

Function SearchName()
Dim ivalue As String
Dim strSQL As String
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb()

ivalue = InputBox("Please enter the name or portion of the name that you wish to search for.", "Customer Name Lookup")
strSQL = "SELECT * FROM CustList WHERE CustName LIKE '*" & ivalue & "*'"

With dbs
Set qdf = .QueryDefs("Search Results")
 
qdf.SQL = strSQL
qdf.Close

DoCmd.OpenQuery "Search Results", acViewNormal, acReadOnly

End With

Set dbs = Nothing

End Function

And I have no idea why yhou were using the recordset but I deleted it completely because from what you have there, it isn't needed at all. You also don't need to close dbs because it won't close it anyway as it is the current database and it needs to remain open, so it is moot.

[Scyclone]

That did the trick, awesome. Thank you