Calculating a value for a new record

I have a simple database which records a list of different reports my team 
produces.  Each report has a unique hopefully sequential number "Report No" 
which is different to the "Record ID" (Autonumber). 

I have a form which I am using to add new reports to the underlying table.  
As I add a new report record I want to look up the highest existing Report No 
value in the table and auto populate the New Report Form Report No Control 
with that number +1.  i.e. if the last report added was numbered 255 the next 
one shoiuld be 256.  I have tried using Max ([Current Report List]![Report 
No])+1 in the Control Source property to create the number on a test form and 
this creates the correct value but when I try using the same expression in 
the Add Report Form it results in the value 1. The only difference between 
the two forms is that the form I want to use Data Entry is set to Yes and 
only shows 1 record when opened but the test form Data Entry is set to No and 
shows 200+ records.  If changed to Yes then the Max expression does not 
produce the correct value.  Any ideas please
0
Utf
2/12/2010 4:27:01 PM
access 16762 articles. 3 followers. Follow

2 Replies
728 Views

Similar Articles

[PageSpeed] 45

Check out this sample from Roger's Access Library:

    AutonumberProblem.mdb
    http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=395


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

"Julian" wrote:

> I have a simple database which records a list of different reports my team 
> produces.  Each report has a unique hopefully sequential number "Report No" 
> which is different to the "Record ID" (Autonumber). 
> 
> I have a form which I am using to add new reports to the underlying table.  
> As I add a new report record I want to look up the highest existing Report No 
> value in the table and auto populate the New Report Form Report No Control 
> with that number +1.  i.e. if the last report added was numbered 255 the next 
> one shoiuld be 256.  I have tried using Max ([Current Report List]![Report 
> No])+1 in the Control Source property to create the number on a test form and 
> this creates the correct value but when I try using the same expression in 
> the Add Report Form it results in the value 1. The only difference between 
> the two forms is that the form I want to use Data Entry is set to Yes and 
> only shows 1 record when opened but the test form Data Entry is set to No and 
> shows 200+ records.  If changed to Yes then the Max expression does not 
> produce the correct value.  Any ideas please
0
Utf
2/12/2010 4:41:01 PM
Eventually sorted thank you.  I tried the DMAX solution but initially got 
ERROR but after I put [ ] around the field name it worked perfectly.

"Tom Wickerath" wrote:

> Check out this sample from Roger's Access Library:
> 
>     AutonumberProblem.mdb
>     http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=395
> 
> 
> Tom Wickerath
> Microsoft Access MVP
> http://www.accessmvp.com/TWickerath/
> __________________________________________
> 
> "Julian" wrote:
> 
> > I have a simple database which records a list of different reports my team 
> > produces.  Each report has a unique hopefully sequential number "Report No" 
> > which is different to the "Record ID" (Autonumber). 
> > 
> > I have a form which I am using to add new reports to the underlying table.  
> > As I add a new report record I want to look up the highest existing Report No 
> > value in the table and auto populate the New Report Form Report No Control 
> > with that number +1.  i.e. if the last report added was numbered 255 the next 
> > one shoiuld be 256.  I have tried using Max ([Current Report List]![Report 
> > No])+1 in the Control Source property to create the number on a test form and 
> > this creates the correct value but when I try using the same expression in 
> > the Add Report Form it results in the value 1. The only difference between 
> > the two forms is that the form I want to use Data Entry is set to Yes and 
> > only shows 1 record when opened but the test form Data Entry is set to No and 
> > shows 200+ records.  If changed to Yes then the Max expression does not 
> > produce the correct value.  Any ideas please
0
Utf
2/15/2010 9:38:01 AM
Reply:

Similar Artilces:

Summing Values using multiple criertia
Does anybody know a formula I could use to sum a range of values based on multiple criertia? Example: Division Type Wage Bulk Driver 200.00 Bulk Admin 400.00 General Admin 500.00 Bulk Driver 100.00 I want to sum the wages for Divison "Bulk" & Type "Driver". How can I do this??? Thanks! Jane =SUMPRODUCT((A2:A4="Bulk")*(B2:B4="Driver")*(C2:C4)) If there are lots of such totals, you may want to consider a pivot table rather than formulas. On Mon, 27 Sep 2004 20:14:27 -0700, "Jane" <anonymous@dis...

how do i change the default value of measure from points to inche.
how do i change the default value of measure from points to inches when setting the width and hight of cells? You don't. Excel uses only points for these measures best wishes -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "yoyo4u" <yoyo4u@discussions.microsoft.com> wrote in message news:33420157-6E05-4A55-9003-088D731E495E@microsoft.com... > how do i change the default value of measure from points to inches when > setting the width and hight of cells? yoyo Row heights are measured in points. There are 72 points to an inch. Th...

Excel Data appearing in new Pop-up window rather than frameset frame
I have a servlet running in Tomcat that delivers an excel data file, the response content type being set to: "application/vnd.ms-excel" My html page uses a frameset to display (amongst other things) the excel data. The data is displayed on the click of a button and is directed to a particuler frame: <FORM method=post action="servlet/XLDataServlet/data.xls" target="exceldata"> <INPUT TYPE=SUBMIT VALUE="Display Excel Data"> </FORM> With Internet Explorer 6.0 the excel data is displayed in the target frame, with Internet Explorer 5.5...

recording changes
I have a field called "TimeStamp" What I would like is that if someone modifiys or make any change in the form that the "TimeStamp" will record the time and date of change. How can this be achieved? sandrao In an event procedure in the form's AfterUpdate event, you could do something like: Me!txtYourTimeStampField = Now() Note that if you use the BeforeUpdate event, every time your code changes the value of this field, the form tries to update, the BeforeUpdate (re-)triggers, and you "loop up" (at least, it seems to me I've seen this...)...

Chart to show Portfolio Value over time?
I'm using Money 2003. I would like to be able to see the $ value I have in my portfolio over time. So that I can weep. Money does have a chart view that allows you to see the PRICE history for a given stock over time - but not the dollar value of your investment in the stock over time. In fact, I can't seem to find a view at all that shows you the net value of your portfolio/individual stocks changing over time. The best I've been able to do is to use the "Net Worth" report and unselect all the other accounts. This has insufficient granularity (months instea...

Insert empty numeric value
Dear all, In VB, I have three textbox which are amount1,amount2 and amount3. After user enter the value in the textbox, I will insert the value into Access table. The table have three columns amount1 , amount2 and amount3, and all are nummeric Type. However, if the user do not enter any value in textbox . The insert statement will become as follows: Insert into table1 (amount1,amount2,amount3) values (,,) Then access complain that there is syntax error in insert statement. Does that mean I cannot insert empty value for the numeric value in access.? How to solve this problem. Than...

Subtracting value from main form
I have a borrow module which will alow user to return item separately. So, I have get the structure of returning it separately. In my main form is the borrowing item, with the loaned quantity and the owed quantity (will be calculated). In the subform, there is the returning transaction. User will need to key in the quantity returned and it will be automatically deducted from the quantity owed. But how am I supposed to get the quantity deducted while it 1 is in main form and the other is in subform? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-fo...

Possible to Look Up Records by Initials?
I have a user who's telling me that prior to this weekend in CRM, she could lookup user records by initials rather than typing in part of the name. I don't see where that setting would have been marked in CRM. Has anyone else heard of this? If so, how did you implement this type of search customization/configuration? Steps to recreate problem: Account screen Account Owner field's magnifying glass Assign Account Assign to another user fields magnifiying glass Look Up Records' Look For field = initials of user you're attempting to Find. I doubt you can use the init...

How to record percentage increases in materials costing sheet.
I am trying to do a materials costing sheet where I would like to be able to periodically increast the base cost of the product by different percentages. E.g. Cost of Apples $10.00 but might be increased by 5% then 2.5% and therefore the base cost would keep changing. I know how do first % increase but not how to then use that figure as the base figure for future adjustments. Thanks for amy help. I am very new to this. You could put a % in one cell. Select the cell and give it a name by Insert-->Name-->Define. Call it "increase". Then, in the cell where you put the f...

