Is it possible to increment a number form the same field from the previous Record?

[evanark]

I have this scenrio, I have drop down list that inserts the number 1 in a subform in datasheet view, the field name is Base Number, is there way to auto increment the previous base number by one in the next record of the datasheet?


Base number
1
Base number
2

This is not going to be used as an autonumber, each time a new account is created, foe instance the dropdown will up a 1 in the first record of the subform field Base number.

 

[gmatriix]

Hello,

I have use this to give sequencial numbers

Expr1: DCount("id","yourtable or query","id <= " & [id])

Hope this helps

 

[RainLover]

What I use is find DMax then add One.

There are a lot of examples here so just search "DMax"

 

[evanark]

Here is example of my form, I have entered the Base number manually, I would like them to be populate automatically. Also this a picture of one record. When I add another new record, I want the Base Number to start at zero on a new system configuations.

The purpose of this because system configuration Primary key in the system configtable is a autonumber, the calculations dont computer right since there will be spaces between the systemconfigID number. I can pull a record old record and update it with an addon system but the systemConfigID will be out of order. The hop spacing is taking the current HopID and subtracting from the previous HopID. This is the major functionality that is required for this project. Manually enter of the Base number has caused the hop spacing on some records to give the incorrect calculation. I figure is this is automated the user can get a accurate calculation. I hope this help, I am not good at explaining things.

 

[bob fitz]

evanark:

Here is example of my form.....

I can't see any attachment to your post.

 

[RainLover]

Here is example of my form, I have entered the Base number manually, I would like them to be populate automatically. Also this a picture of one record. When I add another new record, I want the Base Number to start at zero on a new system configuations.

The purpose of this because system configuration Primary key in the system configtable is a autonumber, the calculations dont computer right since there will be spaces between the systemconfigID number. I can pull a record old record and update it with an addon system but the systemConfigID will be out of order. The hop spacing is taking the current HopID and subtracting from the previous HopID. This is the major functionality that is required for this project. Manually enter of the Base number has caused the hop spacing on some records to give the incorrect calculation. I figure is this is automated the user can get a accurate calculation. I hope this help, I am not good at explaining things.

The AutoNumber Primary key is there to uniquely identify a record. That is its sole purpose. Do not use it for calculations and never show it to a user. It is for the Developer's use only.

Did you do a search on DMax. DMax finds the largest number. When you have that you can add one to get the next number in the sequence. You can also start at any number and increase its value by any amount. 1, 2, 10. 1857. Anything you want.

What did you find in your searches.

 

[evanark]

I just reviewed the code and Ill give it a try. I have a question, can you restart the count when you reach a certain number. each account can have up to 5 possible systems. if you start a new account and add a system, can you start the number from 1 again? or so if I go back and edit a record and add a new system, will the Dmax continue from the number I enter in the base number field? Sorry I am fairly new to vba and DMax. My goal is to keep the hop spacing for each system in a account.

 

[John Big Booty]

The sample here demonstrates one method.

It has separate runs for each office and resets the run each day.

 

[evanark]

The DMax works however, my results are not what I want. I created a school(account) and added 3 systems to the school the saved the record. My hop spacing calculated correctly based of the value of the Hop ID from previous system. The first system in a new school shoud not return a calculation as it is the first system in the school. I started a new school and added 1 system, that system calculated the hop spacing from the last system in the first schools I created. My question is how can I contain the hop spacing within its own schools system set? If i go back and add a another system to a existing school, how can it calculate the hop spacing from the previous system hop ID within that school. I know this sounds confusing, this is what the project sponsor wants the DB to do and I am not sure if thats possible and if it is I am not sure what approach to take to achevie the goal.

This how I calculate the hop spacing
sysHopSpacing = Abs([sysHop1] - DLookup("[sysHop1]", "tblSystemConfiguration", "[sysBaseNumber]=Forms![frmSearchSchools]![frmSubSystem]![sysBaseNumber] - 1 "))

And my table set is attached

 

[RainLover]

It would help a lot if you fixed your table design.

You need to search the web for tuts and samples on database design as a matter of urgency.

 

[evanark]

Disregard the hopping pattern table, its just a reference table. There are a set number of values in that table. I just use a Dlookup for the hop1 based on the network number entered by the user, which auto populates the sysHop1 in the systemconfigurations table. The SystemConfiguration table contains system information. This is for our product, each base station has to have a network number, lockout key, security code and can have up 5 belt packs thus the baseslot 1-5. I combine the network number and lockout key for use to check for duplicate combinations. The Account table houses the school name, its division, conference, system type, we have 3 product lines, number of belt packs and how many are shared. The user table is simply used for login and security levels for each user. I am not sure If I can break the tables up to be in 3NF. I looked at some tutorials but in my situation, I think this is a good possible structure. I am still a beginner though and you guys might see something that I am missing. I welcome any suggestions and criticisms, anything that can help me be a better database designer.

 

[evanark]

I have been think about this problem for a while and I am stuck, can you peform this test by nesting Dlookup so that the hop spacing is calculated with the accountIDs that match? The Accoun tablet and the system table are linked by accAccountID and sysAccountID. Can I base my calculation to stay within the same records as long as both AccountID match? So the only way the calculations are performed with out subtracting form the previous recordset? Sorry to ask so many questions, but I am under time pressure.

I tried this but I think it is setup wrong

sysHopSpacing = Abs([sysHop1] - DLookup("[sysHop1]", "tblSystemConfiguration", "[sysBaseNumber]=Forms![frmTempestCoordination]![frmSubSystem]![sysBaseNumber] - 1 ")) & DLookup("[sysBaseNumber]", _
"tblSystemConfiguration", "[sysAccountID]=Forms![frmTempestCoordination]![accAccountID]")

 

[RainLover]

Evanark,

You really need to work on your tables.

If you don't do that then you will have one coding problem after another.

There is an article on Normalisation in the Link attached to my signature. Suggest you read it a few times.

Good luck with the project.