Update Query Help

[WEGL08]

I greatly appreciate any help you can provide - I haven't been able to find anything useful for my issue. Fair warning: I'm not an Access jedi, so I apologize upfront for my ignorance.

My DB is for holding recipes and providing "batch planning" to the store owner. If you look at the picture 1, this is the form that the user utilizes to select all the cupcake recipes she wants to make for that week (ignore the numeric boxes). Right now she can select up to 10 recipes (or less), hit "Run Query" and utilizing a "Create Table Query" it'll create the following table, listing out the recipe ingredients, quantities, etc. (see picture 2).



The boxes next to the combo box selectors are meant to be multipliers. For example, if she's going to make 3 batches of the red velvet for the week, I was wanting to have an Update Table Query run after the Create Table Query that would multiply all the red velvet cupcake ingredient quantities by 3. The part that's throwing me off is how each cupcake will have multiple records due to having multiple ingredients, so how do I have the multiplier effect all the red velvet records without messing up the other records, etc.


To help clarify some language:
(1) the combo boxes on the form in picture 1 are called cupcakeselect1.....cupcakeselect10
(2) the numeric text boxes on the form in picture 1 are called multiply1...multiply10
(3) the create table query I utilize to make the table in picture 2 is called CupcakeIngredientQuery
(4) The table created from the query mentioned above is called PivotTableSource

The general logic of how I thought this might work is:

IF cupcakeselect1 IS NOT NULL and cupcakeselect1 = PivotTableSource.Cupcake Name, then multiply PivotTableSource.Qty by multiply1 ......repeat for all 10 pairings of cupcakeselect#/multiply#

Sorry for the long post! Thank you in advance for your time, thoughts, and help!

[andy49]

Tbl structure is needed if we're gonna help here I think.

Or even upload a fake database so we can see your setup


Sent from my iPhone using Tapatalk

[WEGL08]

Makes sense. This isn't a super secret, online database...it's just a local project for a friend of mine so I can upload the whole database. I think that might be too large of a file though unless there's another way to share it. For now, table structure is attached. That "pivottablesource" table I spoke about earlier is honestly just a holding table for what that query pulls out so it's not in any type of relationship with any other table. Everything else is depicted in the picture. Nothing fancy.

I appreciate you taking a look.

[andy49]

Ty. I'm surprised that a zipped database wouldn't fit.



And what query are you using currently to get results?


Sent from my iPhone using Tapatalk

[WEGL08]

Just to make this whole thing easier...here's a link to access what you need. It has my DB structure and etc so you can see everything yourself. Let me know if this doesn't work:

https://www.dropbox.com/sh/5ypyazwyd...3qY7UCkSa?dl=0

[davegri]

Here's code for form CupcakeSelection.
Change the Runquery_Click event to the below event procedure (Instead of the embedded macro)

Option Compare Database
Option Explicit


Private Sub clearform_Click()
    Me.cupcakeselect1 = Null
    Me.cupcakeselect2 = Null
    Me.cupcakeselect3 = Null
    Me.cupcakeselect4 = Null
    Me.cupcakeselect5 = Null
    Me.cupcakeselect6 = Null
    Me.cupcakeselect7 = Null
    Me.cupcakeselect8 = Null
    Me.cupcakeselect9 = Null
    Me.cupcakeselect10 = Null
End Sub


Private Sub runquery_Click()
    Dim sSQL As String
    sSQL = "delete * FROM pivottablesource"
    CurrentDb.Execute sSQL, dbFailOnError
    
    If Not IsNull(cupcakeselect1) Then
        Call subUpdate(1)
    End If
    If Not IsNull(cupcakeselect2) Then
        Call subUpdate(2)
    End If
    If Not IsNull(cupcakeselect3) Then
        Call subUpdate(3)
    End If
    If Not IsNull(cupcakeselect4) Then
        Call subUpdate(4)
    End If
    If Not IsNull(cupcakeselect5) Then
        Call subUpdate(5)
    End If
    If Not IsNull(cupcakeselect6) Then
        Call subUpdate(6)
    End If
    If Not IsNull(cupcakeselect7) Then
        Call subUpdate(7)
    End If
    If Not IsNull(cupcakeselect8) Then
        Call subUpdate(8)
    End If
    If Not IsNull(cupcakeselect9) Then
        Call subUpdate(9)
    End If
    If Not IsNull(cupcakeselect10) Then
        Call subUpdate(10)
    End If
