Wrox Programmer Forums
| 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 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
  #1 (permalink)  
Old February 28th, 2008, 11:58 AM
Registered User
 
Join Date: Oct 2006
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default Incrementing Job Order Numbers

Hi,

Im trying to get one of combo boxes to update a field automatically.. There are two choices in this combo box "Gatwick" and "Woking" what i need is when one is selected the JobNo field will update itself to GWO-(the first Unused JobNo) Ive used some VBA to make atleast the GWO- or WWO- appear and it works, but i have no clue as to how to get the next unused order number to appear after it.

This is what i have so far:

Private Sub JobLocation_AfterUpdate()

If Me.JobLocation = "Gatwick" Then

Me.JobNo = "GW0-"
Else
Me.JobNo = "WWO-"
End If

End Sub

An Example of what i want would be:

GWO-0406
GWO-0407
GWO-0408
and
WWO-0624
WWO-0625
WWO-0626

Any help would be greatly appreciated.

Cheers

Marc.

  #2 (permalink)  
Old February 29th, 2008, 09:10 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

This is a dicey issue depending on how many users are in the application at one time. Normally you would do this:

Get the value first:

iJobNo = "SELECT Top 1 myTable.JobNo FROM myTable WHERE [JobLocation] = '" & Me.JobLocation & "' ORDER By JobNo DESC"

Me.JobNo = "GW0-" & iJobNo + 1

I might put this on the Before Insert event, but not on the combo's After Update event since if a user opens an existing record and flips the combo by mistake, then you will get a new job number. Unless you had a new job data entry form that had some other controls to prevent this from happening or being opened to view existing records. The Before Insert event happens once per record without controls, but the JobNo is not generally available until after the record is created and navigated from.

Did any of that help?


mmcdonal

Look it up at: http://wrox.books24x7.com
  #3 (permalink)  
Old February 29th, 2008, 12:17 PM
Registered User
 
Join Date: Oct 2006
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yeah thanks alot mate that sorted it out!

Cheers.

  #4 (permalink)  
Old February 29th, 2008, 12:34 PM
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

Oooh, also, you could put a button on the form that says to generate job no, but it is only enabled if the JobNo field is empty.

In any event, you should disabled the JobNo field so users can't chenge it or create their own.


mmcdonal

Look it up at: http://wrox.books24x7.com


Similar Threads
Thread Thread Starter Forum Replies Last Post
Incrementing the variable value Swetha XSLT 2 April 28th, 2008 04:43 PM
Incrementing numbers RubyRue ASP.NET 1.0 and 1.1 Basics 2 January 30th, 2008 12:16 AM
Incrementing attribute value Pankaj C XSLT 4 October 31st, 2007 05:40 AM
For Loop Not incrementing donrafeal7 Javascript 1 October 24th, 2006 12:24 AM
VBA Column Sort Order and numbers Jack1000 BOOK: Access 2003 VBA Programmer's Reference 1 October 5th, 2006 10:53 PM





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