I would like to track my milage.

Table
MilageId - Autonumber Primary Key
RepId - Number
Milage_Date
Milage_Start_Time
Milage_Start_Milage
Milage_Stop_Time
Milage_Stop_Milage

I have a from for start milage and a form for Stop milage. I would like to be able to have the stopMilage form lookup on the table to see if there is a record with this date and RepId and add the current information to the same record. If nothing is present (I forgot to start my milage for the day) just add a new record.

I have written this code.
I have an error on the last line. It says no current record and stops on the following line.
Me.Bookmark = Me.RecordsetClone.Bookmark

I would appreciate any assistance.

Private Sub Combo7_AfterUpdate()

Dim strSQL As String
Dim lngKey As Long
Dim rktest As Long
lngKey = Nz(DLookup("milageid", "milage", "RepID = " & Me.Combo7 & " AND [Milage_Date] = #" & DATE & "#"), 0)
rktest = Nz(DLookup(milageId, milage, RepID), 0)

If lngKey <> 0 Then
Me.Undo
Me.RecordsetClone.FindFirst "[milageId] = " & lngKey
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

End Sub

Thanks in advance for the help
Rkellogg

I would open the form already filtered by using a Rep combo box, and a date combo box on a main form, and allowing the rep to look up by date to open a start mileage record, or start a new record from the main form. If the rep won't remember if they entered their start mileage for the date, then put a function on the On No Data event of the form to open to a new record. I would not use a look up on the actual form for this purpose.

Does that help?

mmcdonal

Thanks for the response.

I do have a couple more questions.
Where would you put the rep combo box? I have the milage forms opened from the switchboard.

On the second option... Did you mean to have it do the lookup when it opened the form?

Sorry to be so thick headed... I am trying to catch on to this stuff.

rkellogg

No prob.

I don't use the Access switchboards. I use my own forms and set them to open when the app is opened.

Anyway, you have a tabel somewhere with all the reps names. Create a combo box on your switchboard or a seperate form that looks up the LastName,FirstName columns in that table, and grabs the PK. Then create a button to open the mileage form, and pass the parameter (PK) from the combo box. If the form is built on a query, then you can have the query take the parameter from the combo box on the form.

For the first example above:

'----------------
Dim iPK As Integer
Dim sDocName As String
Dim sLink As String

iPK = Me.ComboBoxName
sLink = "[RepID] = " & iPK
sDocName = "frmYourFormName"

DOCmd.OpenForm sDocName, , , sLink
'------------

sLink should be in the WHERE clause area of the OpenForm statement. I forget which comma it comes after but VBA will prompt you.

If you use two combo boxes, one for the rep and one for the date, you can have the date combo box populate itself based on the rep (so that only dates where the rep has started a form will show up), and use the Criteria line in your query designer to take the values like this:

Rep Column:
[Forms]![frmYourFormName].[cboYourRepComboBoxName]

Date Column:
[Forms]![frmYourFormName].[cboDateComboBox]

The form will open showing only the record (or records) for the rep for the date.

Does this help?


mmcdonal

I am working with your first option. I have created a form called MilageSelect and put a combo box to select the rep I put a button on the form to open the Milage form. I added your code to the button and tested it. The milage form opens but when I go to put my milage in there is not a # in the repID part of the form. I have a filed on the milage table that can store the RepID.

How can I get it to fill in the REP Id automatically when opening the form?

After I get it figured out I will have the REP id display the Rep name but have it grayed out so it can't be changed.

Rkellogg

I don't understand no # in the RepID field.

Are you opening on an existing record, or a new record?

If you are opening on a new record, then on the On No Data event, pass the repID from the combo box to the new record. Take it from the form:

Me.RepID = [Forms]![frmYourForm].[cboYourComboBox]

I think that will work.

mmcdonal