If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Auto Number
Hello, I'm new to access and trying to learn by reading and trying...
I would like to use a primary key ID in a table that would be the current year - 0001 and then auto count up for each record. 2008-0001 2008-0002 Then 1/1/2009 start 2009-0001 2009-0002 and so on. If this is a stupid idea I would like to do this in another field in the table. I'm also looking for a way to Close a record so it can't be changed without first reopening the record. Thanks, Robert |
#2
|
|||
|
|||
Auto Number
That wouldn't be autonumber as the term is used in Access. Autonumber is an
automatically generated number that is almost certain to be unique, but it is for use by Access, and in most cases will not be seen by the user. Also, it cannot be guaranteed not to leave gaps in the numbering sequence. If it is possible to avoid gaps it is very, very difficult. However, what you want can be done with an expression. First, though, there is no need to store the year if there is a date field in the record. I will call that field DateField, and the incrementing number NumInc. For purposes of this reply, they are both in a table named tblMain, which is the Record Source for a form. On the form, bind a text box to the NumInc field (that is, select NumInc as its Control Source). This is done on the text box property sheet. To see the Property Sheet, open the form in design view, right click on the text box, and select Properties. This will open a box with five tabs (if it is not already open): Format, Data, Event, Other, and All. Click the Data tab to find the place to set the Control Source. You may already know about the Property Sheet, but you identify yourself as a beginner, so there it is just in case. Also on the Data tab is Default Value. This applies only to new records. In the Default Value row add the expression: =Nz(DMax("NumInc","tblMain","Year([DateField]) = " & Year(Date())),0) + 1 You can hide this text box. In another text box, set the Control Source to: =Year([DateField]) & "-" & Format([NumInc],"0000") Look up the functions Nz, DMax, Year, Date, and Format in Help to learn more about what is going on here. In general, the first expression is telling Access to find the largest value in the NumInc field in a record in which the year in DateField is the same as the current year, and to add one to that number. Nz is for the first record of the year. There is no maximum value, so the DMax result is null. Instead of Null, Access uses a 0 as the value, and adds 1 to it. The second expression combines the Year from DateField, a hyphen, and the number formatted with leading zeros as needed for numbers less than 1000. "Robert Smith" wrote in message ... Hello, I'm new to access and trying to learn by reading and trying... I would like to use a primary key ID in a table that would be the current year - 0001 and then auto count up for each record. 2008-0001 2008-0002 Then 1/1/2009 start 2009-0001 2009-0002 and so on. If this is a stupid idea I would like to do this in another field in the table. I'm also looking for a way to Close a record so it can't be changed without first reopening the record. Thanks, Robert |
#3
|
|||
|
|||
Auto Number
You'll need to set the DefaultValue property in code. If you set it in the
properties sheet its value will remain static until the form is closed and reopened again. Set it in the form's Current event procedure with: Me.NumInc.DefaultValue = _ """" & Nz(DMax("[NumInc]", "[tblMain]", _ "Year([DateField]) = " & Year(Date)), 0) + 1 & """" Note that the DefaultValue property is a string expression regardless of the data type of the field in question so should be wrapped in quotes characters as above. In fact in this case it wouldn't make any difference if the quotes characters were omitted, but in other circumstances they are crucial, particularly with a date/time data type. BTW the control Robert meant you could hide was the NumInc control, not the unbound control which concatenates the current year value with the formatted NumInc value. Another point is that you won't be able to use the DateField and NumInc fields as the primary key as this would not prevent duplicate NumInc values within one year. You would need to create another field, CurrentYear say, with a DefaultValue property of Year(Date()) and use this and NumInc as the composite primary key. In which case the above code could be changed to: Me.NumInc.DefaultValue = _ """" & Nz(DMax("[NumInc]", "[tblMain]", _ "[CurrentYear] = " & Year(Date)), 0) + 1 & """" The CurrentYear field need not be shown on the form of course. Ken Sheridan Stafford, England "Robert Smith" wrote: Thanks for the information. I tried it exactly like you described and even tried it with a blank database using your exact names but I'm even greener than green and didn't get it to work. The box you said I could hide says 2008-39448 Whenever I press Add Record button I get #Error in both boxes. I'm trying to learn Access but it doesn't seem to be willing... "BruceM" wrote in message ... That wouldn't be autonumber as the term is used in Access. Autonumber is an automatically generated number that is almost certain to be unique, but it is for use by Access, and in most cases will not be seen by the user. Also, it cannot be guaranteed not to leave gaps in the numbering sequence. If it is possible to avoid gaps it is very, very difficult. However, what you want can be done with an expression. First, though, there is no need to store the year if there is a date field in the record. I will call that field DateField, and the incrementing number NumInc. For purposes of this reply, they are both in a table named tblMain, which is the Record Source for a form. On the form, bind a text box to the NumInc field (that is, select NumInc as its Control Source). This is done on the text box property sheet. To see the Property Sheet, open the form in design view, right click on the text box, and select Properties. This will open a box with five tabs (if it is not already open): Format, Data, Event, Other, and All. Click the Data tab to find the place to set the Control Source. You may already know about the Property Sheet, but you identify yourself as a beginner, so there it is just in case. Also on the Data tab is Default Value. This applies only to new records. In the Default Value row add the expression: =Nz(DMax("NumInc","tblMain","Year([DateField]) = " & Year(Date())),0) + 1 You can hide this text box. In another text box, set the Control Source to: =Year([DateField]) & "-" & Format([NumInc],"0000") Look up the functions Nz, DMax, Year, Date, and Format in Help to learn more about what is going on here. In general, the first expression is telling Access to find the largest value in the NumInc field in a record in which the year in DateField is the same as the current year, and to add one to that number. Nz is for the first record of the year. There is no maximum value, so the DMax result is null. Instead of Null, Access uses a 0 as the value, and adds 1 to it. The second expression combines the Year from DateField, a hyphen, and the number formatted with leading zeros as needed for numbers less than 1000. "Robert Smith" wrote in message ... Hello, I'm new to access and trying to learn by reading and trying... I would like to use a primary key ID in a table that would be the current year - 0001 and then auto count up for each record. 2008-0001 2008-0002 Then 1/1/2009 start 2009-0001 2009-0002 and so on. If this is a stupid idea I would like to do this in another field in the table. I'm also looking for a way to Close a record so it can't be changed without first reopening the record. Thanks, Robert |
#4
|
|||
|
|||
Auto Number
For some reason I don't see Robert's reply in my newsreader. Your response
to my comments (including Robert's reply, is the first I have seen). I'm not sure what you're saying about the Default Value property on the property sheet. If you're saying the Default Value property remains the same for all records until the form is closed and reopened, that has not been my experience. "Ken Sheridan" wrote in message ... You'll need to set the DefaultValue property in code. If you set it in the properties sheet its value will remain static until the form is closed and reopened again. Set it in the form's Current event procedure with: Me.NumInc.DefaultValue = _ """" & Nz(DMax("[NumInc]", "[tblMain]", _ "Year([DateField]) = " & Year(Date)), 0) + 1 & """" Note that the DefaultValue property is a string expression regardless of the data type of the field in question so should be wrapped in quotes characters as above. In fact in this case it wouldn't make any difference if the quotes characters were omitted, but in other circumstances they are crucial, particularly with a date/time data type. BTW the control Robert meant you could hide was the NumInc control, not the unbound control which concatenates the current year value with the formatted NumInc value. Another point is that you won't be able to use the DateField and NumInc fields as the primary key as this would not prevent duplicate NumInc values within one year. You would need to create another field, CurrentYear say, with a DefaultValue property of Year(Date()) and use this and NumInc as the composite primary key. In which case the above code could be changed to: Me.NumInc.DefaultValue = _ """" & Nz(DMax("[NumInc]", "[tblMain]", _ "[CurrentYear] = " & Year(Date)), 0) + 1 & """" The CurrentYear field need not be shown on the form of course. Ken Sheridan Stafford, England "Robert Smith" wrote: Thanks for the information. I tried it exactly like you described and even tried it with a blank database using your exact names but I'm even greener than green and didn't get it to work. The box you said I could hide says 2008-39448 Whenever I press Add Record button I get #Error in both boxes. I'm trying to learn Access but it doesn't seem to be willing... "BruceM" wrote in message ... That wouldn't be autonumber as the term is used in Access. Autonumber is an automatically generated number that is almost certain to be unique, but it is for use by Access, and in most cases will not be seen by the user. Also, it cannot be guaranteed not to leave gaps in the numbering sequence. If it is possible to avoid gaps it is very, very difficult. However, what you want can be done with an expression. First, though, there is no need to store the year if there is a date field in the record. I will call that field DateField, and the incrementing number NumInc. For purposes of this reply, they are both in a table named tblMain, which is the Record Source for a form. On the form, bind a text box to the NumInc field (that is, select NumInc as its Control Source). This is done on the text box property sheet. To see the Property Sheet, open the form in design view, right click on the text box, and select Properties. This will open a box with five tabs (if it is not already open): Format, Data, Event, Other, and All. Click the Data tab to find the place to set the Control Source. You may already know about the Property Sheet, but you identify yourself as a beginner, so there it is just in case. Also on the Data tab is Default Value. This applies only to new records. In the Default Value row add the expression: =Nz(DMax("NumInc","tblMain","Year([DateField]) = " & Year(Date())),0) + 1 You can hide this text box. In another text box, set the Control Source to: =Year([DateField]) & "-" & Format([NumInc],"0000") Look up the functions Nz, DMax, Year, Date, and Format in Help to learn more about what is going on here. In general, the first expression is telling Access to find the largest value in the NumInc field in a record in which the year in DateField is the same as the current year, and to add one to that number. Nz is for the first record of the year. There is no maximum value, so the DMax result is null. Instead of Null, Access uses a 0 as the value, and adds 1 to it. The second expression combines the Year from DateField, a hyphen, and the number formatted with leading zeros as needed for numbers less than 1000. "Robert Smith" wrote in message ... Hello, I'm new to access and trying to learn by reading and trying... I would like to use a primary key ID in a table that would be the current year - 0001 and then auto count up for each record. 2008-0001 2008-0002 Then 1/1/2009 start 2009-0001 2009-0002 and so on. If this is a stupid idea I would like to do this in another field in the table. I'm also looking for a way to Close a record so it can't be changed without first reopening the record. Thanks, Robert |
#5
|
|||
|
|||
Auto Number
Sorry, I got the two of you mixed up; I meant 'Bruce's reply'.
As regards the DefaultValue property, for simplicity I was unduly brief in my explanation as it’s a little more complex in fact. The behaviour if you set it to an expression of the type you posted in the properties sheet is in my experience as follows: 1. If a series of new records are inserted in the form it updates the value correctly for each new record. 2. If a record prior to the latest one is deleted then this obviously leaves a gap in the sequence, correctly so for most purposes (it is of course possible to 'fill the gaps' with a more complex solution if that should be required). 3. However, if a new row is inserted, then deleted in the same session of the form before a further record is inserted then the DefaultValue property remains static so the next number is 2 after the last number in the table rather than 1 after. Now, its arguable that this is correct behaviour as there is no difference in principle between deleting the latest record and a prior one in the sequence. However, in practice that would for most purposes be regarded as incorrect behaviour, as most people would want the number following the latest row existing in the table as of now to be inserted, rather than the number following the latest record which existed before its deletion. It might be thought that its unlikely that this situation would arise, but Murphy's Law states otherwise! So neither solutions are 'incorrect', but setting the value in code seems to me to safely answer the more usual requirements for this sort of thing. The above is really a question of the 'mechanics' of the application, however, and not overly significant. What's more important is the need to control the uniqueness of the year/incrementing number value combination in the table definition, which does require the current year value to be inserted at a column position in each row. This does mean that the table is not properly normalized of course as the DateField column is functionally dependent on only one part of the key, not 'the key, the whole key and nothing but the key, so help me Codd'. But I can't see any way to avoid that (though I'm more than happy to be corrected if otherwise). Another possible solution of course is not to have a current year or incrementing number column at all, but to compute the serial numbers within each year on the fly from the date column. I suspect this would be inappropriate, however, as the numbers would vary as rows were deleted from the table or if a row were inserted out of date sequence, so as part of a fixed identifier would be useless. Ken Sheridan Stafford, England "BruceM" wrote: For some reason I don't see Robert's reply in my newsreader. Your response to my comments (including Robert's reply, is the first I have seen). I'm not sure what you're saying about the Default Value property on the property sheet. If you're saying the Default Value property remains the same for all records until the form is closed and reopened, that has not been my experience. "Ken Sheridan" wrote in message ... You'll need to set the DefaultValue property in code. If you set it in the properties sheet its value will remain static until the form is closed and reopened again. Set it in the form's Current event procedure with: Me.NumInc.DefaultValue = _ """" & Nz(DMax("[NumInc]", "[tblMain]", _ "Year([DateField]) = " & Year(Date)), 0) + 1 & """" Note that the DefaultValue property is a string expression regardless of the data type of the field in question so should be wrapped in quotes characters as above. In fact in this case it wouldn't make any difference if the quotes characters were omitted, but in other circumstances they are crucial, particularly with a date/time data type. BTW the control Robert meant you could hide was the NumInc control, not the unbound control which concatenates the current year value with the formatted NumInc value. Another point is that you won't be able to use the DateField and NumInc fields as the primary key as this would not prevent duplicate NumInc values within one year. You would need to create another field, CurrentYear say, with a DefaultValue property of Year(Date()) and use this and NumInc as the composite primary key. In which case the above code could be changed to: Me.NumInc.DefaultValue = _ """" & Nz(DMax("[NumInc]", "[tblMain]", _ "[CurrentYear] = " & Year(Date)), 0) + 1 & """" The CurrentYear field need not be shown on the form of course. Ken Sheridan Stafford, England "Robert Smith" wrote: Thanks for the information. I tried it exactly like you described and even tried it with a blank database using your exact names but I'm even greener than green and didn't get it to work. The box you said I could hide says 2008-39448 Whenever I press Add Record button I get #Error in both boxes. I'm trying to learn Access but it doesn't seem to be willing... "BruceM" wrote in message ... That wouldn't be autonumber as the term is used in Access. Autonumber is an automatically generated number that is almost certain to be unique, but it is for use by Access, and in most cases will not be seen by the user. Also, it cannot be guaranteed not to leave gaps in the numbering sequence. If it is possible to avoid gaps it is very, very difficult. However, what you want can be done with an expression. First, though, there is no need to store the year if there is a date field in the record. I will call that field DateField, and the incrementing number NumInc. For purposes of this reply, they are both in a table named tblMain, which is the Record Source for a form. On the form, bind a text box to the NumInc field (that is, select NumInc as its Control Source). This is done on the text box property sheet. To see the Property Sheet, open the form in design view, right click on the text box, and select Properties. This will open a box with five tabs (if it is not already open): Format, Data, Event, Other, and All. Click the Data tab to find the place to set the Control Source. You may already know about the Property Sheet, but you identify yourself as a beginner, so there it is just in case. Also on the Data tab is Default Value. This applies only to new records. In the Default Value row add the expression: =Nz(DMax("NumInc","tblMain","Year([DateField]) = " & Year(Date())),0) + 1 You can hide this text box. In another text box, set the Control Source to: =Year([DateField]) & "-" & Format([NumInc],"0000") Look up the functions Nz, DMax, Year, Date, and Format in Help to learn more about what is going on here. In general, the first expression is telling Access to find the largest value in the NumInc field in a record in which the year in DateField is the same as the current year, and to add one to that number. Nz is for the first record of the year. There is no maximum value, so the DMax result is null. Instead of Null, Access uses a 0 as the value, and adds 1 to it. The second expression combines the Year from DateField, a hyphen, and the number formatted with leading zeros as needed for numbers less than 1000. "Robert Smith" wrote in message ... Hello, I'm new to access and trying to learn by reading and trying... I would like to use a primary key ID in a table that would be the current year - 0001 and then auto count up for each record. 2008-0001 2008-0002 Then 1/1/2009 start 2009-0001 2009-0002 and so on. If this is a stupid idea I would like to do this in another field in the table. I'm also looking for a way to Close a record so it can't be changed without first reopening the record. Thanks, Robert |
#6
|
|||
|
|||
Auto Number
Thanks for the explanation. I did not know that about inserting and
deleting a record immediately, causing the value to skip as you have described. Perhaps this is because I tend to use code rather than the Default Value of a text box. Actually, I tend to use: If Me.NewRecord Then etc. I learned it that way a while ago, and often other things happen for a new record (controls are hidden or disabled or whatever), so there isn't any extra coding (that is, DefaultValue uses one line to accomplish what If Me.NewRecord needs several lines to do, unless there is already is "If Me.NewRecord"). I have since learned that using "If Me.NewRecord" causes the Before Insert event to run before the user has a chance to type anything, and the Dirty event does not run at all, while when using the DefaultValue property the Before Insert event runs when typing begins , and the Dirty event runs too (I think that's how it went). I'm not sure what else happens, but it does tell me that one approach or the other may be needed in specific situations. When people ask about incrementing numbers I usually point them to Roger Carlson's sample database on the topic, which used the Default Value property of a text box. If more complex incrementing is needed I still tend to use the text box Default Value when possible because it is simpler to explain than using VBA, and until now I thought it worked exactly the same way. "Ken Sheridan" wrote in message ... Sorry, I got the two of you mixed up; I meant 'Bruce's reply'. As regards the DefaultValue property, for simplicity I was unduly brief in my explanation as it’s a little more complex in fact. The behaviour if you set it to an expression of the type you posted in the properties sheet is in my experience as follows: 1. If a series of new records are inserted in the form it updates the value correctly for each new record. 2. If a record prior to the latest one is deleted then this obviously leaves a gap in the sequence, correctly so for most purposes (it is of course possible to 'fill the gaps' with a more complex solution if that should be required). 3. However, if a new row is inserted, then deleted in the same session of the form before a further record is inserted then the DefaultValue property remains static so the next number is 2 after the last number in the table rather than 1 after. Now, its arguable that this is correct behaviour as there is no difference in principle between deleting the latest record and a prior one in the sequence. However, in practice that would for most purposes be regarded as incorrect behaviour, as most people would want the number following the latest row existing in the table as of now to be inserted, rather than the number following the latest record which existed before its deletion. It might be thought that its unlikely that this situation would arise, but Murphy's Law states otherwise! So neither solutions are 'incorrect', but setting the value in code seems to me to safely answer the more usual requirements for this sort of thing. The above is really a question of the 'mechanics' of the application, however, and not overly significant. What's more important is the need to control the uniqueness of the year/incrementing number value combination in the table definition, which does require the current year value to be inserted at a column position in each row. This does mean that the table is not properly normalized of course as the DateField column is functionally dependent on only one part of the key, not 'the key, the whole key and nothing but the key, so help me Codd'. But I can't see any way to avoid that (though I'm more than happy to be corrected if otherwise). Another possible solution of course is not to have a current year or incrementing number column at all, but to compute the serial numbers within each year on the fly from the date column. I suspect this would be inappropriate, however, as the numbers would vary as rows were deleted from the table or if a row were inserted out of date sequence, so as part of a fixed identifier would be useless. Ken Sheridan Stafford, England "BruceM" wrote: For some reason I don't see Robert's reply in my newsreader. Your response to my comments (including Robert's reply, is the first I have seen). I'm not sure what you're saying about the Default Value property on the property sheet. If you're saying the Default Value property remains the same for all records until the form is closed and reopened, that has not been my experience. "Ken Sheridan" wrote in message ... You'll need to set the DefaultValue property in code. If you set it in the properties sheet its value will remain static until the form is closed and reopened again. Set it in the form's Current event procedure with: Me.NumInc.DefaultValue = _ """" & Nz(DMax("[NumInc]", "[tblMain]", _ "Year([DateField]) = " & Year(Date)), 0) + 1 & """" Note that the DefaultValue property is a string expression regardless of the data type of the field in question so should be wrapped in quotes characters as above. In fact in this case it wouldn't make any difference if the quotes characters were omitted, but in other circumstances they are crucial, particularly with a date/time data type. BTW the control Robert meant you could hide was the NumInc control, not the unbound control which concatenates the current year value with the formatted NumInc value. Another point is that you won't be able to use the DateField and NumInc fields as the primary key as this would not prevent duplicate NumInc values within one year. You would need to create another field, CurrentYear say, with a DefaultValue property of Year(Date()) and use this and NumInc as the composite primary key. In which case the above code could be changed to: Me.NumInc.DefaultValue = _ """" & Nz(DMax("[NumInc]", "[tblMain]", _ "[CurrentYear] = " & Year(Date)), 0) + 1 & """" The CurrentYear field need not be shown on the form of course. Ken Sheridan Stafford, England "Robert Smith" wrote: Thanks for the information. I tried it exactly like you described and even tried it with a blank database using your exact names but I'm even greener than green and didn't get it to work. The box you said I could hide says 2008-39448 Whenever I press Add Record button I get #Error in both boxes. I'm trying to learn Access but it doesn't seem to be willing... "BruceM" wrote in message ... That wouldn't be autonumber as the term is used in Access. Autonumber is an automatically generated number that is almost certain to be unique, but it is for use by Access, and in most cases will not be seen by the user. Also, it cannot be guaranteed not to leave gaps in the numbering sequence. If it is possible to avoid gaps it is very, very difficult. However, what you want can be done with an expression. First, though, there is no need to store the year if there is a date field in the record. I will call that field DateField, and the incrementing number NumInc. For purposes of this reply, they are both in a table named tblMain, which is the Record Source for a form. On the form, bind a text box to the NumInc field (that is, select NumInc as its Control Source). This is done on the text box property sheet. To see the Property Sheet, open the form in design view, right click on the text box, and select Properties. This will open a box with five tabs (if it is not already open): Format, Data, Event, Other, and All. Click the Data tab to find the place to set the Control Source. You may already know about the Property Sheet, but you identify yourself as a beginner, so there it is just in case. Also on the Data tab is Default Value. This applies only to new records. In the Default Value row add the expression: =Nz(DMax("NumInc","tblMain","Year([DateField]) = " & Year(Date())),0) + 1 You can hide this text box. In another text box, set the Control Source to: =Year([DateField]) & "-" & Format([NumInc],"0000") Look up the functions Nz, DMax, Year, Date, and Format in Help to learn more about what is going on here. In general, the first expression is telling Access to find the largest value in the NumInc field in a record in which the year in DateField is the same as the current year, and to add one to that number. Nz is for the first record of the year. There is no maximum value, so the DMax result is null. Instead of Null, Access uses a 0 as the value, and adds 1 to it. The second expression combines the Year from DateField, a hyphen, and the number formatted with leading zeros as needed for numbers less than 1000. "Robert Smith" wrote in message ... Hello, I'm new to access and trying to learn by reading and trying... I would like to use a primary key ID in a table that would be the current year - 0001 and then auto count up for each record. 2008-0001 2008-0002 Then 1/1/2009 start 2009-0001 2009-0002 and so on. If this is a stupid idea I would like to do this in another field in the table. I'm also looking for a way to Close a record so it can't be changed without first reopening the record. Thanks, Robert |
#7
|
|||
|
|||
Auto Number
Robert:
I wouldn't throw in the towel just yet. Are you by any chance entering the code directly in the properties sheet rather than in the event procedure? If so, this is how to do it: Select the form object by clicking on the small black square in its top left corner in form design view and open its properties sheet if its not already open. Then select the On Current event property in the properties sheet. Click on the 'build' button; that's the one on the right with 3 dots. Select 'Code Builder' in the dialogue, and click OK. The VBA window will open at the event procedure with the first and last lines already in place. Enter the lines of code between these two existing lines. Make sure that the table and field names in the code exactly match their actual names. BTW 'Me' is just a shorthand way of referring to the current form. Strictly speaking it refers to the instance of the class in which the code is running, but don't worry about that; just think of it as referring to the form. Ken Sheridan Stafford, England "Robert Smith" wrote: I tried and still no luck, It now says it cant find the object Me. I guess Access just isn't for me. I'll keep my form in excel and not mess with the Access bs. Thanks for trying! Robert "Ken Sheridan" wrote in message ... Sorry, I got the two of you mixed up; I meant 'Bruce's reply'. As regards the DefaultValue property, for simplicity I was unduly brief in my explanation as it’s a little more complex in fact. The behaviour if you set it to an expression of the type you posted in the properties sheet is in my experience as follows: |
#8
|
|||
|
|||
Auto Number
I'd completely agree that its far better to use the DefaultValue property in
situations like this rather than setting the Value of a control as the former doesn't Dirty the form, so the user can back out without having to Undo the insert operation. Similarly when passing a value from one form to another via the OpenArgs property, e.g. when opening a separate form to insert a row into a table which references the first form's underlying table, its best to use this to set the DefaultValue property of the control bound to the foreign key in the second form rather than its Value as it again allows the user to bail out gracefully if necessary. Another circumstance when the DefaultValue property is more appropriate is when inserting a new row into a referenced table via the NotInList event procedure of a combo box where its necessary to open a form to insert values into other columns besides that into which the NewData value is to be inserted. Here's an example for adding a new city which opens a form and passes the NewData value to it: Private Sub cboCities_NotInList(NewData As String, Response As Integer) Dim ctrl As Control Dim strMessage As String Set ctrl = Me.ActiveControl strMessage = "Add " & NewData & " to list?" If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then DoCmd.OpenForm "frmCities", _ DataMode:=acFormAdd, _ WindowMode:=acDialog, _ OpenArgs:=NewData ' ensure frmCities closed DoCmd.Close acForm, "frmCities" ' ensure city has been added If Not IsNull(DLookup("CityID", "Cities", "City = """ & _ NewData & """")) Then Response = acDataErrAdded Else strMessage = NewData & " was not added to Cities table." MsgBox strMessage, vbInformation, "Warning" Response = acDataErrContinue ctrl.Undo End If Else Response = acDataErrContinue ctrl.Undo End If End Sub The frmCities form's Open event procedure then uses the OpenArgs property to set the City control's DefaultValue property with: Private Sub Form_Open(Cancel As Integer) If Not IsNull(Me.OpenArgs) Then Me.City.DefaultValue = """" & Me.OpenArgs & """" End If End Sub Consequently the user can just close the form if they decide not to add the city, or they can proceed to add other required data such as the County or State in which the city is located. As you'll have noticed the NotInList event procedure's code checks to see if the new city has been added before adding it to the combo box's list and updating the control. Getting back to the question of deleting records in a form this does require a bit of care as if the normal user interface is used the record disappears from the form immediately, but is of course not finally deleted until the deletion is confirmed. I've always felt this to be a little confusing for users as they see the record disappear, which suggests its been deleted, but are then asked to confirm its deletion apparently after the event. Its not difficult to create a custom confirmation routine with some code in the Delete and BeforeDelConfirm event procedures, however. Ken Sheridan Stafford, England "BruceM" wrote: Thanks for the explanation. I did not know that about inserting and deleting a record immediately, causing the value to skip as you have described. Perhaps this is because I tend to use code rather than the Default Value of a text box. Actually, I tend to use: If Me.NewRecord Then etc. I learned it that way a while ago, and often other things happen for a new record (controls are hidden or disabled or whatever), so there isn't any extra coding (that is, DefaultValue uses one line to accomplish what If Me.NewRecord needs several lines to do, unless there is already is "If Me.NewRecord"). I have since learned that using "If Me.NewRecord" causes the Before Insert event to run before the user has a chance to type anything, and the Dirty event does not run at all, while when using the DefaultValue property the Before Insert event runs when typing begins , and the Dirty event runs too (I think that's how it went). I'm not sure what else happens, but it does tell me that one approach or the other may be needed in specific situations. When people ask about incrementing numbers I usually point them to Roger Carlson's sample database on the topic, which used the Default Value property of a text box. If more complex incrementing is needed I still tend to use the text box Default Value when possible because it is simpler to explain than using VBA, and until now I thought it worked exactly the same way. "Ken Sheridan" wrote in message ... Sorry, I got the two of you mixed up; I meant 'Bruce's reply'. As regards the DefaultValue property, for simplicity I was unduly brief in my explanation as it’s a little more complex in fact. The behaviour if you set it to an expression of the type you posted in the properties sheet is in my experience as follows: 1. If a series of new records are inserted in the form it updates the value correctly for each new record. 2. If a record prior to the latest one is deleted then this obviously leaves a gap in the sequence, correctly so for most purposes (it is of course possible to 'fill the gaps' with a more complex solution if that should be required). 3. However, if a new row is inserted, then deleted in the same session of the form before a further record is inserted then the DefaultValue property remains static so the next number is 2 after the last number in the table rather than 1 after. Now, its arguable that this is correct behaviour as there is no difference in principle between deleting the latest record and a prior one in the sequence. However, in practice that would for most purposes be regarded as incorrect behaviour, as most people would want the number following the latest row existing in the table as of now to be inserted, rather than the number following the latest record which existed before its deletion. It might be thought that its unlikely that this situation would arise, but Murphy's Law states otherwise! So neither solutions are 'incorrect', but setting the value in code seems to me to safely answer the more usual requirements for this sort of thing. The above is really a question of the 'mechanics' of the application, however, and not overly significant. What's more important is the need to control the uniqueness of the year/incrementing number value combination in the table definition, which does require the current year value to be inserted at a column position in each row. This does mean that the table is not properly normalized of course as the DateField column is functionally dependent on only one part of the key, not 'the key, the whole key and nothing but the key, so help me Codd'. But I can't see any way to avoid that (though I'm more than happy to be corrected if otherwise). Another possible solution of course is not to have a current year or incrementing number column at all, but to compute the serial numbers within each year on the fly from the date column. I suspect this would be inappropriate, however, as the numbers would vary as rows were deleted from the table or if a row were inserted out of date sequence, so as part of a fixed identifier would be useless. Ken Sheridan Stafford, England "BruceM" wrote: For some reason I don't see Robert's reply in my newsreader. Your response to my comments (including Robert's reply, is the first I have seen). I'm not sure what you're saying about the Default Value property on the property sheet. If you're saying the Default Value property remains the same for all records until the form is closed and reopened, that has not been my experience. "Ken Sheridan" wrote in message ... You'll need to set the DefaultValue property in code. If you set it in the properties sheet its value will remain static until the form is closed and reopened again. Set it in the form's Current event procedure with: Me.NumInc.DefaultValue = _ """" & Nz(DMax("[NumInc]", "[tblMain]", _ "Year([DateField]) = " & Year(Date)), 0) + 1 & """" Note that the DefaultValue property is a string expression regardless of the data type of the field in question so should be wrapped in quotes characters as above. In fact in this case it wouldn't make any difference if the quotes characters were omitted, but in other circumstances they are crucial, particularly with a date/time data type. BTW the control Robert meant you could hide was the NumInc control, not the unbound control which concatenates the current year value with the formatted NumInc value. Another point is that you won't be able to use the DateField and NumInc fields as the primary key as this would not prevent duplicate NumInc values within one year. You would need to create another field, CurrentYear say, with a DefaultValue property of Year(Date()) and use this and NumInc as the composite primary key. In which case the above code could be changed to: Me.NumInc.DefaultValue = _ """" & Nz(DMax("[NumInc]", "[tblMain]", _ "[CurrentYear] = " & Year(Date)), 0) + 1 & """" The CurrentYear field need not be shown on the form of course. Ken Sheridan Stafford, England "Robert Smith" wrote: Thanks for the information. I tried it exactly like you described and even tried it with a blank database using your exact names but I'm even greener than green and didn't get it to work. The box you said I could hide says 2008-39448 Whenever I press Add Record button I get #Error in both boxes. I'm trying to learn Access but it doesn't seem to be willing... "BruceM" wrote in message ... That wouldn't be autonumber as the term is used in Access. Autonumber is an automatically generated number that is almost certain to be unique, but it is for use by Access, and in most cases will not be seen by the user. Also, it cannot be guaranteed not to leave gaps in the numbering sequence. If it is possible to avoid gaps it is very, very difficult. However, what you want can be done with an expression. First, though, there is no need to store the year if there is a date field in the record. I will call that field DateField, and the incrementing number NumInc. For purposes of this reply, they are both in a table named tblMain, which is the Record Source for a form. On the form, bind a text box to the NumInc field (that is, select NumInc as its Control Source). This is done on the text box property sheet. To see the Property Sheet, open the form in design view, right click on the text box, and select Properties. This will open a box with five tabs (if it is not already open): Format, Data, Event, Other, and All. Click the Data tab to find the place to set the Control Source. You may already know about the Property Sheet, but you identify yourself as a beginner, so there it is just in case. Also on the Data tab is Default Value. This applies only to new records. In the Default Value row add the expression: =Nz(DMax("NumInc","tblMain","Year([DateField]) = " & Year(Date())),0) + 1 You can hide this text box. In another text box, set the Control Source to: =Year([DateField]) & "-" & Format([NumInc],"0000") Look up the functions Nz, DMax, Year, Date, and Format in Help to learn more about what is going on here. In general, the first expression is telling Access to find the largest value in the NumInc field in a record in which the year in DateField is the same as the current year, and to add one to that number. Nz is for the first record of the year. There is no maximum value, so the DMax result is null. Instead of Null, Access uses a 0 as the value, and adds 1 to it. The second expression combines the Year from DateField, a hyphen, and the number formatted with leading zeros as needed for numbers less than 1000. "Robert Smith" wrote in message ... Hello, I'm new to access and trying to learn by reading and trying... I would like to use a primary key ID in a table that would be the current year - 0001 and then auto count up for each record. 2008-0001 2008-0002 Then 1/1/2009 start 2009-0001 2009-0002 and so on. If this is a stupid idea I would like to do this in another field in the table. I'm also looking for a way to Close a record so it can't be changed without first reopening the record. Thanks, Robert |
#9
|
|||
|
|||
Auto Number
Wow, thanks! I had not considered that using Default Value does not dirty
the form, so the user can back out without creating an otherwise empty record. I had observed that phenomenon, as I mentioned earlier, but had not put the pieces together. When I back out of a new record in which a Value is set I have needed to test for other fields being filled in, and undo if the Value is the only field with data. Using DefaultValue will greatly simplify things in that regard. Learning on my own has certainly left some gaps in my understanding. Thanks you for the time and attention you have given to my questions. You have imparted a lot of valuable information that will help me greatly in the future. "Ken Sheridan" wrote in message ... I'd completely agree that its far better to use the DefaultValue property in situations like this rather than setting the Value of a control as the former doesn't Dirty the form, so the user can back out without having to Undo the insert operation. Similarly when passing a value from one form to another via the OpenArgs property, e.g. when opening a separate form to insert a row into a table which references the first form's underlying table, its best to use this to set the DefaultValue property of the control bound to the foreign key in the second form rather than its Value as it again allows the user to bail out gracefully if necessary. Another circumstance when the DefaultValue property is more appropriate is when inserting a new row into a referenced table via the NotInList event procedure of a combo box where its necessary to open a form to insert values into other columns besides that into which the NewData value is to be inserted. Here's an example for adding a new city which opens a form and passes the NewData value to it: Private Sub cboCities_NotInList(NewData As String, Response As Integer) Dim ctrl As Control Dim strMessage As String Set ctrl = Me.ActiveControl strMessage = "Add " & NewData & " to list?" If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then DoCmd.OpenForm "frmCities", _ DataMode:=acFormAdd, _ WindowMode:=acDialog, _ OpenArgs:=NewData ' ensure frmCities closed DoCmd.Close acForm, "frmCities" ' ensure city has been added If Not IsNull(DLookup("CityID", "Cities", "City = """ & _ NewData & """")) Then Response = acDataErrAdded Else strMessage = NewData & " was not added to Cities table." MsgBox strMessage, vbInformation, "Warning" Response = acDataErrContinue ctrl.Undo End If Else Response = acDataErrContinue ctrl.Undo End If End Sub The frmCities form's Open event procedure then uses the OpenArgs property to set the City control's DefaultValue property with: Private Sub Form_Open(Cancel As Integer) If Not IsNull(Me.OpenArgs) Then Me.City.DefaultValue = """" & Me.OpenArgs & """" End If End Sub Consequently the user can just close the form if they decide not to add the city, or they can proceed to add other required data such as the County or State in which the city is located. As you'll have noticed the NotInList event procedure's code checks to see if the new city has been added before adding it to the combo box's list and updating the control. Getting back to the question of deleting records in a form this does require a bit of care as if the normal user interface is used the record disappears from the form immediately, but is of course not finally deleted until the deletion is confirmed. I've always felt this to be a little confusing for users as they see the record disappear, which suggests its been deleted, but are then asked to confirm its deletion apparently after the event. Its not difficult to create a custom confirmation routine with some code in the Delete and BeforeDelConfirm event procedures, however. Ken Sheridan Stafford, England "BruceM" wrote: Thanks for the explanation. I did not know that about inserting and deleting a record immediately, causing the value to skip as you have described. Perhaps this is because I tend to use code rather than the Default Value of a text box. Actually, I tend to use: If Me.NewRecord Then etc. I learned it that way a while ago, and often other things happen for a new record (controls are hidden or disabled or whatever), so there isn't any extra coding (that is, DefaultValue uses one line to accomplish what If Me.NewRecord needs several lines to do, unless there is already is "If Me.NewRecord"). I have since learned that using "If Me.NewRecord" causes the Before Insert event to run before the user has a chance to type anything, and the Dirty event does not run at all, while when using the DefaultValue property the Before Insert event runs when typing begins , and the Dirty event runs too (I think that's how it went). I'm not sure what else happens, but it does tell me that one approach or the other may be needed in specific situations. When people ask about incrementing numbers I usually point them to Roger Carlson's sample database on the topic, which used the Default Value property of a text box. If more complex incrementing is needed I still tend to use the text box Default Value when possible because it is simpler to explain than using VBA, and until now I thought it worked exactly the same way. "Ken Sheridan" wrote in message ... Sorry, I got the two of you mixed up; I meant 'Bruce's reply'. As regards the DefaultValue property, for simplicity I was unduly brief in my explanation as it’s a little more complex in fact. The behaviour if you set it to an expression of the type you posted in the properties sheet is in my experience as follows: 1. If a series of new records are inserted in the form it updates the value correctly for each new record. 2. If a record prior to the latest one is deleted then this obviously leaves a gap in the sequence, correctly so for most purposes (it is of course possible to 'fill the gaps' with a more complex solution if that should be required). 3. However, if a new row is inserted, then deleted in the same session of the form before a further record is inserted then the DefaultValue property remains static so the next number is 2 after the last number in the table rather than 1 after. Now, its arguable that this is correct behaviour as there is no difference in principle between deleting the latest record and a prior one in the sequence. However, in practice that would for most purposes be regarded as incorrect behaviour, as most people would want the number following the latest row existing in the table as of now to be inserted, rather than the number following the latest record which existed before its deletion. It might be thought that its unlikely that this situation would arise, but Murphy's Law states otherwise! So neither solutions are 'incorrect', but setting the value in code seems to me to safely answer the more usual requirements for this sort of thing. The above is really a question of the 'mechanics' of the application, however, and not overly significant. What's more important is the need to control the uniqueness of the year/incrementing number value combination in the table definition, which does require the current year value to be inserted at a column position in each row. This does mean that the table is not properly normalized of course as the DateField column is functionally dependent on only one part of the key, not 'the key, the whole key and nothing but the key, so help me Codd'. But I can't see any way to avoid that (though I'm more than happy to be corrected if otherwise). Another possible solution of course is not to have a current year or incrementing number column at all, but to compute the serial numbers within each year on the fly from the date column. I suspect this would be inappropriate, however, as the numbers would vary as rows were deleted from the table or if a row were inserted out of date sequence, so as part of a fixed identifier would be useless. Ken Sheridan Stafford, England "BruceM" wrote: For some reason I don't see Robert's reply in my newsreader. Your response to my comments (including Robert's reply, is the first I have seen). I'm not sure what you're saying about the Default Value property on the property sheet. If you're saying the Default Value property remains the same for all records until the form is closed and reopened, that has not been my experience. "Ken Sheridan" wrote in message ... You'll need to set the DefaultValue property in code. If you set it in the properties sheet its value will remain static until the form is closed and reopened again. Set it in the form's Current event procedure with: Me.NumInc.DefaultValue = _ """" & Nz(DMax("[NumInc]", "[tblMain]", _ "Year([DateField]) = " & Year(Date)), 0) + 1 & """" Note that the DefaultValue property is a string expression regardless of the data type of the field in question so should be wrapped in quotes characters as above. In fact in this case it wouldn't make any difference if the quotes characters were omitted, but in other circumstances they are crucial, particularly with a date/time data type. BTW the control Robert meant you could hide was the NumInc control, not the unbound control which concatenates the current year value with the formatted NumInc value. Another point is that you won't be able to use the DateField and NumInc fields as the primary key as this would not prevent duplicate NumInc values within one year. You would need to create another field, CurrentYear say, with a DefaultValue property of Year(Date()) and use this and NumInc as the composite primary key. In which case the above code could be changed to: Me.NumInc.DefaultValue = _ """" & Nz(DMax("[NumInc]", "[tblMain]", _ "[CurrentYear] = " & Year(Date)), 0) + 1 & """" The CurrentYear field need not be shown on the form of course. Ken Sheridan Stafford, England "Robert Smith" wrote: Thanks for the information. I tried it exactly like you described and even tried it with a blank database using your exact names but I'm even greener than green and didn't get it to work. The box you said I could hide says 2008-39448 Whenever I press Add Record button I get #Error in both boxes. I'm trying to learn Access but it doesn't seem to be willing... "BruceM" wrote in message ... That wouldn't be autonumber as the term is used in Access. Autonumber is an automatically generated number that is almost certain to be unique, but it is for use by Access, and in most cases will not be seen by the user. Also, it cannot be guaranteed not to leave gaps in the numbering sequence. If it is possible to avoid gaps it is very, very difficult. However, what you want can be done with an expression. First, though, there is no need to store the year if there is a date field in the record. I will call that field DateField, and the incrementing number NumInc. For purposes of this reply, they are both in a table named tblMain, which is the Record Source for a form. On the form, bind a text box to the NumInc field (that is, select NumInc as its Control Source). This is done on the text box property sheet. To see the Property Sheet, open the form in design view, right click on the text box, and select Properties. This will open a box with five tabs (if it is not already open): Format, Data, Event, Other, and All. Click the Data tab to find the place to set the Control Source. You may already know about the Property Sheet, but you identify yourself as a beginner, so there it is just in case. Also on the Data tab is Default Value. This applies only to new records. In the Default Value row add the expression: =Nz(DMax("NumInc","tblMain","Year([DateField]) = " & Year(Date())),0) + 1 You can hide this text box. In another text box, set the Control Source to: =Year([DateField]) & "-" & Format([NumInc],"0000") Look up the functions Nz, DMax, Year, Date, and Format in Help to learn more about what is going on here. In general, the first expression is telling Access to find the largest value in the NumInc field in a record in which the year in DateField is the same as the current year, and to add one to that number. Nz is for the first record of the year. There is no maximum value, so the DMax result is null. Instead of Null, Access uses a 0 as the value, and adds 1 to it. The second expression combines the Year from DateField, a hyphen, and the number formatted with leading zeros as needed for numbers less than 1000. "Robert Smith" wrote in message ... Hello, I'm new to access and trying to learn by reading and trying... I would like to use a primary key ID in a table that would be the current year - 0001 and then auto count up for each record. 2008-0001 2008-0002 Then 1/1/2009 start 2009-0001 2009-0002 and so on. If this is a stupid idea I would like to do this in another field in the table. I'm also looking for a way to Close a record so it can't be changed without first reopening the record. Thanks, Robert |
#10
|
|||
|
|||
Auto Number
-- p120mb "Robert Smith" wrote: Thanks for the information. I tried it exactly like you described and even tried it with a blank database using your exact names but I'm even greener than green and didn't get it to work. The box you said I could hide says 2008-39448 Whenever I press Add Record button I get #Error in both boxes. I'm trying to learn Access but it doesn't seem to be willing... "BruceM" wrote in message ... That wouldn't be autonumber as the term is used in Access. Autonumber is an automatically generated number that is almost certain to be unique, but it is for use by Access, and in most cases will not be seen by the user. Also, it cannot be guaranteed not to leave gaps in the numbering sequence. If it is possible to avoid gaps it is very, very difficult. However, what you want can be done with an expression. First, though, there is no need to store the year if there is a date field in the record. I will call that field DateField, and the incrementing number NumInc. For purposes of this reply, they are both in a table named tblMain, which is the Record Source for a form. On the form, bind a text box to the NumInc field (that is, select NumInc as its Control Source). This is done on the text box property sheet. To see the Property Sheet, open the form in design view, right click on the text box, and select Properties. This will open a box with five tabs (if it is not already open): Format, Data, Event, Other, and All. Click the Data tab to find the place to set the Control Source. You may already know about the Property Sheet, but you identify yourself as a beginner, so there it is just in case. Also on the Data tab is Default Value. This applies only to new records. In the Default Value row add the expression: =Nz(DMax("NumInc","tblMain","Year([DateField]) = " & Year(Date())),0) + 1 You can hide this text box. In another text box, set the Control Source to: =Year([DateField]) & "-" & Format([NumInc],"0000") Look up the functions Nz, DMax, Year, Date, and Format in Help to learn more about what is going on here. In general, the first expression is telling Access to find the largest value in the NumInc field in a record in which the year in DateField is the same as the current year, and to add one to that number. Nz is for the first record of the year. There is no maximum value, so the DMax result is null. Instead of Null, Access uses a 0 as the value, and adds 1 to it. The second expression combines the Year from DateField, a hyphen, and the number formatted with leading zeros as needed for numbers less than 1000. "Robert Smith" wrote in message ... Hello, I'm new to access and trying to learn by reading and trying... I would like to use a primary key ID in a table that would be the current year - 0001 and then auto count up for each record. 2008-0001 2008-0002 Then 1/1/2009 start 2009-0001 2009-0002 and so on. If this is a stupid idea I would like to do this in another field in the table. I'm also looking for a way to Close a record so it can't be changed without first reopening the record. Thanks, Robert |
Thread Tools | |
Display Modes | |
|
|