Update subform based on main form combo box (1 Viewer)

[Hedge01]

Hi hoping someone can help.

I have a main form with a combo box on the main form with 3 options Shanghai, Ningbo, Qingdao
A sub form with a combo box from an imbedded query with multiple columns Vessel, Voyage, ETA, ETDShanghai(Column3) , ETDNingbo(Column4), ETDQingdao(Column5). These date fields have different dates based on the vessel and voyage selected.

If shanghai is selected on the main form the ETD field in the subform should be the ETDShangai Value as being column(3).

Lines for Voyage and ETA work correctly. The last 3 lines i need help!

Private Sub VesselName_AfterUpdate()
Me.Voyage = Me.VesselName.Column(1)
Me.ETA = Me.VesselName.Column(2)
Parent.OriginPort. "Shanghai" Then Me.ETD = Me.VesselName.Column(3)
Else
Parent.OriginPort. "Ningbo" Then Me.ETD = Me.VesselName.Column(4)
Else
Parent.OriginPort. "Qingdao" Then Me ETD = Me.VesselName.Column(5)

End Sub

 

[June7]

There is no If Then to go with the Else. And there can be only one Else.

If ... Then
...
ElseIf ...
...
ElseIf ...
'''
Else
...
End If

But maybe Select Case structure would be better.

Select Case Me.Parent.OriginPort
   Case "Shanghai"
        Me.ETD = Me.VesselName.Column(3)
   Case "Ningbo"
        Me.ETD = Me.VesselName.Column(4)
   Case "Qingdo"
        Me.ETD = Me.VesselName.Column(5)
End Select

 

[Hedge01]

Thanks so much, really appreciate the help.

 

[June7]

Here's another variation:

Dim x As Integer
With Me
x = Switch(.Parent.OriginPort = "Shanghai", 3, .Parent.OriginPort = "Ningbo", 4, .Parent.OriginPort = "Qingbo", 5)
.ETD = .VesselName.Column(x)
End With

 

[Pat Hartman]

If shanghai is selected on the main form the ETD field in the subform should be the ETDShangai Value as being column(3).

Nothing stored in the mainform should cause a change to any subform record. What you have is a design flaw.

If a value in the mainform controls something you want to see associated with a subform record, then calculate the value in the query that is used as the RecordSource for the subform. You can use a function or embed the command using something like the Switch suggested by June.

Personally, I wouldn't use a Switch because for something like this where there could be additions, you would have to find all the queries with the Switch and update them. A better solution is a table that relates the city with the ship. That way you can use a query that joins the city name with the cross reference table and pull in the ship name.

CityName, ShipName
Shanghai, ship1
Ningbo, ship2
Qingdo, ship3

Then if you add a new city, just add a row. If you replace ship1 with ship4, just change the name in the first column.

There are other considerations when using a properly normalized schema. For this particular situation, ships could easily change over time. So in 2023 ship 1 was going to Shanghai but in 2024, ship 4 was going to Shanghai but ship 1 was reassigned to Ningbo. So, just joining to the cross reference table in the query would result in incorrect historical data. Therefore, you need to store the name of the ship when you create the voyage record.

Without seeing your schema or understanding why city is in the parent record by ship is in the child record, I can't give any advice. It seems from the outside looking in that City and ship name belong in the same table rather than different tables.

 

[cheekybuddha]

If ... Then
...
ElseIf ...
...
ElseIf ...
'''
Else
...
End If

Small technical adjustment:

If ... Then
...
ElseIf ... Then
...
ElseIf ... Then
'''
Else
...
End If