Update field on form that matches a given list

[rbbrmtstherd]

Good morning,

This is my first post, so please bear with me if I am not posting in the correct format. I am fairly new to VBA, but have been using/learning it in my current job to create some reporting/tracking tools in access. I have been using a lot of other peoples' threads on here to figure out much of what I need to do, but I am stuck right now.

I am having an issue right now where I have a form ("FrmTrlrInfo") and on it I have two fields ("Carrier" and "SCAC"), along with around 10 other fields. These are all tied to TblData as their control source. I have another table "TblCarrierList" that has a list of all available Carrier/SCAC combinations (with the fields "Carrier" and "SCAC". In FrmTrlrInfo, Carrier is a dropdown list that uses the available options in TblCarrierList. What I want to happen, is that after the Carrier is selected on the form, SCAC (on the form) is updated with the corresponding SCAC value from TblCarrierList.

I tried using a query to update the table, but it would not update the current record I was working on.

Code:

Private Sub Carrier_AfterUpdate()

docmd.OpenQuery "QryUpdateSCAC"

Me.Refresh

End Sub

Any thoughts or advice would be greatly appreciated.

 

[Setty]

Hi,

You can pull the SCAC value directly from a Combo box provided you have the settings as shown here below. A combo box or list box can have multiple colums or fields as its row source. Also there is flexibilty in showing or hiding the fields in the drop down list or list columns. The column count property must match the number of fields in the row source sql statement. The column widths must also correspond to the number of fields in sql statement.

Property Sheet:

Data (Tab):

RowSource: SELECT Carrier, SCAC from TblCarrierList;

Row Source Type: Table/Query

Bound Column: 1

Format (Tab):

Column Count : 2

Column Widths : 1”;1”

Column Heads: No

List Rows: 8

List Width: 2"
(It should match the sum of column widths (1+1). The column width could be fractions – you can set as 0.6”; 0.75” based on the data width in those fields)

If column widths is entered as 1”;0” – it means you want to show only the first field in the drop down. The second field is not visible but available for use any time as long as the form is open.

If you have set all the properties as shown here, then in the after_update event of Carrier combo box control, enter the following code.

Private Sub Carrier_AfterUpdate()

    If Not IsNull(Me!Carrier) Then
    
        Me!SCAC = Me!Carrier.Column(1)
        
    End If

End Sub

Please note the columns are zero based. Column(0) is Carrier – first field in sql statement
Column(1) is SCAC – second field in sql statement

Also note the Bound Column property set as 1. Bound column is not zero based. If it is set as 1, it means it is set to first field in the sql statement – i.e. ‘Carrier’ field.

Open the form and test by selecting different Carrier values in Carrier combo box. You should see the corresponding value in SCAC text box.

There is no need to run any update query as shown in your code.

Surendran Setty