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

[Joe Y]

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.

Recipe ID:  1001
 
Ingredient    Percentage    Allergen
 
Milk          10%           Milk
Wheat Flour   50%           Wheat
Water         35%        
Sugar          4%    
Salt           1%
 
 

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.

Private Sub Txt_Recipe_Allergen_AfterUpdate()
 
Set db = CurrentDb()
Set rs = db.OpenRecordset("Select '[Allergen]' From [Q_Recipe_Allergen] WHERE '[recipeID]=Me![Txt_RecipeID]'")
 
Me.Txt_Recipe_Allergen = rs
 
End Sub
 

Thanks in advance for helps.

[NeoPa]

You may find what you're after by going through Combining Rows-Opposite of Union.

[Joe Y]

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

[NeoPa]

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 :

Private Sub Txt_Recipe_Allergen_AfterUpdate()
    Dim strSQL As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
 
    strSQL = "SELECT [Recipe_ID]" & _
                  ", Max(Concat([Recipe_ID], Nz([Allergen],''))) AS [Allergens] " & _
             "FROM   [Q_Recipe_Allergen] " & _
             "WHERE  ([RecipeID] = Me![Txt_RecipeID] & ")"
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSQL)
    Me.Txt_Recipe_Allergen = rs!Allergens
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.

[Joe Y]

Thanks NeoPa,

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

Dim strSQL As String
 

To

Dim strSQL As QueryDef
 

Then the error message “Type Mismatch” appeared and Access highlighted the “&” at the end of line

"FROM [Q_Recipe_Allergen]" & _
 

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.

[NeoPa]

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).

[NeoPa]

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.