Auto Numbering in Form

I need to find a way to display in a form text box the highest numerical
value entered in any one of four fields for the last record entered.

For example: If 
Field_1 is 0001
Field_2 is 0008
Field_3 is 0005 
Field_4 is 0004

When the form is opened, the textbox would display the number 0008. The
textbox should display the highest of the four fields of the last record.

-- 
Message posted via http://www.accessmonster.com

0
JP
8/16/2007 10:23:51 PM
access 16762 articles. 3 followers. Follow

7 Replies
416 Views

Similar Articles

[PageSpeed] 50

Define "last".  The way Access stores records and the way you conceptualize 
"last" may not match up.

Comparing "across" is something you do in a spreadsheet.  It's rarely 
appropriate in a well-normalized relational database, like Access.

And why?  You've described a "how" (get max of 'last' record's 4 fields). 
If you'll describe a bit more about what having that (and what those 
represent) will allow you to do, the newsgroup readers may be able to offer 
more specific suggestions to meet your underlying business need.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"JP via AccessMonster.com" <u36225@uwe> wrote in message 
news:76cce54f4e74d@uwe...
>I need to find a way to display in a form text box the highest numerical
> value entered in any one of four fields for the last record entered.
>
> For example: If
> Field_1 is 0001
> Field_2 is 0008
> Field_3 is 0005
> Field_4 is 0004
>
> When the form is opened, the textbox would display the number 0008. The
> textbox should display the highest of the four fields of the last record.
>
> -- 
> Message posted via http://www.accessmonster.com
> 


0
Jeff
8/16/2007 10:33:47 PM
The four Fields represent specific numbers used in customer billing. When the
user opens the form the new record may or not require this specific number,
but the user is a where of the highest of numbers. What I left out before was
that the textbox should display highest of the four fields when they are
actually used. In other words, several records may pass before a value or
values are entered, so the textbox should keep track of this number.

Thank you for your help!


Jeff Boyce wrote:
>Define "last".  The way Access stores records and the way you conceptualize 
>"last" may not match up.
>
>Comparing "across" is something you do in a spreadsheet.  It's rarely 
>appropriate in a well-normalized relational database, like Access.
>
>And why?  You've described a "how" (get max of 'last' record's 4 fields). 
>If you'll describe a bit more about what having that (and what those 
>represent) will allow you to do, the newsgroup readers may be able to offer 
>more specific suggestions to meet your underlying business need.
>
>Regards
>
>Jeff Boyce
>Microsoft Office/Access MVP
>
>>I need to find a way to display in a form text box the highest numerical
>> value entered in any one of four fields for the last record entered.
>[quoted text clipped - 7 lines]
>> When the form is opened, the textbox would display the number 0008. The
>> textbox should display the highest of the four fields of the last record.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/200708/1

0
JP
8/16/2007 10:57:52 PM
Based on your description, you'll want to reconsider using a spreadsheet, 
or, consider taking on the fairly steep learning curve to pick up more of 
what Access requires and offers.

Doing what you are describing is relatively simple in Access ... IF you've 
created a data structure that is well-normalized.  Repeating fields (same 
type data, multiple columns) is not well-normalized.

Is there a reason you have to do this in Access?

