Place a 'calculated' value in each new record.

[Robeen]

Hi,



I'm using Access 2010.

An Order Entry table that users will be adding records to has an Order Number field that consists of the 4-digit year concatenated with a 4-digit sequential number.

For example, the first Order entered this year would be Order Number 20110001, the second number - 20110002 etc.

How do I get the next Order Number in the sequence to populate the Order Number Box whenever the users start entering a new order - and set the focus to the next field on the Order Entry Form?

Thanks,

Robeen

[pbaldy]

See if this helps

http://www.baldyweb.com/CustomAutonumber.htm

[Robeen]

Hi Paul,

Thanks for the pointer - it was definitely helpful.

What I still need is how to get my new [incremented] number into the Order Number field of the Order Entry Form when a new record is created.

That's the part I'm having trouble with.

There's a button on the form for 'New Order'. I want to put this whole 'procedure' into that button.

I'd appreciate help with that!

Thanks,

Robeen

[pbaldy]

Most common is probably either behind a "Save" button or in the after insert event of the form.

[Robeen]

I know where to put the procedure - I just don't know what to put IN it - and how.


I'm more used to putting VB or VBA behind a button. With Access 2010 - I go into the 'On Click' Event and it opens a Macro window and that's throwing me.

Any tips on what I do once I'm IN that 'Macro window'? Do I create a function in a Module and then do a 'Run Code' in the Macro Window?

That's the part I'm struggling with.

Thanks!

[pbaldy]

I haven't worked a lot with 2010, but you should be able to force it to VBA like this:

http://www.baldyweb.com/FirstVBA.htm

As to what goes there:

Me.TextboxYouWantTheValueIn = YourCalculationHere

[Robeen]

Hi Paul,
I was not able to force it to VBA the way you suggested [by typing in '[Event Procedure]' in the OnClick Event in the Properties Sheet. Access said it couldn't find the object 'Event Procedure' - and suggested typing in either a public function that was in a module or it said this:
"Make sure that the function is either:
Defined in the code for the form or report.
- or -
A public function that is in a module (not a class module)."
So I'm guessing there should be a way to get into the code for the Form - but I still haven't figured out how.
I'd appreciate any suggestions, educated guesses, wild conjecturing, clutching at straws . . .

Any help will be appreciated!!

Thanks!

[Robeen]

Hi Paul & others.
I realize that I forgot to mention one important detail in my original question.
I created a Web Database.
You cannot use VBA in a web database in Access 2010. You have to use Macros.
Here's the explanation I found in Help this morning:
"Access Web Applications Access Web Applications are a new feature of Access 2010 with which you can publish an application to a Microsoft SharePoint server that is running Access Services. This enables other people to use your database via a standard Internet browser, instead of having to have Access installed on their computers. However, because VBA code is incompatible with the Web publishing feature, if you plan to publish your application as an Access Web Application, you must use only macros to perform programming tasks."
Sorry I didn't mention that.

I'm not sure how this will impact my progress or if I'll be able to do what I'm planning using macros.

[pbaldy]

Sorry, I should have thought of that. I knew there was no VBA allowed in Web databases. You should be able to do it with a macro, using the SetValue action I think. I don't have 2010 loaded here, but I know the macro window is a lot different, and more powerful.

[Robeen]

I know I'm straying off topic a little but I'll ask anyway.

My query [QueryDesigner] in Access 2010:
SELECT Max([BoxNumber]) AS HighNumber
FROM Master;
DOES return the highest BoxNumber from the table.

When I do this in VBA I get the first BoxNumber in the table - rather than the HIGHEST value:
. . .

 
SQL = "SELECT Max(BoxNumber) AS BigBox FROM [Master] GROUP BY BoxNumber"
 
With dbsA.OpenRecordset(SQL)
If Not .EOF Then
Box = !BigBox
End If
.Close
End With
 
MsgBox "In AddNextBoxNumber(). Highest Box Number = " & Box

Any ideas what's going wrong?
Thanks,
Robeen

[pbaldy]

Sure, you added the GROUP BY clause in the VBA code. That will get you the highest box number per box number; in other words you'll get every box number. Drop that clause and use the SQL from your test.

[Robeen]

Somebody dope slap me.
I had the SQL correct to begin with [just like from my access query designer] but my variable 'Box' was defined as 'Number' and I kept getting an 'Overflow'. Well - the largest number in my table is 2011003 - and I guess 'Number' wasn't able to get that.
I changed it to double and it came back with the highest BoxNumber from the table.
Thanks again Paul!
Really appreciate your help.

[pbaldy]

Happy to help! You probably had it as Integer, which can't handle a number that big. You probably want Long Integer (Long in VBA), which can handle up to 2,147,483,647.