Solved

Creating an Access Form to Track Mileage

Posted on 2003-11-12
16
859 Views
Last Modified: 2011-04-14
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
       

0
Comment
Question by:robbdfw
  • 6
  • 6
  • 4
16 Comments
 
LVL 2

Assisted Solution

by:rthomsen
rthomsen earned 75 total points
ID: 9735841
Hi

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.
0
 

Expert Comment

by:slatif
ID: 9736294
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.

0
 

Author Comment

by:robbdfw
ID: 9739994
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.
0
 
LVL 2

Expert Comment

by:rthomsen
ID: 9740222
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.
0
 

Author Comment

by:robbdfw
ID: 9740309
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 & "')"
0
 
LVL 2

Expert Comment

by:rthomsen
ID: 9740351
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.
0
 
LVL 2

Expert Comment

by:rthomsen
ID: 9740361
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
0
 

Author Comment

by:robbdfw
ID: 9743415
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
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Accepted Solution

by:
slatif earned 75 total points
ID: 9743493
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)
    dbs.Close

End If

Hope this will solve the problem.
0
 
LVL 2

Expert Comment

by:rthomsen
ID: 9743540
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
0
 

Expert Comment

by:slatif
ID: 9743659
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
0
 

Author Comment

by:robbdfw
ID: 9743955
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 ?
0
 

Expert Comment

by:slatif
ID: 9744109
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.

0
 

Author Comment

by:robbdfw
ID: 9745093
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?
0
 
LVL 2

Expert Comment

by:rthomsen
ID: 9748317
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
0
 

Author Comment

by:robbdfw
ID: 9751739
That solved the problem- setting the table property to allow zero length set to "YES".
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now