By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,581 Members | 2,023 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,581 IT Pros & Developers. It's quick & easy.

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

jeremystocker
P: 4
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
Expand|Select|Wrap|Line Numbers
  1. Dim SearchID As String
  2. Dim QRY_ID As String
  3. Dim RST As Dao.Recordset
  4. Dim db As Database
  5. Dim product As String
  6.  
  7. Set RST = CurrentDb.OpenRecordset("SELECT [Outreach Data].[Case Log ID],
  8. [Outreach Data].[First Name], [Outreach Data].[Last Name] " & _
  9. "FROM [Outreach Data] " & _
  10. "WHERE ((([Outreach Data].[Case Log ID]) Like
  11. [Forms]![Import_FRM]![CaseLogIDTXT]));")
  12. SearchID = Me.CaseLogIDTXT
  13.  
  14. 'DoCmd.OpenQuery ("CID_QRY")
  15. QRY_ID = RST![Case Log ID]
  16.  
  17. If QRY_ID = SearchID Then
  18.   MsgBox ("Found")
  19. Else
  20.   MsgBox ("Not Found")
  21. End If
  22.  
  23. End Sub
2 Weeks Ago #1

✓ answered by 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?
Expand|Select|Wrap|Line Numbers
  1. If DCount("*", "Outreach Data", "[Outreach Data].[Case Log ID] Like '" & _
  2.           [Forms]![Import_FRM]![CaseLogIDTXT] & "'") > 0 Then
  3.   MsgBox ("Found")
  4. Else
  5.   MsgBox ("Not Found")
  6. End If

Share this Question
Share on Google+
8 Replies


NeoPa
Expert Mod 15k+
P: 31,561
Hi Jeremy.

Welcome to Bytes.com.

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

jeremystocker
P: 4
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.
2 Weeks Ago #3

Expert 100+
P: 635
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
Expand|Select|Wrap|Line Numbers
  1. "WHERE ((([Outreach Data].[Case Log ID]) Like[Forms]![Import_FRM]![CaseLogIDTXT]));""
with this. Note, text criteria need delimiting with either quotes or an apostrophies.
Expand|Select|Wrap|Line Numbers
  1. "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.

Expand|Select|Wrap|Line Numbers
  1. "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
2 Weeks Ago #4

ADezii
Expert 5K+
P: 8,668
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?
Expand|Select|Wrap|Line Numbers
  1. If DCount("*", "Outreach Data", "[Outreach Data].[Case Log ID] Like '" & _
  2.           [Forms]![Import_FRM]![CaseLogIDTXT] & "'") > 0 Then
  3.   MsgBox ("Found")
  4. Else
  5.   MsgBox ("Not Found")
  6. End If
2 Weeks Ago #5

jeremystocker
P: 4
Thank you. your solution works perfectly.
2 Weeks Ago #6

jeremystocker
P: 4
Thank you for your efforts.
2 Weeks Ago #7

ADezii
Expert 5K+
P: 8,668
You are quite welcome, good luck with your Project.
2 Weeks Ago #8

NeoPa
Expert Mod 15k+
P: 31,561
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 :
Expand|Select|Wrap|Line Numbers
  1. If IsNull(DLookup("*" _
  2.                 , "[Outreach Data]" _
  3.                 , "[Case Log ID]=[Forms]![Import_FRM]![CaseLogIDTXT]")) Then
  4.     Call MsgBox("Not Found")
  5. Else
  6.     Call MsgBox("Found")
  7. End If
2 Weeks Ago #9

Post your reply

Sign in to post your reply or Sign up for a free account.