Autofill Fields in Form (1 Viewer)

[Lee83]

Hi,

I have a form that has a field called scope of work which is a combo box that you have 3 options to select from.

I have set up a table called scope of work which has other fields
ie: Scope of work, Est Man Hours, Normal Rate, Overtime Rate and Est Due Date.

The combo box works fine selecting the scope of work but I want the fields on the form to automatically fill in the other information. Say they select option 1 in the scope of work based on this option it will fill in 40hrs at $150 per hour normal rate and $180 per hour overtime rate and this will take 4 weeks to complete. Based on the start date field it will also calculate 4 weeks from now for the completion date.

I have tried everything from queries using SQL statements, joining tables one to many etc.. and for some reason I cannot seem to get it to work for me and I've spent hours searching around for a solution..

If anyone has any ideas it would be greatly greatly greatly appreciated!!

Thanks a Bunch

 

[Brian1960]

Use an event

The easiest way is to use an event such as LostFocus or OnChange to trigger the different options and then do a refresh.

Private Sub cboScope_Change()
    Me.OtherObject.Value = Me.cboScope.Column(2)
    Me.WageRate = Me.cboScope.Column(3)
End Sub

You can also use the dateadd function to workout an end date using an entered start date.

Me.EndDate = DateAdd('ww',Me.cboScope.Column(4),Me.startDate)

The combo must have the appropriate query of course.
Select tblName.Field1,tblName.Field2,tblName.Field3 tblName.Field4 from tblName
If you don't want to show all the fields in the drop down then use 0cm.

Hope this helps.

 

[Lee83]

Autofill Fields

Thanks

That was very helpful! and is all working now
I ended up using the following;

Private Sub Scope_Change()

Me.Est_Man_Hours = Me.Scope.Column(1)
Me.Normal_Rate = Me.Scope.Column(2)
Me.Overtime_Rate = Me.Scope.Column(3)
Me.Est_Due_Date = Me.Start_Date + Me.Scope.Column(4)


End Sub