Incrementing field in forms only when opened

[CINQUEFOIL22]

Here is my question, I hope I can explain it so you understand.
I have a table in my database "TblPurchaseOrder". From that I created a form "FrmPurchaseOrder" that opens in datasheet view in add only mode for people to enter in the product to be ordered on that purchase order. I currently have a code in there:
Me.PONumber = Nz(DMax("Val([PONumber])", "TblPurchaseOrder")) + 1

It works wonderfully, when I click that field the number increases. But here is my problem, I want the increase, but only on each new open. Our purchase orders can have multiple lines. Each time the form is opened it would be for a new purchase order and I need that purchase order number to stay the same for each line during that opening of the form.

Can anyone help me?

 

[vbaInet]

If the code is in the Load event of your form it will only run once.

 

[CINQUEFOIL22]

I love this forum and how quickly everyone answers. I did what you said and sure enough it did load the way I wanted. Now here is my next question. Lets say I fill in the first line's product and it populates 712 for my purchase order number, is it possible to now make my second line for that purchase order field to reflect 712 as well?

 

[vbaInet]

Save it into a variable and you can use it as many times as you wish.

 

[CINQUEFOIL22]

I have to be honest I am not sure what you are meaning by saving it into a variable. Could you explain?

 

[vbaInet]

You have the code in the Load event, save that 712 value into a variable of type Long and you can use it for your other purchases.

 

[CINQUEFOIL22]

I am sorry to be a pain, but I am self taught and questions asked when I can't figure something out in Access. I am not sure where this "variable" spot is that you are talking about. But if I am understanding you correctly it is going to put 712 there all the time. But each time I open the form the PO number will increase by 1. So if I am on purchase order number 720, I don't want the next line populating 712. I want it to copy the first line of 720.

 

[vbaInet]

When you close the form the value of the variable automatically gets discarded. You see now?

 

[CINQUEFOIL22]

I think I see...But I am still lost on where I put the variable. Is it a formula? And do I need to put it in each time I open the form?

 

[vbaInet]

Dim the variable in the Declarations section of the form module. Just below Options Compare Database

 

[CINQUEFOIL22]

I am so sorry, but I don't understand what you are saying... I am trying but I am confused...

 

[vbaInet]

Are you saying you don't know how to declare a variable?

 

[CINQUEFOIL22]

Pretty much, yup. I have never done that before so it is pretty new to me.

 

[Brianwarnock]

As Vba said
Dim the variable in the Declarations section of the form module. Just below Options Compare Database

ie

Options Compare Database

Public newPONumber as long

then in the code

newPoNumber = Nz(DMax("Val([PONumber])", "TblPurchaseOrder")) + 1

Me.PONumber = newPONumber


the newPONumber was declared as Public and can be used anywhere in your project

Brian

 

[CINQUEFOIL22]

Ready to throw my flippin' pc through the wall, even though I know it isn't the dumb one...It's me...

I think I did what you told me to do. I created a class module. The left drop down box said general and the right said declarations.

This is what I put in.

Option Compare Database
Public newPONumber As Long
newPONumber = Nz(DMax("Val([PONumber])", "TblPurchaseOrder")) + 1
Me.PONumber = newPONumber

I open the form, and I get this error:
Compile Error:
Invalid Outside Procedure


What the heck am I doing wrong?

 

[vbaInet]

Ready to throw my flippin' pc through the wall,

Not the wisest thing to do. You will end up buying a new one

We didn't mention creating a class module. We're talking about the same vba code page where the LOAD event is located.

 

[Brianwarnock]

perhaps I wasn't clear but what you will end up with is something like this

Option Compare Database
Public newPONumber As Long

Private Sub Form_Load()

newPONumber = Nz(DMax("Val([PONumber])", "TblPurchaseOrder")) + 1
Me.PONumber = newPONumber

End Sub

Brian

 

[Brianwarnock]

Never having done this I decided on a little exrcise to ensure that I was not talking rubbish, I ended up with this

Option Compare Database
Public newponumber As Long
Private Sub Form_Load()
newponumber = Nz(DMax("Val([PONumber])", "Table1")) + 1
Me.ponumber = newponumber   'This can be ommitted and the user click in the field

End Sub

Private Sub ponumber_Click()
Me.ponumber = newponumber
End Sub

Thus for subsequent rows the number is entered when the user clicks in the field

hope this helps

Brian

 

[vbaInet]

Just thought the variable can just be Private or Dim since it's only needed in the module. Don't you think Brian?

 

[Brianwarnock]

no it cannot pick it up in the on click event as well if it is not Public

Brian