End Sub


Sub subUpdate(arg As Long)
    Dim sSQL As String
    Select Case arg
        Case 1
            sSQL = "INSERT INTO PivotTableSource  ([Cupcake Name], [# Cupcake Made], [Ingredient Name], Qty, Unit, Component) " _
                & "SELECT Cupcake.[cupcake name], Cupcake.[# Cupcake Made], Ingredient.[Ingredient Name], ([Cupcake_Ingredient].[Qty]* " & multiply1 & ") as Qty, " _
                & "Measurements.Unit, Component.Component " _
                & "FROM Measurements INNER JOIN (Ingredient INNER JOIN (Cupcake INNER JOIN (Component INNER JOIN " _
                & "Cupcake_Ingredient ON Component.[Component ID] = Cupcake_Ingredient.[Component ID]) ON Cupcake.[Cupcake ID] = Cupcake_Ingredient.[Cupcake ID]) " _
                & "ON Ingredient.[Ingredient ID] = Cupcake_Ingredient.[Ingredient ID]) ON Measurements.[Unit ID] = Cupcake_Ingredient.[Units ID] " _
                & "WHERE Cupcake.[cupcake name]= '" & [cupcakeselect1] & "';"
            'Debug.Print sSQL
            CurrentDb.Execute sSQL, dbFailOnError
        Case 2
            sSQL = "INSERT INTO PivotTableSource  ([Cupcake Name], [# Cupcake Made], [Ingredient Name], Qty, Unit, Component) " _
                & "SELECT Cupcake.[cupcake name], Cupcake.[# Cupcake Made], Ingredient.[Ingredient Name], ([Cupcake_Ingredient].[Qty]* " & multiply2 & ") as Qty, " _
                & "Measurements.Unit, Component.Component " _
                & "FROM Measurements INNER JOIN (Ingredient INNER JOIN (Cupcake INNER JOIN (Component INNER JOIN " _
                & "Cupcake_Ingredient ON Component.[Component ID] = Cupcake_Ingredient.[Component ID]) ON Cupcake.[Cupcake ID] = Cupcake_Ingredient.[Cupcake ID]) " _
                & "ON Ingredient.[Ingredient ID] = Cupcake_Ingredient.[Ingredient ID]) ON Measurements.[Unit ID] = Cupcake_Ingredient.[Units ID] " _
                & "WHERE Cupcake.[cupcake name]= '" & [cupcakeselect2] & "';"
            'Debug.Print sSQL
            CurrentDb.Execute sSQL, dbFailOnError
        Case 3
            sSQL = "INSERT INTO PivotTableSource  ([Cupcake Name], [# Cupcake Made], [Ingredient Name], Qty, Unit, Component) " _
                & "SELECT Cupcake.[cupcake name], Cupcake.[# Cupcake Made], Ingredient.[Ingredient Name], ([Cupcake_Ingredient].[Qty]* " & multiply3 & ") as Qty, " _
                & "Measurements.Unit, Component.Component " _
                & "FROM Measurements INNER JOIN (Ingredient INNER JOIN (Cupcake INNER JOIN (Component INNER JOIN " _
                & "Cupcake_Ingredient ON Component.[Component ID] = Cupcake_Ingredient.[Component ID]) ON Cupcake.[Cupcake ID] = Cupcake_Ingredient.[Cupcake ID]) " _
                & "ON Ingredient.[Ingredient ID] = Cupcake_Ingredient.[Ingredient ID]) ON Measurements.[Unit ID] = Cupcake_Ingredient.[Units ID] " _
                & "WHERE Cupcake.[cupcake name]= '" & [cupcakeselect3] & "';"
            'Debug.Print sSQL
            CurrentDb.Execute sSQL, dbFailOnError
        Case 4
            sSQL = "INSERT INTO PivotTableSource  ([Cupcake Name], [# Cupcake Made], [Ingredient Name], Qty, Unit, Component) " _
                & "SELECT Cupcake.[cupcake name], Cupcake.[# Cupcake Made], Ingredient.[Ingredient Name], ([Cupcake_Ingredient].[Qty]* " & multiply4 & ") as Qty, " _
                & "Measurements.Unit, Component.Component " _
                & "FROM Measurements INNER JOIN (Ingredient INNER JOIN (Cupcake INNER JOIN (Component INNER JOIN " _
                & "Cupcake_Ingredient ON Component.[Component ID] = Cupcake_Ingredient.[Component ID]) ON Cupcake.[Cupcake ID] = Cupcake_Ingredient.[Cupcake ID]) " _
                & "ON Ingredient.[Ingredient ID] = Cupcake_Ingredient.[Ingredient ID]) ON Measurements.[Unit ID] = Cupcake_Ingredient.[Units ID] " _
                & "WHERE Cupcake.[cupcake name]= '" & [cupcakeselect4] & "';"
            'Debug.Print sSQL
            CurrentDb.Execute sSQL, dbFailOnError
        Case 5
            sSQL = "INSERT INTO PivotTableSource  ([Cupcake Name], [# Cupcake Made], [Ingredient Name], Qty, Unit, Component) " _
                & "SELECT Cupcake.[cupcake name], Cupcake.[# Cupcake Made], Ingredient.[Ingredient Name], ([Cupcake_Ingredient].[Qty]* " & multiply5 & ") as Qty, " _
                & "Measurements.Unit, Component.Component " _
                & "FROM Measurements INNER JOIN (Ingredient INNER JOIN (Cupcake INNER JOIN (Component INNER JOIN " _
                & "Cupcake_Ingredient ON Component.[Component ID] = Cupcake_Ingredient.[Component ID]) ON Cupcake.[Cupcake ID] = Cupcake_Ingredient.[Cupcake ID]) " _
                & "ON Ingredient.[Ingredient ID] = Cupcake_Ingredient.[Ingredient ID]) ON Measurements.[Unit ID] = Cupcake_Ingredient.[Units ID] " _
                & "WHERE Cupcake.[cupcake name]= '" & [cupcakeselect5] & "';"
            'Debug.Print sSQL
            CurrentDb.Execute sSQL, dbFailOnError
        Case 6
            sSQL = "INSERT INTO PivotTableSource  ([Cupcake Name], [# Cupcake Made], [Ingredient Name], Qty, Unit, Component) " _
                & "SELECT Cupcake.[cupcake name], Cupcake.[# Cupcake Made], Ingredient.[Ingredient Name], ([Cupcake_Ingredient].[Qty]* " & multiply6 & ") as Qty, " _
                & "Measurements.Unit, Component.Component " _
                & "FROM Measurements INNER JOIN (Ingredient INNER JOIN (Cupcake INNER JOIN (Component INNER JOIN " _
                & "Cupcake_Ingredient ON Component.[Component ID] = Cupcake_Ingredient.[Component ID]) ON Cupcake.[Cupcake ID] = Cupcake_Ingredient.[Cupcake ID]) " _
                & "ON Ingredient.[Ingredient ID] = Cupcake_Ingredient.[Ingredient ID]) ON Measurements.[Unit ID] = Cupcake_Ingredient.[Units ID] " _
                & "WHERE Cupcake.[cupcake name]= '" & [cupcakeselect6] & "';"
            'Debug.Print sSQL
            CurrentDb.Execute sSQL, dbFailOnError
        Case 7
            sSQL = "INSERT INTO PivotTableSource  ([Cupcake Name], [# Cupcake Made], [Ingredient Name], Qty, Unit, Component) " _
                & "SELECT Cupcake.[cupcake name], Cupcake.[# Cupcake Made], Ingredient.[Ingredient Name], ([Cupcake_Ingredient].[Qty]* " & multiply7 & ") as Qty, " _
                & "Measurements.Unit, Component.Component " _
                & "FROM Measurements INNER JOIN (Ingredient INNER JOIN (Cupcake INNER JOIN (Component INNER JOIN " _
                & "Cupcake_Ingredient ON Component.[Component ID] = Cupcake_Ingredient.[Component ID]) ON Cupcake.[Cupcake ID] = Cupcake_Ingredient.[Cupcake ID]) " _
                & "ON Ingredient.[Ingredient ID] = Cupcake_Ingredient.[Ingredient ID]) ON Measurements.[Unit ID] = Cupcake_Ingredient.[Units ID] " _
                & "WHERE Cupcake.[cupcake name]= '" & [cupcakeselect7] & "';"
            'Debug.Print sSQL
            CurrentDb.Execute sSQL, dbFailOnError
        Case 8
            sSQL = "INSERT INTO PivotTableSource  ([Cupcake Name], [# Cupcake Made], [Ingredient Name], Qty, Unit, Component) " _
                & "SELECT Cupcake.[cupcake name], Cupcake.[# Cupcake Made], Ingredient.[Ingredient Name], ([Cupcake_Ingredient].[Qty]* " & multiply8 & ") as Qty, " _
                & "Measurements.Unit, Component.Component " _
                & "FROM Measurements INNER JOIN (Ingredient INNER JOIN (Cupcake INNER JOIN (Component INNER JOIN " _
                & "Cupcake_Ingredient ON Component.[Component ID] = Cupcake_Ingredient.[Component ID]) ON Cupcake.[Cupcake ID] = Cupcake_Ingredient.[Cupcake ID]) " _
                & "ON Ingredient.[Ingredient ID] = Cupcake_Ingredient.[Ingredient ID]) ON Measurements.[Unit ID] = Cupcake_Ingredient.[Units ID] " _
                & "WHERE Cupcake.[cupcake name]= '" & [cupcakeselect8] & "';"
            'Debug.Print sSQL
            CurrentDb.Execute sSQL, dbFailOnError
        Case 9
            sSQL = "INSERT INTO PivotTableSource  ([Cupcake Name], [# Cupcake Made], [Ingredient Name], Qty, Unit, Component) " _
                & "SELECT Cupcake.[cupcake name], Cupcake.[# Cupcake Made], Ingredient.[Ingredient Name], ([Cupcake_Ingredient].[Qty]* " & multiply9 & ") as Qty, " _
                & "Measurements.Unit, Component.Component " _
                & "FROM Measurements INNER JOIN (Ingredient INNER JOIN (Cupcake INNER JOIN (Component INNER JOIN " _
                & "Cupcake_Ingredient ON Component.[Component ID] = Cupcake_Ingredient.[Component ID]) ON Cupcake.[Cupcake ID] = Cupcake_Ingredient.[Cupcake ID]) " _
                & "ON Ingredient.[Ingredient ID] = Cupcake_Ingredient.[Ingredient ID]) ON Measurements.[Unit ID] = Cupcake_Ingredient.[Units ID] " _
                & "WHERE Cupcake.[cupcake name]= '" & [cupcakeselect9] & "';"
            'Debug.Print sSQL
            CurrentDb.Execute sSQL, dbFailOnError
        Case 10
            sSQL = "INSERT INTO PivotTableSource  ([Cupcake Name], [# Cupcake Made], [Ingredient Name], Qty, Unit, Component) " _
                & "SELECT Cupcake.[cupcake name], Cupcake.[# Cupcake Made], Ingredient.[Ingredient Name], ([Cupcake_Ingredient].[Qty]* " & multiply10 & ") as Qty, " _
                & "Measurements.Unit, Component.Component " _
                & "FROM Measurements INNER JOIN (Ingredient INNER JOIN (Cupcake INNER JOIN (Component INNER JOIN " _
                & "Cupcake_Ingredient ON Component.[Component ID] = Cupcake_Ingredient.[Component ID]) ON Cupcake.[Cupcake ID] = Cupcake_Ingredient.[Cupcake ID]) " _
                & "ON Ingredient.[Ingredient ID] = Cupcake_Ingredient.[Ingredient ID]) ON Measurements.[Unit ID] = Cupcake_Ingredient.[Units ID] " _
                & "WHERE Cupcake.[cupcake name]= '" & [cupcakeselect10] & "';"
            'Debug.Print sSQL
            CurrentDb.Execute sSQL, dbFailOnError
    End Select
End Sub

[WEGL08]

That worked beautifully. Thank you so much! If I make ask - to further my learning (I'm a newbie)....what language is that? What can I look up to learn some of that code and how it functions? And you took away all those error messages for when I rewrite over the table (and it's data) when running that query! Thanks again!

[WEGL08]

Okay I lied slightly. The first time I ran it with your code embedded, it worked like a charm. But now, it doesn't seem to be working. I get an error message that says "Runtime Error Cannot Delete from Specified Tables." When I run the debugger, It highlights this particular line of code (see picture). I'm in over my head here, any idea what's causing it to fail after that initial successful attempt?

[Micron]

OK, I like to play with code - just one of my vices. Aside from the fact that most of us (?) consider storing calculations as bad practice, I might condense the above as something like the following (NOTE: the ending semi-colon isn't required in vba sql - ";" and of course, this is untested).

Option Compare Database
Option Explicit
Dim i As Integer
Dim ctlName As String

Private Sub clearform_Click()
For i = 1 to 10
   ctlName = "cupcakeselect" & i
   Me(ctlName) = Null
Next

End Sub

Private Sub runquery_Click()
Dim sSQL As String

CurrentDb.Execute "delete * FROM pivottablesource", dbFailOnError

For i = 1 to 10
  sSQL = "INSERT INTO PivotTableSource  ([Cupcake Name], [# Cupcake Made], [Ingredient Name], Qty, Unit, Component) " _
  & "SELECT Cupcake.[cupcake name], Cupcake.[# Cupcake Made], Ingredient.[Ingredient Name], ([Cupcake_Ingredient].[Qty]* " & multiply1 & ") as Qty, " _
  & "Measurements.Unit, Component.Component " _
  & "FROM Measurements INNER JOIN (Ingredient INNER JOIN (Cupcake INNER JOIN (Component INNER JOIN " _
  & "Cupcake_Ingredient ON Component.[Component ID] = Cupcake_Ingredient.[Component ID]) ON Cupcake.[Cupcake ID] = Cupcake_Ingredient.[Cupcake ID]) " _
  & "ON Ingredient.[Ingredient ID] = Cupcake_Ingredient.[Ingredient ID]) ON Measurements.[Unit ID] = Cupcake_Ingredient.[Units ID] " _
  & "WHERE Cupcake.[cupcake name]= '" & [cupcakeselect"

  ctlName = "cupcakeselect" & i
   If Not IsNull(ctlName) Then
     sSQL = sSQL & i & "]'"
     CurrentDb.Execute sSQL, dbFailOnError
   End If
Next

End Sub

If we were to avoid storing calculations, then the approach might be a different query that feeds a report for the ingredients list.

[Micron]

Upon re-reading your first post, I see via 3rd paragraph that a make table query is part of the process. I'll bet somewhere between the first and subsequent attempt that the table was deleted and not re-created. That would mean the sequence of events needs to be modified, but if you are deleting that table in some procedure, better rethink that process. You should know that frequent deleting and re-creating of a table can cause significant file size bloat and trigger database corruption. See if the table that the code is trying to delete from is in the navigation pane - or is it gone or just not properly named in code?

PS - the language is VBA (Visual Basic for Applications) not to be confused with Visual Basic (VB)

[davegri]

Okay I lied slightly. The first time I ran it with your code embedded, it worked like a charm. But now, it doesn't seem to be working. I get an error message that says "Runtime Error Cannot Delete from Specified Tables." When I run the debugger, It highlights this particular line of code (see picture). I'm in over my head here, any idea what's causing it to fail after that initial successful attempt?

I think it's because somewhere in your code you delete PivotTableSource. Then when my code tries to empty the table, it doesn't exist.
Here is your database in a state that does not exhibit that error:Attachment 28189
I had to remove the image from the main form in order to upload the DB. That image added 3MB to the zipped file.

The make table query, "cupcakeIngredientQuery" should not be run. In fact, it should be deleted.

[WEGL08]

Sorry for the delayed response. I have been out of town for a bit this weekend. I appreciate your response. I'm a bit confused though, that .zip file looks to be the original file I shared? Did you by chance attached the wrong file or am I missing something? Thanks again!

[WEGL08]

Actually, I see what you are saying...I deleted the "cupcakeIngredientQuery" so it wouldn't delete the table / mess up your code....after doing that your code works perfectly! I'm impressed! Thanks a million!