VBA looking up Row Source incorrectly

[anthgav]

Hi

I have a form called 'frmBin' which has a sub form called 'frmBin subform'.
Within the subform I have some VBA which looks up some values and updates the database when 'binType' control is updated.



Here is the VBA...

'Calculates the bin width based on product dimensions or bin type dimensions
Private Sub recalculateBinSize()
        Dim requiredBinWidthByProduct As Long
        Dim requiredBinWidthByBinType As Integer
        Dim updateBinWidthMethod As String
        Dim binID As Integer
        Dim updateBinWidthByProduct As String
        Dim updateBinWidthByBinType As String
        
        requiredBinWidthByProduct = Nz([allocatedBinId].[Column](10), 0)
        Debug.Print "requiredBinWidthByProduct " & requiredBinWidthByProduct
        requiredBinWidthByBinType = Nz([allocatedBinId].[Column](6), 0)
        Debug.Print "requiredBinWidthByBinType " & requiredBinWidthByBinType
        updateBinWidthMethod = Nz([allocatedBinId].[Column](5), 0)
        Debug.Print "updateBinWidthMethod " & updateBinWidthMethod
        binID = Nz([allocatedBinId].[Column](4), 0)
        Debug.Print "binID " & binID
        updateBinWidthByProduct = "UPDATE tblBin SET [bin_width_mm] ='" & (requiredBinWidthByProduct) & "' WHERE [bin_id] = " & (binID)
        updateBinWidthByBinType = "UPDATE tblBin SET [bin_width_mm] ='" & (requiredBinWidthByBinType) & "' WHERE [bin_id] = " & (binID)
        
        If updateBinWidthMethod = -1 Then
            CurrentDb.Execute updateBinWidthByProduct
        ElseIf updateBinWidthMethod = 0 Then
            CurrentDb.Execute updateBinWidthByBinType
        End If
End Sub


Private Sub binType_AfterUpdate()
    recalculateBinSize
End Sub

The VBA looks up it's values from the Row Source attached to 'allocatedBinId' control.
Everytime I update 'binType' the VBA runs but the values being pulled from the Row Source are not correct, I think it seems to pull the values from the original Row Source from when the page loads or the first location that's loaded, I tried different things to make the Row Source provide the correct information but I'm struggling to make it work correctly. I am able to identify this as I've added some debug lines and are viewing the numbers in the immediate window.

I've attached a sample database with my form.

PlannerBinForm.zip

I would be greatful for some assistance on this.

[June7]

First, don't see need to include lookup tables in the subform RecordSource, although shouldn't hurt if join types are correct. However, join between tblBin and tblAllocatedBin in qryBinSubFormA appears backwards. Just use tblAllocatedBin as RecordSource.

Second, also don't see need to run UPDATE action, just set value of textbox on main form: Me.Parent.binWidthBox = requiredBinWidthByProduct

Also, [bin_width_mm] is a number type field which means do not use apostrophe delimiters.

[anthgav]

First, don't see need to include lookup tables in the subform RecordSource, although shouldn't hurt if join types are correct. However, join between tblBin and tblAllocatedBin in qryBinSubFormA appears backwards. Just use tblAllocatedBin as RecordSource.

Second, also don't see need to run UPDATE action, just set value of textbox on main form: Me.Parent.binWidthBox = requiredBinWidthByProduct

Also, [bin_width_mm] is a number type field which means do not use apostrophe delimiters.

Thanks

I've changed the rowsource directly to the table and updated the VBA to the below, I've also added Me.allocatedBinId.Requery to try and update the rowsource after a change to binType.

