Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    May 2003
    Location
    Pennsylvania, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cycling through check boxes on a form--Re: Post (Access 2003)

    In <post#=373362>post 373362</post#>, Hans presented 3 ways to cycle through controls on a form. I used his checkbox method in this post to transfer data from a a series of check boxes on a form to a table. Here's my code:

    Private Sub NextRecord_Click()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim i As Integer

    Set db = CurrentDb
    Set rs = db.OpenRecordset("Markets-Articles")
    rs.MoveLast
    For i = 1 To 9
    If Me.Controls("Market" & i) = True Then
    rs.AddNew
    rs![MarketID] = i
    rs![ArticleID] = Me![ID]
    rs.Update
    Me.Controls("Market" & i) = False
    End If
    Next i
    rs.Close

    DoCmd.GoToRecord , , acNext

    End Sub

    The application is to index some records with controlled (pre-defined) index terms. I envisioned that the user would check the terms that apply, then click a button to go to the next record. The code above is associated with the OnClick property of the button and works fine (Thanks, Hans! Cut and paste is a wonderful thing!!). However, I want the user to be able to go back and view previously indexed records and be able to change the terms if desired. How can I do this?

    The table Markets-Articles is linked to the form by the Article ID number and has 2 columns: ArticleID and MarketID. MarketID is linked to another table that has the list of market index terms. Both relationships are 1 to 1. This allows as many of the index terms as checked (or none) to be associated with the article. The problem I am having is that when I go back to a previously indexed record, the check boxes don't get filled in. (Maybe I don't have the database design right??)

    There are several other index categories (like Markets), and once I get the code working for the first one, it will be a simple matter to duplicate it for the other categories.

    I also played around with the listbox method and got it to work. How do I erase the selections after capturing the data?

    Hans, or anyone else, any help you can give me will be most appreciated.

    Thanks!

    Don.

    PS. An unrelated question: The first time I tried the code, it gave me "No current record" because the destination table was empty. I added a dummy record to the table to work around this. Is there any way I could have avoided the error in the first place?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Cycling through check boxes on a form--Re: Post (Access 2003)

    To start with your last question: you don't need the line

    rst.MoveLast

    New records will be added at the end anyway, and this line causes an error if there are no records yet.

    About your main question: I wouldn't do it like this.

    If the user is likely to tick only a few check boxes, I would use a continuous subform based on the Markets-Articles table, linked to the main form by ArticleID. The subform would contain a combo box to select a MarketID. No code needed - the subform will automatically display the records associated with the ArticleID on the main form, and changes will be saved automatically.

    If it is essential that the user can see all MarketIDs at once, I would add a Yes/No field to the Markets-Articles table, and use code to create a record for each MarketID when a new ArticleID is created. I would use a continuous subform here too, with a check box bound to the Yes/No field. Again, no code needed for the check boxes - the values will be saved automatically.

  3. #3
    Star Lounger
    Join Date
    May 2003
    Location
    Pennsylvania, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cycling through check boxes on a form--Re: Post (Access 2003)

    Hans:

    Thanks for your suggestion. But it doesn't seem to be working the way you said it would. Attached is a zip file containing a sample database with 4 article records. The indexer will not be adding new articles--she will be presented with the articles and just apply the index terms. (I have just included one of the index term categories in the attached database; there are several of them in the production database.)

    I put the index terms in a listbox on the subform and set MultiSelect to Extended so the indexer could choose as many terms as desired.

    What am I doing wrong?

    Thanks for the help.

    Don.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Cycling through check boxes on a form--Re: Post (Access 2003)

    Your design is completely different from what I suggested. If you want to use a list box, you don't need a subform. Instead, you need an unbound list box on the main form, with code to select the appropriate items when the user moves to a record, and to save the selected items to the intermediate table.

    See attached version.

  5. #5
    Star Lounger
    Join Date
    May 2003
    Location
    Pennsylvania, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cycling through check boxes on a form--Re: Post (Access 2003)

    Hans:

    This is exactly what I had in mind. You make it look so easy!!

    Thanks very much.

    Don.

Posting Permissions

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