Ms Access Lookup

I have a form called frmAddEditFuelData which updates tblFuelLog. tblFuelLog has the following fields FuelId, VehicleID, OdoAtFillup. I need on the form Afterupdate (OdoAtFillup) to do the following, look up another table called tblVehicles if tblVehicles."StartOdo" =<0 then  tblVehicles.StartOdo  = tblFuelLog."OdoAtFillup" where tblFuelLog."VehicleId" = tblVehicles."VehicleID"
then it need have a msgbox (No Start miles has been set for this Vehicle, this will be StartOdo) vbok. after ok has been clicked I need it to  Delete current record with no msg telling them it is deleting the record and then close the form
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


I was not really sure on what dataset you meant to delete, but I assume the "Fuel Log Entry"
I have created an event for Update of OdoAtFillup and read out the values of the form.
I use them to read the dataset and then treat the data as you described. I added some more prompting, which you for sure can easily remove, but for the testing it is much more helpful to see what is going on. So, in case that you have an entry >0 I calculate and message the difference in miles from the stored to the entered value, which then you can as well remove.

I hope attached code helps you in your task you want to achieve.
Option Compare Database

Private Sub OdoAtFillup_AfterUpdate()
    Dim SQL As String
    Dim SQL2 As String
    Dim FindRecordCount As Integer
    Dim miles, car
    miles = Me.OdoAtFillup.Value
    car = Me.VehicleID.Value
    SQL2 = "UPDATE [tblVehicles] SET [tblVehicles].[StartOdo] = " & miles & _
            " WHERE ( [tblVehicles].[VehicleId] = " & car & " )"
   SQL = "SELECT [tblVehicles].[StartOdo] FROM [tblVehicles]  " & _
         "WHERE ( [tblVehicles].[VehicleId] = " & car & " );"
   Dim myDB As Database
   Dim rstVehicles As Recordset
   Dim fldLoop As Field

   Set myDB = CurrentDb
   Set rstVehicles = myDB.OpenRecordset(SQL)
    If rstVehicles.EOF Then
        FindRecordCount = 0
        FindRecordCount = rstVehicles.RecordCount
    End If
    If (FindRecordCount = 0) Then
        MsgBox ("no records found for VehicleID " & Me.VehicleID)
        startOdo = rstVehicles.Fields("StartOdo")
        MsgBox "StartOdo= " & startOdo
        If (startOdo <= 0) Then
            DoCmd.SetWarnings False
            erg = MsgBox("No Start miles had been set for this Vehicle, this will be StartOdo", vbOKOnly)
            DoCmd.RunSQL (SQL2)
            DoCmd.RunCommand (acCmdDeleteRecord)
            DoCmd.SetWarnings True
            MsgBox ("Mileage run with last fuel: " & (Me.OdoAtFillup - startOdo))
        End If

    End If
   Set rstVehicles = Nothing
   Set myDB = Nothing
End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
Without any context, this is hard to follow.

For example, is StartOdo the very first odometer reading this vehicle has ever had, or is this just the current reading when they fill up?
It seems like you are setting this current meter reading as the StartOdo if it is less than zero at this pump?
Why is there no the starting Odometer reading already in that table, or why would in need to change?

You also did not post the datatypes in the field. This is needed if you want he exact syntax.

Finally, it is also not clear if there may be a better way to do this...

In other words, explanations are great, but again, without any context (a sample database), they can sometimes end up being hard to follow.

So my guess is that you are looking for the general syntax
To get the StartOdo from a table use something like this:

Dim lngStartOdo as long
lngStartOdo =Dlookup("StartOdo","tblVehicles", "VehicleID=" & YourVehicleIDFormField)
If lngStartOdo=0 Then
    'Do Something
    'Do Something else
end if

(again, you may be able to "tighten" this up with an IIF() function, but this makes it easier to see write if I don't know all the details)

Blueice13085Author Commented:
Ok the StartOdo is the the starting odometer of the vehicle with a fuel tank, and I don't want to show the gallons and cost of the fuel up sense these #'s would mess the MPG up
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Blueice13085Author Commented:
Works GREAT, I changed a few things on being
If (IsNull(StartOdo <= 0)) Then
Incase the StartOdo was no value

should I use nz? because when I use If (IsNull(StartOdo <= 0)) Then and the value is 0 it don't work, and when I use If (StartOdo <= 0) Then and there is no value it don't work
I am not sure what you try to achieve in your last question, but you have to beware, Null and 0 are two different things.
My code already takes into account if the return is "no dataset" and IsNull( StartOdo <= 0 ) is useless, as (StartOdo <= 0) should only be true or false but never Null

I had my first if/else to verify if there is a dataset and the second takes care of what to do with the value. You will not find this out with your if statement.

I am glad to have been of any help.
Jeffrey CoachmanMIS LiasonCommented:
@ Blueice13085
I am confused here too...
Why would the starting value (startOdo) ever be null?
I will presume that the StartOdo will always be zero (set the default value for this field to zero, so that as soon as you create a record StartOdo is zero.)
Then you never have to worry about StartOdo ever being null...
...and your Zero error should go away too...

...Then you can just use :
If StartOdo = 0 Then..

But here, once again, a sample database would save all this time of you getting errors and asking us what is wrong...
Then us guessing what might be wrong..., then us guessing wrong, and you asking us what is wrong again....
Make sense?

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.