HOWTO create a Mail Merge Template in MSCRM with multiple child records
Hi, I have created successfully a basic Mail Merge template in MS Word in Dynamics CRM 4.0 for Order Entity. My challenge now is how to design a template that retrieves the order details (product and prices) related to the order record? This sounds like a parent-child template. Any idea? On Jun 2, 1:26=A0pm, "Benjie Fallar III" <bfall...@hotmail.com> wrote: > Hi, > I have created successfully a basic Mail Merge template in MS Word in > Dynamics CRM 4.0 for Order Entity. > My challenge now is how to design a template that retrieves the order > details (prod...

Calculating values for empty cells.
Hello. I have a very simple problem that I cannot find the answer to. I have data in two columns, some of the data in one of the columns is missing and I want to automatically extrapolate what the data should be based on the trend. How can I get Excel to fill in empty values without overwriting the known values. Below is a sample of my data. 1500 1600 1700 1800 4000 1887 5700 1900 5500 1910 7300 1912 8100 1920 8800 1926 10100 1930 11900 1936 12200 1938 -- Ryan Taylor rtaylor@stgeorgeconsulting.com Not sure what yo...

Recording ownership in an LLC partnership
I'm a partner in an LLC company and would like to track my ownership interest in Money 2004. It's not "real" obvious how I might go about this, particularly ... What account type should be used? How do I record my portion of the profits earned, for which I'm am personally taxed? How do I record the gain in ownership value these profits provided when I haven't necessarily received them in cash? How do I record a portion of these profits as received in cash when they aren't dividends but distributions? I'd prefer to use an investment oriented approach rather ...

Replace null value with the previous value?
I have a database that was just imported that has approximately 388000 records. The problem is that there is information about a person in multiple different records but the name did not come across with each record. (So I have 10 records with information for a certain name, but the name only appears in field 1 of the first record and not the subsequent 9, etc.) I need to create a query or expression that will fill field 1 with the preceding value if it is null. This way I will have all the information for field 1 in a manner that I can link and combine data. Simply I need to fil...

Can you record a macro in Publisher like you can in Excel?
In Excel I use the feature to Record a Macro. Excel records the keystrokes and converts to VB. Does Publisher have that option? In my version, Publisher 2003, I can Create a Macro, and it takes me into VB. However, I don't know how to code VB. Darlene wrote: > In Excel I use the feature to Record a Macro. Excel records the keystrokes > and converts to VB. Does Publisher have that option? In my version, > Publisher 2003, I can Create a Macro, and it takes me into VB. However, I > don't know how to code VB. Publisher does not have a Macro Recorder, sorry. --...

How do I find a value on a line?
I made a line graph of data to use as a calibration. I know the y value and I want to find the X value. Is there a way that I find find this specifically on the line without using a trendline formula or guessing by looking at the gridlines? The only way to find a specific value is to use the formula. Rgs, Bou If you can accept piece-wise linear interpolation, see Interactive Chart http://www.tushar- mehta.com/excel/software/interactive_chart_display/index.html -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity s...

endnotes on new page
Is there a way to put all the endnotes together on a new page? I've tried inserting a page break in the endnote separator and tried setting the endnote separator to start on a new page, but those methods didn't work. Thanks. -- ~ Janet Insert a page break before the endnote separator. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "Janet S" <JanetS@discussions.microsoft.com> wrote in message news:C...

New Windows Tempalte LIbrary (WTL) 7.1 released
This version provides full support for Visual Studio .NET 2003 and Windows XP and CE. Download links: http://wtl.50megs.com/wtl.htm > This version provides full support for Visual Studio .NET 2003 and Windows > XP and CE. > > Download links: > http://wtl.50megs.com/wtl.htm Wow - intersting way to promote your politicala agenda. Back on topic.. has anyone ever written documentation for WTL? One of the beefs I had about it when tring to use it 3 years ago was the utter lack of documentation. -- Jim Johnson ...

New to Money 2006
I was using Money 2004 and upgraded it to 2006 last night (God only knows, why :)). I started to hate the idea of Passport and Web Publishing my account details and so did not choose the Passport option. I was able to install with regular Money stand alone password and still update all my accounts by downloading them from the respective financial institutions (as I used to do with 2004). The problem is with MBNA America. I want to download the credit card transactions. But the online setup process is asking me for passport setup. I looked at the description of this bank on Microsoft site...

