Updating the record source of a subform

[Aremo]

I currently have a query that a subform uses to display some results. In this case, there are 3 columns displaying the name of a sandwich, the ingredients, and the number of servings of each ingredient. It looks like this:

SandwichName IngredientName Servings
Accordian Lettuce 1
Accordian Ham 1
Accordian Cheddar 1
Accordian Mayonnaise 1

That is the current output to the subform. The SQL statement I use to generate this list is here:

SELECT [TBL_Sandwich].[SandwichName], [TBL_Ingredient].[IngredientName], [TBL_SandwichContent].[SandwichTotalServings]
FROM (TBL_Sandwich INNER JOIN TBL_SandwichContent ON TBL_Sandwich.SandwichID=TBL_SandwichContent.SandwichID) INNER JOIN TBL_Ingredient ON TBL_SandwichContent.IngredientID=TBL_Ingredient.IngredientID
WHERE TBL_Sandwich.SandwichID=6;

I have bolded the last item the 'SandwichID'. It is this number I need to change dynamically based on a button being pressed on the main form. Essentially they enter a new sandwich, put the ingredients in, save it io the database, and I save the servings for each item as 1. Now I'd like the new sandwich to be displayed in the subform using the new ID of the sandwich which was just created.

If I try to put this long SQL statement in the RecordSource property I am told it's too long for the property to handle so I can't set this property dynamically.

I'm new here so I can't post pictures so I am sorry if I didn't explain that well enough. The quick version of this is how to get the subform to requery with the new ID number of the item I just generated. I can get the ID number via DLookup, that's easy, but setting the subform to use this is my problem.

Thanks!

 

[Aremo]

I should clarify that I want to use the subform to allow the user to easily change the number of servings. The subform would be populated with the information and the user simply changes the serving number (allowing for more of any ingredient)

 

[JHB]

Is the subform insert in the main form?
If yes - then set the recordsource for the subform to a table or a query, and then use the "Link Master Fields" and "Link Child Fields" property.
You can post picture/database if you zip it!

 

[Aremo]

The subform is in the main form. The subform is named 'SandwichSuborm'
It is in datasheet mode and has NO recordsource or Query assigned to it at startup. I cannot use a saved query as I have no idea what the sandwich Id number I need to look up will be and I would need to modify the query dynamically. All the items are the form are unbound and I use SQL to populate list boxes.


Here is what I am doing now that is unsuccessful.

Dim myNewSandwich As String
Dim stryQryDef As String

strQryDef = "SELECT [TBL_Sandwich].[SandwichName], [TBL_Ingredient].[IngredientName], [TBL_SandwichContent].[SandwichTotalServings] FROM (TBL_Sandwich INNER JOIN TBL_SandwichContent ON TBL_Sandwich.SandwichID=TBL_SandwichContent.SandwichID) INNER JOIN TBL_Ingredient ON TBL_SandwichContent.IngredientID=TBL_Ingredient.IngredientID WHERE TBL_Sandwich.SandwichID="

/* other code and things happen here */

myNewSandwich = DLookup("SandwichID", "TBL_Sandwich", "TBL_Sandwich.SandwichName = '" & Me.SandwichName.Value & "'")

strQryDef = strQryDef & myNewSandwich & ";"

/* this is the line of code that fails... */

Me.SandwichSubform.Form.RecordSource = strQryDef


ERROR 2467 run-time ERROR: the expression you entered refers to an object that is closed or doesn't exist

 

[Aremo]

Gad... okay I just figured this out. I need to set a recordsource for the subform at startup if I want to change the recordsource later. I created a saved query based on the SQL statement I wrote with the first sandwich ID and used that as the recordsource. I then was able to change it without issue. I will set the subform as invisible at startup and turn it visible when the new information is sent to the form

 

[spikepl]

Gad... okay I just figured this out.

Not really. What you've accomplished is to hammer a square peg into a round hole. Your way is not how subforms are used normally.

Do yourself a favour and do one of the web tutorial on forms and subforms. Or even easier - make a form and a subform using the wizard.

 

[Aremo]

I think I will do myself a bigger favor and ignore your asinine comment.

 

[jdraw]

Here is a recent youtube video that goes through the creation of form and subform, and describes the subform and the related control.
It deals with search, but the form/subform design are well explained.

Good luck.

 

[spikepl]

Your personal opinion about my comment is only your personal opinion. It does not detract from the comment nor does it affect the comment's validity.

Three people have now advised you to do something other than what you've done.