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
|
|||
|
|||
Creating a New Record in Sub Form_Current
I have a form and sub-form, respectively bound to tables which are linked in
a standard one-many relationship. In contrast to the main form, AllowAdditions is set True for the sub-form. On opening, the latter is required to determine whether the first record is already complete. This it does by testing whether a constituent ID field has a non-zero value. If not, then the current record is virgin and is completed directly by the entry of the appropriate data. If the record is already complete, however, then, under certain defined conditions, a new blank record is created and presented for completion. The VBA used to add the new record and move to it is:– If (Me![ID] 0) And [Condition] Then DoCmd.GoToRecord , , acNewRec ([Condition] is a Boolean field, set appropriately elsewhere.) This works, but only in Sub Form_Current. No new record is created if this code is run in Sub Form_Open or Sub Form_Load. Further code is run, in Sub Form_Current, after the new record is created. This enables, disables, hides or displays various command buttons which are, or are not, made available according to the conditions. The exit button, for example, is not normally enabled until the data entry to a new record is complete. This conditional interlocking generally works well. There is, however, a considerable difficulty created by the way in which Sub Form_Current operates. When the first record is found to be complete, the above VBA creates and displays a new record as required. The button interlocking code next runs but, unbidden, then re-runs for the preceding record – the completed one which occasioned the creation of the new record. The command button interlocks are thus set correctly for the new record and then immediately reset for the conditions applying to the previous record – in most cases inappropriately. I have only been able to find two ways of preventing this, neither of which is elegant. The first is to create a Boolean variable, at module level, called, in my case, ‘FirstPass’. This is set True in Sub Form_Open and it is this value upon which entry to the button interlocking code in Sub Form_Current is made conditional. The first action of the interlocking code is then to set FirstPass False, preventing this code from being re-run. The second method is not to use the GoToRecord command but to attach any new records using an append query and then to navigate to them. It is not a pretty technique either. Clearly, I do not fully understand the operation of Sub Form_Current. Can anyone enlighten me and perhaps suggest a more workmanlike method of achieving my ends? -- Peter Hallett |
#2
|
|||
|
|||
Creating a New Record in Sub Form_Current
Peter, any attempt to use Form_Current to force the entry of a new record is
flawed. As you found, this event can fire muliple times for one record, and this behavior changes depending on the version of Access you are using. That means even if you did succeed in creating a workaround, you are still creating a maintenance nightmare that is likely to break in future versions. More importantly, forcing the entry as soon as the user arrives at a new record is highly undesirable. It creates blank records (where nothing was entered.) It creates orphan records (where the parent form is also at a new record, and so the foreign key is null.) As soon as the user tabs out of a new record, it creates another one. And even if you code around all those issues, it will create concurrency issues for you - error messages like "Access stopped the operation because you and another user were ..." In summary, any code that dirties a record in Form_Current is asking for trouble, and it makes no sense to force the user into the entry of a new record just because the visited the end of the records. There has to be a better approach to achieve whatever it is that you need. Perhaps you want a related record created whenever a new record is added in the main form? If so, could the main form's AfterInsert event do that for you? -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Peter Hallett" wrote in message news I have a form and sub-form, respectively bound to tables which are linked in a standard one-many relationship. In contrast to the main form, AllowAdditions is set True for the sub-form. On opening, the latter is required to determine whether the first record is already complete. This it does by testing whether a constituent ID field has a non-zero value. If not, then the current record is virgin and is completed directly by the entry of the appropriate data. If the record is already complete, however, then, under certain defined conditions, a new blank record is created and presented for completion. The VBA used to add the new record and move to it is:- If (Me![ID] 0) And [Condition] Then DoCmd.GoToRecord , , acNewRec ([Condition] is a Boolean field, set appropriately elsewhere.) This works, but only in Sub Form_Current. No new record is created if this code is run in Sub Form_Open or Sub Form_Load. Further code is run, in Sub Form_Current, after the new record is created. This enables, disables, hides or displays various command buttons which are, or are not, made available according to the conditions. The exit button, for example, is not normally enabled until the data entry to a new record is complete. This conditional interlocking generally works well. There is, however, a considerable difficulty created by the way in which Sub Form_Current operates. When the first record is found to be complete, the above VBA creates and displays a new record as required. The button interlocking code next runs but, unbidden, then re-runs for the preceding record - the completed one which occasioned the creation of the new record. The command button interlocks are thus set correctly for the new record and then immediately reset for the conditions applying to the previous record - in most cases inappropriately. I have only been able to find two ways of preventing this, neither of which is elegant. The first is to create a Boolean variable, at module level, called, in my case, 'FirstPass'. This is set True in Sub Form_Open and it is this value upon which entry to the button interlocking code in Sub Form_Current is made conditional. The first action of the interlocking code is then to set FirstPass False, preventing this code from being re-run. The second method is not to use the GoToRecord command but to attach any new records using an append query and then to navigate to them. It is not a pretty technique either. Clearly, I do not fully understand the operation of Sub Form_Current. Can anyone enlighten me and perhaps suggest a more workmanlike method of achieving my ends? -- Peter Hallett |
#3
|
|||
|
|||
Creating a New Record in Sub Form_Current
Allen,
Many thanks for the response. It has reinforced my impression that any attempt to do things in ways that do not suit Access generally results in failure. The best thing is to adopt a different approach before you dig a big hole for yourself. I had, and still have, a nasty feeling about this one and, although I have yet to find an appropriate solution, I am grateful to you for confirming that I have little to gain by pursuing the previous path. I find it disappointing, however, that, although Access provides the command DoCmd.GoToRecord , , acNewRec, it does not seem possible to use it, in the present context, to create a new record and go to it. It would have been gratifying had it done what it says on the label! There appears to be a little residual misunderstanding. Perhaps I could clear this by restating the problem in somewhat different terms. A customer (main form) seeks to buy a new flight voucher (sub-form). On opening, the sub-form therefore needs to present a virgin record for completion. However, where customers have already purchased vouchers, these are listed in the table to which the sub-form is bound. In these cases the sub-form therefore opens, displaying the first of the existing voucher records. Of the possible solutions I mentioned earlier, one is to use the sub-form navigation buttons to create and select the required new record, but this is clumsy. It involves an additional, and actually unnecessary, manual operation. Since the ‘new voucher’ option has already been selected, it is obvious that none of the existing records should be presented for amendment. On the other hand, it is informative for the navigation bar to show that the new record is, for example, no. 8 of 8, and also convenient to be able to scroll back through the existing voucher records to check their details before issuing a new voucher. It would not therefore be sensible to set the sub-form SQL so as to exclude existing records. Nor, for the same reason, would it be advisable to use an unbound form. A second option is to use an append query to concatenate a new record to the sub-form table before opening the form. It would then be necessary to issue a GoTo Last Record command when the sub-form opens. Is that, however, going to lead to similar trouble in Sub Form_Current? As to your final suggestion, the above should make clear that the problem is not one of adding a new sub-form record when a new main form record is created. Instead, the need is to add a new sub-form record to the complement associated with an existing main form record and to navigate directly and automatically to this record. If you have any further thoughts, I would be grateful to share them. -- Peter Hallett "Allen Browne" wrote: Peter, any attempt to use Form_Current to force the entry of a new record is flawed. As you found, this event can fire muliple times for one record, and this behavior changes depending on the version of Access you are using. That means even if you did succeed in creating a workaround, you are still creating a maintenance nightmare that is likely to break in future versions. More importantly, forcing the entry as soon as the user arrives at a new record is highly undesirable. It creates blank records (where nothing was entered.) It creates orphan records (where the parent form is also at a new record, and so the foreign key is null.) As soon as the user tabs out of a new record, it creates another one. And even if you code around all those issues, it will create concurrency issues for you - error messages like "Access stopped the operation because you and another user were ..." In summary, any code that dirties a record in Form_Current is asking for trouble, and it makes no sense to force the user into the entry of a new record just because the visited the end of the records. There has to be a better approach to achieve whatever it is that you need. Perhaps you want a related record created whenever a new record is added in the main form? If so, could the main form's AfterInsert event do that for you? -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Peter Hallett" wrote in message news I have a form and sub-form, respectively bound to tables which are linked in a standard one-many relationship. In contrast to the main form, AllowAdditions is set True for the sub-form. On opening, the latter is required to determine whether the first record is already complete. This it does by testing whether a constituent ID field has a non-zero value. If not, then the current record is virgin and is completed directly by the entry of the appropriate data. If the record is already complete, however, then, under certain defined conditions, a new blank record is created and presented for completion. The VBA used to add the new record and move to it is:- If (Me![ID] 0) And [Condition] Then DoCmd.GoToRecord , , acNewRec ([Condition] is a Boolean field, set appropriately elsewhere.) This works, but only in Sub Form_Current. No new record is created if this code is run in Sub Form_Open or Sub Form_Load. Further code is run, in Sub Form_Current, after the new record is created. This enables, disables, hides or displays various command buttons which are, or are not, made available according to the conditions. The exit button, for example, is not normally enabled until the data entry to a new record is complete. This conditional interlocking generally works well. There is, however, a considerable difficulty created by the way in which Sub Form_Current operates. When the first record is found to be complete, the above VBA creates and displays a new record as required. The button interlocking code next runs but, unbidden, then re-runs for the preceding record - the completed one which occasioned the creation of the new record. The command button interlocks are thus set correctly for the new record and then immediately reset for the conditions applying to the previous record - in most cases inappropriately. I have only been able to find two ways of preventing this, neither of which is elegant. The first is to create a Boolean variable, at module level, called, in my case, 'FirstPass'. This is set True in Sub Form_Open and it is this value upon which entry to the button interlocking code in Sub Form_Current is made conditional. The first action of the interlocking code is then to set FirstPass False, preventing this code from being re-run. The second method is not to use the GoToRecord command but to attach any new records using an append query and then to navigate to them. It is not a pretty technique either. Clearly, I do not fully understand the operation of Sub Form_Current. Can anyone enlighten me and perhaps suggest a more workmanlike method of achieving my ends? -- Peter Hallett |
#4
|
|||
|
|||
Creating a New Record in Sub Form_Current
you could add code to move to a new record in the subform *when the subform
control is entered*. so when you move a particular record in the main form, the existing subform records will show; as soon as the cursor enters the subform, it jumps to a new record - and the existing records are still available for review. add the following code to the Enter event of the subform control (on the main form), as Private Sub SubformControlName_Enter() With Me!SubformControlName.Form DoCmd.RunCommand acCmdRecordsGoToNew End With End Sub substitute the correct name of the subform control, of course. hth "Peter Hallett" wrote in message ... Allen, Many thanks for the response. It has reinforced my impression that any attempt to do things in ways that do not suit Access generally results in failure. The best thing is to adopt a different approach before you dig a big hole for yourself. I had, and still have, a nasty feeling about this one and, although I have yet to find an appropriate solution, I am grateful to you for confirming that I have little to gain by pursuing the previous path. I find it disappointing, however, that, although Access provides the command DoCmd.GoToRecord , , acNewRec, it does not seem possible to use it, in the present context, to create a new record and go to it. It would have been gratifying had it done what it says on the label! There appears to be a little residual misunderstanding. Perhaps I could clear this by restating the problem in somewhat different terms. A customer (main form) seeks to buy a new flight voucher (sub-form). On opening, the sub-form therefore needs to present a virgin record for completion. However, where customers have already purchased vouchers, these are listed in the table to which the sub-form is bound. In these cases the sub-form therefore opens, displaying the first of the existing voucher records. Of the possible solutions I mentioned earlier, one is to use the sub-form navigation buttons to create and select the required new record, but this is clumsy. It involves an additional, and actually unnecessary, manual operation. Since the 'new voucher' option has already been selected, it is obvious that none of the existing records should be presented for amendment. On the other hand, it is informative for the navigation bar to show that the new record is, for example, no. 8 of 8, and also convenient to be able to scroll back through the existing voucher records to check their details before issuing a new voucher. It would not therefore be sensible to set the sub-form SQL so as to exclude existing records. Nor, for the same reason, would it be advisable to use an unbound form. A second option is to use an append query to concatenate a new record to the sub-form table before opening the form. It would then be necessary to issue a GoTo Last Record command when the sub-form opens. Is that, however, going to lead to similar trouble in Sub Form_Current? As to your final suggestion, the above should make clear that the problem is not one of adding a new sub-form record when a new main form record is created. Instead, the need is to add a new sub-form record to the complement associated with an existing main form record and to navigate directly and automatically to this record. If you have any further thoughts, I would be grateful to share them. -- Peter Hallett "Allen Browne" wrote: Peter, any attempt to use Form_Current to force the entry of a new record is flawed. As you found, this event can fire muliple times for one record, and this behavior changes depending on the version of Access you are using. That means even if you did succeed in creating a workaround, you are still creating a maintenance nightmare that is likely to break in future versions. More importantly, forcing the entry as soon as the user arrives at a new record is highly undesirable. It creates blank records (where nothing was entered.) It creates orphan records (where the parent form is also at a new record, and so the foreign key is null.) As soon as the user tabs out of a new record, it creates another one. And even if you code around all those issues, it will create concurrency issues for you - error messages like "Access stopped the operation because you and another user were ..." In summary, any code that dirties a record in Form_Current is asking for trouble, and it makes no sense to force the user into the entry of a new record just because the visited the end of the records. There has to be a better approach to achieve whatever it is that you need. Perhaps you want a related record created whenever a new record is added in the main form? If so, could the main form's AfterInsert event do that for you? -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Peter Hallett" wrote in message news I have a form and sub-form, respectively bound to tables which are linked in a standard one-many relationship. In contrast to the main form, AllowAdditions is set True for the sub-form. On opening, the latter is required to determine whether the first record is already complete. This it does by testing whether a constituent ID field has a non-zero value. If not, then the current record is virgin and is completed directly by the entry of the appropriate data. If the record is already complete, however, then, under certain defined conditions, a new blank record is created and presented for completion. The VBA used to add the new record and move to it is:- If (Me![ID] 0) And [Condition] Then DoCmd.GoToRecord , , acNewRec ([Condition] is a Boolean field, set appropriately elsewhere.) This works, but only in Sub Form_Current. No new record is created if this code is run in Sub Form_Open or Sub Form_Load. Further code is run, in Sub Form_Current, after the new record is created. This enables, disables, hides or displays various command buttons which are, or are not, made available according to the conditions. The exit button, for example, is not normally enabled until the data entry to a new record is complete. This conditional interlocking generally works well. There is, however, a considerable difficulty created by the way in which Sub Form_Current operates. When the first record is found to be complete, the above VBA creates and displays a new record as required. The button interlocking code next runs but, unbidden, then re-runs for the preceding record - the completed one which occasioned the creation of the new record. The command button interlocks are thus set correctly for the new record and then immediately reset for the conditions applying to the previous record - in most cases inappropriately. I have only been able to find two ways of preventing this, neither of which is elegant. The first is to create a Boolean variable, at module level, called, in my case, 'FirstPass'. This is set True in Sub Form_Open and it is this value upon which entry to the button interlocking code in Sub Form_Current is made conditional. The first action of the interlocking code is then to set FirstPass False, preventing this code from being re-run. The second method is not to use the GoToRecord command but to attach any new records using an append query and then to navigate to them. It is not a pretty technique either. Clearly, I do not fully understand the operation of Sub Form_Current. Can anyone enlighten me and perhaps suggest a more workmanlike method of achieving my ends? -- Peter Hallett |
#5
|
|||
|
|||
Creating a New Record in Sub Form_Current
A couple of options:
1. As Tina says you can take the person to the new record with: With Me.[NameOfSubformHere] .SetFocus .Form![NameOfFirstContorlInSubformHere].SetFocus RunCommand acCmdRecordsGotoNew End With The subform's nav. buttons will show "8 of 8" as you desire, but the existing records will probably scroll up out of view, so the new record is the only one the user can see, and the cursor is in the first field ready to enter data. There is no problem with creating spurious records if the user does scroll up (to previous records) and down again (to the new record) because you are not using Form_Current. 2. If you do want to add a new record to the subform completely programmatically, you could AddNew to the RecordsetClone of the subform, and then set its Bookmark to that of the new record in the clone set. Again, this will work fine as long as you are not doing it in Form_Current. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Peter Hallett" wrote in message ... Allen, Many thanks for the response. It has reinforced my impression that any attempt to do things in ways that do not suit Access generally results in failure. The best thing is to adopt a different approach before you dig a big hole for yourself. I had, and still have, a nasty feeling about this one and, although I have yet to find an appropriate solution, I am grateful to you for confirming that I have little to gain by pursuing the previous path. I find it disappointing, however, that, although Access provides the command DoCmd.GoToRecord , , acNewRec, it does not seem possible to use it, in the present context, to create a new record and go to it. It would have been gratifying had it done what it says on the label! There appears to be a little residual misunderstanding. Perhaps I could clear this by restating the problem in somewhat different terms. A customer (main form) seeks to buy a new flight voucher (sub-form). On opening, the sub-form therefore needs to present a virgin record for completion. However, where customers have already purchased vouchers, these are listed in the table to which the sub-form is bound. In these cases the sub-form therefore opens, displaying the first of the existing voucher records. Of the possible solutions I mentioned earlier, one is to use the sub-form navigation buttons to create and select the required new record, but this is clumsy. It involves an additional, and actually unnecessary, manual operation. Since the 'new voucher' option has already been selected, it is obvious that none of the existing records should be presented for amendment. On the other hand, it is informative for the navigation bar to show that the new record is, for example, no. 8 of 8, and also convenient to be able to scroll back through the existing voucher records to check their details before issuing a new voucher. It would not therefore be sensible to set the sub-form SQL so as to exclude existing records. Nor, for the same reason, would it be advisable to use an unbound form. A second option is to use an append query to concatenate a new record to the sub-form table before opening the form. It would then be necessary to issue a GoTo Last Record command when the sub-form opens. Is that, however, going to lead to similar trouble in Sub Form_Current? As to your final suggestion, the above should make clear that the problem is not one of adding a new sub-form record when a new main form record is created. Instead, the need is to add a new sub-form record to the complement associated with an existing main form record and to navigate directly and automatically to this record. If you have any further thoughts, I would be grateful to share them. -- Peter Hallett "Allen Browne" wrote: Peter, any attempt to use Form_Current to force the entry of a new record is flawed. As you found, this event can fire muliple times for one record, and this behavior changes depending on the version of Access you are using. That means even if you did succeed in creating a workaround, you are still creating a maintenance nightmare that is likely to break in future versions. More importantly, forcing the entry as soon as the user arrives at a new record is highly undesirable. It creates blank records (where nothing was entered.) It creates orphan records (where the parent form is also at a new record, and so the foreign key is null.) As soon as the user tabs out of a new record, it creates another one. And even if you code around all those issues, it will create concurrency issues for you - error messages like "Access stopped the operation because you and another user were ..." In summary, any code that dirties a record in Form_Current is asking for trouble, and it makes no sense to force the user into the entry of a new record just because the visited the end of the records. There has to be a better approach to achieve whatever it is that you need. Perhaps you want a related record created whenever a new record is added in the main form? If so, could the main form's AfterInsert event do that for you? -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Peter Hallett" wrote in message news I have a form and sub-form, respectively bound to tables which are linked in a standard one-many relationship. In contrast to the main form, AllowAdditions is set True for the sub-form. On opening, the latter is required to determine whether the first record is already complete. This it does by testing whether a constituent ID field has a non-zero value. If not, then the current record is virgin and is completed directly by the entry of the appropriate data. If the record is already complete, however, then, under certain defined conditions, a new blank record is created and presented for completion. The VBA used to add the new record and move to it is:- If (Me![ID] 0) And [Condition] Then DoCmd.GoToRecord , , acNewRec ([Condition] is a Boolean field, set appropriately elsewhere.) This works, but only in Sub Form_Current. No new record is created if this code is run in Sub Form_Open or Sub Form_Load. Further code is run, in Sub Form_Current, after the new record is created. This enables, disables, hides or displays various command buttons which are, or are not, made available according to the conditions. The exit button, for example, is not normally enabled until the data entry to a new record is complete. This conditional interlocking generally works well. There is, however, a considerable difficulty created by the way in which Sub Form_Current operates. When the first record is found to be complete, the above VBA creates and displays a new record as required. The button interlocking code next runs but, unbidden, then re-runs for the preceding record - the completed one which occasioned the creation of the new record. The command button interlocks are thus set correctly for the new record and then immediately reset for the conditions applying to the previous record - in most cases inappropriately. I have only been able to find two ways of preventing this, neither of which is elegant. The first is to create a Boolean variable, at module level, called, in my case, 'FirstPass'. This is set True in Sub Form_Open and it is this value upon which entry to the button interlocking code in Sub Form_Current is made conditional. The first action of the interlocking code is then to set FirstPass False, preventing this code from being re-run. The second method is not to use the GoToRecord command but to attach any new records using an append query and then to navigate to them. It is not a pretty technique either. Clearly, I do not fully understand the operation of Sub Form_Current. Can anyone enlighten me and perhaps suggest a more workmanlike method of achieving my ends? -- Peter Hallett |
#6
|
|||
|
|||
Creating a New Record in Sub Form_Current
Many thanks to you and Tina for the responses, which should certainly provide
a solution. I am sorry that it has taken me so long to reply, or to rate your contributions, but my Internet connection has been unusable for over a week. Apparently, a squirrel was not only using my telephone line for transport but was also having his breakfast from the insulation. British Telecom eventually had to rig a temporary cable to bypass the damage - and that does not happen overnight. I am now quickly taking advantage of the restored service before the squirrel decides to avail himself of the new food supply. -- Peter Hallett "Allen Browne" wrote: A couple of options: 1. As Tina says you can take the person to the new record with: With Me.[NameOfSubformHere] .SetFocus .Form![NameOfFirstContorlInSubformHere].SetFocus RunCommand acCmdRecordsGotoNew End With The subform's nav. buttons will show "8 of 8" as you desire, but the existing records will probably scroll up out of view, so the new record is the only one the user can see, and the cursor is in the first field ready to enter data. There is no problem with creating spurious records if the user does scroll up (to previous records) and down again (to the new record) because you are not using Form_Current. 2. If you do want to add a new record to the subform completely programmatically, you could AddNew to the RecordsetClone of the subform, and then set its Bookmark to that of the new record in the clone set. Again, this will work fine as long as you are not doing it in Form_Current. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Peter Hallett" wrote in message ... Allen, Many thanks for the response. It has reinforced my impression that any attempt to do things in ways that do not suit Access generally results in failure. The best thing is to adopt a different approach before you dig a big hole for yourself. I had, and still have, a nasty feeling about this one and, although I have yet to find an appropriate solution, I am grateful to you for confirming that I have little to gain by pursuing the previous path. I find it disappointing, however, that, although Access provides the command DoCmd.GoToRecord , , acNewRec, it does not seem possible to use it, in the present context, to create a new record and go to it. It would have been gratifying had it done what it says on the label! There appears to be a little residual misunderstanding. Perhaps I could clear this by restating the problem in somewhat different terms. A customer (main form) seeks to buy a new flight voucher (sub-form). On opening, the sub-form therefore needs to present a virgin record for completion. However, where customers have already purchased vouchers, these are listed in the table to which the sub-form is bound. In these cases the sub-form therefore opens, displaying the first of the existing voucher records. Of the possible solutions I mentioned earlier, one is to use the sub-form navigation buttons to create and select the required new record, but this is clumsy. It involves an additional, and actually unnecessary, manual operation. Since the 'new voucher' option has already been selected, it is obvious that none of the existing records should be presented for amendment. On the other hand, it is informative for the navigation bar to show that the new record is, for example, no. 8 of 8, and also convenient to be able to scroll back through the existing voucher records to check their details before issuing a new voucher. It would not therefore be sensible to set the sub-form SQL so as to exclude existing records. Nor, for the same reason, would it be advisable to use an unbound form. A second option is to use an append query to concatenate a new record to the sub-form table before opening the form. It would then be necessary to issue a GoTo Last Record command when the sub-form opens. Is that, however, going to lead to similar trouble in Sub Form_Current? As to your final suggestion, the above should make clear that the problem is not one of adding a new sub-form record when a new main form record is created. Instead, the need is to add a new sub-form record to the complement associated with an existing main form record and to navigate directly and automatically to this record. If you have any further thoughts, I would be grateful to share them. -- Peter Hallett "Allen Browne" wrote: Peter, any attempt to use Form_Current to force the entry of a new record is flawed. As you found, this event can fire muliple times for one record, and this behavior changes depending on the version of Access you are using. That means even if you did succeed in creating a workaround, you are still creating a maintenance nightmare that is likely to break in future versions. More importantly, forcing the entry as soon as the user arrives at a new record is highly undesirable. It creates blank records (where nothing was entered.) It creates orphan records (where the parent form is also at a new record, and so the foreign key is null.) As soon as the user tabs out of a new record, it creates another one. And even if you code around all those issues, it will create concurrency issues for you - error messages like "Access stopped the operation because you and another user were ..." In summary, any code that dirties a record in Form_Current is asking for trouble, and it makes no sense to force the user into the entry of a new record just because the visited the end of the records. There has to be a better approach to achieve whatever it is that you need. Perhaps you want a related record created whenever a new record is added in the main form? If so, could the main form's AfterInsert event do that for you? -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Peter Hallett" wrote in message news I have a form and sub-form, respectively bound to tables which are linked in a standard one-many relationship. In contrast to the main form, AllowAdditions is set True for the sub-form. On opening, the latter is required to determine whether the first record is already complete. This it does by testing whether a constituent ID field has a non-zero value. If not, then the current record is virgin and is completed directly by the entry of the appropriate data. If the record is already complete, however, then, under certain defined conditions, a new blank record is created and presented for completion. The VBA used to add the new record and move to it is:- If (Me![ID] 0) And [Condition] Then DoCmd.GoToRecord , , acNewRec ([Condition] is a Boolean field, set appropriately elsewhere.) This works, but only in Sub Form_Current. No new record is created if this code is run in Sub Form_Open or Sub Form_Load. Further code is run, in Sub Form_Current, after the new record is created. This enables, disables, hides or displays various command buttons which are, or are not, made available according to the conditions. The exit button, for example, is not normally enabled until the data entry to a new record is complete. This conditional interlocking generally works well. There is, however, a considerable difficulty created by the way in which Sub Form_Current operates. When the first record is found to be complete, the above VBA creates and displays a new record as required. The button interlocking code next runs but, unbidden, then re-runs for the preceding record - the completed one which occasioned the creation of the new record. The command button interlocks are thus set correctly for the new record and then immediately reset for the conditions applying to the previous record - in most cases inappropriately. I have only been able to find two ways of preventing this, neither of which is elegant. The first is to create a Boolean variable, at module level, called, in my case, 'FirstPass'. This is set True in Sub Form_Open and it is this value upon which entry to the button interlocking code in Sub Form_Current is made conditional. The first action of the interlocking code is then to set FirstPass False, preventing this code from being re-run. The second method is not to use the GoToRecord command but to attach any new records using an append query and then to navigate to them. It is not a pretty technique either. Clearly, I do not fully understand the operation of Sub Form_Current. Can anyone enlighten me and perhaps suggest a more workmanlike method of achieving my ends? -- Peter Hallett |
#7
|
|||
|
|||
Creating a New Record in Sub Form_Current
I am now quickly taking advantage of the
restored service before the squirrel decides to avail himself of the new food supply. lol g "Peter Hallett" wrote in message ... Many thanks to you and Tina for the responses, which should certainly provide a solution. I am sorry that it has taken me so long to reply, or to rate your contributions, but my Internet connection has been unusable for over a week. Apparently, a squirrel was not only using my telephone line for transport but was also having his breakfast from the insulation. British Telecom eventually had to rig a temporary cable to bypass the damage - and that does not happen overnight. I am now quickly taking advantage of the restored service before the squirrel decides to avail himself of the new food supply. -- Peter Hallett "Allen Browne" wrote: A couple of options: 1. As Tina says you can take the person to the new record with: With Me.[NameOfSubformHere] .SetFocus .Form![NameOfFirstContorlInSubformHere].SetFocus RunCommand acCmdRecordsGotoNew End With The subform's nav. buttons will show "8 of 8" as you desire, but the existing records will probably scroll up out of view, so the new record is the only one the user can see, and the cursor is in the first field ready to enter data. There is no problem with creating spurious records if the user does scroll up (to previous records) and down again (to the new record) because you are not using Form_Current. 2. If you do want to add a new record to the subform completely programmatically, you could AddNew to the RecordsetClone of the subform, and then set its Bookmark to that of the new record in the clone set. Again, this will work fine as long as you are not doing it in Form_Current. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Peter Hallett" wrote in message ... Allen, Many thanks for the response. It has reinforced my impression that any attempt to do things in ways that do not suit Access generally results in failure. The best thing is to adopt a different approach before you dig a big hole for yourself. I had, and still have, a nasty feeling about this one and, although I have yet to find an appropriate solution, I am grateful to you for confirming that I have little to gain by pursuing the previous path. I find it disappointing, however, that, although Access provides the command DoCmd.GoToRecord , , acNewRec, it does not seem possible to use it, in the present context, to create a new record and go to it. It would have been gratifying had it done what it says on the label! There appears to be a little residual misunderstanding. Perhaps I could clear this by restating the problem in somewhat different terms. A customer (main form) seeks to buy a new flight voucher (sub-form). On opening, the sub-form therefore needs to present a virgin record for completion. However, where customers have already purchased vouchers, these are listed in the table to which the sub-form is bound. In these cases the sub-form therefore opens, displaying the first of the existing voucher records. Of the possible solutions I mentioned earlier, one is to use the sub-form navigation buttons to create and select the required new record, but this is clumsy. It involves an additional, and actually unnecessary, manual operation. Since the 'new voucher' option has already been selected, it is obvious that none of the existing records should be presented for amendment. On the other hand, it is informative for the navigation bar to show that the new record is, for example, no. 8 of 8, and also convenient to be able to scroll back through the existing voucher records to check their details before issuing a new voucher. It would not therefore be sensible to set the sub-form SQL so as to exclude existing records. Nor, for the same reason, would it be advisable to use an unbound form. A second option is to use an append query to concatenate a new record to the sub-form table before opening the form. It would then be necessary to issue a GoTo Last Record command when the sub-form opens. Is that, however, going to lead to similar trouble in Sub Form_Current? As to your final suggestion, the above should make clear that the problem is not one of adding a new sub-form record when a new main form record is created. Instead, the need is to add a new sub-form record to the complement associated with an existing main form record and to navigate directly and automatically to this record. If you have any further thoughts, I would be grateful to share them. -- Peter Hallett "Allen Browne" wrote: Peter, any attempt to use Form_Current to force the entry of a new record is flawed. As you found, this event can fire muliple times for one record, and this behavior changes depending on the version of Access you are using. That means even if you did succeed in creating a workaround, you are still creating a maintenance nightmare that is likely to break in future versions. More importantly, forcing the entry as soon as the user arrives at a new record is highly undesirable. It creates blank records (where nothing was entered.) It creates orphan records (where the parent form is also at a new record, and so the foreign key is null.) As soon as the user tabs out of a new record, it creates another one. And even if you code around all those issues, it will create concurrency issues for you - error messages like "Access stopped the operation because you and another user were ..." In summary, any code that dirties a record in Form_Current is asking for trouble, and it makes no sense to force the user into the entry of a new record just because the visited the end of the records. There has to be a better approach to achieve whatever it is that you need. Perhaps you want a related record created whenever a new record is added in the main form? If so, could the main form's AfterInsert event do that for you? -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Peter Hallett" wrote in message news I have a form and sub-form, respectively bound to tables which are linked in a standard one-many relationship. In contrast to the main form, AllowAdditions is set True for the sub-form. On opening, the latter is required to determine whether the first record is already complete. This it does by testing whether a constituent ID field has a non-zero value. If not, then the current record is virgin and is completed directly by the entry of the appropriate data. If the record is already complete, however, then, under certain defined conditions, a new blank record is created and presented for completion. The VBA used to add the new record and move to it is:- If (Me![ID] 0) And [Condition] Then DoCmd.GoToRecord , , acNewRec ([Condition] is a Boolean field, set appropriately elsewhere.) This works, but only in Sub Form_Current. No new record is created if this code is run in Sub Form_Open or Sub Form_Load. Further code is run, in Sub Form_Current, after the new record is created. This enables, disables, hides or displays various command buttons which are, or are not, made available according to the conditions. The exit button, for example, is not normally enabled until the data entry to a new record is complete. This conditional interlocking generally works well. There is, however, a considerable difficulty created by the way in which Sub Form_Current operates. When the first record is found to be complete, the above VBA creates and displays a new record as required. The button interlocking code next runs but, unbidden, then re-runs for the preceding record - the completed one which occasioned the creation of the new record. The command button interlocks are thus set correctly for the new record and then immediately reset for the conditions applying to the previous record - in most cases inappropriately. I have only been able to find two ways of preventing this, neither of which is elegant. The first is to create a Boolean variable, at module level, called, in my case, 'FirstPass'. This is set True in Sub Form_Open and it is this value upon which entry to the button interlocking code in Sub Form_Current is made conditional. The first action of the interlocking code is then to set FirstPass False, preventing this code from being re-run. The second method is not to use the GoToRecord command but to attach any new records using an append query and then to navigate to them. It is not a pretty technique either. Clearly, I do not fully understand the operation of Sub Form_Current. Can anyone enlighten me and perhaps suggest a more workmanlike method of achieving my ends? -- Peter Hallett |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Adding tables | Gertjan | Running & Setting Up Queries | 1 | December 19th, 2005 04:20 PM |
Next Record keeps making new record even if nothing entered on for | Groucho | Using Forms | 4 | November 1st, 2005 12:49 AM |
Creating a new record in a table related to 2nd table via 3rd tabl | Danny | Database Design | 6 | December 30th, 2004 06:41 PM |
Creating multiple records from one record | Marge | Running & Setting Up Queries | 1 | December 7th, 2004 11:22 PM |
Creating a new record | Phil Matish, MCSE | Using Forms | 2 | July 5th, 2004 01:31 AM |