Help with last mileage record (1 Viewer)

[Waddy]

I am currently trying to get my form to show the last mileage in the start mileage text box and then you type in the end mileage in the end mileage text box and then calculate.

I have got it to put in the site and date etc when you select from the combo box but cant get it to show the last mileage it had and put it in the start mileage and then calculate when you have filled in all the functions.

I have tried many things but cant get it done. I have attached a test file on what I have been trying to do for some support.

I hope you can help me

I deleted the last thread and put on a new one as I uploaded a file with linked tables. These are now local.

 

[Gasman]

I think DMax would do it for you?

dmax("EndMileage","tblMileage" ,"VehicleReg='" & Me.Combo10 & "'")

You might want to give a more meaningful names to the controls as well?

HTH

I am currently trying to get my form to show the last mileage in the start mileage text box and then you type in the end mileage in the end mileage text box and then calculate.

I have got it to put in the site and date etc when you select from the combo box but cant get it to show the last mileage it had and put it in the start mileage and then calculate when you have filled in all the functions.

I have tried many things but cant get it done. I have attached a test file on what I have been trying to do for some support.

I hope you can help me

I deleted the last thread and put on a new one as I uploaded a file with linked tables. These are now local.

 

[Waddy]

Hi Gasman,

Would this go into the control source?

I will be doing better control names in the final version, just doing this for testing.

Thank you so much for your advice and support in this?

 

[Gasman]

Might want to surround it with a NZ() as well for the first lookup?

 

[Waddy]

I have just tried the following in the control source but it does not work

=DMax("EndMileage","tblMileage","VehicleReg='" & [Me].[Combo10] & "'")

Sorry

 

[Gasman]

Hi Gasman,

Would this go into the control source?

I will be doing better control names in the final version, just doing this for testing.

Thank you so much for your advice and support in this?

I don't think so, as then you could not bind the field to the table.?

I put it in the AfterUpdate event of the combo, but you could put it in the Change event where you have the rest of your code.?

Could you bring it in with the rest of the combo source and assign as you have done for the other controls?

 

[Gasman]

I have just tried the following in the control source but it does not work

=DMax("EndMileage","tblMileage","VehicleReg='" & [Me].[Combo10] & "'")

Sorry

For the control source you would use

=DMax("EndMileage","tblMileage","VehicleReg='" & [Combo10] & "'")

but as I said, I think you need to bind that control to the table.?

 

[Waddy]

That's correct I do need this info to be binded, so can anyone recommend any work rounds?

Thank you

 

[Gasman]

That's correct I do need this info to be binded, so can anyone recommend any work rounds?

Thank you

Yes, I've already told you, set the control in an event on the form.
You are using the Change event to set the reg and description, why not there.?

However I would have used the AfterUpdate event of the combo for that code, as I think the change event fires on every keystroke.?

Google that.

 

[Waddy]

Thank you

I will have a go.

 

[Waddy]

Thank you gas man, that works a treat.

Now I just need to know how to do a calculation field in the total miles

 

[Gasman]

Well I would have thought that the total miles might be along the lines of

DMax(EndMileage) - DMin(StartMileage) ?

Note that is pesudo code. You need to write the DMin/Max functions correctly, but you already know DMax, DMin is the same format.?

 

[Waddy]

Gasman, thank you!!!!

It all now works!!!! amazing.

Now to tidy it all up and do some testing.