Another option might be to collect the data (in Access), export it to Excel, 
and do your comparison there?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"JP via AccessMonster.com" <u36225@uwe> wrote in message 
news:76cd3132e591a@uwe...
> The four Fields represent specific numbers used in customer billing. When 
> the
> user opens the form the new record may or not require this specific 
> number,
> but the user is a where of the highest of numbers. What I left out before 
> was
> that the textbox should display highest of the four fields when they are
> actually used. In other words, several records may pass before a value or
> values are entered, so the textbox should keep track of this number.
>
> Thank you for your help!
>
>
> Jeff Boyce wrote:
>>Define "last".  The way Access stores records and the way you 
>>conceptualize
>>"last" may not match up.
>>
>>Comparing "across" is something you do in a spreadsheet.  It's rarely
>>appropriate in a well-normalized relational database, like Access.
>>
>>And why?  You've described a "how" (get max of 'last' record's 4 fields).
>>If you'll describe a bit more about what having that (and what those
>>represent) will allow you to do, the newsgroup readers may be able to 
>>offer
>>more specific suggestions to meet your underlying business need.
>>
>>Regards
>>
>>Jeff Boyce
>>Microsoft Office/Access MVP
>>
>>>I need to find a way to display in a form text box the highest numerical
>>> value entered in any one of four fields for the last record entered.
>>[quoted text clipped - 7 lines]
>>> When the form is opened, the textbox would display the number 0008. The
>>> textbox should display the highest of the four fields of the last 
>>> record.
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access/200708/1
> 


0
Jeff
8/17/2007 3:46:08 PM
It is ideal for me to use Access for many reasons. 

Is what I've describe possible to do with the DMax function?

Thanks


Jeff Boyce wrote:
>Based on your description, you'll want to reconsider using a spreadsheet, 
>or, consider taking on the fairly steep learning curve to pick up more of 
>what Access requires and offers.
>
>Doing what you are describing is relatively simple in Access ... IF you've 
>created a data structure that is well-normalized.  Repeating fields (same 
>type data, multiple columns) is not well-normalized.
>
>Is there a reason you have to do this in Access?
>
>Another option might be to collect the data (in Access), export it to Excel, 
>and do your comparison there?
>
>Regards
>
>Jeff Boyce
>Microsoft Office/Access MVP
>
>> The four Fields represent specific numbers used in customer billing. When 
>> the
>[quoted text clipped - 32 lines]
>>>> textbox should display the highest of the four fields of the last 
>>>> record.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/200708/1

0
JP
8/17/2007 4:28:05 PM
DMax will give the the maximum value for a single field over a number of 
rows (where the number of rows can be influenced through a Where clause).

You haven't given any indication whether or not the 4 fields are in a single 
row, or are the same field in 4 separate rows. If they're 4 fields in a 
single row, then DMax won't do anything for you.

It may well be ideal for you to use Access, but it will be even better for 
you if you use it properly!

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"JP via AccessMonster.com" <u36225@uwe> wrote in message 
news:76d65c9906e08@uwe...
> It is ideal for me to use Access for many reasons.
>
> Is what I've describe possible to do with the DMax function?
>
> Thanks
>
>
> Jeff Boyce wrote:
>>Based on your description, you'll want to reconsider using a spreadsheet,
>>or, consider taking on the fairly steep learning curve to pick up more of
>>what Access requires and offers.
>>
>>Doing what you are describing is relatively simple in Access ... IF you've
>>created a data structure that is well-normalized.  Repeating fields (same
>>type data, multiple columns) is not well-normalized.
>>
>>Is there a reason you have to do this in Access?
>>
>>Another option might be to collect the data (in Access), export it to 
>>Excel,
>>and do your comparison there?
>>
>>Regards
>>
>>Jeff Boyce
>>Microsoft Office/Access MVP
>>
>>> The four Fields represent specific numbers used in customer billing. 
>>> When
>>> the
>>[quoted text clipped - 32 lines]
>>>>> textbox should display the highest of the four fields of the last
>>>>> record.
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access/200708/1
> 


0
Douglas
8/17/2007 5:54:54 PM
I’m trying to display the maximum value of multiple fields, over a number of
records.

This is the best visual representation of the kind of situation I’m up
against.

Column1-------Column2-------Column3-------Column4
======================================

0001---------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
0002------------0003--------------------------------------------------
0004---------------------------------------------------------------------
----------------------------------------------------------------------------
0005------------0006-------------0007------------------------------

