Find Maximum Value of a Field and Add 1

When I create a new record in a form, I need the ID field on the form to automatically be the highest number found in the ID field plus 1. The table name is tblContacts. Is there a formula for this and where do I put it? Autonumber is not an option

Sponsored Links:

Related Topics

Add 1 to a field when another filed reach a certain value.
I was wondering if anyone could help me with my problem. I'm new to MS Access 2003 and I've searched for an answer many times and still found none.

I made a form which is bounded to a table. The form or table has 2 fields named "points" and "free_time". The field "points" has a validation rule of <=300, which means - that field should have a maximum input value of up to 300 only. Now, what I was aiming for is to find a formula or code that when the field "points" reaches its maximum value which is 300, it will automatically add 1 (one) to another field called "free_time".

In simple terms 300 points = 1 free time, but I don't know how to make that happen in my form.

And if it's possible, after adding 1 to free_time, the field points would go back to zero since it cannot exceed the value

Bolding a record in a field that is the max
Pretty sure this is not possible but I thought I would ask. Is there a way to bold one record in a field in a query? Instead of having the maximum show up in the totals row, I would like to bold the maximum value in a column. Is this possible? I there some function out there that bolds text?

How To Caluculte the maximum values from the table and assingn to the textbox
For reading this thread.
I am a beginer of access programming.

I have a problem in doing a task with my form.

Actually I have a button to add a new record which opens a new form there I enter the values to the record. But when I press the Addnew record button I want to calculate the maximum of the Identity field +1 and open the new form with that new number which I have calculated. How can I do this

Find 2nd or 3rd highest record with a query
There are fairly simple queries to find the maximum or minimum value for a field in a grouped by query that may have 2-3 or more groupings, but is it possible to find, say the 2nd and/or 3rd highest values and their associated fields in an ACCESS (2003) query? or must one resort to VBA programming. In either case, how is this done - if it can be done

Performing Function on a Row of data
I want to do something similarly to what I do in excel which is to find the minimum or maximum in a row or particular range of a row.


Name | 2008 | 2009 | 2010 | 2012

Fred | 4 | 7 | 9 | 11

Joe | 11 | 22 | 3 | 323

I want to be able to calculate the minimum or maximum number for Fred. for all 4 years.

Access Max Function
I am trying to find the oldest employee in a table using a simple query. I have already determined age, but, now whenever I try to find the maximum age using the Max Function, it still shows all of the ages instead of the single field.

Set the Maximum Characters for a Text Field
Before setting the maximum characters for a text field, you must understand database tables and be able to navigate Access 2003. Think about who will access the data and how they will use it as you plan your database table's design. Then follow these guidelines.

Add unbound row to form
I have created a continuous form bound to a query (X-Tab) where the detail row is a series of unbound text boxes. I then wrote some vba to populate the detail row from the query recordset.(The vba code also populates the heading row which are also unbound text boxes.)

Everything works fine except I do not know how to add or reference a new detail row when a I move to the next record in the recordset, and as a result my form shows me one row for eachrecord repeating whatever data was in the ending record.

So my question is: How do I populate the detail row so that one detail row matches one record from the recordset.

