Creating an Access Form to Track Mileage

I am trying to create a form that my employees can use to track their business mileage expense.  The database consists of only a few fields:


I would like the person to be able to enter the starting date for the week in an unbound text box.  Directly under this text box are 7 rows as follows:


 Date                 mileage              Trip Reason                     Comments
dd/mm/yy (automatically incremented by 1 day based on date entered above)

I would like the 7 rows of Dates automatically populated based on the date that was initially entered in the unbound text box at the top of the form.  This would enable the employee to enter 1 week of mileage detail at one shot.

Once the employee fills all the boxes in I would like have him click a submit button that would post everything into the database, excluding any row that had "zero" mileage.

Any thoughts or suggestions would be appreciated.  In addition if anyone knows of some code that is out there that I could use please let me know.


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.


In the lost focus event of the unbound text box write something like the following

txtdate1.value = Format$(txtUnboundDate.value,"DD-MMM-YYYY")
txtdate2.value = Format$(dateadd("d",1,txtUnboundDate.value),"DD-MMM-YYYY")
txtdate3.value = Format$(dateadd("d",2,txtUnboundDate.value),"DD-MMM-YYYY")
txtdate4.value = Format$(dateadd("d",3,txtUnboundDate.value),"DD-MMM-YYYY")
txtdate5.value = Format$(dateadd("d",4,txtUnboundDate.value),"DD-MMM-YYYY")
txtdate6.value = Format$(dateadd("d",5,txtUnboundDate.value),"DD-MMM-YYYY")
txtdate7.value = Format$(dateadd("d",6,txtUnboundDate.value),"DD-MMM-YYYY")

When you tab off the first text box, that day plus the next 6 days should appear in the following text boxes.
Use following piece of code to populate the 7 text bixes.

Dim arr As String
Private Sub t0_Exit(Cancel As Integer)

arr = t0.Text
t1.Text = Format(DateAdd("d", 1, arr), "MM/DD/YYYY")
t2.Text = Format(DateAdd("d", 2, arr), "MM/DD/YYYY")
t3.Text = Format(DateAdd("d", 3, arr), "dd/mm/YY")
t4.Text = Format(DateAdd("d", 4, arr), "dd/mm/YY")
t5.Text = Format(DateAdd("d", 5, arr), "dd/mm/YY")
t6.Text = Format(DateAdd("d", 6, arr), "dd/mm/YY")
t7.Text = Format(DateAdd("d", 7, arr), "dd/mm/YY")

End Sub

Where t0 is the text box where they enter the date to begin with and t1, t2, .....t7 are seven text boxes under that.

Design a table with following fields. Date, Milage, trip reason, comment. When the employee click on the submit button check the milage value for each of seven rows and if it's not 0 then insert all four values(Date, milage, reason, comment) in your table.

robbdfwAuthor Commented:
I see how the code works for populating the date down 7 rows.

My question is what code do I need on the submit button to actually check to see if any of the values are 0? and to append the data to the database..

A little confused.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

You Need Something like this

if txtmileage1.value > 0 then
    SQL = "INSERT INTO Mileages ( TripDate, Mileage, Reason,Comments) VALUES (#" & 
              txtdate1.value & "#," & txtmileage1.value & ",'" & txtreason1.value & "','" & 
              txtcomments1.value & "')"
end if

repeat this for each row and it will add in the non zero mileages.
robbdfwAuthor Commented:
Rthomsen: I think I get it...quick question: What is the purpose of the # in this string-

(#" & txtdate1.value & "#," & txtmileage1.value & ",'" & txtreason1.value & "','" & 
              txtcomments1.value & "')"
When entering dates in a SQL statement in Access they have to be surrounded by # characters and text has be surrounded by '

When the values are replaced the actual SQL string should look something like this

INSERT INTO Mileages (TripDate, Mileage, Reason, Comments) VALUES (#13-Nov-2003#, 23,'Had to go on a trip','No Comment')

Hope this helps.
It should also be noted that an error will occur if the user enters single quotes in the text fields.

I usually replace single quotes with apostrophes
robbdfwAuthor Commented:
I have my form working great and am just having some issues with a submit button that inserts the data into my database. I keep getting compile errors.

Here is the line of code to input the data from the first row into the database:

Dim readit As String

Dim dbs As Database

Set dbs = OpenDatabase("Mileage Data.mdb")
  readit = "INSERT INTO Mileage Data ( Date, Mileage, Purpose,Comments) VALUES (#"&
              t1.value & "#," & t1mileage.value & ",'" & t1reason.value & "','" &
              t1comments.value & "')"
    dbs.Execute (readit)

End If

End Sub
Following is the correct syntax:

Dim dbs As Database

Set dbs = OpenDatabase("Mileage Data.mdb")
  readit = "INSERT INTO Mileage Data ( Date, Mileage, Purpose,Comments) VALUES (#" & _
              t1.Value & "#," & t1mileage.Value & ",'" & t1reason.Value & "','" & _
              t1comments.Value & "')"
    dbs.Execute (readit)

End If

Hope this will solve the problem.

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
Here are a couple of possible problems

1.  Because your table name has a space in it write it like this [Mileage Data]

2.  You may get an error because your field name is called Date.  Date is a function in vba that returns the current date.  Try calling the field TripDate or something like that.

rthomsen is write dont use the "date" as field name.

Here is the complete code:

Dim dbs As Database

Set dbs = CurrentDb
  readit = "INSERT INTO [Mileage Data] ( MDate, Mileage, Purpose,Comments) VALUES (#" & _
              t1.Value & "#," & t1mileage.Value & ",'" & t1reason.Value & "','" & _
              t1comments.Value & "');"
    dbs.Execute (readit)
robbdfwAuthor Commented:
Awesome. It worked.  I do however have two questions:

1) If I have an entry for mileage >0 and leave the other two fields blank (purpose and comments) the entry does not post to my database

2) After i hit submit and everything posts what code would I need to append at the end that would clear everything ?
If there is no purpose then put a space in both or either fileds. Here is how it will work:

  readit = "INSERT INTO [Mileage Data] ( MDate, Mileage, Purpose,Comments) VALUES (#" & _
              t1.Value & "#," & t1mileage.Value & ",'" & IIf(t1reason.Value <> "", t1reason.Value, " ") & "','" & _
              IIf(t1comments.Value <> "", t1comments.Value, " ") & "');"
    dbs.Execute (readit)

To clear everything use the following code for all the fileds on your form:

For Example:
t0.Value = ""
t1.Value = ""
t1comments.Value = ""

Everything should be fine now.

robbdfwAuthor Commented:
Here i go again:

If I fill all 7 rows and submit everything works fine without flaw regardless of how many times I run through it, but if I submit a form with only 3 rows for example it posts to the database but if I try to hit submit another time I get a runtime error 3134.  So close but so far.  Do I need to reset all my variables as it runs through the routine again?
Are you using Access 97?  It won't add the record if you don't enter a reason or comment because the property in the table called allow zero length is set to no.  It's kind of weird that it doesn't give an error message or anything.  Just set the property to yes and it should work.

What is the error message for runtime error 3134?  You shouldn't need to reset any variables defined in the submit code but if you have any variables that are global in the form module then you should reset them.  You would probably want to reset the text boxes in the 7 rows as well to avoid duplicate submission
robbdfwAuthor Commented:
That solved the problem- setting the table property to allow zero length set to "YES".
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.