Each of the values above are examples of numbers entered into four different
textboxes by the users through a form. Each textbox is assigned to each
Column. 
I would like to have an additional textbox within the form that displays the
next available number of the last record where the number was actually used.
Therefore, the function must keep track of the maximum value across all four
Columns. Also, some rows do not contain anything (because the specific record
did not require the use of the number), but the textbox will still keep track
of the next available number. In the example above the textbox would display
“0008”. The user may or may not need to use this number for the particular
entry, but never the less “0008” is waiting to be used. 


Douglas J. Steele wrote:
>DMax will give the the maximum value for a single field over a number of 
>rows (where the number of rows can be influenced through a Where clause).
>
>You haven't given any indication whether or not the 4 fields are in a single 
>row, or are the same field in 4 separate rows. If they're 4 fields in a 
>single row, then DMax won't do anything for you.
>
>It may well be ideal for you to use Access, but it will be even better for 
>you if you use it properly!
>
>> It is ideal for me to use Access for many reasons.
>>
>[quoted text clipped - 27 lines]
>>>>>> textbox should display the highest of the four fields of the last
>>>>>> record.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/200708/1

0
JP
8/17/2007 8:11:52 PM
I would strongly advise rethinking your data design. While that may work 
well in Excel, it won't work well in a relational database.

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"JP via AccessMonster.com" <u36225@uwe> wrote in message 
news:76d850bee960e@uwe...
> I'm trying to display the maximum value of multiple fields, over a number 
> of
> records.
>
> This is the best visual representation of the kind of situation I'm up
> against.
>
> Column1-------Column2-------Column3-------Column4
> ======================================
>
> 0001---------------------------------------------------------------------
> ----------------------------------------------------------------------------
> ----------------------------------------------------------------------------
> 0002------------0003--------------------------------------------------
> 0004---------------------------------------------------------------------
> ----------------------------------------------------------------------------
> 0005------------0006-------------0007------------------------------
>
> Each of the values above are examples of numbers entered into four 
> different
> textboxes by the users through a form. Each textbox is assigned to each
> Column.
> I would like to have an additional textbox within the form that displays 
> the
> next available number of the last record where the number was actually 
> used.
> Therefore, the function must keep track of the maximum value across all 
> four
> Columns. Also, some rows do not contain anything (because the specific 
> record
> did not require the use of the number), but the textbox will still keep 
> track
> of the next available number. In the example above the textbox would 
> display
> "0008". The user may or may not need to use this number for the particular
> entry, but never the less "0008" is waiting to be used.
>
>
> Douglas J. Steele wrote:
>>DMax will give the the maximum value for a single field over a number of
>>rows (where the number of rows can be influenced through a Where clause).
>>
>>You haven't given any indication whether or not the 4 fields are in a 
>>single
>>row, or are the same field in 4 separate rows. If they're 4 fields in a
>>single row, then DMax won't do anything for you.
>>
>>It may well be ideal for you to use Access, but it will be even better for
>>you if you use it properly!
>>
>>> It is ideal for me to use Access for many reasons.
>>>
>>[quoted text clipped - 27 lines]
>>>>>>> textbox should display the highest of the four fields of the last
>>>>>>> record.
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access/200708/1
> 


0
Douglas
8/20/2007 9:21:41 PM
Reply:

Similar Artilces:

Form
Hi, I am in the process of building an image database. I am almost done with my database. I included a field in a form which is not in my parent table. Now I need to include it in the table. Can somebody explain me how to do it? Thank you verymuch! I assume that the form is bounded aleady to the main table. 1. Open the table in design view and add the field you want 2. Open the form in design view 3. Open the form properties, by clicking on the top left corner of the form 4. Put the cursor in the RecordSource property of the form, on the right you'll see a button with three dots, cli...

Filtered Form Query
I have a form that is filtered using Allen Browne's ajbFindAsUType module (http://allenbrowne.com/AppFindAsUType.html). I want to now create a query that utilizes only the filtered records as its source in order to perform a computation. Is there a way to pass the filtered Recordset into a query via a command button? You can use the RecordsetClone of the form to step through the records. You may be able to pass the Filter from the form on to whatever other operation you need to perform. For example, you could create a report that summarizes the data, and open it like ...

