Autonumber based on a field?

[kbrooks]

I set up a database to register patients when our main patient accounting system goes down for whatever reason. This was my first db ever so it's really thrown together and ugly, and I'm planning to do it over from scratch this week, before our system goes down for planned maintenance next week.

Each patient is assigned an account number (autonumber) in a certain range. (I got it to start at a certain number, instead of 1). The problem is, there should be 2 ranges. One range for inpatients, and one range for outpatients. I would have a field set up for the user to select inpatient or outpatient, and then (hopefully) have the account # choose from the correct range.

Is this even possible? Or am I living in a dream world? On the current db, I have 2 forms set up....one for inpatient and one for outpatient, with a different field (account# and ipaccount#) on each, to get around that. But I'd like to have it on 1 if at all possible.

TIA

 

[RichMorrison]

It's probably possible but why do it ?

Do you expect all the users of this app to say "Aha, this number begins with 5 so this person is an inpatient" ?

Just add a field that indicates inpatient/outpatient status.

RichM

 

[kbrooks]

In our patient accounting system, the system assigns from a range of account numbers based on ip/op status. Users as well as nurses are accustomed to recognizing the status by the account number, since that prints on the label and not the word "Inpatient" or "Outpatient".

Once our main system is back up, we then have to turn around and enter all the patients that were recorded in this Access database. This is a temporary db, and doesn't store information long term. And patients have to be entered with the account# they are given originally, since that number has been given to various departments for charge entry, order results, etc.

 

[RichMorrison]

Oh all right then.

So it sounds like you want to present 2 lists to the users; inpatient numbers and outpatient numbers.

Right ?

So we can visualize a form with a yes/no control and a listbox. When the user toggles the yes/no control, you would like to rebuild a list of patient numbers.

Then the user clicks on a number from the list and some form appears with detail patient info.

OK so far ?

Then what is the range of inpatient account numbers versus outpatient account numbers ? If you know that inpatients are 5000 or more, for example, then you can make 2 queries. QueryIn will select all account numbers greater than 4999. QueryOut will select all less that 5000.

Back to the form:
1) You need an "Onchange" event sub for the yes/no control. In the event sub you would set the RecordSource of the listbox to QueryIn or QueryOut and Requery the list box.
2) You need an "AfterUpdate" event sub for the listbox to open some form with the appropriate patient data.

HTH,
RichM

 

[kbrooks]

Well not exactly, unless I'm misunderstanding you. I want the db to ASSIGN the number, and not the user selecting one from a list. Currently I have it autonumbering starting at 800000, and the inpatient would start at something like 200300600.

The tricky thing is, the number range will change everytime we get ready to use this db, as we keep assigning numbers in our main system while this db sits idle. So when we're preparing for downtime on the main system, I'd have to find the last used number and change the range in the db.

I'm rethinking this whole thing now because it's sounding too darn complicated for what it's intended for....a temporary storage of patient data until the main system is up. I thank you for your help, but I'm going to see if I can't think out an easier way somehow.....

 

[RichMorrison]

<<
I'm rethinking this whole thing now because it's sounding too darn complicated for what it's intended for....
>>

Amen.

BTW, the "Access Developer's Handbook" has a section on custom autonumbers. You might find it helpful.

RichM