Trying to get global value as entry on multiple tables

[David]

I don't know if I can explain this correctly. I have three seperate tables
with matching forms to enter info in three different items where the info
cannot easily be combined. What I want is a global catalog number that
automatically increases each time a new record is created by any of the
forms. So if I use form 1 to create 3 records they would get catalog numbers
1-3. Now if I open another form and make 2 more records that are recorded on
a different table i'm trying to get catalog numbers 4-5. Also to make it
tough I want that catalog number stored in the corresponding table.
Before I had a seperate catalog number for each time and used Dmax (...)+1
to update the catalog number but that could end up causing 3 different items
to have the same catalog number.
I think i'm going to need some sort of global table that keeps track of the
numbers, then somehow reference the numbers on each form and use a query to
sort it all out for the reports. One of my main questions then is how do I
have multiple forms(each with a different table) write to the global so the
numbers stay in order, then how would I display this number in a readable
fashion on each form?

Thanks,
David

 

[Jeff Boyce]

David

It may be that you're describing a "sub-typing" situation, in which you have
a "global catalog number" identifying each item, but the item could be a
dog, an ocean liner or an electronic memory chip. Yes, I can see that you'd
need separate fields/attributes for each, so I can see the three separate
tables for the data.

If you use a form for 'registering' the item, you could also 'categorize'
it. This would: 1) give you the opportunity to get the 'next' sequence
number, and 2) designate the appropriate sub-form for the category.

I can imagine a main form (*item only*) and sub-form construction. The
sub-form source would be one of your three existing (but perhaps somewhat
modified) forms.

Does that make sense in your situation?

(by the way, don't get caught in the 'using Autonumbers' trap -- they are
unique row identifiers, but are not generally suitable for human
consumption)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[David]

I think I see what you are saying. In one of my forms I have a sub form that
gets its reference number from the main form by saying for the default value
of the cell by saying "default value = Forms!MainForm!IDnumber"

So if I create a global table with a index autonumber and a globalcatalog
number that uses the Dmax (...)=1 each subform that opens using the default
value thing above so each form no matter which one will have a unique catalog
number.

I'm going to give it a try and see what happens.

Thanks,
David