Generating Two Part Numbers In a Subform (1 Viewer)

[C.F.]

I want to have Access generate a number that includes: a) an Acquisition Number, provided by the parent form; and b) a sequence of numbers for Conditions – 01, 02…, 11, 12, etc.. The number, to reside in a subform, will look like “9147.01” where 9147 is the Acquisition Number and .01 is the first of several possible conditions.

Do I have to originate VBA code? Can I get a few pointers or suggestions?

 

[dkinley]

Can try something like this in the subform control's default value ...

= Me.Parent!ControlName & "." & (conditions)

where (conditions) is whatever it is the conditions you want to insert.

-dK

 

[C.F.]

Inserted the following in the default value of the control in the subform:

=[Me].[Forms]![Frm Testy for Cond Too w Tabs frm]![Acquistion Number] & ".01"

I got the error "The object doesn't contain the Automation object 'Me'.

Also, I've been cautioned about spaces in my control names -- I'll have to do better on with the next database!

 

[C.F.]

I'm very much a novice and still stuck. Would anyone give me a clue about the use of "Me", or any other tip to getting my Condition number created?

By the way, I used & ".01" to address one aspect of the numbering at a time.

 

[dkinley]

You said what you wanted was in a subform and that the information you wanted was in the main form.

= Me.Parent! is basically saying look to the parent of this subform (which is the mainform). On this parent form is a control that I want the value of.

So in the first part of the default value (of the control in the subform), it is asking for the first number you wanted. The & "." is inserting a decimal point after that number. The & (conditions) is for whatever statement you were using to place the rest of it after. I didn't know the conditions but was basically showing you where to put it once formulized.

-dK

 

[dkinley]

Just in case it is version dependant, use ...

= Forms!frmMainFormName!txtControlName

To ensure the reference is correct.

-dK

 

[dkinley]

I've put together a quick demo as one way to do this. I made some assumptions that you wanted to increment each part number based on a number assigned.

What it does is look at the last SomeOtherNumber used and increment it by .001. If there are no subrecords, it takes the SomeNumber and adds the OtherNumberStart to it.

Again, I am not sure what you wanted or the conditional expressions, etc. I did it just as a demo to hopefully get you going down the right path. Most importantly, this does not provide error-checking, validation, or anything of that nature. In other words do not trust it. It is just a simple feasibility study to show you a path to do what you want, not a finalized applet.

-dK

 

[C.F.]

Thanks dK!

I'm getting #Name?

What I want is in a subform. To make it simple (for me), I'm just trying to get the acqusition Number to work first.

I copied from your entry the code = Me.Parent!ControlName
I copied the Control Name and added brackets =Me.Parent![Acquistion Number]
and I get this =[Me].[Parent]![Acquistion Number].

When I return to the form view of the parent form, I see #Name?
where I'd hoped the Acqusition Number would be.

Why am I getting #Name? and not my Acqusition Number ?

 

[dkinley]

Not sure what you are getting - it didn't show up in the post.

Download and checkout the demo. I think it might something along the lines you are after. Modify the Me.Parent and use the full reference (in an earlier post) to make sure you are getting good data. Also, as an aside since you are working with subforms - here is a good page for those references...

http://www.mvps.org/access/forms/frm0031.htm

-dK

 

[C.F.]

dkinley - Thanks very much for the sample database! It works great! My problem is that I cannot figure out how you make it work -- how the "SomeOtherNumber" gets its information. I see where the information comes from, but I can not for the life of me figure out how it does the calculation. I've even built a replica of your sample database to help with my anaylsis to no avail. Any further suggestions?

In need of help. CF

 

[dkinley]

Ha! Took me a bit to remember it, too. I was looking at the demo again going "How did I do that?"

Okay ... if you go into the properties of the subform, you will see "=GetDefault("frmMain","txtSomeOtherNumber")" in the BeforeInsert event.
I forgot that I use this trick from time to time. I could have went into the code builder and called the function but just threw it on this line - apologies.

This calls the GetDefault function in the module "Utilities". That function then looks at the other text fields to come up with the default value for the SomeOtherNumber control.

-dK