Access Question

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

jmo422

Thread Starter
Joined
Dec 30, 2007
Messages
34
I have a table with various information about work orders done in our shop.

When I am adding a new job, I would like access to look at the last record or highest work order number and add 1 to the number for the next job, I thought you could possibly do a query and put +1 in the criteria, but what type of query would it be. Or maybe I am totally off.

Thanks

Jay Morlan
 
Joined
Apr 7, 2007
Messages
439
Just add the field data type in your table definition of AutoNumber, this automatically increments by one; call it something like OrderID.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
As MRdNK says the easiest way is to use the built in Autonumber feature, it does have some drawbacks though.
If you create and then cancel a new record, or delete the last record the Autonumber can't go back and use that number again, so you have blanks in your Work Order Numbers. If you are not allowed Blanks in the Work Order sequence you have to resort to VBA Code to increment the number instead.
 

jmo422

Thread Starter
Joined
Dec 30, 2007
Messages
34
I dont have any experience with VBA Code, but willing to try. How would I go about using VBA to get the numbers to move in increments of one.

Thanks

Jay Morlan
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Jay, what form do you want the Work Order Number to take, is it a special Format, do you want to show preceding zeroes?
i.e. 0001, 0002 to 9999
or
2008 - 0001 to 2008 - 9999
changing to
2009 - 0001 to 2009 - 9999
next year.
 

jmo422

Thread Starter
Joined
Dec 30, 2007
Messages
34
I do not have to have the date in front of the work order, currently we use a four digit number such as 7468, which is the number that we are currently on as the weekend begins. I just would like the work order field in the table and form to automatically go to 7469 and so on.

Thanks

Jay Morlan

Also while I am on here, I have yet another problem, in my Work Orders Table I have two hyperlinks that take me directly to the pictures of the unit that correspond with the work order and I have another hyperlink that takes me to the quote that has been saved for the work order. The picutures being located in the S:\Work Order Pictures\Work Order # Folder and the Quotes being saved under F:\2008 Quotes\OCT 2008\Work Order #. How can I get these two hyperlink fields to automatically fill in the first part of the file location and then read off the work order number and fill that part in as well.

Thanks

Jay Morlan \
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Jay, I will be able to do something for you on the VBA for the automatic incrementing of the PO number, possibly by Monday.
I do not use Hyperlinks, I have a Field on the Form that shows the photo or Document which when double clicked opens the actual Photo or Document.
I use a VBA "Browser" to browse to the required file.

Are you in the USA?
 

jmo422

Thread Starter
Joined
Dec 30, 2007
Messages
34
I am located in the US

I appreciate you helping me with these questions.

Thanks

Jay Morlan
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Jay, what is the actual Name of your PO Number Field in the table and on the Form?
I also need the name of the Table as well.
It would be easier still if you could take a Copy of your database, delete all the records and then Compact & Repair it, Zip it and post it on here as an Attachment.
 

jmo422

Thread Starter
Joined
Dec 30, 2007
Messages
34
Field Name- Work Order
Table- Lead Time Dates
Form - Lead Time Dates
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Jay, try this in the After Update Event of the First Field on the Form that is not the Work Order Field, the Work Order Field Property "Enabled" should be set to "No".

Dim rs As Object, sql As String, code As String, last As Integer
sql = "SELECT Lead_Time_Dates.* " & _
"FROM Lead_Time_Dates "
Set rs = CurrentDb.OpenRecordset(sql)
rs.MoveLast
last = rs![Work Order]
rs.Close
Set rs = Nothing
Me.Work_Order = last + 1

Note the Underscores "_" in the Table's Name, you will need to add those to your Table's name as well, VBA created SQL does not like Table names with spaces in them.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top