Default value for custom field?
How can I populate a custom field with a default value? Specifically, I have a custom field "DisplayName" associated with the Quote Detail object. I want initially to populate this field with the value of the product name field when a new product is added to a quote. The user can then edit the DisplayName custom field if desired. The DisplayName custom field will be used as the product name on a Crystal Reports quote form. ...

Sorting records in the Report Design View
Currently, I use "Sorting and Grouping" tool in the Report Design View to create group headers so that I can group my data accordingly. However, I would like to know if I can apply a custom sort order. For example, the field name that I want to group is "Region". I have records of "East", "North", "South", and "West". Is there a way that I can order by "North", "South", "East", and "West" instead? If I use the "Sorting and Grouping" tool, it won't work because it only accepts a...

records in current month
Hi I want to use a query that returns records in the current month from a date field, but cant work the query out - anyone help please Thanks Alec Set the criteria under the date field to: Between Date()-Day(Date())+1 AND DateSerial(Year(Date()),Month(Date())+1,0) -- Duane Hookom Microsoft Access MVP "alecgreen" wrote: > Hi > > I want to use a query that returns records in the current month from a > date field, but cant work the query out - anyone help please > > Thanks > > Alec > > ...

Installing Office 2007 on new laptop
I tried to install Office 2007 on my new laptop which came with the 60 day free trial of Office 2007. After the disk has been installed and I click on the icon to open the program I get an error message that says something like "You are unable to sign in because the server is unavailable". I have tried uninstalling and reinstalling several times and get the same result. This was a disk purchased as a student/instructor disk and has never been used before. On 3/21/2010 11:28 AM, Linda wrote: > I tried to install Office 2007 on my new laptop which came with the 60 ...

Moving Exchange 2003 to new Hardware #2
I'm in the middle of my main server move.. this server is Primary Domain Controller, exchange 2003 server, DNS and DHCP hosts, AntiVirus (Symantec), and print server. This is on a Win2k3 sp 1 OS. I've moved Symantec first, that was easy. Now I'm moving Exchange 2003 - I've installed Exchange, both servers are now running the same version and I see both in the EMS. I've started to set the public folders to replicate from my oldserver to newserver.. The public folders replicated twice, and now stopped.. they are out of sync (I think this is related to a problem I'l...

Extract Values from a Column
I have a worksheet used to track time spent on various jobs. One column is for the JOB # while others are for descriptions, etc. I would like to have formulas or possible a macro (if necessary) to sum the time spent on different jobs. For example, say (for simplicity) that each row is equal to 1 unit of time. Then in this row I put 342 for JOB # 342. After the entire day I have worked on say 5 different jobs. I would like to have a cell that says "Total time spent on Job # 342" and then next to it a formula that would look at the column and count all the values that are equal t...

can I create a new resume?
I would like to create a new resume. Can you help? This is the Microsoft Excel newsgroup. I am unaware of any newsgroup that provides help with resumes, but perhaps you intended to post in the Microsoft Word newsgroup, in which case, this tutorial may help you: http://www.officearticles.com/tutorials/write_your_resume_in_microsoft_word.htm ************ Anne Troy www.OfficeArticles.com "in need of help." <in need of help.@discussions.microsoft.com> wrote in message news:4245D9F3-C5CF-4C64-A70F-E8930F899954@microsoft.com... >I would like to create a new resume. Can you he...