Record Number that updates automatically

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I hope I can explain this clearly.

I have a DB that records details of properties. A master table
(tbl_SRMASTER) records the Portfolio certificate (PK = SRIDX) and a secondary
table (tbl_SRDETAILS) records the detailed information regarding each
property within that portfolio (double PK = SRIDX & SRLINE). One portfolio
can have a number of properties.

I have set up a form - tbl_SRMASTER is behind the main form and
tbl_SRDETAILS becomes the subform. The form would look something like this:

SRIDX = SR001 (on master form)

(Detail on subform)
SRLINE Property
1 House #1
2 House #2
3 Office #1
4 Office #2
etc.

Okay, after all that, my query is this: If I delete a line, I want the
number in the next line to replace the line that I deleted, ie. if using the
example data above, I deleted Line 2, I want the data to look like this:

SRLINE Property
1 House #1
2 Office #1
3 Office #2

At the moment if I delete a line, I have to manually renumber each line
after that deleted one to get a sequential list, and that is not feasible,
and obviously I would want to automate this, if possible.

I thought about using DMax, but that wouldn't work because tbl_SRDETAILS
would store a number of the same SRLINEs depending on the SRIDX. SRLINE is
used mainly as a sorting mechanism for each property.

Can someone help me with this? Or is there another way entirely to do
this????

Thanks in advance
Winsa
 
This question comes up a lot. In reality, there is no point in what you are
doing. Your primary keys should be of Autonumber data type. It will handle
generating a new number for your. It will not, however, fill in a gap. It
is not necessary to do so. You primary key values should not be used for
anything other than maintaining table relationships.
It is also unusual from a business perspective to indentify entities with
sequential number that are subject to change. If you delete #35 and create a
new #35, then later, how do you know historically which #35 your are
referencing?
From a technical perspective, this is a bad idea for at least two reasons.
First, every time you delete a record, you would have to run through both
tables and update every record in them. Also, it appears you are using this
to relate the two tables. So, it compound the problem because now you have
to ensure that all the child records for each parent record get updated
properly.
My suggestion is you don't implement this plan. It is a really bad idea.
 
Okay, after all that, my query is this: If I delete a line, I want the
number in the next line to replace the line that I deleted, ie. if using the
example data above, I deleted Line 2, I want the data to look like this:
Click to expand...

Klatuu's right: this is a Bad Idea.

Think about it. You have an agent, two customers, a partner, and four
prospects all of whom are *really, really* interested in property
number 3. They have that number in their minds, on Post-It Notes, on
printouts, in emails.

You delete a row and suddenly it's Property number 2.

How do you correct all those printouts, emails, Post-It notes, and
neurons?

John W. Vinson[MVP]
 
Hi John & Klatuu

Thanks for your responses. I understand that what I was asking can be a bad
idea.

The situation is a bit simpler than you think though. I don't have any need
to track history as I'm only recording properties for insurance purposes and
therefore only need whatever property is current. The users don't refer the
properties by their number, it is more just a mechanism for me to sort them.

Unfortunately I'm working with a hand-me down database, and as much as I
feared this, I think I'm going to have to go back to basics and redesign it
from scratch.

Thanks again for your input.

Regards
Winsa
 
The situation is a bit simpler than you think though. I don't have any need
to track history as I'm only recording properties for insurance purposes and
therefore only need whatever property is current. The users don't refer the
properties by their number, it is more just a mechanism for me to sort them.
Click to expand...

Sort by data in the table.

You can't even GENERATE this number without sorting by it first!

John W. Vinson[MVP]
 
Back
Top