Link to home
Start Free TrialLog in
Avatar of R Davignon
R DavignonFlag for United States of America

asked on

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:

Date
Mileage
TripReason
Comments

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:

ENTER DaTE (UNBOUND TEXT BOX)

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

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.

Robbdfw
       

SOLUTION
Avatar of rthomsen
rthomsen

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of slatif
slatif

Use following piece of code to populate the 7 text bixes.

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

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

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.

Avatar of R Davignon

ASKER

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.
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 & "')"
    currentdb.Execute(SQL)
end if

repeat this for each row and it will add in the non zero mileages.
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
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)
    dbs.Close

End If

End Sub
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start 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
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)
    dbs.Close
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)
    dbs.Close

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.

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
That solved the problem- setting the table property to allow zero length set to "YES".