Trouble with filling in miles

[liltechy]

I have a Perdiem database that I am working with and I have a form with a main form with a subform. The main form contains these fields EmpNo, Name, Jobclass, PrimarySite. My subform contains these fields: EmpNo, Location (drop down), CostCenter, Date, Miles, NumberDays, Amount. What I am trying to do is when I select the location that the employee has gone to I need the miles to be automatically filled in based on the employees PrimarySite and the Location. My subform is based a query from these two tblMileage and tblTransactions. How do I get the miles field to automatically be filled in? PLEASE HELP!!!

liltechy

 

[MichaelRed]

Iit is probably not the best concept. as an employee may not always depart from the same place (site) to go to a next one.

Further, routes between sites might change, thus even if the origin is "defined" as a fixed point (at least for hte individual), the mileage can easily vary.

Aslo, consider that soem employees migh need to go to the same destination amd choose to 'car pool', thus only one should be elegibla for the mileage?

I am sure that many other scenarios fo varying the mileage are quite reasonable. These are only a small sample of plausible rationales, so I would not expect it to be pratical to 'programmatically' consider all of the instances and account for them in a common manner using general apporaches.




At BEST, I would venture to establish a point to point 'matrix' like those in most maps / atlases and use this as a lookup to establish a 'reasonable' mileage. The entered mileage would be "challenged" (e.g. some additional Proof / reason required if the entered mileage were not within some variance of the established value).

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[liltechy]

The employee will always depart from their PrimarySite. A mileage table has been created for this. The fields in the tblMileage are MileageID, PrimarySite, Location, Miles.

liltechy

 

[Norris68]

Private Sub cboLocation_OnClick()
Me.txtMiles = NZ(DLookup("Miles","tblMileage","Location='" & Me.cboLocation & "' AND PrimarySite='" & Forms("MainForm&quot.txtPrimarySite & "'&quot,0)
End Sub

Assumes you have applied a txt/cbo prefix to field names as appropriate, and PrimarySite & Location are text fields. It uses the DLookup function to look up the mileage from your table based on the Location combo on your subform and the PrimarySite textbox on your 'MainForm'. The mileage will be set to zero if the primary site/location combination is not found in the table (DLookup will return Null).

A more versatile way (but more expensive) may be to buy geographical data (as provided by Geoplan here in the UK) which gives a grid reference for each post code, then use trignometry/Pythagoras to work out the distance between the two points (as the crow flies).

 

[liltechy]

Shouldn't I put this in the AfterUpdate event of the Location control?

liltechy