[Ladyk18]

So I am trying to put together a form in Microsoft Access that will autopopulate a demobilization number immediately before saving the record. I am no expert in Access and am not sure where to physically put my coding.
Here is the coding I am using... the fields are tailored to what I have in my working copy.

Me.demobgenerator = Nz(DMax("[Demob Number]", "Master Database"), 0) + 1
DoCmd.RunCommand acCmdSaveRecord

So where should I put this coding / is it correct for what I want? See below for more details...

The form based on my query will pick up records that have the status set to demobbing and transfers the selected fields from the datasheet to the appropriate boxes on the form. I would like the demobilization number txt box to automatically recognize the incident number (field in the datasheet) and the apply the next available number (starting at 1 to say 00000) for that demobilization at that incident. (I would also like to have '-D-' separating the info.)
(Example : Incident-D-001, or TIM001-D-001 but then for another incidient TIM002-D-001, TIM002-D-002, TIM002-D-003... etc.)

[ScottGem]

Please see my blog on Sequential Numbering. https://scottgem.wordpress.com/2009/...ial-numbering/ It discusses all these issues and more.

If you have any questions after reviewing the blog please fee free to ask.

[Ladyk18]

Hi Scott,
I did indeed start my search with reading your blog however, I need clarification as to where the coding would physically go in the VBA? See attached photo... You'll notice I have the coding between savebutton_Click and Private Subsavebutton_Click_Err...

Attachment 47292

[Ladyk18]

Forgot to mention that I also have the identifier expression already in place on the txtbox that should display the demobilization number.

[ScottGem]

The problem here is that when you generate the number is up to you. The key issues is that you need to commit the record immediately after generating the number to prevent duplication. So you don't want to generate the number until data entry is just about complete.

I notice that you have a demobgenerator code stub. So it looks like you were going to put it behind a button. Now you have it when you click a text box. Not sure why. Again, you can run the code anytime that makes sense to you. I like to use a button to generate the code and prompt the user that this will save the record as currently entered so they can cancel if it is too soon.

Does that help?

[Ladyk18]

Ok I see what you are saying... the code was sitting under a txt box... I have now moved it under the savebutton_Click().

However when I tried to test out the save button, it says I need to debug my code.Attachment 47293

[ScottGem]

What was the exact error message? Do you have a control on your form named savebutton? Do you have a field in your table named Demob Number?

[Ladyk18]

The error message is :
Run-time error '-2147352567 (80020009)':
You can't assign a value to this object.

savebutton is the name of the command button to save the record. And yes I have a field in my master table named Demob Number.

[ScottGem]

And you do have a textbox control named demobgenerator? The error message is indicating it may not be a control with a value property.

[Ladyk18]

That's correct. The textbox in which I would like the number to generate in is called demobgenerator

[ScottGem]

It should work then. Can you attach a stripped down version of your app (zip it first). And I'll take a look.

[Ladyk18]

Resource_Tracking_TableEntry TEST - Copy.zip

[ScottGem]

Ahh I found the problem. The control you were trying to write to has an expression as it's ControlSource. That's why you can't write to it. What you need to do is have a hidden control on the form bound to the Demob Number field and then reference that control in your expression.

I'm attaching your db back with those changes.

[Ladyk18]

Hi again,

Not sure I understood what you said. For some reason the Zip file won't let me open it with the changes you made.
I don't think I did it correctly because now I am receiving a different message when I hit the saverecord button: #Type!

So what I did was make a hidden txtbox on the form that was bound to the demob number field in the datasheet. Then I changed the control source on the demob generator txtbox to reflect the first change. So ="-D-" & Format([txtdemob number],"00000") which is in the location of where my number would generate/appear on the form.
Did I miss something?
Thanks for your patience on this by the way, I really appreciate your help!

[ScottGem]

That is basically what I did. Does it work?

[Ladyk18]

No... The txtbox just says #Type! And nothing happens now when I click the saverecord button.

[ScottGem]

You need to change the code behind the SaveRecord button to populate the hidden control you added.

[Ladyk18]

So I changed the code behind the SaveRecord button to populate the new hidden control I added but I am still getting a the #Type! Error
as well as Run-time error'-2147352567 (80020009)': You can't assign a value to this object when I hit the saverecord button.

Image attached is the change I made to the code to populate hidden control in VBA formatAttachment 47306

[ScottGem]

You can't assign a value to the control demodgenerator because you have an expression as the controlsource. You have to set up a new control with a controlsource of the Demod Number field in the table. You then need to change the SaveRecord code to assign the next number to that new control.

[ScottGem]

Not sure why you had a problem with the zip file so I uploaded the file to my SkyDirve. You can download using this link:

http://1drv.ms/1JzNn4j