Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2007
    Posts
    88

    Unanswered: how to avoid displaying the blank records in the continuous form

    Hi Gurus,

    I have a continuous form with a query as a record source.
    The query is based on one table with a "Keep" flag (boolean field) (YES/NO) in the table. If the "keep" field is YES, that record will not be belong to the query and thus, won't display in the continuous form. The continuous form contain a few fields in the query also has a check box allow user to check whether he want to Keep this record, which also will update the table field - "Keep" to Yes. The code behind the form will use refresh, requery, and repaint to reflect the change immediately. So if one record was checked by the user, it will immediately disappear in the form.

    Normally, when you open a access table, there is always a blank record at the bottom of the table. I think same will apply to query. The blank record also display at the bottom of the continuous form. Normally, user clicks the keep check box pretty fast and some times accidentally click the check box for the blank record and the program will error out. How to avoid the continuous form display the blank record or prevent the program error out even though the user click the check box for the blank record.

    Thank you very much for help.

    NewGuy

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    What I do on a continuous form is set the allowadditions property of the form to false. Then I have a button called "Add New Rec" (in the form header so it will always show even if there are no records). When the user clicks it, I run this code:

    me.allowadditions = true
    docmd.gotorecord,,acnewrec
    me!DateEntered = Date() <- I usually set some value in any field to generate the autonumber. Since I always have a DateEntered field in all data tables, I set this value.
    'Refresh < - not sure if the refresh is necessarily needed - I never need it but you may find a need.
    me.allowadditions = false
    me.SomeField.setfocus

    Regarding your checkbox error, you may have to have an autonumber generated. Try using the Before Update event of the checkbox to possibly generate the autonumber by setting another value in a field or possibly the refresh command (although this doesn't seem like it would be the problem.) You can also test for a new record in the BeforeUpdate event such as: if me.newrec = true then....(again though, I don't think you would normally have to do this.) Do you have an autonumber field in the recordsource (table) and on the subform? I've gotten in the habit of almost always having an autonumber field, especially on updating often data tables. I "think" this was a possible reason for getting an error in clicking on the checkbox on a new record.

    I might recommend:
    Add an autonumber to the subform if you don't already have one.
    Then try the checkbox and see if you get an error on a new record.
    If you do (or already have an autonumber), then try the code in the BeforeUpdate event on the checkbox (although something still doesn't feel right that you should have to do this) or the code to "Add New Rec".

    I just tested a continous sub form with a checkbox and I would get an error if the Main form record doesn't have a record generated (autonumber). You may have to set a value on the Main form first (or test if the autonumber field on the Main form for is not null.) Also, your relational ID field between the 2 tables should be on the continous form and set the default value of this field to =Forms!MyMainForm!MyAutonumberfield (you can make it invisible.) You don't want the linking field on the continous form left blank for a default value. It also should be a required field in the table. Having it set up correctly should give you an error that the XXXID field cannot be blank if you try to enter a new record in the subform when you're on a blank record on the main form. Again, you could also possibly set a value on the main form, or test to see if the autonumber is not null and then set the recordsource of the subform so it's not an issue, or test for it in the Before Update event of the continous form. Either method, make sure your default value on the ID field =Forms!MyMainForm!MyAutonumberField.

    You also might find the highlight edited record form for continous forms (Form2) in this example interesting: http://www.dbforums.com/showpost.php...6&postcount=39

    It nicely shows how to change colors with a few lines of code on a new record in a continous form and highlights the currently edited record.
    Last edited by pkstormy; 11-16-07 at 00:43.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Aug 2007
    Posts
    88

    Fantastic!This is exactly what I need to know.

    Quote Originally Posted by pkstormy
    What I do on a continuous form is set the allowadditions property of the form to false. Then I have a button called "Add New Rec" (in the form header so it will always show even if there are no records). When the user clicks it, I run this code:

    me.allowadditions = true
    docmd.gotorecord,,acnewrec
    me!DateEntered = Date() <- I usually set some value in any field to generate the autonumber. Since I always have a DateEntered field in all data tables, I set this value.
    'Refresh < - not sure if the refresh is necessarily needed - I never need it but you may find a need.
    me.allowadditions = false
    me.SomeField.setfocus

    Regarding your checkbox error, you may have to have an autonumber generated. Try using the Before Update event of the checkbox to possibly generate the autonumber by setting another value in a field or possibly the refresh command (although this doesn't seem like it would be the problem.) You can also test for a new record in the BeforeUpdate event such as: if me.newrec = true then....(again though, I don't think you would normally have to do this.) Do you have an autonumber field in the recordsource (table) and on the subform? I've gotten in the habit of almost always having an autonumber field, especially on updating often data tables. I "think" this was a possible reason for getting an error in clicking on the checkbox on a new record.

    I might recommend:
    Add an autonumber to the subform if you don't already have one.
    Then try the checkbox and see if you get an error on a new record.
    If you do (or already have an autonumber), then try the code in the BeforeUpdate event on the checkbox (although something still doesn't feel right that you should have to do this) or the code to "Add New Rec".

    I just tested a continous sub form with a checkbox and I would get an error if the Main form record doesn't have a record generated (autonumber). You may have to set a value on the Main form first (or test if the autonumber field on the Main form for is not null.) Also, your relational ID field between the 2 tables should be on the continous form and set the default value of this field to =Forms!MyMainForm!MyAutonumberfield (you can make it invisible.) You don't want the linking field on the continous form left blank for a default value. It also should be a required field in the table. Having it set up correctly should give you an error that the XXXID field cannot be blank if you try to enter a new record in the subform when you're on a blank record on the main form. Again, you could also possibly set a value on the main form, or test to see if the autonumber is not null and then set the recordsource of the subform so it's not an issue, or test for it in the Before Update event of the continous form. Either method, make sure your default value on the ID field =Forms!MyMainForm!MyAutonumberField.

    You also might find the highlight edited record form for continous forms (Form2) in this example interesting: http://www.dbforums.com/showpost.php...6&postcount=39

    It nicely shows how to change colors with a few lines of code on a new record in a continous form and highlights the currently edited record.
    Paul,
    Thank you so much for spending time to explain to me in such a detail manner. It works very well for me. Fantastic! Thanks again.

    Newguy

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You're welcome. Thanks for letting us know it works.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •