Maybe my code is wrong somewhere?

[lccrews]

I have a recipe making process built in Access. When you select an ingredient from the recipe it will, based on another selection (SKiD), run particular calculations and add the right amounts to the Master Batch Record that will be printed.

The problem, I set the number of inputs to I = 1 to To DLookup("[Num of Rows]", "qry_BP40_Gummy_We04_Pt4"). When I select Orange as my flavor, the code works correctly. When I choose Cherry for some reason, the code behaves incorrectly. It groups all individual numbers into 1 large number and adds that as a record. The code should divide the recipe quantity into equal partitions (as many as there are rows) and add the divided raw material and quantity as a record. My Next I says to run that same query again and again until the aforementioned DLookup.

At first I thought this was a MS Access glitch or one of those unexpected behaviors from Access, but this is ongoing and occurs whenever I select any flavor besides Orange.

Hopefully someone here will know what is going on. Or my worst fear may be true: this is just a weird glitch.

Please bear in mind, I'm a beginner at coding.

Private Sub We04_Pt1()
        DoCmd.SetWarnings (WarningsOff)
        DoCmd.OpenQuery "qry_BP40_Gummy_We04_Pt5"
        DoCmd.OpenQuery "qry_BP40_Gummy_UPK_Add_Pt1"
        DoCmd.SetWarnings (WarningsOn)
End Sub


Private Sub We04_Pt2()
    Dim I As Integer
    For I = 1 To DLookup("[Num of Rows]", "qry_BP40_Gummy_We04_Pt4")
        DoCmd.SetWarnings (WarningsOff)
        DoCmd.OpenQuery "qry_BP40_Gummy_We04_Pt6"
    Next I
        DoCmd.OpenQuery "qry_BP40_Gummy_UPK_Add_Pt2"
        DoCmd.SetWarnings (WarningsOn)
        DoCmd.RefreshRecord
End Sub


Private Sub Command522_Click()
    Dim I As Integer
        If DLookup("[Total]", "qry_BP40_Gummy_We04_Pt3") <= 30 Then
            DoCmd.SetWarnings (WarningsOff)
            DoCmd.OpenQuery "qry_BP40_Gummy_We04_Pt8"
            DoCmd.OpenQuery "qry_BP40_Gummy_UPK_Add_Pt4"
            DoCmd.SetWarnings (WarningsOn)
            DoCmd.RefreshRecord
        Else
        If DLookup("[NumofRows2]", "qry_BP40_Gummy_We04_Pt4") <= 2 And Me.SKID <> "SKID5-F" Then
            DoCmd.SetWarnings (WarningsOff)
            DoCmd.OpenQuery "qry_BP40_Gummy_We04_Pt5"
            DoCmd.OpenQuery "qry_BP40_Gummy_We04_Pt9"
            DoCmd.OpenQuery "qry_BP40_Gummy_UPK_Add_Pt1"
            DoCmd.OpenQuery "qry_BP40_Gummy_UPK_Add_Pt2"
            DoCmd.SetWarnings (WarningsOn)
            DoCmd.RefreshRecord
        Else
            Select Case Me.SKID
                Case "SKID5"
                    Call We04_Pt1
                    Call We04_Pt2
                Case "SKID5-F"
                    DoCmd.SetWarnings (WarningsOff)
                    DoCmd.OpenQuery "qry_BP40_Gummy_We04_Pt8"
                    DoCmd.SetWarnings (WarningsOn)
                    DoCmd.RefreshRecord
                Case "SKID6"
                    For I = 1 To DLookup("[NumofRows]", "qry_BP40_Gummy_We04_Pt4")
                        DoCmd.SetWarnings (WarningsOff)
                        DoCmd.OpenQuery "qry_BP40_Gummy_We04_Pt7"
                        DoCmd.SetWarnings (WarningsOn)
                        DoCmd.RefreshRecord
                    Next I
                Case "SKID7"
                    For I = 1 To DLookup("[NumofRows3]", "qry_BP40_Gummy_We04_Pt4")
                        DoCmd.SetWarnings (WarningsOff)
                        DoCmd.OpenQuery "qry_BP40_Gummy_We04_Pt10"
                    Next I
                        DoCmd.SetWarnings (WarningsOn)
                        DoCmd.RefreshRecord
                End Select
            End If
        End If
End Sub

[aytee111]

Have you stepped through the code line by line? Maybe "orange" goes thru a different set of instructions that the others.

[lccrews]

When I step through it line by line, the code works as expected. Nowhere in the code is there anything about flavor either. It's weird. The code should only care about SKID and RAW MATERIAL.

[aytee111]

That doesn't make a lot of sense! Can you attach your database? Pare it down to just the basics.

[lccrews]

Here it is. Just open "...MBR_Process" first and then click View MBR. From there the "Weighing" tab is where the problem is. Thanks for taking a look.

