Autonumber Next Increment

coolcatkelso

Registered User.
Local time
Today, 18:37
Joined
Jan 5, 2009
Messages
279
Hiya

Trying to find the next Autonumber for WorkorderID

Getting stuck on the SELECT Statement

Function ShowIdentity() As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = DBEngine(0)(0)
db.Execute "INSERT INTO Workorders (CustomerID, EmployeeID, DateofAcceptance, StartDate, RepairNotes, EndDate, SalesTaxRate, Comments, QuoteID )"
SELECT Quotes.CustomerID, Quotes.EmployeeID, Quotes.DateofQuote, Quotes.RequestedStartDate, Quotes.WorkRequested, Quotes.ExpectedEndDate, Quotes.SalesTaxRate, Quotes.Comments, Quotes.QuoteID
FROM Quotes;
Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
ShowIdentity = rs!LastID
rs.Close
Set rs = Nothing
Set db = Nothing
End Function

Doesn't matter what I do, the SELECT statement is showing RED all the time and coming up with Styntax Error

What am I doing wrong?

Am I correct in thinking, that if I have the INSERT INTO statement in here, I shouldn't needthe append query?

Cheers
CCK
________
BODY SCIENCE
 
Last edited:
Your error is because the SELECT statement is outside the quotes. If you want it on a different line, you'd need to use line continuation characters and concatenate the string together. For testing, why don't you put the whole thing on one line, inside the quotes. That will let you debug one problem at a time. If you'll recall, I believe I gave you a link to a tutorial about how to build SQL in VBA.
 
Hiya Paul

Thinking about it now, I think you did, I'm sure I have it printed somewhere.

Did you get the last update on the problems I've been having?
________
Vermont Medical Marijuana
 
Last edited:
It looked like Bob had everything under control, so I stopped paying attention. Too many chefs in the kitchen type of thing.
 
lol fair enough m8

I'm still very confused on this whole thing, Think the easiest way is to have it with the subform, and find the increment number of the new record, Get that working and everything will be fine.. Tried it manually and it works perfectly, so just need to figure out the Autonumber
________
WENDIE 99
 
Last edited:
From the looks of that, it's designed to be a stand alone public function, which would go in a standard module. You would call it from some other place in code like:

VariableName = ShowIdentity()

which would append a record and then return the ID of that record to this variable. That said, and as I said before, without a WHERE clause, that's going to append however many records are in the Quotes table.
 
Hmmm, ok

I think I'm slowly getting this now lol at least eh

You able to stick around a wee bit and guide me on this one?
________
Herbal Health
 
Last edited:
I'm actually leaving soon, but I'll be checking in later.
 
lol 3/4 way there.. feel much further away than that.. At the breaking point now lol, sat here since 4pm today, now 2:16am and still at the same point lol
________
Fx
 
Last edited:
Thats it, I give up now lol had enough.. Access has won

Doesn't matter what I do, what I read, I just can't get the autonumber bit working

Anyone feel free to play around with it and gives a shout lol

I'm off to bang my head of the wall for a few hours
________
The Cigar Boss
 

Attachments

Last edited:
Hiya paul

What you mean Password - Can't look?
________
ASS
 
Last edited:
I mean I get a password prompt when I try to unzip it. Very annoying.
 
So, anyone who has had a look been able to get the WorkorderID going?

Still banging my head off a wall here lol
________
Coach purses
 
Last edited:
Without pouring back over this thread or over your sample app, what is your core goal?
(Forget about functionality or code for the moment).
Summarise what you're trying to do.

You point to examples of adding records in code and retrieving the new Autonumber value.
Is that the sum of your desire? Are you actually attempting to do this through a form UI rather than purely adding records in code?
 
Hi Leigh

My goal on this is -

I create a Quote, click a button ( Create as workorder) and have the quote copied into the workorder with the Subform of materials

So far, I have got the append working and it sends the quote to Workorders and creates a new WorkorderID for me. I need to find that new ID and have it copied into the Quote, subform Materials so it will link up with the new workorder
________
Coach Purses
 
Last edited:
If you look in the DB attachment

It opens up with my mainform.. Click on DateofQuote linked in blue and it will take you to that Quote. Click on Create Workorder, and it copys the quote to the correct place but not the subform
________
MONTANA MEDICAL MARIJUANA DISPENSARIES
 

Attachments

Last edited:

Users who are viewing this thread

Back
Top Bottom