MS Access/VBA: how to make this statement work

[azu_daioh]

I have this:

Private Sub DIFO_NUM_AfterUpdate()

If Me.NewRecord = True Then
Me.FO_MGR.RowSourceType = "Table/Query"
Me.FO_MGR.RowSource = "SELECT Name FROM [Staff Info]" & "WHERE
Title = 'Mgr'
End If

End Sub


I know I need to add something but I dont know what. I have a combo-
box "DIFO_NUM" listing all the field offices numbers. In theory, I
would like it to automatically complete the Field Office and Regional
Manager fields depending on DIFO_NUM value.

I only want it to do this on NEW RECORDS, older records does not need
to change the value of the FO_MGR & REG_MGR.

Thank you
Sharon

 

[Pat Hartman \(MVP\)]

To be perfectly correct, you don't need to store the FO_MGR and REG_MGR
since you can get them easily by joining to the [Staff Info] table. You
only need to store the ID that will get you there.

On rare occassions it is necessary to store data. One common instance of
this is unit price in an order entry application. When you add an item to
an order, you need to store the current price in the item detail table
because you don't want the price to change when you change the standard
price in the product table. The simplest way to do this is to add the
"extra" columns to the query you use as your combo's RowSource. You can
hide the columns since there is no need to see them when the combo is
opened. In the AfterUpdate event of the combo, place the code to copy the
extra columns from the combo to the current record:

Me.FO_MGR = Me.SomeCombo.Column(2)
Me.REG_MGR = Me.SomeCombo.Column(3)

The columns of the combo's RowSource are a zero-based array. That means
that the first item is (0), the second is (1), the third is (2), etc. I
made the assumption that these two columns would be added as the third and
fourth columns in the query. If that is not the case, adjust the column
number accordingly.

There is no need to worry about whether the record is new or not. Whenever
the controling value is changed, you want to change the two dependent
fields.