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 |
#11
|
|||
|
|||
Calculating numbers, AfterUpdate event procedure
Thanks, Ken.
GREAT NEWS!!! I created a fresh form and following your advice. The End Number is now working and the Start Number works for the second package of forms issued. However, when the first package is issued (00001-00025) the start number still shows up as 00000. I edited the control so that the first number equals 1, but when I entered 25 as the quantity issued, the end number was 00026, which makes sense considering the controls. However, how can I get the first number issued to show as 00001? Also, how do I get the date to automatically appear when I tab to that field? I truly appreciate all your help, Ken. "Ken Sheridan" wrote: The only possible problem I can see is that Number_Issued seems to be the name both of the table and a field in it as you've used it for both the field name and, as the second argument of the DMax function, the table name. While unusual, and I'd say inadvisable, I don't think it’s prohibited, however. In my sample code I originally used DocumentsIssued as the table name. Unless the use of Number_Issued here as the table name is incorrect (but I'd expect a run-time error if so) the behaviour you are getting is as though the AfterUpdate event procedure isn't executing at all. I'd suggest you debug it by creating a breakpoint in the procedure; you can then see (a) if it is in fact executing (if it isn't the debugger won't break into the code) and (b) what value is assigned to the intNumberIssued variable, and hence to the End_Number. However, as you do have a date/time field for the issue of the document there is an alternative approach which would mean you only need the Number_Issued field in the table, and neither start nor end number fields as both can be computed. You'd need to be happy with the implications of this, which is what I was referring to by 'rules', as it would mean that if at any time you amended the number issued value in any row in the table all later start and end numbers for issues of that document would be re-computed, rather than being static. This approach requires no code at all as both values can be computed by expressions as the ControlSource properties of unbound controls on a form (or in a report or as computed columns in a query). Lets first be absolutely sure that we understand what is what, so for the sake of the expressions below I'm assuming the following: 1. The table name is DocumentsIssued. 2. The date/time field's name is DateTimeIssued. 3. The number issued field's name is Number_Issued 4. The documents reference is a text field Form_Number 5. The unbound control for the start number is txtStartNumber. 6. The unbound control for the end number is txtEndNumber. For the txtStartNumber control the ControlSource would be as follows. Note that this should all be entered as a single line: =Nz(DSum("Number_Issued","DocumentsIssued","Form_N umber =""" & [Form_Number] & """ And DateTimeIssued #" & Format([DateTimeIssued],"mm/dd/yyyy hh:nn:ss") & "#"),0) And for txtEndNunumber: =[txtStartNumber] + [Number_Issued] The above does assume of course that all date/time values for the issue of a particular form number are distinct. I've also assumed you want the numbering to be zero-based, i.e. the start number will be 0 for the first record per form number. If you want to start from 1 you'd add 1 to each of the ControlSource expressions. Ken Sheridan Stafford, England "Etta" wrote: Actually, the AfterUpdate event procedure attached to Number_Issued is: Option Compare Database Private Sub NumberIssued_AfterUpdate() Dim intNumberIssued As Integer Dim strCriteria As String strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """" intNumberIssued = Nz(DMax("[End_Number]", "[Number_Issued]", strCriteria), 0) Me.[End_Number] = intNumberIssued + Me.[Number_Issued] End Sub The above code results in a Starting Number of -00025 and Ending Number of 00000 when I enter 25 in the Number Issued field. I think I've looked at this too many times and am missing some obvious problem with how I've copied your (Ken's) instructions. "Etta" wrote: I deleted the AfterUpdate event procedure in End_Number and added the following as an AfterUpdate event procedure in Number_Issued: Option Compare Database Private Sub End_Number_AfterUpdate() End Sub End Sub Private Sub Number_Issued_AfterUpdate() Dim intNumber_Issued As Integer Dim strCriteria As String strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """" intNumber_Issued = Nz(DMax("[End_Number]", "[Number_Issued]", strCriteria), 0) Me.[End_Number] = intNumber_Issued + Me.[Number_Issued] End Sub My results are still 025 for the number issued, -00025 as the start number, and 00000 as the end number. I do have a field for date&time when a package of forms is issued. The "rules" for numbering are simple -- once a number is entered in the Number_Issued field, Access should compute the Start_Number and the End_Number. Right now, the Start_Number is being calculated by "=[End_Number]-[Number_Issued]" Thanks again, Ken, for you help. As a novice, I would not have been able to get where I am now in my database without your assistance. "Ken Sheridan" wrote: You say that the code is the AfterUpdate event procedure for the *End_Number* control (though the procedure name suggests otherwise). It should be the AfterUpdate event procedure for the *Number_Issued* control. If it is the latter then I don't understand why this is happening. The intNumberIssued variable should, when the code executes, be assigned a value of zero if no documents of this type have yet been issued. This should then be added to the 25 you entered to give a value of 25 as the End number. The Start Number should be this less 25 to give a value of zero. If the code is in the correct procedure, therefore, I don't at first sight see why you are getting this behaviour and some debugging would be advisable, but if it’s the wrong procedure then that would explain it. Perhaps the simplest solution would be to delete the procedure completely (wherever it is) then recreate it from the Number_Issued control's properties sheet. Do the records have a field for entering the date/time when the documents are issued BTW? If so then that would possibly allow a totally different approach to computing the numbers. It might not be in line with the 'rules' on which the numbering is predicated, however, so I'd need to be sure of the basis of these before suggesting this as an alternative approach. Ken Sheridan Stafford, England "Etta" wrote: Thanks again, Ken. I still need your help. After selecting a form number, which I was able to successfully link to Forms#_Title table, I entered 25 in the Number Issued box, which resulted in -00025 as the Start Number (which is an unbound text box) and 00000 box as the End_Number box. Here is what is in the After Update Even Procedure for End_Number: Private Sub NumberIssued_AfterUpdate() Dim intNumberIssued As Integer Dim strCriteria As String strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """" intNumberIssued = Nz(DMax("[End_Number]", "[DocumentsIssued]", strCriteria), 0) Me.[End_Number] = intNumberIssued + Me.[Number_Issued] I don't have a DocumentsIssued field. Is that what's causing my problem? "Ken Sheridan" wrote: I don’t know how to set up a “computed control on the form with a ControlSource of: =[End_Number] - [Form_Issued] I assume that should be [Number_Issued] not [Form_Issued]. All you do is add an unbound text box to the form from the toolbox, then enter =[End_Number] - [Form_Issued] as its ControlSource property in its properties sheet. I'd also suggest you change its Name property to something meaningful like txtStartNumber. Also, I would like to add the validation feature you mention below. Can you outline the steps I would need to follow? In the Change event procedure of the Number_Issued control put something like the following code: If IsNull(Me.[Number_Issued]) Then MsgBox "Please select a form first.", vbExclamation, "Invalid Operation" Me.[Number_Issued].Undo End If Another issue just arose, Ken. When I typed 25 in the Number_Issued field, I got a syntax error message: Private Sub Number_Issued_AfterUpdate(). How can I fix this? Sounds like there's a syntax error in the AfterUpdate event procedure's code. It should be: Dim intNumberIssued As Integer Dim strCriteria as String strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """" intNumberIssued = Nz(Dmax("[End_Number]","[DocumentsIssued]",strCriteria),0) Me.[End_Number] = intNumberIssued + Me.[Number_Issued] Check that the table and field names in the code are all correct. Ken Sheridan Stafford, England |
#12
|
|||
|
|||
Calculating numbers, Control can't be edited
Hi, Ken,
I need to add three fields from two other tables to the DocumentsIssued form. These fields are Issued To (a drop down menu of all employees, which is linked to an external table), Issued From (a drop down menu of members my department) and Reconciled, which can be a checkbox. I had this working in an older form (Forms Issued), so I copied those fields and labels to DocumentsIssued. Evidently, that's not how things are done, as I now get an error message "Control can't be edited; it's bound to unknown field." How can I add these fields to DocumentsIssued form? "Etta" wrote: Thanks, Ken. GREAT NEWS!!! I created a fresh form and following your advice. The End Number is now working and the Start Number works for the second package of forms issued. However, when the first package is issued (00001-00025) the start number still shows up as 00000. I edited the control so that the first number equals 1, but when I entered 25 as the quantity issued, the end number was 00026, which makes sense considering the controls. However, how can I get the first number issued to show as 00001? Also, how do I get the date to automatically appear when I tab to that field? I truly appreciate all your help, Ken. "Ken Sheridan" wrote: The only possible problem I can see is that Number_Issued seems to be the name both of the table and a field in it as you've used it for both the field name and, as the second argument of the DMax function, the table name. While unusual, and I'd say inadvisable, I don't think it’s prohibited, however. In my sample code I originally used DocumentsIssued as the table name. Unless the use of Number_Issued here as the table name is incorrect (but I'd expect a run-time error if so) the behaviour you are getting is as though the AfterUpdate event procedure isn't executing at all. I'd suggest you debug it by creating a breakpoint in the procedure; you can then see (a) if it is in fact executing (if it isn't the debugger won't break into the code) and (b) what value is assigned to the intNumberIssued variable, and hence to the End_Number. However, as you do have a date/time field for the issue of the document there is an alternative approach which would mean you only need the Number_Issued field in the table, and neither start nor end number fields as both can be computed. You'd need to be happy with the implications of this, which is what I was referring to by 'rules', as it would mean that if at any time you amended the number issued value in any row in the table all later start and end numbers for issues of that document would be re-computed, rather than being static. This approach requires no code at all as both values can be computed by expressions as the ControlSource properties of unbound controls on a form (or in a report or as computed columns in a query). Lets first be absolutely sure that we understand what is what, so for the sake of the expressions below I'm assuming the following: 1. The table name is DocumentsIssued. 2. The date/time field's name is DateTimeIssued. 3. The number issued field's name is Number_Issued 4. The documents reference is a text field Form_Number 5. The unbound control for the start number is txtStartNumber. 6. The unbound control for the end number is txtEndNumber. For the txtStartNumber control the ControlSource would be as follows. Note that this should all be entered as a single line: =Nz(DSum("Number_Issued","DocumentsIssued","Form_N umber =""" & [Form_Number] & """ And DateTimeIssued #" & Format([DateTimeIssued],"mm/dd/yyyy hh:nn:ss") & "#"),0) And for txtEndNunumber: =[txtStartNumber] + [Number_Issued] The above does assume of course that all date/time values for the issue of a particular form number are distinct. I've also assumed you want the numbering to be zero-based, i.e. the start number will be 0 for the first record per form number. If you want to start from 1 you'd add 1 to each of the ControlSource expressions. Ken Sheridan Stafford, England "Etta" wrote: Actually, the AfterUpdate event procedure attached to Number_Issued is: Option Compare Database Private Sub NumberIssued_AfterUpdate() Dim intNumberIssued As Integer Dim strCriteria As String strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """" intNumberIssued = Nz(DMax("[End_Number]", "[Number_Issued]", strCriteria), 0) Me.[End_Number] = intNumberIssued + Me.[Number_Issued] End Sub The above code results in a Starting Number of -00025 and Ending Number of 00000 when I enter 25 in the Number Issued field. I think I've looked at this too many times and am missing some obvious problem with how I've copied your (Ken's) instructions. "Etta" wrote: I deleted the AfterUpdate event procedure in End_Number and added the following as an AfterUpdate event procedure in Number_Issued: Option Compare Database Private Sub End_Number_AfterUpdate() End Sub End Sub Private Sub Number_Issued_AfterUpdate() Dim intNumber_Issued As Integer Dim strCriteria As String strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """" intNumber_Issued = Nz(DMax("[End_Number]", "[Number_Issued]", strCriteria), 0) Me.[End_Number] = intNumber_Issued + Me.[Number_Issued] End Sub My results are still 025 for the number issued, -00025 as the start number, and 00000 as the end number. I do have a field for date&time when a package of forms is issued. The "rules" for numbering are simple -- once a number is entered in the Number_Issued field, Access should compute the Start_Number and the End_Number. Right now, the Start_Number is being calculated by "=[End_Number]-[Number_Issued]" Thanks again, Ken, for you help. As a novice, I would not have been able to get where I am now in my database without your assistance. "Ken Sheridan" wrote: You say that the code is the AfterUpdate event procedure for the *End_Number* control (though the procedure name suggests otherwise). It should be the AfterUpdate event procedure for the *Number_Issued* control. If it is the latter then I don't understand why this is happening. The intNumberIssued variable should, when the code executes, be assigned a value of zero if no documents of this type have yet been issued. This should then be added to the 25 you entered to give a value of 25 as the End number. The Start Number should be this less 25 to give a value of zero. If the code is in the correct procedure, therefore, I don't at first sight see why you are getting this behaviour and some debugging would be advisable, but if it’s the wrong procedure then that would explain it. Perhaps the simplest solution would be to delete the procedure completely (wherever it is) then recreate it from the Number_Issued control's properties sheet. Do the records have a field for entering the date/time when the documents are issued BTW? If so then that would possibly allow a totally different approach to computing the numbers. It might not be in line with the 'rules' on which the numbering is predicated, however, so I'd need to be sure of the basis of these before suggesting this as an alternative approach. Ken Sheridan Stafford, England "Etta" wrote: Thanks again, Ken. I still need your help. After selecting a form number, which I was able to successfully link to Forms#_Title table, I entered 25 in the Number Issued box, which resulted in -00025 as the Start Number (which is an unbound text box) and 00000 box as the End_Number box. Here is what is in the After Update Even Procedure for End_Number: Private Sub NumberIssued_AfterUpdate() Dim intNumberIssued As Integer Dim strCriteria As String strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """" intNumberIssued = Nz(DMax("[End_Number]", "[DocumentsIssued]", strCriteria), 0) Me.[End_Number] = intNumberIssued + Me.[Number_Issued] I don't have a DocumentsIssued field. Is that what's causing my problem? "Ken Sheridan" wrote: I don’t know how to set up a “computed control on the form with a ControlSource of: =[End_Number] - [Form_Issued] I assume that should be [Number_Issued] not [Form_Issued]. All you do is add an unbound text box to the form from the toolbox, then enter =[End_Number] - [Form_Issued] as its ControlSource property in its properties sheet. I'd also suggest you change its Name property to something meaningful like txtStartNumber. Also, I would like to add the validation feature you mention below. Can you outline the steps I would need to follow? In the Change event procedure of the Number_Issued control put something like the following code: If IsNull(Me.[Number_Issued]) Then MsgBox "Please select a form first.", vbExclamation, "Invalid Operation" Me.[Number_Issued].Undo End If Another issue just arose, Ken. When I typed 25 in the Number_Issued field, I got a syntax error message: Private Sub Number_Issued_AfterUpdate(). How can I fix this? Sounds like there's a syntax error in the AfterUpdate event procedure's code. It should be: Dim intNumberIssued As Integer Dim strCriteria as String strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """" intNumberIssued = Nz(Dmax("[End_Number]","[DocumentsIssued]",strCriteria),0) Me.[End_Number] = intNumberIssued + Me.[Number_Issued] Check that the table and field names in the code are all correct. Ken Sheridan Stafford, England |
#13
|
|||
|
|||
Calculating numbers, AfterUpdate event procedure
You don't say how you are approaching it, the original way or computing on
the basis of the date/time value. I'm assuming its still the original way. If the start number is to be 1 for the first package issued the start number for the second package would logically then be 26 (if 25 forms were issued in the first record), not 25, otherwise there is an inconsistency in the bases used between the first issue and subsequent ones. That's easily engineered by adding 1 to the start number: =[End_Number]-[Number_Issued] + 1 If, however, you want the first start number to be 1 but (accepting the logical inconsistency) the second to be 25, and the start numbers thereafter to be the same as the preceding end number, then you'd have to make it conditional like so: =IIf(IsNull(DLookup("[End_Number]", "["YourTableName]", "[Form_Number] = """ & [Form_Number] & """")), 1, [End_Number]-[Number_Issued] + 1) To get the date/time to automatically show the simplest thing is to set its DefaultValue property to the Now() function. That will show it as soon as you move the form to a new record. To show it only when focus is moved to the control on the form put the following in its GotFocus event procedu If Not IsNull([YourDateTimeControl]) Then [YourDateTimeControl] = Now() End If Ken Sheridan Stafford, England "Etta" wrote: Thanks, Ken. GREAT NEWS!!! I created a fresh form and following your advice. The End Number is now working and the Start Number works for the second package of forms issued. However, when the first package is issued (00001-00025) the start number still shows up as 00000. I edited the control so that the first number equals 1, but when I entered 25 as the quantity issued, the end number was 00026, which makes sense considering the controls. However, how can I get the first number issued to show as 00001? Also, how do I get the date to automatically appear when I tab to that field? I truly appreciate all your help, Ken. "Ken Sheridan" wrote: The only possible problem I can see is that Number_Issued seems to be the name both of the table and a field in it as you've used it for both the field name and, as the second argument of the DMax function, the table name. While unusual, and I'd say inadvisable, I don't think it’s prohibited, however. In my sample code I originally used DocumentsIssued as the table name. Unless the use of Number_Issued here as the table name is incorrect (but I'd expect a run-time error if so) the behaviour you are getting is as though the AfterUpdate event procedure isn't executing at all. I'd suggest you debug it by creating a breakpoint in the procedure; you can then see (a) if it is in fact executing (if it isn't the debugger won't break into the code) and (b) what value is assigned to the intNumberIssued variable, and hence to the End_Number. However, as you do have a date/time field for the issue of the document there is an alternative approach which would mean you only need the Number_Issued field in the table, and neither start nor end number fields as both can be computed. You'd need to be happy with the implications of this, which is what I was referring to by 'rules', as it would mean that if at any time you amended the number issued value in any row in the table all later start and end numbers for issues of that document would be re-computed, rather than being static. This approach requires no code at all as both values can be computed by expressions as the ControlSource properties of unbound controls on a form (or in a report or as computed columns in a query). Lets first be absolutely sure that we understand what is what, so for the sake of the expressions below I'm assuming the following: 1. The table name is DocumentsIssued. 2. The date/time field's name is DateTimeIssued. 3. The number issued field's name is Number_Issued 4. The documents reference is a text field Form_Number 5. The unbound control for the start number is txtStartNumber. 6. The unbound control for the end number is txtEndNumber. For the txtStartNumber control the ControlSource would be as follows. Note that this should all be entered as a single line: =Nz(DSum("Number_Issued","DocumentsIssued","Form_N umber =""" & [Form_Number] & """ And DateTimeIssued #" & Format([DateTimeIssued],"mm/dd/yyyy hh:nn:ss") & "#"),0) And for txtEndNunumber: =[txtStartNumber] + [Number_Issued] The above does assume of course that all date/time values for the issue of a particular form number are distinct. I've also assumed you want the numbering to be zero-based, i.e. the start number will be 0 for the first record per form number. If you want to start from 1 you'd add 1 to each of the ControlSource expressions. Ken Sheridan Stafford, England "Etta" wrote: Actually, the AfterUpdate event procedure attached to Number_Issued is: Option Compare Database Private Sub NumberIssued_AfterUpdate() Dim intNumberIssued As Integer Dim strCriteria As String strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """" intNumberIssued = Nz(DMax("[End_Number]", "[Number_Issued]", strCriteria), 0) Me.[End_Number] = intNumberIssued + Me.[Number_Issued] End Sub The above code results in a Starting Number of -00025 and Ending Number of 00000 when I enter 25 in the Number Issued field. I think I've looked at this too many times and am missing some obvious problem with how I've copied your (Ken's) instructions. "Etta" wrote: I deleted the AfterUpdate event procedure in End_Number and added the following as an AfterUpdate event procedure in Number_Issued: Option Compare Database Private Sub End_Number_AfterUpdate() End Sub End Sub Private Sub Number_Issued_AfterUpdate() Dim intNumber_Issued As Integer Dim strCriteria As String strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """" intNumber_Issued = Nz(DMax("[End_Number]", "[Number_Issued]", strCriteria), 0) Me.[End_Number] = intNumber_Issued + Me.[Number_Issued] End Sub My results are still 025 for the number issued, -00025 as the start number, and 00000 as the end number. I do have a field for date&time when a package of forms is issued. The "rules" for numbering are simple -- once a number is entered in the Number_Issued field, Access should compute the Start_Number and the End_Number. Right now, the Start_Number is being calculated by "=[End_Number]-[Number_Issued]" Thanks again, Ken, for you help. As a novice, I would not have been able to get where I am now in my database without your assistance. "Ken Sheridan" wrote: You say that the code is the AfterUpdate event procedure for the *End_Number* control (though the procedure name suggests otherwise). It should be the AfterUpdate event procedure for the *Number_Issued* control. If it is the latter then I don't understand why this is happening. The intNumberIssued variable should, when the code executes, be assigned a value of zero if no documents of this type have yet been issued. This should then be added to the 25 you entered to give a value of 25 as the End number. The Start Number should be this less 25 to give a value of zero. If the code is in the correct procedure, therefore, I don't at first sight see why you are getting this behaviour and some debugging would be advisable, but if it’s the wrong procedure then that would explain it. Perhaps the simplest solution would be to delete the procedure completely (wherever it is) then recreate it from the Number_Issued control's properties sheet. Do the records have a field for entering the date/time when the documents are issued BTW? If so then that would possibly allow a totally different approach to computing the numbers. It might not be in line with the 'rules' on which the numbering is predicated, however, so I'd need to be sure of the basis of these before suggesting this as an alternative approach. Ken Sheridan Stafford, England "Etta" wrote: Thanks again, Ken. I still need your help. After selecting a form number, which I was able to successfully link to Forms#_Title table, I entered 25 in the Number Issued box, which resulted in -00025 as the Start Number (which is an unbound text box) and 00000 box as the End_Number box. Here is what is in the After Update Even Procedure for End_Number: Private Sub NumberIssued_AfterUpdate() Dim intNumberIssued As Integer Dim strCriteria As String strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """" intNumberIssued = Nz(DMax("[End_Number]", "[DocumentsIssued]", strCriteria), 0) Me.[End_Number] = intNumberIssued + Me.[Number_Issued] I don't have a DocumentsIssued field. Is that what's causing my problem? "Ken Sheridan" wrote: I don’t know how to set up a “computed control on the form with a ControlSource of: =[End_Number] - [Form_Issued] I assume that should be [Number_Issued] not [Form_Issued]. All you do is add an unbound text box to the form from the toolbox, then enter =[End_Number] - [Form_Issued] as its ControlSource property in its properties sheet. I'd also suggest you change its Name property to something meaningful like txtStartNumber. Also, I would like to add the validation feature you mention below. Can you outline the steps I would need to follow? In the Change event procedure of the Number_Issued control put something like the following code: If IsNull(Me.[Number_Issued]) Then MsgBox "Please select a form first.", vbExclamation, "Invalid Operation" Me.[Number_Issued].Undo End If Another issue just arose, Ken. When I typed 25 in the Number_Issued field, I got a syntax error message: Private Sub Number_Issued_AfterUpdate(). How can I fix this? Sounds like there's a syntax error in the AfterUpdate event procedure's code. It should be: Dim intNumberIssued As Integer Dim strCriteria as String strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """" intNumberIssued = Nz(Dmax("[End_Number]","[DocumentsIssued]",strCriteria),0) Me.[End_Number] = intNumberIssued + Me.[Number_Issued] Check that the table and field names in the code are all correct. Ken Sheridan Stafford, England |
#14
|
|||
|
|||
Calculating numbers, Control can't be edited
For the first two, Issued To and Issued By, you need foreign key columns in
the DocumentsIssued table. These would normally be numbers of long integer data type referencing the primary keys of the Employees table (which can be an autonumber). Do not use names as keys; they can be duplicated. The names of the foreign key columns would be the ControlSource properties of two combo boxes on the form with a RowSource property such as: SELECT EmployeeID, FirstName & " " & LastName AS FullName FROM Employees ORDER BY LastName, FirstName; And with a BoundColumn property of 1, a ColumnCount property of 2 and a ColumnWidths property of 0cm;8cm (the first dimension must be zero but the size of the second isn't crucial so long as its at least as wide as the combo box). The Reconciled column would be a Boolean (Yes/No) column in the DocumentsIssued table and would be the ControlSource property of a check box on the form. Ken Sheridan Stafford, England "Etta" wrote: Hi, Ken, I need to add three fields from two other tables to the DocumentsIssued form. These fields are Issued To (a drop down menu of all employees, which is linked to an external table), Issued From (a drop down menu of members my department) and Reconciled, which can be a checkbox. I had this working in an older form (Forms Issued), so I copied those fields and labels to DocumentsIssued. Evidently, that's not how things are done, as I now get an error message "Control can't be edited; it's bound to unknown field." How can I add these fields to DocumentsIssued form? "Etta" wrote: Thanks, Ken. GREAT NEWS!!! I created a fresh form and following your advice. The End Number is now working and the Start Number works for the second package of forms issued. However, when the first package is issued (00001-00025) the start number still shows up as 00000. I edited the control so that the first number equals 1, but when I entered 25 as the quantity issued, the end number was 00026, which makes sense considering the controls. However, how can I get the first number issued to show as 00001? Also, how do I get the date to automatically appear when I tab to that field? I truly appreciate all your help, Ken. "Ken Sheridan" wrote: The only possible problem I can see is that Number_Issued seems to be the name both of the table and a field in it as you've used it for both the field name and, as the second argument of the DMax function, the table name. While unusual, and I'd say inadvisable, I don't think it’s prohibited, however. In my sample code I originally used DocumentsIssued as the table name. Unless the use of Number_Issued here as the table name is incorrect (but I'd expect a run-time error if so) the behaviour you are getting is as though the AfterUpdate event procedure isn't executing at all. I'd suggest you debug it by creating a breakpoint in the procedure; you can then see (a) if it is in fact executing (if it isn't the debugger won't break into the code) and (b) what value is assigned to the intNumberIssued variable, and hence to the End_Number. However, as you do have a date/time field for the issue of the document there is an alternative approach which would mean you only need the Number_Issued field in the table, and neither start nor end number fields as both can be computed. You'd need to be happy with the implications of this, which is what I was referring to by 'rules', as it would mean that if at any time you amended the number issued value in any row in the table all later start and end numbers for issues of that document would be re-computed, rather than being static. This approach requires no code at all as both values can be computed by expressions as the ControlSource properties of unbound controls on a form (or in a report or as computed columns in a query). Lets first be absolutely sure that we understand what is what, so for the sake of the expressions below I'm assuming the following: 1. The table name is DocumentsIssued. 2. The date/time field's name is DateTimeIssued. 3. The number issued field's name is Number_Issued 4. The documents reference is a text field Form_Number 5. The unbound control for the start number is txtStartNumber. 6. The unbound control for the end number is txtEndNumber. For the txtStartNumber control the ControlSource would be as follows. Note that this should all be entered as a single line: =Nz(DSum("Number_Issued","DocumentsIssued","Form_N umber =""" & [Form_Number] & """ And DateTimeIssued #" & Format([DateTimeIssued],"mm/dd/yyyy hh:nn:ss") & "#"),0) And for txtEndNunumber: =[txtStartNumber] + [Number_Issued] The above does assume of course that all date/time values for the issue of a particular form number are distinct. I've also assumed you want the numbering to be zero-based, i.e. the start number will be 0 for the first record per form number. If you want to start from 1 you'd add 1 to each of the ControlSource expressions. Ken Sheridan Stafford, England "Etta" wrote: Actually, the AfterUpdate event procedure attached to Number_Issued is: Option Compare Database Private Sub NumberIssued_AfterUpdate() Dim intNumberIssued As Integer Dim strCriteria As String strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """" intNumberIssued = Nz(DMax("[End_Number]", "[Number_Issued]", strCriteria), 0) Me.[End_Number] = intNumberIssued + Me.[Number_Issued] End Sub The above code results in a Starting Number of -00025 and Ending Number of 00000 when I enter 25 in the Number Issued field. I think I've looked at this too many times and am missing some obvious problem with how I've copied your (Ken's) instructions. "Etta" wrote: I deleted the AfterUpdate event procedure in End_Number and added the following as an AfterUpdate event procedure in Number_Issued: Option Compare Database Private Sub End_Number_AfterUpdate() End Sub End Sub Private Sub Number_Issued_AfterUpdate() Dim intNumber_Issued As Integer Dim strCriteria As String strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """" intNumber_Issued = Nz(DMax("[End_Number]", "[Number_Issued]", strCriteria), 0) Me.[End_Number] = intNumber_Issued + Me.[Number_Issued] End Sub My results are still 025 for the number issued, -00025 as the start number, and 00000 as the end number. I do have a field for date&time when a package of forms is issued. The "rules" for numbering are simple -- once a number is entered in the Number_Issued field, Access should compute the Start_Number and the End_Number. Right now, the Start_Number is being calculated by "=[End_Number]-[Number_Issued]" Thanks again, Ken, for you help. As a novice, I would not have been able to get where I am now in my database without your assistance. "Ken Sheridan" wrote: You say that the code is the AfterUpdate event procedure for the *End_Number* control (though the procedure name suggests otherwise). It should be the AfterUpdate event procedure for the *Number_Issued* control. If it is the latter then I don't understand why this is happening. The intNumberIssued variable should, when the code executes, be assigned a value of zero if no documents of this type have yet been issued. This should then be added to the 25 you entered to give a value of 25 as the End number. The Start Number should be this less 25 to give a value of zero. If the code is in the correct procedure, therefore, I don't at first sight see why you are getting this behaviour and some debugging would be advisable, but if it’s the wrong procedure then that would explain it. Perhaps the simplest solution would be to delete the procedure completely (wherever it is) then recreate it from the Number_Issued control's properties sheet. Do the records have a field for entering the date/time when the documents are issued BTW? If so then that would possibly allow a totally different approach to computing the numbers. It might not be in line with the 'rules' on which the numbering is predicated, however, so I'd need to be sure of the basis of these before suggesting this as an alternative approach. Ken Sheridan Stafford, England "Etta" wrote: Thanks again, Ken. I still need your help. After selecting a form number, which I was able to successfully link to Forms#_Title table, I entered 25 in the Number Issued box, which resulted in -00025 as the Start Number (which is an unbound text box) and 00000 box as the End_Number box. Here is what is in the After Update Even Procedure for End_Number: Private Sub NumberIssued_AfterUpdate() Dim intNumberIssued As Integer Dim strCriteria As String strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """" intNumberIssued = Nz(DMax("[End_Number]", "[DocumentsIssued]", strCriteria), 0) Me.[End_Number] = intNumberIssued + Me.[Number_Issued] I don't have a DocumentsIssued field. Is that what's causing my problem? "Ken Sheridan" wrote: I don’t know how to set up a “computed control on the form with a ControlSource of: =[End_Number] - [Form_Issued] I assume that should be [Number_Issued] not [Form_Issued]. All you do is add an unbound text box to the form from the toolbox, then enter =[End_Number] - [Form_Issued] as its ControlSource property in its properties sheet. I'd also suggest you change its Name property to something meaningful like txtStartNumber. Also, I would like to add the validation feature you mention below. Can you outline the steps I would need to follow? In the Change event procedure of the Number_Issued control put something like the following code: If IsNull(Me.[Number_Issued]) Then MsgBox "Please select a form first.", vbExclamation, "Invalid Operation" Me.[Number_Issued].Undo End If Another issue just arose, Ken. When I typed 25 in the Number_Issued field, I got a syntax error message: Private Sub Number_Issued_AfterUpdate(). How can I fix this? Sounds like there's a syntax error in the AfterUpdate event procedure's code. It should be: Dim intNumberIssued As Integer Dim strCriteria as String strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """" intNumberIssued = Nz(Dmax("[End_Number]","[DocumentsIssued]",strCriteria),0) Me.[End_Number] = intNumberIssued + Me.[Number_Issued] Check that the table and field names in the code are all correct. Ken Sheridan Stafford, England |
#15
|
|||
|
|||
Calculating numbers, AfterUpdate event procedure
Hi, Ken,
I should have mentioned that I went with your suggestion of using the date/time value with the following as the Control Source: a=Nz(DSum("Number_Issued","DocumentsIssued","Form_ Number =""" & [Form_Number] & """ And DateTimeIssued #" & Format([DateTimeIssued],"mm/dd/yyyy hh:nn:ss") & "#"),0)" How can this be modified to result in 00001 as the start number for the first package and 00025 the end number for the first package of 25? The second package of 25 would start with 00026 and end with 00050. As ever, thanks for your help. "Ken Sheridan" wrote: You don't say how you are approaching it, the original way or computing on the basis of the date/time value. I'm assuming its still the original way. If the start number is to be 1 for the first package issued the start number for the second package would logically then be 26 (if 25 forms were issued in the first record), not 25, otherwise there is an inconsistency in the bases used between the first issue and subsequent ones. That's easily engineered by adding 1 to the start number: =[End_Number]-[Number_Issued] + 1 If, however, you want the first start number to be 1 but (accepting the logical inconsistency) the second to be 25, and the start numbers thereafter to be the same as the preceding end number, then you'd have to make it conditional like so: =IIf(IsNull(DLookup("[End_Number]", "["YourTableName]", "[Form_Number] = """ & [Form_Number] & """")), 1, [End_Number]-[Number_Issued] + 1) To get the date/time to automatically show the simplest thing is to set its DefaultValue property to the Now() function. That will show it as soon as you move the form to a new record. To show it only when focus is moved to the control on the form put the following in its GotFocus event procedu If Not IsNull([YourDateTimeControl]) Then [YourDateTimeControl] = Now() End If Ken Sheridan Stafford, England "Etta" wrote: Thanks, Ken. GREAT NEWS!!! I created a fresh form and following your advice. The End Number is now working and the Start Number works for the second package of forms issued. However, when the first package is issued (00001-00025) the start number still shows up as 00000. I edited the control so that the first number equals 1, but when I entered 25 as the quantity issued, the end number was 00026, which makes sense considering the controls. However, how can I get the first number issued to show as 00001? Also, how do I get the date to automatically appear when I tab to that field? I truly appreciate all your help, Ken. "Ken Sheridan" wrote: The only possible problem I can see is that Number_Issued seems to be the name both of the table and a field in it as you've used it for both the field name and, as the second argument of the DMax function, the table name. While unusual, and I'd say inadvisable, I don't think it’s prohibited, however. In my sample code I originally used DocumentsIssued as the table name. Unless the use of Number_Issued here as the table name is incorrect (but I'd expect a run-time error if so) the behaviour you are getting is as though the AfterUpdate event procedure isn't executing at all. I'd suggest you debug it by creating a breakpoint in the procedure; you can then see (a) if it is in fact executing (if it isn't the debugger won't break into the code) and (b) what value is assigned to the intNumberIssued variable, and hence to the End_Number. However, as you do have a date/time field for the issue of the document there is an alternative approach which would mean you only need the Number_Issued field in the table, and neither start nor end number fields as both can be computed. You'd need to be happy with the implications of this, which is what I was referring to by 'rules', as it would mean that if at any time you amended the number issued value in any row in the table all later start and end numbers for issues of that document would be re-computed, rather than being static. This approach requires no code at all as both values can be computed by expressions as the ControlSource properties of unbound controls on a form (or in a report or as computed columns in a query). Lets first be absolutely sure that we understand what is what, so for the sake of the expressions below I'm assuming the following: 1. The table name is DocumentsIssued. 2. The date/time field's name is DateTimeIssued. 3. The number issued field's name is Number_Issued 4. The documents reference is a text field Form_Number 5. The unbound control for the start number is txtStartNumber. 6. The unbound control for the end number is txtEndNumber. For the txtStartNumber control the ControlSource would be as follows. Note that this should all be entered as a single line: =Nz(DSum("Number_Issued","DocumentsIssued","Form_N umber =""" & [Form_Number] & """ And DateTimeIssued #" & Format([DateTimeIssued],"mm/dd/yyyy hh:nn:ss") & "#"),0) And for txtEndNunumber: =[txtStartNumber] + [Number_Issued] The above does assume of course that all date/time values for the issue of a particular form number are distinct. I've also assumed you want the numbering to be zero-based, i.e. the start number will be 0 for the first record per form number. If you want to start from 1 you'd add 1 to each of the ControlSource expressions. Ken Sheridan Stafford, England "Etta" wrote: Actually, the AfterUpdate event procedure attached to Number_Issued is: Option Compare Database Private Sub NumberIssued_AfterUpdate() Dim intNumberIssued As Integer Dim strCriteria As String strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """" intNumberIssued = Nz(DMax("[End_Number]", "[Number_Issued]", strCriteria), 0) Me.[End_Number] = intNumberIssued + Me.[Number_Issued] End Sub The above code results in a Starting Number of -00025 and Ending Number of 00000 when I enter 25 in the Number Issued field. I think I've looked at this too many times and am missing some obvious problem with how I've copied your (Ken's) instructions. "Etta" wrote: I deleted the AfterUpdate event procedure in End_Number and added the following as an AfterUpdate event procedure in Number_Issued: Option Compare Database Private Sub End_Number_AfterUpdate() End Sub End Sub Private Sub Number_Issued_AfterUpdate() Dim intNumber_Issued As Integer Dim strCriteria As String strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """" intNumber_Issued = Nz(DMax("[End_Number]", "[Number_Issued]", strCriteria), 0) Me.[End_Number] = intNumber_Issued + Me.[Number_Issued] End Sub My results are still 025 for the number issued, -00025 as the start number, and 00000 as the end number. I do have a field for date&time when a package of forms is issued. The "rules" for numbering are simple -- once a number is entered in the Number_Issued field, Access should compute the Start_Number and the End_Number. Right now, the Start_Number is being calculated by "=[End_Number]-[Number_Issued]" Thanks again, Ken, for you help. As a novice, I would not have been able to get where I am now in my database without your assistance. "Ken Sheridan" wrote: You say that the code is the AfterUpdate event procedure for the *End_Number* control (though the procedure name suggests otherwise). It should be the AfterUpdate event procedure for the *Number_Issued* control. If it is the latter then I don't understand why this is happening. The intNumberIssued variable should, when the code executes, be assigned a value of zero if no documents of this type have yet been issued. This should then be added to the 25 you entered to give a value of 25 as the End number. The Start Number should be this less 25 to give a value of zero. If the code is in the correct procedure, therefore, I don't at first sight see why you are getting this behaviour and some debugging would be advisable, but if it’s the wrong procedure then that would explain it. Perhaps the simplest solution would be to delete the procedure completely (wherever it is) then recreate it from the Number_Issued control's properties sheet. Do the records have a field for entering the date/time when the documents are issued BTW? If so then that would possibly allow a totally different approach to computing the numbers. It might not be in line with the 'rules' on which the numbering is predicated, however, so I'd need to be sure of the basis of these before suggesting this as an alternative approach. Ken Sheridan Stafford, England "Etta" wrote: Thanks again, Ken. I still need your help. After selecting a form number, which I was able to successfully link to Forms#_Title table, I entered 25 in the Number Issued box, which resulted in -00025 as the Start Number (which is an unbound text box) and 00000 box as the End_Number box. Here is what is in the After Update Even Procedure for End_Number: Private Sub NumberIssued_AfterUpdate() Dim intNumberIssued As Integer Dim strCriteria As String strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """" intNumberIssued = Nz(DMax("[End_Number]", "[DocumentsIssued]", strCriteria), 0) Me.[End_Number] = intNumberIssued + Me.[Number_Issued] I don't have a DocumentsIssued field. Is that what's causing my problem? "Ken Sheridan" wrote: I don’t know how to set up a “computed control on the form with a ControlSource of: =[End_Number] - [Form_Issued] I assume that should be [Number_Issued] not [Form_Issued]. All you do is add an unbound text box to the form from the toolbox, then enter =[End_Number] - [Form_Issued] as its ControlSource property in its properties sheet. I'd also suggest you change its Name property to something meaningful like txtStartNumber. Also, I would like to add the validation feature you mention below. Can you outline the steps I would need to follow? In the Change event procedure of the Number_Issued control put something like the following code: If IsNull(Me.[Number_Issued]) Then MsgBox "Please select a form first.", vbExclamation, "Invalid Operation" Me.[Number_Issued].Undo End If Another issue just arose, Ken. When I typed 25 in the Number_Issued field, I got a syntax error message: Private Sub Number_Issued_AfterUpdate(). How can I fix this? Sounds like there's a syntax error in the AfterUpdate event procedure's code. It should be: Dim intNumberIssued As Integer Dim strCriteria as String strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """" intNumberIssued = Nz(Dmax("[End_Number]","[DocumentsIssued]",strCriteria),0) Me.[End_Number] = intNumberIssued + Me.[Number_Issued] Check that the table and field names in the code are all correct. Ken Sheridan Stafford, England |
#16
|
|||
|
|||
Calculating numbers, Control can't be edited
Hi, Ken,
I added the two foreign key columns in DocumentsIssued Table and titled them Issued To and Issued By, with number as data type for each. Is that correct? The primary key for Issued To is Emp#, which is a unique number in the Employee Table, and the primary key for Dept_Staff is an autonumber. On the DocumentsIssued Form, I added the combo box for Issued To,with the row source set as SELECT Employee.EmpNum, Employee.EmpLastName, Employee.EmpFirstName FROM Employee ORDER BY [EmpLastName]; This results in the list of employees appearing when I tab to that field, but when I attempt to select a name, I get the same old error message: Control can't be edited; it is bound to unknown field "Issued To." The same thing happens with the Issued By field, which has the row source of SELECT QA_Staff.[QA_ID], QA_Staff.Name FROM QA_Staff ORDER BY [Name]; . That is the error message "Control can't be edited; it is bound to unknown field "QA_Staff," appears. On the bright side, I did manage to get the Reconciled column to work just fine! "Ken Sheridan" wrote: For the first two, Issued To and Issued By, you need foreign key columns in the DocumentsIssued table. These would normally be numbers of long integer data type referencing the primary keys of the Employees table (which can be an autonumber). Do not use names as keys; they can be duplicated. The names of the foreign key columns would be the ControlSource properties of two combo boxes on the form with a RowSource property such as: SELECT EmployeeID, FirstName & " " & LastName AS FullName FROM Employees ORDER BY LastName, FirstName; And with a BoundColumn property of 1, a ColumnCount property of 2 and a ColumnWidths property of 0cm;8cm (the first dimension must be zero but the size of the second isn't crucial so long as its at least as wide as the combo box). The Reconciled column would be a Boolean (Yes/No) column in the DocumentsIssued table and would be the ControlSource property of a check box on the form. Ken Sheridan Stafford, England "Etta" wrote: Hi, Ken, I need to add three fields from two other tables to the DocumentsIssued form. These fields are Issued To (a drop down menu of all employees, which is linked to an external table), Issued From (a drop down menu of members my department) and Reconciled, which can be a checkbox. I had this working in an older form (Forms Issued), so I copied those fields and labels to DocumentsIssued. Evidently, that's not how things are done, as I now get an error message "Control can't be edited; it's bound to unknown field." How can I add these fields to DocumentsIssued form? "Etta" wrote: Thanks, Ken. GREAT NEWS!!! I created a fresh form and following your advice. The End Number is now working and the Start Number works for the second package of forms issued. However, when the first package is issued (00001-00025) the start number still shows up as 00000. I edited the control so that the first number equals 1, but when I entered 25 as the quantity issued, the end number was 00026, which makes sense considering the controls. However, how can I get the first number issued to show as 00001? Also, how do I get the date to automatically appear when I tab to that field? I truly appreciate all your help, Ken. "Ken Sheridan" wrote: The only possible problem I can see is that Number_Issued seems to be the name both of the table and a field in it as you've used it for both the field name and, as the second argument of the DMax function, the table name. While unusual, and I'd say inadvisable, I don't think it’s prohibited, however. In my sample code I originally used DocumentsIssued as the table name. Unless the use of Number_Issued here as the table name is incorrect (but I'd expect a run-time error if so) the behaviour you are getting is as though the AfterUpdate event procedure isn't executing at all. I'd suggest you debug it by creating a breakpoint in the procedure; you can then see (a) if it is in fact executing (if it isn't the debugger won't break into the code) and (b) what value is assigned to the intNumberIssued variable, and hence to the End_Number. However, as you do have a date/time field for the issue of the document there is an alternative approach which would mean you only need the Number_Issued field in the table, and neither start nor end number fields as both can be computed. You'd need to be happy with the implications of this, which is what I was referring to by 'rules', as it would mean that if at any time you amended the number issued value in any row in the table all later start and end numbers for issues of that document would be re-computed, rather than being static. This approach requires no code at all as both values can be computed by expressions as the ControlSource properties of unbound controls on a form (or in a report or as computed columns in a query). Lets first be absolutely sure that we understand what is what, so for the sake of the expressions below I'm assuming the following: 1. The table name is DocumentsIssued. 2. The date/time field's name is DateTimeIssued. 3. The number issued field's name is Number_Issued 4. The documents reference is a text field Form_Number 5. The unbound control for the start number is txtStartNumber. 6. The unbound control for the end number is txtEndNumber. For the txtStartNumber control the ControlSource would be as follows. Note that this should all be entered as a single line: =Nz(DSum("Number_Issued","DocumentsIssued","Form_N umber =""" & [Form_Number] & """ And DateTimeIssued #" & Format([DateTimeIssued],"mm/dd/yyyy hh:nn:ss") & "#"),0) And for txtEndNunumber: =[txtStartNumber] + [Number_Issued] The above does assume of course that all date/time values for the issue of a particular form number are distinct. I've also assumed you want the numbering to be zero-based, i.e. the start number will be 0 for the first record per form number. If you want to start from 1 you'd add 1 to each of the ControlSource expressions. Ken Sheridan Stafford, England "Etta" wrote: Actually, the AfterUpdate event procedure attached to Number_Issued is: Option Compare Database Private Sub NumberIssued_AfterUpdate() Dim intNumberIssued As Integer Dim strCriteria As String strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """" intNumberIssued = Nz(DMax("[End_Number]", "[Number_Issued]", strCriteria), 0) Me.[End_Number] = intNumberIssued + Me.[Number_Issued] End Sub The above code results in a Starting Number of -00025 and Ending Number of 00000 when I enter 25 in the Number Issued field. I think I've looked at this too many times and am missing some obvious problem with how I've copied your (Ken's) instructions. "Etta" wrote: I deleted the AfterUpdate event procedure in End_Number and added the following as an AfterUpdate event procedure in Number_Issued: Option Compare Database Private Sub End_Number_AfterUpdate() End Sub End Sub Private Sub Number_Issued_AfterUpdate() Dim intNumber_Issued As Integer Dim strCriteria As String strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """" intNumber_Issued = Nz(DMax("[End_Number]", "[Number_Issued]", strCriteria), 0) Me.[End_Number] = intNumber_Issued + Me.[Number_Issued] End Sub My results are still 025 for the number issued, -00025 as the start number, and 00000 as the end number. I do have a field for date&time when a package of forms is issued. The "rules" for numbering are simple -- once a number is entered in the Number_Issued field, Access should compute the Start_Number and the End_Number. Right now, the Start_Number is being calculated by "=[End_Number]-[Number_Issued]" Thanks again, Ken, for you help. As a novice, I would not have been able to get where I am now in my database without your assistance. "Ken Sheridan" wrote: You say that the code is the AfterUpdate event procedure for the *End_Number* control (though the procedure name suggests otherwise). It should be the AfterUpdate event procedure for the *Number_Issued* control. If it is the latter then I don't understand why this is happening. The intNumberIssued variable should, when the code executes, be assigned a value of zero if no documents of this type have yet been issued. This should then be added to the 25 you entered to give a value of 25 as the End number. The Start Number should be this less 25 to give a value of zero. If the code is in the correct procedure, therefore, I don't at first sight see why you are getting this behaviour and some debugging would be advisable, but if it’s the wrong procedure then that would explain it. Perhaps the simplest solution would be to delete the procedure completely (wherever it is) then recreate it from the Number_Issued control's properties sheet. Do the records have a field for entering the date/time when the documents are issued BTW? If so then that would possibly allow a totally different approach to computing the numbers. It might not be in line with the 'rules' on which the numbering is predicated, however, so I'd need to be sure of the basis of these before suggesting this as an alternative approach. Ken Sheridan Stafford, England "Etta" wrote: Thanks again, Ken. I still need your help. After selecting a form number, which I was able to successfully link to Forms#_Title table, I entered 25 in the Number Issued box, which resulted in -00025 as the Start Number (which is an unbound text box) and 00000 box as the End_Number box. Here is what is in the After Update Even Procedure for End_Number: Private Sub NumberIssued_AfterUpdate() Dim intNumberIssued As Integer Dim strCriteria As String strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """" intNumberIssued = Nz(DMax("[End_Number]", "[DocumentsIssued]", strCriteria), 0) Me.[End_Number] = intNumberIssued + Me.[Number_Issued] I don't have a DocumentsIssued field. Is that what's causing my problem? "Ken Sheridan" wrote: I don’t know how to set up a “computed control on the form with a ControlSource of: =[End_Number] - [Form_Issued] I assume that should be [Number_Issued] not [Form_Issued]. All you do is add an unbound text box to the form from the toolbox, then enter =[End_Number] - [Form_Issued] as its ControlSource property in its properties sheet. I'd also suggest you change its Name property to something meaningful like txtStartNumber. Also, I would like to add the validation feature you mention below. Can you outline the steps I would need to follow? In the Change event procedure of the Number_Issued control put something like the following code: If IsNull(Me.[Number_Issued]) Then MsgBox "Please select a form first.", vbExclamation, "Invalid Operation" Me.[Number_Issued].Undo End If Another issue just arose, Ken. When I typed 25 in the Number_Issued field, I got a syntax error message: Private Sub Number_Issued_AfterUpdate(). How can I fix this? Sounds like there's a syntax error in the AfterUpdate event procedure's code. It should be: Dim intNumberIssued As Integer Dim strCriteria as String strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """" intNumberIssued = Nz(Dmax("[End_Number]","[DocumentsIssued]",strCriteria),0) Me.[End_Number] = intNumberIssued + Me.[Number_Issued] Check that the table and field names in the code are all correct. Ken Sheridan Stafford, England |
#17
|
|||
|
|||
Calculating numbers, Control can't be edited
The ControlSource properties of the two combo boxes should be the names of
the relevant foreign key columns, i.e. [Issued To] and [Issued By]. It sounds like you have the first correct, so why you are getting an error I don't know. It sounds like the second is using a table name, QA_Staff, as its ControlSource, however?? Try deleting the controls from the form and adding them again by dragging from the field list on the toolbar in form design view. That will add text boxes to the form, so then right click it and select Change To | Combo Box from the shortcut menu and give it the properties as I described: BoundColumn 1, ColumnCount 2, ColumnWidths 0cm;8cm. For the RowSource you might prefer to concatenate the names into a single column, e.g. SELECT EmpNum, EmpFirstName & " " EmpLastName AS FullName FROM Employee ORDER BY EmpLastName, EmpFirstName; If not you'd need to change the ColumnCount and ColumnWidths properties to accommodate the three columns. I assume EmpNum is the correct column name; you refer to it as Emp# elsewhere. You seem to be having some trouble understanding the underlying principles involved here. You might like to spend some time examining how the sample Northwind database which comes with Access works. Have a look at how the underlying reality is modelled by the tables, each representing one entity type, and how these are related by means of their primary and foreign key columns. The Relationships window is the best place to see this expressed visually. Also look at how forms are used to provide an interface with the data, particularly the orders form and its order details subform. Examine the RecordSource for the forms/subform and how the form/subform's controls are bound to the columns in the underlying tables. It might also be worth your while getting hold of a good general purpose book on Access such as John L Viescas's 'Running Microsoft Access'. This includes sample files on CD for you to work with. Ken Sheridan Stafford, England "Etta" wrote: Hi, Ken, I added the two foreign key columns in DocumentsIssued Table and titled them Issued To and Issued By, with number as data type for each. Is that correct? The primary key for Issued To is Emp#, which is a unique number in the Employee Table, and the primary key for Dept_Staff is an autonumber. On the DocumentsIssued Form, I added the combo box for Issued To,with the row source set as SELECT Employee.EmpNum, Employee.EmpLastName, Employee.EmpFirstName FROM Employee ORDER BY [EmpLastName]; This results in the list of employees appearing when I tab to that field, but when I attempt to select a name, I get the same old error message: Control can't be edited; it is bound to unknown field "Issued To." The same thing happens with the Issued By field, which has the row source of SELECT QA_Staff.[QA_ID], QA_Staff.Name FROM QA_Staff ORDER BY [Name]; . That is the error message "Control can't be edited; it is bound to unknown field "QA_Staff," appears. On the bright side, I did manage to get the Reconciled column to work just fine! "Ken Sheridan" wrote: For the first two, Issued To and Issued By, you need foreign key columns in the DocumentsIssued table. These would normally be numbers of long integer data type referencing the primary keys of the Employees table (which can be an autonumber). Do not use names as keys; they can be duplicated. The names of the foreign key columns would be the ControlSource properties of two combo boxes on the form with a RowSource property such as: SELECT EmployeeID, FirstName & " " & LastName AS FullName FROM Employees ORDER BY LastName, FirstName; And with a BoundColumn property of 1, a ColumnCount property of 2 and a ColumnWidths property of 0cm;8cm (the first dimension must be zero but the size of the second isn't crucial so long as its at least as wide as the combo box). The Reconciled column would be a Boolean (Yes/No) column in the DocumentsIssued table and would be the ControlSource property of a check box on the form. Ken Sheridan Stafford, England "Etta" wrote: Hi, Ken, I need to add three fields from two other tables to the DocumentsIssued form. These fields are Issued To (a drop down menu of all employees, which is linked to an external table), Issued From (a drop down menu of members my department) and Reconciled, which can be a checkbox. I had this working in an older form (Forms Issued), so I copied those fields and labels to DocumentsIssued. Evidently, that's not how things are done, as I now get an error message "Control can't be edited; it's bound to unknown field." How can I add these fields to DocumentsIssued form? "Etta" wrote: Thanks, Ken. GREAT NEWS!!! I created a fresh form and following your advice. The End Number is now working and the Start Number works for the second package of forms issued. However, when the first package is issued (00001-00025) the start number still shows up as 00000. I edited the control so that the first number equals 1, but when I entered 25 as the quantity issued, the end number was 00026, which makes sense considering the controls. However, how can I get the first number issued to show as 00001? Also, how do I get the date to automatically appear when I tab to that field? I truly appreciate all your help, Ken. "Ken Sheridan" wrote: The only possible problem I can see is that Number_Issued seems to be the name both of the table and a field in it as you've used it for both the field name and, as the second argument of the DMax function, the table name. While unusual, and I'd say inadvisable, I don't think it’s prohibited, however. In my sample code I originally used DocumentsIssued as the table name. Unless the use of Number_Issued here as the table name is incorrect (but I'd expect a run-time error if so) the behaviour you are getting is as though the AfterUpdate event procedure isn't executing at all. I'd suggest you debug it by creating a breakpoint in the procedure; you can then see (a) if it is in fact executing (if it isn't the debugger won't break into the code) and (b) what value is assigned to the intNumberIssued variable, and hence to the End_Number. However, as you do have a date/time field for the issue of the document there is an alternative approach which would mean you only need the Number_Issued field in the table, and neither start nor end number fields as both can be computed. You'd need to be happy with the implications of this, which is what I was referring to by 'rules', as it would mean that if at any time you amended the number issued value in any row in the table all later start and end numbers for issues of that document would be re-computed, rather than being static. This approach requires no code at all as both values can be computed by expressions as the ControlSource properties of unbound controls on a form (or in a report or as computed columns in a query). Lets first be absolutely sure that we understand what is what, so for the sake of the expressions below I'm assuming the following: 1. The table name is DocumentsIssued. 2. The date/time field's name is DateTimeIssued. 3. The number issued field's name is Number_Issued 4. The documents reference is a text field Form_Number 5. The unbound control for the start number is txtStartNumber. 6. The unbound control for the end number is txtEndNumber. For the txtStartNumber control the ControlSource would be as follows. Note that this should all be entered as a single line: =Nz(DSum("Number_Issued","DocumentsIssued","Form_N umber =""" & [Form_Number] & """ And DateTimeIssued #" & Format([DateTimeIssued],"mm/dd/yyyy hh:nn:ss") & "#"),0) And for txtEndNunumber: =[txtStartNumber] + [Number_Issued] The above does assume of course that all date/time values for the issue of a particular form number are distinct. I've also assumed you want the numbering to be zero-based, i.e. the start number will be 0 for the first record per form number. If you want to start from 1 you'd add 1 to each of the ControlSource expressions. Ken Sheridan Stafford, England "Etta" wrote: Actually, the AfterUpdate event procedure attached to Number_Issued is: Option Compare Database Private Sub NumberIssued_AfterUpdate() Dim intNumberIssued As Integer Dim strCriteria As String strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """" intNumberIssued = Nz(DMax("[End_Number]", "[Number_Issued]", strCriteria), 0) Me.[End_Number] = intNumberIssued + Me.[Number_Issued] End Sub The above code results in a Starting Number of -00025 and Ending Number of 00000 when I enter 25 in the Number Issued field. I think I've looked at this too many times and am missing some obvious problem with how I've copied your (Ken's) instructions. "Etta" wrote: I deleted the AfterUpdate event procedure in End_Number and added the following as an AfterUpdate event procedure in Number_Issued: Option Compare Database Private Sub End_Number_AfterUpdate() End Sub End Sub Private Sub Number_Issued_AfterUpdate() Dim intNumber_Issued As Integer Dim strCriteria As String strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """" intNumber_Issued = Nz(DMax("[End_Number]", "[Number_Issued]", strCriteria), 0) Me.[End_Number] = intNumber_Issued + Me.[Number_Issued] End Sub My results are still 025 for the number issued, -00025 as the start number, and 00000 as the end number. I do have a field for date&time when a package of forms is issued. The "rules" for numbering are simple -- once a number is entered in the Number_Issued field, Access should compute the Start_Number and the End_Number. Right now, the Start_Number is being calculated by "=[End_Number]-[Number_Issued]" Thanks again, Ken, for you help. As a novice, I would not have been able to get where I am now in my database without your assistance. "Ken Sheridan" wrote: You say that the code is the AfterUpdate event procedure for the *End_Number* control (though the procedure name suggests otherwise). It should be the AfterUpdate event procedure for the *Number_Issued* control. If it is the latter then I don't understand why this is happening. The intNumberIssued variable should, when the code executes, be assigned a value of zero if no documents of this type have yet been issued. This should then be added to the 25 you entered to give a value of 25 as the End number. The Start Number should be this less 25 to give a value of zero. If the code is in the correct procedure, therefore, I don't at first sight see why you are getting this behaviour and some debugging would be advisable, but if it’s the wrong procedure then that would explain it. Perhaps the simplest solution would be to delete the procedure completely (wherever it is) then recreate it from the Number_Issued control's properties sheet. Do the records have a field for entering the date/time when the documents are issued BTW? If so then that would possibly allow a totally different approach to computing the numbers. It might not be in line with the 'rules' on which the numbering is predicated, however, so I'd need to be sure of the basis of these before suggesting this as an alternative approach. Ken Sheridan Stafford, England "Etta" wrote: Thanks again, Ken. I still need your help. After selecting a form number, which I was able to successfully link to Forms#_Title table, I entered 25 in the Number Issued box, which resulted in -00025 as the Start Number (which is an unbound text box) and 00000 box as the End_Number box. Here is what is in the After Update Even Procedure for End_Number: Private Sub NumberIssued_AfterUpdate() Dim intNumberIssued As Integer Dim strCriteria As String strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """" intNumberIssued = Nz(DMax("[End_Number]", "[DocumentsIssued]", strCriteria), 0) Me.[End_Number] = intNumberIssued + Me.[Number_Issued] I don't have a DocumentsIssued field. Is that what's causing my problem? "Ken Sheridan" wrote: I don’t know how to set up a “computed control on the form with a ControlSource of: =[End_Number] - [Form_Issued] I assume that should be [Number_Issued] not [Form_Issued]. All you do is add an unbound text box to the form from the toolbox, then enter =[End_Number] - [Form_Issued] as its ControlSource property in its properties sheet. I'd also suggest you change its Name property to something meaningful like txtStartNumber. Also, I would like to add the validation feature you mention below. Can you outline the steps I would need to follow? In the Change event procedure of the Number_Issued control put something like the following code: If IsNull(Me.[Number_Issued]) Then MsgBox "Please select a form first.", vbExclamation, "Invalid Operation" Me.[Number_Issued].Undo End If Another issue just arose, Ken. When I typed 25 in the Number_Issued field, I got a syntax error message: Private Sub Number_Issued_AfterUpdate(). How can I fix this? Sounds like there's a syntax error in the AfterUpdate event procedure's code. It should be: Dim intNumberIssued As Integer Dim strCriteria as String strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """" intNumberIssued = Nz(Dmax("[End_Number]","[DocumentsIssued]",strCriteria),0) Me.[End_Number] = intNumberIssued + Me.[Number_Issued] Check that the table and field names in the code are all correct. Ken Sheridan Stafford, England |
#18
|
|||
|
|||
Calculating numbers, AfterUpdate event procedure
Ken, Someone responded to this post, but I am unable to find the response. I
have started a new thread, Caclulating numbers, part 3. "Etta" wrote: Hi, Ken, I should have mentioned that I went with your suggestion of using the date/time value with the following as the Control Source: a=Nz(DSum("Number_Issued","DocumentsIssued","Form_ Number =""" & [Form_Number] & """ And DateTimeIssued #" & Format([DateTimeIssued],"mm/dd/yyyy hh:nn:ss") & "#"),0)" How can this be modified to result in 00001 as the start number for the first package and 00025 the end number for the first package of 25? The second package of 25 would start with 00026 and end with 00050. As ever, thanks for your help. "Ken Sheridan" wrote: You don't say how you are approaching it, the original way or computing on the basis of the date/time value. I'm assuming its still the original way. If the start number is to be 1 for the first package issued the start number for the second package would logically then be 26 (if 25 forms were issued in the first record), not 25, otherwise there is an inconsistency in the bases used between the first issue and subsequent ones. That's easily engineered by adding 1 to the start number: =[End_Number]-[Number_Issued] + 1 If, however, you want the first start number to be 1 but (accepting the logical inconsistency) the second to be 25, and the start numbers thereafter to be the same as the preceding end number, then you'd have to make it conditional like so: =IIf(IsNull(DLookup("[End_Number]", "["YourTableName]", "[Form_Number] = """ & [Form_Number] & """")), 1, [End_Number]-[Number_Issued] + 1) To get the date/time to automatically show the simplest thing is to set its DefaultValue property to the Now() function. That will show it as soon as you move the form to a new record. To show it only when focus is moved to the control on the form put the following in its GotFocus event procedu If Not IsNull([YourDateTimeControl]) Then [YourDateTimeControl] = Now() End If Ken Sheridan Stafford, England "Etta" wrote: Thanks, Ken. GREAT NEWS!!! I created a fresh form and following your advice. The End Number is now working and the Start Number works for the second package of forms issued. However, when the first package is issued (00001-00025) the start number still shows up as 00000. I edited the control so that the first number equals 1, but when I entered 25 as the quantity issued, the end number was 00026, which makes sense considering the controls. However, how can I get the first number issued to show as 00001? Also, how do I get the date to automatically appear when I tab to that field? I truly appreciate all your help, Ken. "Ken Sheridan" wrote: The only possible problem I can see is that Number_Issued seems to be the name both of the table and a field in it as you've used it for both the field name and, as the second argument of the DMax function, the table name. While unusual, and I'd say inadvisable, I don't think it’s prohibited, however. In my sample code I originally used DocumentsIssued as the table name. Unless the use of Number_Issued here as the table name is incorrect (but I'd expect a run-time error if so) the behaviour you are getting is as though the AfterUpdate event procedure isn't executing at all. I'd suggest you debug it by creating a breakpoint in the procedure; you can then see (a) if it is in fact executing (if it isn't the debugger won't break into the code) and (b) what value is assigned to the intNumberIssued variable, and hence to the End_Number. However, as you do have a date/time field for the issue of the document there is an alternative approach which would mean you only need the Number_Issued field in the table, and neither start nor end number fields as both can be computed. You'd need to be happy with the implications of this, which is what I was referring to by 'rules', as it would mean that if at any time you amended the number issued value in any row in the table all later start and end numbers for issues of that document would be re-computed, rather than being static. This approach requires no code at all as both values can be computed by expressions as the ControlSource properties of unbound controls on a form (or in a report or as computed columns in a query). Lets first be absolutely sure that we understand what is what, so for the sake of the expressions below I'm assuming the following: 1. The table name is DocumentsIssued. 2. The date/time field's name is DateTimeIssued. 3. The number issued field's name is Number_Issued 4. The documents reference is a text field Form_Number 5. The unbound control for the start number is txtStartNumber. 6. The unbound control for the end number is txtEndNumber. For the txtStartNumber control the ControlSource would be as follows. Note that this should all be entered as a single line: =Nz(DSum("Number_Issued","DocumentsIssued","Form_N umber =""" & [Form_Number] & """ And DateTimeIssued #" & Format([DateTimeIssued],"mm/dd/yyyy hh:nn:ss") & "#"),0) And for txtEndNunumber: =[txtStartNumber] + [Number_Issued] The above does assume of course that all date/time values for the issue of a particular form number are distinct. I've also assumed you want the numbering to be zero-based, i.e. the start number will be 0 for the first record per form number. If you want to start from 1 you'd add 1 to each of the ControlSource expressions. Ken Sheridan Stafford, England "Etta" wrote: Actually, the AfterUpdate event procedure attached to Number_Issued is: Option Compare Database Private Sub NumberIssued_AfterUpdate() Dim intNumberIssued As Integer Dim strCriteria As String strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """" intNumberIssued = Nz(DMax("[End_Number]", "[Number_Issued]", strCriteria), 0) Me.[End_Number] = intNumberIssued + Me.[Number_Issued] End Sub The above code results in a Starting Number of -00025 and Ending Number of 00000 when I enter 25 in the Number Issued field. I think I've looked at this too many times and am missing some obvious problem with how I've copied your (Ken's) instructions. "Etta" wrote: I deleted the AfterUpdate event procedure in End_Number and added the following as an AfterUpdate event procedure in Number_Issued: Option Compare Database Private Sub End_Number_AfterUpdate() End Sub End Sub Private Sub Number_Issued_AfterUpdate() Dim intNumber_Issued As Integer Dim strCriteria As String strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """" intNumber_Issued = Nz(DMax("[End_Number]", "[Number_Issued]", strCriteria), 0) Me.[End_Number] = intNumber_Issued + Me.[Number_Issued] End Sub My results are still 025 for the number issued, -00025 as the start number, and 00000 as the end number. I do have a field for date&time when a package of forms is issued. The "rules" for numbering are simple -- once a number is entered in the Number_Issued field, Access should compute the Start_Number and the End_Number. Right now, the Start_Number is being calculated by "=[End_Number]-[Number_Issued]" Thanks again, Ken, for you help. As a novice, I would not have been able to get where I am now in my database without your assistance. "Ken Sheridan" wrote: You say that the code is the AfterUpdate event procedure for the *End_Number* control (though the procedure name suggests otherwise). It should be the AfterUpdate event procedure for the *Number_Issued* control. If it is the latter then I don't understand why this is happening. The intNumberIssued variable should, when the code executes, be assigned a value of zero if no documents of this type have yet been issued. This should then be added to the 25 you entered to give a value of 25 as the End number. The Start Number should be this less 25 to give a value of zero. If the code is in the correct procedure, therefore, I don't at first sight see why you are getting this behaviour and some debugging would be advisable, but if it’s the wrong procedure then that would explain it. Perhaps the simplest solution would be to delete the procedure completely (wherever it is) then recreate it from the Number_Issued control's properties sheet. Do the records have a field for entering the date/time when the documents are issued BTW? If so then that would possibly allow a totally different approach to computing the numbers. It might not be in line with the 'rules' on which the numbering is predicated, however, so I'd need to be sure of the basis of these before suggesting this as an alternative approach. Ken Sheridan Stafford, England "Etta" wrote: Thanks again, Ken. I still need your help. After selecting a form number, which I was able to successfully link to Forms#_Title table, I entered 25 in the Number Issued box, which resulted in -00025 as the Start Number (which is an unbound text box) and 00000 box as the End_Number box. Here is what is in the After Update Even Procedure for End_Number: Private Sub NumberIssued_AfterUpdate() Dim intNumberIssued As Integer Dim strCriteria As String strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """" intNumberIssued = Nz(DMax("[End_Number]", "[DocumentsIssued]", strCriteria), 0) Me.[End_Number] = intNumberIssued + Me.[Number_Issued] I don't have a DocumentsIssued field. Is that what's causing my problem? "Ken Sheridan" wrote: I don’t know how to set up a “computed control on the form with a ControlSource of: =[End_Number] - [Form_Issued] I assume that should be [Number_Issued] not [Form_Issued]. All you do is add an unbound text box to the form from the toolbox, then enter =[End_Number] - [Form_Issued] as its ControlSource property in its properties sheet. I'd also suggest you change its Name property to something meaningful like txtStartNumber. Also, I would like to add the validation feature you mention below. Can you outline the steps I would need to follow? In the Change event procedure of the Number_Issued control put something like the following code: If IsNull(Me.[Number_Issued]) Then MsgBox "Please select a form first.", vbExclamation, "Invalid Operation" Me.[Number_Issued].Undo End If Another issue just arose, Ken. When I typed 25 in the Number_Issued field, I got a syntax error message: Private Sub Number_Issued_AfterUpdate(). How can I fix this? Sounds like there's a syntax error in the AfterUpdate event procedure's code. It should be: Dim intNumberIssued As Integer Dim strCriteria as String strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """" intNumberIssued = Nz(Dmax("[End_Number]","[DocumentsIssued]",strCriteria),0) Me.[End_Number] = intNumberIssued + Me.[Number_Issued] Check that the table and field names in the code are all correct. Ken Sheridan Stafford, England |
#19
|
|||
|
|||
Calculating numbers, Control can't be edited
Ken,
I finally got the DocumentsIssued form to work, except for the Start number (00000 instead of 00001). Thank you for your advice and patience. "Ken Sheridan" wrote: The ControlSource properties of the two combo boxes should be the names of the relevant foreign key columns, i.e. [Issued To] and [Issued By]. It sounds like you have the first correct, so why you are getting an error I don't know. It sounds like the second is using a table name, QA_Staff, as its ControlSource, however?? Try deleting the controls from the form and adding them again by dragging from the field list on the toolbar in form design view. That will add text boxes to the form, so then right click it and select Change To | Combo Box from the shortcut menu and give it the properties as I described: BoundColumn 1, ColumnCount 2, ColumnWidths 0cm;8cm. For the RowSource you might prefer to concatenate the names into a single column, e.g. SELECT EmpNum, EmpFirstName & " " EmpLastName AS FullName FROM Employee ORDER BY EmpLastName, EmpFirstName; If not you'd need to change the ColumnCount and ColumnWidths properties to accommodate the three columns. I assume EmpNum is the correct column name; you refer to it as Emp# elsewhere. You seem to be having some trouble understanding the underlying principles involved here. You might like to spend some time examining how the sample Northwind database which comes with Access works. Have a look at how the underlying reality is modelled by the tables, each representing one entity type, and how these are related by means of their primary and foreign key columns. The Relationships window is the best place to see this expressed visually. Also look at how forms are used to provide an interface with the data, particularly the orders form and its order details subform. Examine the RecordSource for the forms/subform and how the form/subform's controls are bound to the columns in the underlying tables. It might also be worth your while getting hold of a good general purpose book on Access such as John L Viescas's 'Running Microsoft Access'. This includes sample files on CD for you to work with. Ken Sheridan Stafford, England |
|
Thread Tools | |
Display Modes | |
|
|