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

How to return multiple values from an aggregated dataset to a textbox?

P: 74
I am trying to build a mechanism that will return value from multiple records in a query to a textbox. I started with placing DLookup function in the textbox’s data control source. Later, I realized DLookup will only return the first matched value. Then I learned that I should use Recordset to accomplish this.

Suppose I have a recipe as below.

Expand|Select|Wrap|Line Numbers
  1. Recipe ID:  1001
  2.  
  3. Ingredient    Percentage    Allergen
  4.  
  5. Milk          10%           Milk
  6. Wheat Flour   50%           Wheat
  7. Water         35%        
  8. Sugar          4%    
  9. Salt           1%
  10.  
  11.  
The value that should be returned to the textbox is allergens from each ingredient in the recipe. In this case, it should return “Milk Wheat” to the allergen text box.

The ingredient name, allergen and percentage exist in an Access query named Q_Recipe_Allergen where several tables are joined. The rows of above recipe are from a SubForm that resides in a MainForm containing recipe header information, such as RecipeID.

I tried the following codes, but it didn’t work. I know something is missing but I don’t know how to fix it.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Txt_Recipe_Allergen_AfterUpdate()
  2.  
  3. Set db = CurrentDb()
  4. Set rs = db.OpenRecordset("Select '[Allergen]' From [Q_Recipe_Allergen] WHERE '[recipeID]=Me![Txt_RecipeID]'")
  5.  
  6. Me.Txt_Recipe_Allergen = rs
  7.  
  8. End Sub
  9.  
Thanks in advance for helps.
Nov 16 '11 #1
Share this Question
Share on Google+
6 Replies


NeoPa
Expert Mod 15k+
P: 31,709
You may find what you're after by going through Combining Rows-Opposite of Union.
Nov 17 '11 #2

P: 74
NeoPa,

I think I need more helps and directions.

I assume that I supposed to replace ‘Item’ and ‘GroupBy’ in the thread you referred to with ‘Allergen’ and ‘recipeID’ in my case.

Then, I place the Concat function in a public module and the query to be placed in the textbox’s event?

Do I need to declare or set function of any variable to return the text from the query to the textbox? Sorry, I am still unfamiliar with how VBA works to catch or display data.

Thanks,
Joe
Nov 17 '11 #3

NeoPa
Expert Mod 15k+
P: 31,709
I'm not sure I have all the details required but this example is my best guess at this point. Try it out and let me know how it goes :

Expand|Select|Wrap|Line Numbers
  1. Private Sub Txt_Recipe_Allergen_AfterUpdate()
  2.     Dim strSQL As String
  3.     Dim db As DAO.Database
  4.     Dim rs As DAO.Recordset
  5.  
  6.     strSQL = "SELECT [Recipe_ID]" & _
  7.                   ", Max(Concat([Recipe_ID], Nz([Allergen],''))) AS [Allergens] " & _
  8.              "FROM   [Q_Recipe_Allergen] " & _
  9.              "WHERE  ([RecipeID] = Me![Txt_RecipeID] & ")"
  10.     Set db = CurrentDb()
  11.     Set rs = db.OpenRecordset(strSQL)
  12.     Me.Txt_Recipe_Allergen = rs!Allergens
  13. End Sub
PS. Whatever should trigger this code I very much doubt it should be the Txt_Recipe_Allergen_AfterUpdate() event procedure. I don't know where this was coming from but you are trying to set the value - not respond to it having been set.
Nov 17 '11 #4

P: 74
Thanks NeoPa,

I am still working on this. I got “Object Required” when I test the codes. Then I changed the code

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
To

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As QueryDef
  2.  
Then the error message “Type Mismatch” appeared and Access highlighted the “&” at the end of line

Expand|Select|Wrap|Line Numbers
  1. "FROM [Q_Recipe_Allergen]" & _
  2.  
I checked the spelling of field and query names. I haven’t found misspelling, so far. Other than misspelling, does this error message “Type Mismatch” mean something else?

On the other hand, I meant to use Default Value of the text box to return query value, but it only takes Expression Builder, not VBA code. I don’t know which event procedure would be the best for setting the returned value from VBA.
Nov 21 '11 #5

NeoPa
Expert Mod 15k+
P: 31,709
You need to determine as a matter of priority when you want this code to run. If you describe that clearly to me I will suggest an event to use for the procedure.

As a separate issue we need to find what, if anything, is wrong with the code. Check out When Posting (VBA or SQL) Code for some necessary tips on posting code (Your VBA code particularly). Point #A2 explains how you need to report errors if you have known ones you cannot resolve yourself. You needn't post the code again if it's exactly as shown in post #4 (and it should be - I have no interest in this stage in going off on any unnecessary tangents).
Nov 21 '11 #6

NeoPa
Expert Mod 15k+
P: 31,709
Joe Y:
On the other hand, I meant to use Default Value of the text box to return query value, but it only takes Expression Builder, not VBA code. I don’t know which event procedure would be the best for setting the returned value from VBA.
I'm afraid I have no idea what you're talking about here. It looks like English but I don't know what you're saying.
Nov 21 '11 #7

Post your reply

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