two columns range of numbers need to list all numbers in the range
have two columns range of numbers i need to list each number in the range start end 5 9 15 19 20 29 i need for each row to show the numbers in the range 5 6 7 8 9 15 16 .. .. pls help Was this post helpful to you? Check your other post. arsovat wrote: > > have two columns range of numbers i need to list each number in the range > start end > 5 9 > 15 19 > 20 29 > i need for each row to show the numbers in the range > 5 > 6 > 7 > 8 > 9 > 15 > 16 > . > . > pls help > > Was this post he...

sequential invoice numbering
How do I formulate a cell to add one to the previous invoice# each time the "xxxx" invoice file is opened. Please and thank you Scott You could add code to the workbook_open event, thus (to add this open the VBE (Alt+F11) and right click on the ThisWorkbook entry for the file name. Paste the code in the resultant window, close the VBE and save the workbook. (Change the references accordingly) Private Sub Workbook_Open() Worksheets("Sheet1").Range("A1").Value = Worksheets("Sheet1").Range("A1").Value + 1 End Sub -- HTH Nick Hodge Microso...

Page Numbers in Publisher 2000
I am completing our Employee Handbook and do not want page umbers on my first 9 pages. Page 10 is the first one I want numbered beginning with page #1. Instead my page #10 is labeled page #1. Can anyone help and let me know if this is possible with this software? Thanks. -- Kay -- Kay Correction "Kay" wrote: > I am completing our Employee Handbook and do not want page numbers on my first 9 pages. Page 10 is the first one I want numbered beginning with page #1. > Instead my page #10 is labeled page #10. Can anyone help and let me know if > this is possibl...

Having 3 .set files with different ISV's and custom form modifications
Currently we have 2 seperate SQL instances for 2 different environments. We want to be able to create one environment that has 3 companies, with one .set file with customizations, and alot of ISV's; one company with customizations and 2 ISV's; and create one new company with a plain vanilla GP only 2 ISV's no customizations; We currently have the 2 instances with the first 2 set up that way and now looking to consolidate into one environment with the company dropdowns together. Will this be a pain to manage or is it even possible?? ...

