Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2004
    Posts
    47

    Unanswered: Subform Problem with Unbound Objects

    Ok, so here is the problem:

    I have 3 tables from which I run a SELECT SQL statement.

    Then I have a main form and subform. The main form has 2 textboxes who will fill the filters of the SQL statement. And I have a subform which is created from the query (it is bound to the query). Every time the user enters a value, the subform displays the results. Pretty standard...

    Now, I want to put a checkbox in the subform so that the user can check off some of the records and after he hits a SUBMIT button on the main form, another APPEND query is run which takes the values from the subform and appends them to a 4th different table. I have created both the query and the button and VBA code to run it when clicked.

    The problem is that whenever the SELECT query fills the subform (datasheet layout), the checkboxes are the same for each record. This means that whenever a user clicks on one checkbox, all checkboxes respond ... something like a Check All function in your mailbox. And I don't want that effect. I want each of the checkboxes to be unique for each record. I've been banging my head over this for a day already. Can't figure it out.

    Someone can help me?

    a.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Your checkbox needs to be bound ... What you bind it to is up to you. What I've done in the past is bind the checkbox to a column in the query/table and set it rowsource to a SEELECT query on a "YesNo" table that has 1 column as 2 rows one being Yes and the other being No (or any other pair you wish). Then when you select a value in the checkbox, it goes into the table/query ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Sep 2004
    Posts
    47

    Continous Form

    This can't work on a continous form. I couldn't understand what you really meant by that.

    The whole deal is the following:

    You have two tables:

    Student
    Class

    Inbetween there is bridge table which is the Roster (basically which student takes which class).

    Now...The idea is that I can take the roster output it on the screen and leave it up to the user the check off the records. Once the SUBMIT button is clicked the APPEND query sends the values from the subform to the Table which keeps track of the attendance. Inside the attendance table there are the same fields as in the subform except there is a DATE column which keeps track of the date of entry.

    a.

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by astrosapiens
    This can't work on a continous form. I couldn't understand what you really meant by that.

    The whole deal is the following:

    You have two tables:

    Student
    Class

    Inbetween there is bridge table which is the Roster (basically which student takes which class).

    Now...The idea is that I can take the roster output it on the screen and leave it up to the user the check off the records. Once the SUBMIT button is clicked the APPEND query sends the values from the subform to the Table which keeps track of the attendance. Inside the attendance table there are the same fields as in the subform except there is a DATE column which keeps track of the date of entry.

    a.
    Why the hell not??? I sure did that ... Look the checkbox is to indicate that these are the selected records right? The checkbox value is not going to be saved as part of your process ... Your append query needs to be modded to filter on those records where the Check Column equals true/1/whatever you want to use ... And when you put records in the saubform (table or query), BY DEFAULT ,you add a Check Column that is set to 0/False/whatever you want to use ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    If you want to store a Yes/No for each record then you need to have it as part of your query. When you add it to a subform, the way you did, access shows it n times but it is only storing one value.

    There are several ways to do what you want. Either you have to add a Yes/No field to the base table and include it in your query. Or, you can create a 'work' table with the Yes/No field. You can populate the 'work' table by appending your query results to it (first Delete then Append), or you can have your query results create a table with a Make Table query. Either way you need a Yes/No field for each record. Hope that makes sense.

    Keep in mind, there are definite pitfalls when you have to do this. In a multi-user environment, you need to either have an extra field with the user's name to store his/her selections, or what I like to do is to have a local table in the frontend. This assumes you have your database split into a frontend (forms, reports, etc.) and a backend (tables). For my applications, any tables in the frontend are considered part of the code and are temporary.

  6. #6
    Join Date
    Sep 2004
    Posts
    47

    Thanks

    Thank you both for the input.

    I created an new Yes/No field in that table and the column is cleared before every time that table is queried. However, now I have a problem with the Append query, I am going to post a separate topic about.

    a.

Posting Permissions

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