Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 2nd, 2006, 06:17 PM
Registered User
 
Join Date: Sep 2006
Location: Nampa, ID, USA.
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
Reply With Quote
  #2 (permalink)  
Old September 5th, 2006, 07:14 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
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
Reply With Quote
  #3 (permalink)  
Old September 6th, 2006, 11:17 PM
Registered User
 
Join Date: Sep 2006
Location: Nampa, ID, USA.
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
Reply With Quote
  #4 (permalink)  
Old September 7th, 2006, 06:26 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
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
Reply With Quote
  #5 (permalink)  
Old September 7th, 2006, 11:42 PM
Registered User
 
Join Date: Sep 2006
Location: Nampa, ID, USA.
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
Reply With Quote
  #6 (permalink)  
Old September 8th, 2006, 06:28 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 01:32 PM.


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