Formula for current month minus one = Quarter number in a macro.
I have a macro that does several things. The last step should be to save the file to a network drive with a name and quarter number appended to it. The file save command I have got is: - ActiveWorkbook.SaveAs Filename:= _ "Y:\Skip Register\Quarterly Charging Period ?", FileFormat:=xlExcel9795, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False The ? in the file name should be substituted by the formula as described below. The macro is to be run in the month following a quarter (i.e. run macro in April to get Quar...

Display Query Results on top of a Form
I have a query which runs from a control button on a form. When the query runs, the results are displayed behind the form. The user then has to minimize the form in order to see the query results. How do you set the query results to display on top of the form? You could put the following line in the code for the On Click event of the button being pushed: DoCmd.Minimize That will minimize the form and the query should be on top. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Kristin M" wrote: &...

Loading subform with data from main form
I thought this would do it: I put this in the before insert of the subform: Me!txtJobID = [Forms]![frmInvoice]![txtJobID] frmInvoice is the main form. What can I do differently to make this work? Thanks. SAC wrote: >I thought this would do it: > >I put this in the before insert of the subform: > >Me!txtJobID = [Forms]![frmInvoice]![txtJobID] > >frmInvoice is the main form. > >What can I do differently to make this work? You could use that code in the BeforeUpdate event, but this whole exercise would be automatic if you set the subform control's Link M...

How to change left bottom note that tell number records found?
Hi, At the left bottom corner of Exel 2003 spreadsheet, a display note 'Ready' remains as it is, though I have filtered the items using the filter command, the note usually displays number of records found. How to return back the display note 'number of records found' in Excel 2003 spreadsheet? -- Thank you, Cpviv If you have any formulas that read these filtered records, you will typically not be able to view the # of records found. If you go to Tool - Options - Calculation and change to Manual, you would be able to view # of records. Beware, as this will not au...

displaying a table on a form so that we can insert a new record at any place rather that at the end
how can i display the table on a form so that the user is able to insert a new record not only in the last, but also in any place of his desire. he must be able to insert a new record even before the first record. how can i accomplish this? can any body help me out please........ hi Ravindar, ravindar thati wrote: > how can i display the table on a form so that the user is able to > insert a new record not only in the last, but also in any place of his > desire. he must be able to insert a new record even before the first > record. Tables in Access (in a database) are a differen...

Suggest Names/Auto Fill
This is driving me crazy. Outlook has picked up on an email address i incorrectly typed in Now anytime i try to type the correct address it suggest/auto fills the wrong name until i finish typing the right address. Is there any way to get outlook to stop doing this? Can we clear the bad name out?? Please advise Any names it should be trying to Autofill should be coming straight from your address book. So if you go into your address book and find the offending address, simply delete it and type in the correct one. Or, if you want, you can completely disable Autofill by going t...

can you count the number of letters (inc spaces) in a single sell
I have a spreedsheet which has 3000 rows. Each cell has a different amount of words in it... ie ABC Learning Centre Ferrymead. Is it possible to count the number of characters (letters) including spaces in each cell??? (please note each cell has a different number of letters in it)... or do I have to do this manually? none of the help options seem to help me... Thanks use the formula =len( -- PF Wannab ----------------------------------------------------------------------- PF Wannabe's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2579 View this thread: ...

Guide to setting up Resources and using Auto Accept Agent
I am setting up an exchange 2003 system and was looking for guidance on how to create resources that can be booked - like conference rooms as part of meetings. Also, do I need to use Auto Accept Agents to complete the process of people booking these resources? Thanks Please could someone advise on this? I am hoping to find someone with information ASAP. "Makor" wrote: > I am setting up an exchange 2003 system and was looking for guidance on how > to create resources that can be booked - like conference rooms as part of > meetings. Also, do I need to use Auto Accept...

Sort numbers alphabetically
Let's say I have a list: 1 2 5 20 35 I want it alphabetical, ie: 1 2 20 35 5 Even if I make them text, they still sort numerically. Is there some trick? Thanks, Joe One method................ In B1 enter =LEFT(A1) Copy down. Copy and paste special>values. Select column A and B and sort on column B Gord Dibben MS Excel MVP On Tue, 26 Aug 2008 14:19:10 -0700 (PDT), Pungh0Li0 <joe.dellwo@gmail.com> wrote: >Let's say I have a list: >1 >2 >5 >20 >35 > >I want it alphabetical, ie: >1 >2 >20 >35 >5 > >Even if I make them text...

convert month text (MAR) to month number (3)
I am trying to work with a data set that unfortunately has spit out all the dates in a text format - i.e. 03/01/2009 is MAR 3 2009. Is there a way to convert that text date to an actual date format? Any advice or suggestions are greatly appreciated. Thanks -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200912/1 Debug.Print CDate("MAR 3 2009") = 3/3/2009 One problem with the CDate function is that it will bomb out on things that can't be evaluated as a date. Therefore you may want to use the IsDate fun...

Excel auto-fit
I accidently dragged and deleted the format width autofit option on my toolbar. I want it back. I know you can double click the column top to get the whole column to autofit; but sometimes I just want a cell to be the autofit width for the entire column. How can I get it back? You can reset toolbars under tools>customize>toolbars and reset Regards, Peo Sjoblom "NancyC" wrote: > I accidently dragged and deleted the format width autofit option on my > toolbar. I want it back. I know you can double click the column top to get > the whole column to autofit;...

Need to automaticallu input date in form
This seems to be a very popular question posed on the forum, and I have read through and tried the most viable solutions given to others. I have three diferent forms that update a table with three different date fields. Although I have set the default value for all the date fields in the table to =Date()- 1, this only works on one of the forms, even though that field in each form is given same properties. The forms are used to update an inventory table containing part numbers, wip dates and amounts, packing dates and amounts, and shipping dates and amounts. I use the three different forms bec...

Count 350 SS numbers, exclude duplicates
I need to be able to count information based on approximately 350 social security numbers, exlcuding duplicates. Any ideas on the formula? Thanks so much, Marsha Hi Marsha, One way =SUMPRODUCT((A1:A200<>"")/COUNTIF(A1:A200,A1:A200&"")) -- HTH RP (remove nothere from the email address if mailing direct) "Marsha" <Marsha@discussions.microsoft.com> wrote in message news:E362D9A0-8A06-43E4-81DD-0B2F28C6C18E@microsoft.com... > I need to be able to count information based on approximately 350 social > security numbers, exlcuding duplic...

auto Name #2
I have a user that has Outlook 2003 connected to our Exchange 2003 (just like everybody else here) Her problem is that when she starts typing in a persons name, the auto complete doesn't pop up unless she has already sent that person an email that day. Yes, her setting in Outlook does say to use teh auto name feature. Any other ideas? That's a symptom of a corrupt profile. Create a new one. -- Russ Valentine [MVP-Outlook] "Johnfli" <john@ivhs.us> wrote in message news:eFm2BnF8FHA.3928@TK2MSFTNGP10.phx.gbl... >I have a user that has Outlook 2003 connected to ou...

CRM toolbar is not visible in an Outlook custom contact form
Greetings, We have a client using a Custom Outlook Contact Form and when they open the form, the CRM toolbar is not visible nor does it appear that it can be added via View.Toolbars. Does anyone know a way to add the CRM toolbar to a custom Outlook Contact form so that we can use the Track in CRM and Set Parent functionality on a contact. This is for Office 2003 SP2. Jack Bender ...

Order Entry Form
I have a form with a subform. The subform only asks for product and quantity. First time through this form works great. Second entry skips the product code and goes directly to quantity. I have tried creating this form from the tables and also a query. I have checked all the tab stops and everything else I can think of but can't seem to find what is making this happen. -- Taylor Taylor, Did you create the subform so that you know that know code is causing this? If not, then open a module and do a find on the word 'setfocus' (no quotes). If no results use the word &...

Possible To Circumvent Single-Response To Auto-Replies Via Outlook Rules?
This is a multi-part message in MIME format. ------=_NextPart_000_0009_01C6638F.C85E8A70 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable In my company, users contact the internal IT support center via several = means, not the least of which is by email. I am trying to set up an = Outlook client to auto-reply to every incoming email with a template = message I previously created. The problem is that when using Outlook = rules to accomplish this, I am limited by the same restrictions in place = when dealing with the 'Out Of Office'...

Word 2007
In Word 2003 you can define a new number format list with Number style (None). It is not possible in Word 2007 because the OK button is not activated when (None) is choosen. To create a list like this is useful if you want to repeat a text on every line in a list without a number. My work around for this time was to do a list in Word 2003 version and then copy it into Word 2007. I look forward to get an answere from someone out there. Best regards, Lisa It is true that you can't do this for simple lists in Word 2007, but you can do it for multilevel lists; see h...

Changing the order of page numbers
Example: On a 10 page doc. you want the first 5 pages to be numbered '1' to '5' then the next 5 pages to be numbered '14' to '18' Is there a way of changing page numbers like the above example? Thanks! Ben Ben Don't understand why you want to do this but the best way is to record a macro while you do it manually. 1. print pages 1 to 5 2. go into page setup and change the first page from auto to the value 9 3. print pages 6 to 10. The page count should start at 14. 4 go into page set up and change the first page back to auto. you'll get so...