'Calculates the bin width based on product dimensions or bin type dimensions
Private Sub recalculateBinSize()
        Dim requiredBinWidthByProduct As Long
        Dim requiredBinWidthByBinType As Integer
        Dim updateBinWidthMethod As String
        Dim binID As Integer
        Dim updateBinWidthByProduct As String
        Dim updateBinWidthByBinType As String
        
        requiredBinWidthByProduct = Nz([allocatedBinId].[Column](10), 0)
        Debug.Print "requiredBinWidthByProduct " & requiredBinWidthByProduct
        requiredBinWidthByBinType = Nz([allocatedBinId].[Column](6), 0)
        Debug.Print "requiredBinWidthByBinType " & requiredBinWidthByBinType
        updateBinWidthMethod = Nz([allocatedBinId].[Column](5), 0)
        Debug.Print "updateBinWidthMethod " & updateBinWidthMethod
        
        If updateBinWidthMethod = -1 Then
            Me.Parent.binWidthBox = requiredBinWidthByProduct
        ElseIf updateBinWidthMethod = 0 Then
            Me.Parent.binWidthBox = requiredBinWidthByBinType
        End If
End Sub


Private Sub binType_AfterUpdate()
    Me.allocatedBinId.Requery
    recalculateBinSize
End Sub

The rowsource still doesn't seem to be updating, what else do I need to do?

See updated file below.
PlannerBinForm(2).zip

[June7]

I said subform RecordSource needed fixing. I did not mention a RowSource.

[anthgav]

I said subform RecordSource needed fixing. I did not mention a RowSource.

Sorry, so you did, here is my updated version with RecordSource changed.

PlannerBinForm(3).zip

[June7]

You have combobox bound to autonumber allocated_bin_ID. This makes no sense. Should probably be allocated_bin field.

Exactly what records are you trying to list in combobox? If changing bin type impacts the RowSource then record edit must be committed to table before combobox is requeried. Record is committed when: 1. Close table/query/form or 2. move to another record or 3. run code to save.

[anthgav]

Ok, I've changed that. Any idea why the RowSource doesn't update the new details from the binType?
PlannerBinForm(4).zip

[June7]

I edited previous post possibly after you read it. Have you added code to save record then Requery combobox?

[anthgav]

The RowSource for Allocated Bin combo box was added purely to provide my values for the VBA instead of doing numerous DLookups, the box will actually be hidden once I get this working. The only changeable value on the sub form is the Bin Type combo box, I need to be able to change the Bin Type and then set the Bin Width mm in the parent form once this has been changed. All the other fields are there so I can see what is in that Bin Type but are intentionally not changeable as that's done in another form. I added the Requery and renamed the Allocated Bin control after you asked me to change it but forgot to change the name in the VBA which I've now corrected.

You were correct, the form needs to save before the RowSource can be updated, I added 'DoCmd.RunCommand acCmdSaveRecord' into the AfterUpdate and it is working how I want it to now.

'Calculates the bin width based on product dimensions or bin type dimensions
Private Sub recalculateBinSize()
        Dim requiredBinWidthByProduct As Long
        Dim requiredBinWidthByBinType As Integer
        Dim updateBinWidthMethod As String
        Dim binID As Integer
        Dim updateBinWidthByProduct As String
        Dim updateBinWidthByBinType As String
        
        updateBinWidthMethod = Nz([allocatedBin].[Column](5), 0)
        Debug.Print "updateBinWidthMethod " & updateBinWidthMethod
        
        If updateBinWidthMethod = -1 Then
            requiredBinWidthByProduct = Nz([allocatedBin].[Column](10), 0)
            Debug.Print "requiredBinWidthByProduct " & requiredBinWidthByProduct
            Me.Parent.binWidthBox = requiredBinWidthByProduct
        ElseIf updateBinWidthMethod = 0 Then
            requiredBinWidthByBinType = Nz([allocatedBin].[Column](6), 0)
            Debug.Print "requiredBinWidthByBinType " & requiredBinWidthByBinType
            Me.Parent.binWidthBox = requiredBinWidthByBinType
        End If
End Sub


Private Sub binType_AfterUpdate()
    DoCmd.RunCommand acCmdSaveRecord
    Me.allocatedBin.Requery
    recalculateBinSize
End Sub

Is this method OK or is there a better way of doing it?

New file
PlannerBinForm(5).zip

[June7]

If it works then method is OK. Not going to second guess your design. Just glad it's working now.