Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 2nd, 2006, 06:17 PM
Registered User
 
Join Date: Sep 2006
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default Me.Bookmark = Me.RecordsetClone.Bookmark

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
 
Old September 5th, 2006, 07:14 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old September 6th, 2006, 11:17 PM
Registered User
 
Join Date: Sep 2006
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old September 7th, 2006, 06:26 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old September 7th, 2006, 11:42 PM
Registered User
 
Join Date: Sep 2006
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old September 8th, 2006, 06:28 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Automate bookmark creation hogg Word VBA 1 November 12th, 2008 04:35 AM
Help with bookmark code on IE7 bvis Java Basics 0 December 16th, 2006 10:15 AM
Bookmark Style Terry Joseph Migliorino CSS Cascading Style Sheets 2 February 10th, 2006 07:52 AM
iframe bookmark problem reberman Javascript How-To 0 December 20th, 2005 12:38 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.