Some background. Each unbound control in the detail section is named "Col" and then a sequential number. So I have Col1, Col2, Col3, etc to Colx (the maximum field the query can return.

I have done the same with the heading being Hed1, Hed2, etc to the maximum. Now I need to be able to reference Row1Col1, Row1Col2, Row1Col3, etc and then Row2Col1, Row2Col2,Row2Col3, etc to the maximum number of records.

Find 2nd or 3rd highest record with a query
There are fairly simple queries to find the maximum or minimum value for a field in a grouped by query that may have 2-3 or more groupings, say the 2nd and/or 3rd highest values and their associated fields in an ACCESS (2003) query? or must one resort to VBA programming. In either case,

Maximum number of macros, queries, reports etc.
I am using Access 2003. Is there a maximum number of Macros, Queries, Reports, Tables, etc that I can have in a database? Is it a maximum by type of object or is it one big lump sum?

Primary key - maximum number of fields
I have a simple question which I can't really find the answer to. I'm studying for an exam, and one of the sample questions are "what is the maximum number of fields that can form part of the primary key in a MS Access database"?

The options are (a) 1 (b) 2 (c) 3 or (d) none of the above.

I am thinking that is is not unlimited, but certainly greater than

Code to fill data with zeros or spaces
I've been looking for a VB code that can go through every row in my table and fill the cells with blank spaces or zeros to the maximum field length.

It would need to go through every column and row.

For example:

If I have a field that has a maximum field length of 10 characters and the cell has data which is 5 characters - I need the remainder to be filled with 5 blank spaces if its a text cell.

If I have a field that has a maximum field length of 15 characters and the cell has data which is 2 characters - I need the remainder to be filled with 13 blank spaces.

navigate between forms dynamically
I have developed a form to display the pareto analysis of a table ( by building a query initially and the making a form using chart wizard).

The item that appears as maximum I.e. the one on the left most side has another report about what causes this item to be maximum.

The item that occupies this 'maximum' place changes from time to time. thus it has to be dynamic. Something like what we have for mobile maybe where we select what we are interested in and that opens and once we are done we can go back to the earlier page.

Querying field length
I have 2 tables. Table1 defines the permitted field lentghs for Table2

NameOfField MaxLength
Field1 50
Field2 20
Field3 20
etc etc

Field1 Field2 Field3
Example 12345 54321

What I need to do is to query the data in Table2 to find any data that exceeds the maximum permitted field length and then shows which field is at fault.

There are more than 50 fields and hundreds of records, I'd like to avoid doing this manually

value is less than the minimum or greater than the maximum
I am running an access append query in access 2010. And I keep getting the following error:

"you cannot record changes because a value you entered violates the settings | defined for this table or list (for example, a value is less than the minimum or greater than the maximum). correct the error and try again"

I have looked in the two tables I am importing from and to in order to determine the field data types in both; the fields were the same so I am slightly confused.

DMAX() function to find latest action
I am having problems writing a query against two tables. I have 1 table which logs the top level details for an investigation and then a second table which logs the actions. Both tables have the same field INV_NBR and this links the actions (table 2) back to the original investigation record (table 1). Each time an action is added to table two it is allocated the next incremental number (1-6) in the field INV_TRAN_NO. As such, I have mutiple records in table 2 with the same INV_NBR and various values for INV_TRAN_NO. I want to find the maximum value of INV_TRAN_NO for each INV_NBR. Does htis make sense?

I've tried using DMAX() but keep getting #Error as the result

How to set up different lookup lists for one datasheet value
Main form

Group Name: ----- | (this field is a combo list which the user can select Group A, B or C)

Subform in datasheet format (is located on the Main form)





Basically, this is how I want the form to work:

If the user selects Group A from the main form, they can then add options in the subform datasheet "specific" to only Group A.

If the user selects Group B, they can then add options in the subform datasheet "specific" to only Group B etc.

The user can add as many options in the datasheet as they want (up to the maximum listed in the list for the specific group).

I've searched the web but could not find any examples of this scenario (I.e. subform list dependent upon main form value).

Adding additional fields in form
I need to know if it is possible to have a box in a form that you could select to add another field.

For example:

Field Experience Semester:
Field Experience Year:
Field Experience Name:

Add another field experience?(Here I need to be able to click and below the above, it will present 3 more identical fields)

I am not sure if this is possible.thought I would ask. Couldn't find anything out on the web

Selecting maximum value from different fields
I am making a form that looks like this (it's just the rough draft, trying to figure the works first)

what it does, the user picks values from the combo boxes occ1, detect1, sever_eco, sever_hs, sever_envi.
the textbox below sever_eco combobox is a calculated field that is the multiplication of occ1*detect1*sever_eco. and so on for sever_hs and sever_envi.

the problem:
the text box in the blue square, the idea is that this text box picks the maximum value from the text boxes in the red box (I.e. the calculated fields)

I've tried using Maximum([sever_eco],[sever_hs,[sever_envi]) but returned the error #Name?

Update Table
this beginner. I have a table called Threads with about 9 columns. I would like to go thru table and perform a math function (using data in two of the columns) and place the results into a 3rd column.
Ist column Maximum Qty, 2nd column Current Qty, 3rd column Reorder Qty.
So would like: (Reorder Qty = Maximum Qty Current Qty).
Guys, Ive spent about 2 days watching on-line videos, tutorials, and whatever I can find, but nothing seems to help or work