Special case for incrementing numbers (1 Viewer)

[swaroop1012]

Hi All,

I have a real big problem with auto incrementing of numbers which must increment on a special case. Its very urgent guys pls help me.

I am here with attaching the database "db1" for clear understanding.


I have around 16 wards with a code assigned for each ward. This data is tabulated in "Wardinfo" table. I designed a form "Main" which has different fields. The main concern here is the "Refno" field which should look like BH-1, Cl-1, BH-2, CA-1 etc.(The first two letters are ward codes)

For this i used the Nz and Dmax functions in after update of Refno field.

My problem here:

first give wardname as Brickhill (BH-1).
Click Saverecord and then add another record.
This time again give wardname as Brickhill (BH-2) and save it.
Next give any another wardname and save it (XX-1).
Now if u give Brickhill again, it should be (BH-3) but its coming up as BH-2.

The same thing happens if u proceed further with any other ward names.

Can anybody help me please.

Thanks in advance.

Ps: I am also attaching another small sample database "DemoAutoIncrementA2000" i got from this forum when i asked the problem before. It works well and i used the same coding and all in my database.

View attachment db1.zip

View attachment DemoAutoIncrementA2000.zip

 

[gemma-the-husky]

I WOULD NOT DO IT THIS WAY

I would actually use an autonumber as the ward id field, and also have a text field allowing you to enter any ward description you want. It's more efficient, and that way you can also change the ward name easily, if you want. with something like a hospital where there is a finite number of wards, there is no need to try and do autoincrementing, as you are. Surely everyone in the hospital is aware of the ward numbers/names

I assume you haven't got very far - if you can change it, you will find things are far easier down the line.

When you assign a patient, you need to select/enter the autonumber field value - but you ACTUALLY DO this, by selecting from a combo box that ACTUALLY SHOWS you the ward name you designated.

Also, Do not be tempted to set the wardid in subsidary tables as a lookup field - just keep it as a number, and use a coimbo box on the form.

 

[Atomic Shrimp]

For all this hassle, wouldn't it just be easier to create and manually populate a table containing a list of all the wards that exist, along with their codes?

Unless there is a need to assign brand new ward codes on a constant, ongoing basis, what's the point of developing a way to create the codes?

 

[swaroop1012]

I WOULD NOT DO IT THIS WAY

I would actually use an autonumber as the ward id field, and also have a text field allowing you to enter any ward description you want. It's more efficient, and that way you can also change the ward name easily, if you want. with something like a hospital where there is a finite number of wards, there is no need to try and do autoincrementing, as you are. Surely everyone in the hospital is aware of the ward numbers/names

I assume you haven't got very far - if you can change it, you will find things are far easier down the line.

When you assign a patient, you need to select/enter the autonumber field value - but you ACTUALLY DO this, by selecting from a combo box that ACTUALLY SHOWS you the ward name you designated.

Also, Do not be tempted to set the wardid in subsidary tables as a lookup field - just keep it as a number, and use a coimbo box on the form.

Hi Dave,

Did you had a look at the other attachment i have sent. I should get the output that way. I cant actually figure out what you are advising. Please explain it a bit eloborated.

Thanks.

 

[swaroop1012]

For all this hassle, wouldn't it just be easier to create and manually populate a table containing a list of all the wards that exist, along with their codes?

Unless there is a need to assign brand new ward codes on a constant, ongoing basis, what's the point of developing a way to create the codes?

I already populated a table with all the wards and their codes. Thats wardinfo table.

Thanks