Auto filling fields in based on values in a form

[sal]

Auto filling fields based on values in a form

This is one of those rare occasions where values from a related table must be stored in the main table; These are river mile start and end points that are default values but not always actual values. The purpose of this action is to bypass cascading combos and speed up data entry such that the moment the Section is entered the RM's fill.

The code I have is similar to another post except it is placed in the After_Update event of the field I wish to update from. However, my code is producing a compile error: Argument not optional. The debugger points to Me.Section:

Private Sub Section_AfterUpdate()
Me.RMLower = DLookup("[RMLower]", "[Survey Sections]", "Section_ID = '" & Me.Section & "'")
End Sub

Section_ID (a text field) is the common value in the table; Section is the control containing the related value; Survey Sections is the lookup table; and RMLower the field contains the value as well as the control to be updated.

If I could get this to work I would like to add another field, RMUpper as well, presumably as a second, nearly identical line.

 

[Pat Hartman]

I don't see the syntax error but then I'm not a compiler. A more efficient way to do this is to include RMLower and RMUpper in the RowSource for Section. Then in the AfterUpdate event of the combo where the user chooses Section, you can copy the other data.

Me.txtRmLower = Me.cboSection.Column(2)
Me.txtRmUpper = Me.cboSection.Column(3)

This is more efficient because it eliminates the two DLookup() queries. Adjust the column to the columns relevant in your situation. Remember the RowSource is zero based so .Column(0) is the first column, .Column(1) is the second, etc. Also don't forget to change the ColumnCount and ColumnWidth properties of cboSection.

 

[sal]

Thank you very much for the suggestion. But for some reason the same error is occurring:

Private Sub Section_AfterUpdate()
Me.RMLower = Me.Section.Column(3)
Me.RMUpper = Me.Section.Column(4)
End Sub

at .Section (this is the name of the control.

 

[Pat Hartman]

Add a break point and step through the code so you can determine which statement is causing the error.