Assigning the output of a query to a variable for use in VBA

[jeremystocker]

In the code below I am attempting to use the result of SQL or query to check if the value exists in a table.

Me.CaseLogIDTXT is the input field on the form
[Case Log ID] is the field being checked

thanks in advance for any help

Dim SearchID As String
Dim QRY_ID As String
Dim RST As Dao.Recordset
Dim db As Database
Dim product As String
 
Set RST = CurrentDb.OpenRecordset("SELECT [Outreach Data].[Case Log ID],
[Outreach Data].[First Name], [Outreach Data].[Last Name] " & _
"FROM [Outreach Data] " & _
"WHERE ((([Outreach Data].[Case Log ID]) Like
[Forms]![Import_FRM]![CaseLogIDTXT]));")
SearchID = Me.CaseLogIDTXT
 
'DoCmd.OpenQuery ("CID_QRY")
QRY_ID = RST![Case Log ID]
 
If QRY_ID = SearchID Then
  MsgBox ("Found")
Else
  MsgBox ("Not Found")
End If
 
End Sub

[NeoPa]

Hi Jeremy.

Welcome to Bytes.com.

Do you have a question in there anywhere. It's hard to know how to help without one ;-)

[jeremystocker]

When I attempt to run this procedure it simply does not work. I get an error message that states "Too few Parameters" for the SQL in lines 7 -11. However, if I create an actual query with the same SQL the query will run albeit without passing the result into the procedure. I am just attempting to get this bit of code to work correctly with my database.

Thank you.

[MikeTheBike]

Hi Jeremy

I assume that [Case Log ID] is a text field.

as [Forms]![Import_FRM]![CaseLogIDTXT] will be trated as a string literal in VBA and not recongnised as the for control value, replace this

"WHERE ((([Outreach Data].[Case Log ID]) Like[Forms]![Import_FRM]![CaseLogIDTXT]));""

with this. Note, text criteria need delimiting with either quotes or an apostrophies.

"WHERE ((([Outreach Data].[Case Log ID]) Like ‘“ & me.CaseLogIDTXT & “’));"

Also if [Case Log ID] is the primary key, and the complete primary key is entered in CaseLogIDTXT, then this would be better.

"WHERE ((([Outreach Data].[Case Log ID]) = ‘“ & me.CaseLogIDTXT & “’));";"

Just as an aside, it's not a good idea to have spaces in field names.

HTH

[ADezii]

The Code can be made to work, that is not a problem. Unless I am missing something, wouldn't the following Code accomplish the same goal with only a couple Lines of Code?

If DCount("*", "Outreach Data", "[Outreach Data].[Case Log ID] Like '" & _
          [Forms]![Import_FRM]![CaseLogIDTXT] & "'") > 0 Then
  MsgBox ("Found")
Else
  MsgBox ("Not Found")
End If

[jeremystocker]

Thank you. your solution works perfectly.

[jeremystocker]

Thank you for your efforts.

[ADezii]

You are quite welcome, good luck with your Project.

[NeoPa]

Hi Jeremy.

It looks like there's very little left for me to say. I get tied up with work for a few hours and people come in and just answer all the questions. What can you do? Perhaps there are a couple of comments I can make just to pad out already adequate answers.

1. I would normally use DLookup() here rather than DCount(). They both determine the answer for you but the former can stop and return the answer as soon as any matching record is dicovered whereas the latter must process through the whole table before finishing. In most cases this won't be noticeable but can be sometimes.
2. "Like" and "=" only behave differently when wildcard characters are used. This can be useful in some situations. Here it seems clear that the "=" would be more appropriate.
3. Your original use of [Forms]![Import_FRM]![CaseLogIDTXT] is actually perfectly appropriate. It's perfectly valid to convert it to a string literal in the code - but it isn't necessary. The Expression Service, which interprets and executes the SQL code, is perfectly capable of handling both approaches.
4. Generally speaking we encourage our members and experts to avoid the use of word processors when working with code. However, be prepared to convert quote characters of the wrong form when you see them. It does mean that it's dangerous to use copy and paste - which is why we discourage that in the first place, but even experienced experts like MikeTheBike here, slip up sometimes. Just be prepared. Showing the code using the [CODE] tags at least makes such things easily visible so you can spot them.

Just for interest, I'll post the solution that incorporates these comments :

If IsNull(DLookup("*" _
                , "[Outreach Data]" _
                , "[Case Log ID]=[Forms]![Import_FRM]![CaseLogIDTXT]")) Then
    Call MsgBox("Not Found")
Else
    Call MsgBox("Found")
End If