Subform to display records based on form combobox (1 Viewer)

[Leo_Polla_Psemata]

Hi
This is about making offers to customers (shipping, dealing with freight)
I have two linked tables/form.subform, which i use for creating offers to my clients.

In the form, there is combo box from which i chose the area, for example LATAM (= Latin America) or FET (far east) or TAT (trans atlantic) and so on.
There are around 18 different areas.
Now, for each area, there are different elements-freight surcharges that i have to bring in my quotation and fill with rates.

For example
If the area is FET then there are four surcharges (oft/faf/doc/hcs)
If the area is TAT thn there are eight different surcharges i have to fill in (oft/buc/psc/pss/thc and so on)
If the area is EXW then a different group of surcharges (oft/faf/buc).

Each one of these 18 different areas, uses different number of surcharges.
It is a real headache to make sure each time, i haven't forgotten any surcharge line.

Question, is there any method from which, once i chose a code (the area) from combo box,
then, i find in subform listed all the surcharges ready to fill in with rates ?
Subform view is Datasheet

 

[Auntiejack56]

Leo, is your combobox populated from a table containing all the areas and codes, or have you hardcoded them in as 18 values?

 

[Leo_Polla_Psemata]

Yes, these areas are stored in a third table, then, the combo box retrieve exclusively data from this table only.

 

[Auntiejack56]

So this would be one (very quick) way to do it. Add a new column to the table with the surcharges:
For the FET record, the new column would contain: oft/faf/doc/hcs
For the TAT record, the new column would contain: oft/buc/psc/pss/thc
For the EXW record, the new column would contain: oft/faf/buc
and so on. You must include the slashes, but no quotes or spaces.

Then go to the combo, open the properties and add an extra column. That is, in the Properties Format tab, set Column Count to 2, set Column Widths to 1;0.
And change your query to pick up the extra column.

Then, in the Events for the combo, your code for After Update would be as follows (but note that my combo name is scrAreas, and I don't know where your OfferID comes from but you can fix that, and I don't know your table and column names but you can fix those too):

Private Sub scrAreas_AfterUpdate()
On Error GoTo Catch_Error
Dim arrSurcharges, n As Long
Dim dbs As DAO.Database, strSQL As String
Dim nOfferID As Long

    If Len(Nz(Me.scrAreas.Column(1), "")) > 0 Then
        arrSurcharges = Split(Me.scrAreas.Column(1), "/")
        nOfferID = 999 '' I don't know where you get the ID of the current offer from, it's up to you to put it in here instead of 999
        If MsgBox("You are about to add " & UBound(arrSurcharges) + 1 & " surcharge lines to this offer." & vbCrLf & vbCrLf & "Proceed?", vbOKCancel, "Confirm Surcharges") = vbOK Then
            Set dbs = CurrentDb()
            For n = 0 To UBound(arrSurcharges)
                strSQL = "INSERT INTO tblOfferLines ( OfferID, SurchargeCode, SurchargeAmt ) " _
                        & " VALUES (" & nOfferID & ", '" & arrSurcharges(n) & "', 0);"
                dbs.Execute strSQL
            Next
            Me.[the name of your subform].Form.Requery
        End If
    End If
Proc_Exit:
    Set dbs = Nothing
    Exit Sub
Catch_Error:
    MsgBox Err.Description & vbCrLf & vbCrLf & "Automatic insertion of Surcharges failed. Surcharges must be added manually.", vbInformation, "Cannot add surcharges."
    Resume Proc_Exit
    Resume
End Sub

 

[Leo_Polla_Psemata]

So this would be one (very quick) way to do it. Add a new column to the table with the surcharges:
For the FET record, the new column would contain: oft/faf/doc/hcs
For the TAT record, the new column would contain: oft/buc/psc/pss/thc
For the EXW record, the new column would contain: oft/faf/buc
and so on. You must include the slashes, but no quotes or spaces.

Then go to the combo, open the properties and add an extra column. That is, in the Properties Format tab, set Column Count to 2, set Column Widths to 1;0.
And change your query to pick up the extra column.

Then, in the Events for the combo, your code for After Update would be as follows (but note that my combo name is scrAreas, and I don't know where your OfferID comes from but you can fix that, and I don't know your table and column names but you can fix those too):

Private Sub scrAreas_AfterUpdate()
On Error GoTo Catch_Error
Dim arrSurcharges, n As Long
Dim dbs As DAO.Database, strSQL As String
Dim nOfferID As Long

    If Len(Nz(Me.scrAreas.Column(1), "")) > 0 Then
        arrSurcharges = Split(Me.scrAreas.Column(1), "/")
        nOfferID = 999 '' I don't know where you get the ID of the current offer from, it's up to you to put it in here instead of 999
        If MsgBox("You are about to add " & UBound(arrSurcharges) + 1 & " surcharge lines to this offer." & vbCrLf & vbCrLf & "Proceed?", vbOKCancel, "Confirm Surcharges") = vbOK Then
            Set dbs = CurrentDb()
            For n = 0 To UBound(arrSurcharges)
                strSQL = "INSERT INTO tblOfferLines ( OfferID, SurchargeCode, SurchargeAmt ) " _
                        & " VALUES (" & nOfferID & ", '" & arrSurcharges(n) & "', 0);"
                dbs.Execute strSQL
            Next
            Me.[the name of your subform].Form.Requery
        End If
    End If
Proc_Exit:
    Set dbs = Nothing
    Exit Sub
Catch_Error:
    MsgBox Err.Description & vbCrLf & vbCrLf & "Automatic insertion of Surcharges failed. Surcharges must be added manually.", vbInformation, "Cannot add surcharges."
    Resume Proc_Exit
    Resume
End Sub

Thank you very much, got it, i will try it home later this evening.