[aytee111]

Invalid Use of Null - is that what this thread is all about? That would have helped us to know that.

Whenever you use DLookup you need to handle it not finding anything, otherwise it will return an error. So throughout the code, add error handling for them. For me it is easier to do them outside IF's - define a variable and then say "x = DLookup........", then I can check the value of X before continuing.

To cut down on queries not returning data, you could consider using cascading comboboxes on your form (only showing data that matches the other combobox(es).

[June7]

The queries have dynamic parameters that reference controls on form. One of them is the Flavor combobox.

I never use dynamic parameters in query.

Selecting Cherry results in "invalid use of Null" error because no records are returned in query qry_BP40_Gummy_We04_Pt4 and the DLookup() returns Null. Handle Null. Don't run code if Null is returned.

Why are you using query with no JOIN clauses?

I also prefer to construct SQL action statements in VBA instead of running query objects.

[lccrews]

Invalid use of Null is NOT what this post is about. Did you change the ingredient before selecting Cherry? The code executes when I did it. The issue is that the divided record that should be entered as many times as there are rows is NOT being duplicated appropriately. For example I select ingredient 123456, the calculation should return an append query with "123456 Input: 6" That record should be appended as many times as stated in [Num of Rows] from another query. This is printed so the operators know how much of what to weigh out.

Say 123456 has an input of 18 and a [Num of Rows] = 3. The append query would read "123456 Input: 6" 3 times. Now when I select Cherry for some reason I get "123456 Input: 18" instead of 3 entries of "123456 Input: 6".

[June7]

I am not familiar enough with your data to assure any combination of the 3 inputs will return records. I made random choices in the first 2 and selected Cherry. No records. Need to design to handle that possibility.

So I have to get past the 'invalid use of null' before can even begin to evaluate the other issue.

What is 'ingredient'? I see choices for RawMaterial and Skid and Flavor. 123456 does not show as a choice. I see two choices for RawMaterial, 129413 - COLOR - P-20,000-EWS and 129417 - COLOR - CC-500-WS.

[aytee111]

Sorry, didn't read your note. Will look further.

[lccrews]

Right above the raw material field, there is a label describing the following:
129413 - COLOR - P-20,000-EWS belongs to orange
129417 - COLOR - CC-500-WS belongs to cherry

[aytee111]

My code looks different to yours. This is what happens when I select Cherry (only 2 records added):

        Else
        If DLookup("[NumofRows2]", "qry_BP40_Gummy_We04_Pt4") <= 2 And Me.SKID <> "SKID5-F" Then
            DoCmd.SetWarnings (WarningsOff)
            DoCmd.OpenQuery "qry_BP40_Gummy_We04_Pt5"
            DoCmd.OpenQuery "qry_BP40_Gummy_We04_Pt9"
            DoCmd.SetWarnings (WarningsOn)
            DoCmd.RefreshRecord

[lccrews]

Oh my Aytee111... I had just figured this out when you posted. It is that line that I didn't catch. [NumofRows2] from qry_BP40_Gummy_We04_Pt4 is definitely 2 in this scenario. I was struggling like a mofo trying to uncover that. Thank you for reviewing my case anyways. There's a flaw there and I need to re-write that portion. Can you point me in the right direction of setting error handling? I'd like to display a message other than the default so the user doesn't get a chance to see behind the scenes.

[June7]

Exactly what I was just about to post. I finally got a combination that did not trigger the 'invalid use of null' and it hits that code block. All of the Cherry records show 2 in that field.

Whenever possible don't let code get to the point of triggering run-time error. In this case, like:

If Nz(DLookup(...),0) <> 0 Then
run your code here
End If

Maybe even set a variable to the expression so returned value can easily be used elsewhere in code.


Here's one tutorial on error handling http://allenbrowne.com/ser-23a.html

However, don't implement this until ready to publish db for users. Error handler code makes debugging more frustrating. Often have to disable so debugger will break on line of code causing issue.

[aytee111]

First you must decide what you WANT to happen each time an error can occur. Say, for each DLookup, what do you want to happen? And in this case where the same fields are being used in multiple queries, the first thing you can do when they click the button is check the values - do the dlookup before anything, and then you can tell the users that the combination that they entered isn't valid. I would still prefer cascading comboboxes, however.

Error-handling, code such as this:
Dim J As Integer
J = DLookup("[Num of Rows]", "qry_BP40_Gummy_We04_Pt4")


1 - you can have a line before it: On Error GoTo We04_Pt2_Err, which is a handle just before the End Sub, and in there display the error using a MsgBox or whatever you want. This will handle all errors that may occur in the entire subroutine
2 - you can have a line before it: On Error Resume Next, the process will see the line of code, produce an error but do nothing about it. After the line you can say If Err <> 0 Then .... and decide what to do, exit sub, show error message, etc. This will handle only one error at a time.