Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2006
    Posts
    7

    Unanswered: Updating Unbound Combo Boxes in a Bound Form

    I'm trying to create a form in MS Access 2003 which will allow the user to edit information in a continuous form. The form is set up so that in each row, there are two combo boxes (one bound to a column in a table which has relationship information, and is the same as the form is bound to, and one bound to another column in that table). The form is bound to a table which has the information for the first two combo boxes. I have 3 other combo boxes and 3 text fields in each row of the continuous form which should at the creation of the form display the appropriate values from a different table than the form is bound to, and should at form close save the changes in that different table.

    One of the problems I am experiencing is that changing the value of one of the unbound combo boxes changes the value for all of them on the continuous form. Are there any solutions for this besides creating a temporary table to which I can bind all the fields on the form?

    Thanks for your help.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Nope. That's a feature(curse) of unbound combos in continuos forms ... You'll need to bind the combo to some table or query to get values to "stick" to each row ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Jul 2006
    Posts
    7
    If I were to bind the combo box to a query: could I use the form's CurrentRecord object to get information specifically for that row? Would I be able to change the values in the unbound combo boxes and text fields by clicking on them/typing in values?

    Could I make the entire form unbound and only bind the individual combo boxes to fields in different tables? Can I bind the now-unbound combo boxes to fields in a table to which the form is not bound?

    Thanks for your help--I'm a newbie to Access and I have been finding every single task which I must perform to be incredibly difficult, as if MS excluded all the features which I would ever need...so if you can relieve any more headaches I've been having, I would be very grateful

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by CapKrugers
    If I were to bind the combo box to a query: could I use the form's CurrentRecord object to get information specifically for that row? Would I be able to change the values in the unbound combo boxes and text fields by clicking on them/typing in values?

    Could I make the entire form unbound and only bind the individual combo boxes to fields in different tables? Can I bind the now-unbound combo boxes to fields in a table to which the form is not bound?

    Thanks for your help--I'm a newbie to Access and I have been finding every single task which I must perform to be incredibly difficult, as if MS excluded all the features which I would ever need...so if you can relieve any more headaches I've been having, I would be very grateful
    Yes.

    Yes.

    Yes.

    Yes.

    Any more questions?
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Jul 2006
    Posts
    7
    Actually, yes!

    I'm trying very hard to bind the currently unbound combo boxes and text fields to a different table, but I don't see how I can do this and have the combo boxes and text fields not be read-only. Also, I need a way of synchronizing the values across all of the controls on the form: for each relative (given by relativeID and appointmentID in the table), I need to display the first three diseases he or she has (if they exist--the diseases are given by an instanceID which is 1, 2, or 3 for the first 3 diseases). How can I make sure that the diseases in each row (one row per relative) are matched up with the relationship type/bloodline information in the first two combo boxes, if my form is bound to the table with information about the relationship type/bloodline and not the disease table?

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    2 things going on here ... To bind the combos, you can bind them to a table/query thru the ControlSource (which sets the value selected to that row) and you can bind the combos to a DIFFERENT RowSource which gives you the values that you want to be able to save ...

    Back to basics here: If you use Continuous Forms, the controls in the continuous part of the form MUST be bound to make them useful ...

    As for your questions ... I'm not seeing how you've constructed this form nor how your tables are setup and related ... Basically, I'm not visualizing it right now ...
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Jul 2006
    Posts
    7
    I thought that visualizing it might be a problem. Attached is a mock-up of how it should look. The form right now is bound only to the table which has the "Relationship" and "Bloodline" information. The rest of the combo boxes and what should be text fields (underneath the "Age of diagnosis" labels) need to display information which is found in a different table.

    Each row should display a distinct relative. Each disease has an "instanceID" identifying it, as well as an associated "relativeID."

    I know that the RowSource for the actual values which will be in the combo box drop-down list is different than the ControlSource. I'm having problems, though, binding the combo boxes to the table with disease information in it. I guess I don't understand what type a ControlSource is--can I set it to be a string containing an SQL query? A DAO.Recordset object? You can tell I am thoroughly confused and frustrated by Access/VBA, and having co-workers say "it looks simple enough" and then not being able to do it themselves or offer any working suggestions does not help the situation!
    Attached Thumbnails Attached Thumbnails MammoScreen.PNG  

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Oh this is NOT simple at all ... This one complex little bugger. Whomever was saying that has/had their head up their ass. Here's what I would do: Build a temporary table to base your form on ... Define what 1 record (row) should contain ... (I see a problem here with your relationship model - at what point does the row end? Is there 1 or more relationships represented in each row? Or is each "end" of a relationship a row?)

    Bind your controls to the columns of that temp table ... Make your tables that are used for the binding of your combos (data source of the combos) ...

    You have a few questions to answer ...
    Back to Access ... ADO is not the way to go for speed ...

  9. #9
    Join Date
    Jul 2006
    Posts
    7
    I encountered a similar problem in the past and the solution was to create a temporary table, however, the main database guy thought it would be a bad idea when I raised the possibility of synchronization issues. That is, even though only one person should be editing this at one time, there's still a possibility that if two people edit them at one time then they might overwrite each other's changes. This would be picked up in a table-level triggered audit log that is in place for the system, but they decided against the temporary table idea after I had already sunk a considerable amount of time into it. So, I guess I'll have to raise this issue again...thanks for the help. It really bugs me that there's no way to do something which seems so simple in Access, and you can understand how having to deal with problems similar to this as a first-time Access user has really increased my skills with Google data mining!

    Each relative can only have one kind of relationship with the person filling out the form, so each record/row for a relative would end after the third disease. Only one relative is represented by one row, so that should at least simplify things. Still, the issue of having combo boxes change values is what I have to deal with. Is that the complexity which you were referring to, or is it that you thought the relationship design would be confusing?

    And again, thanks for the trouble.

  10. #10
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by CapKrugers
    I encountered a similar problem in the past and the solution was to create a temporary table, however, the main database guy thought it would be a bad idea when I raised the possibility of synchronization issues. That is, even though only one person should be editing this at one time, there's still a possibility that if two people edit them at one time then they might overwrite each other's changes. This would be picked up in a table-level triggered audit log that is in place for the system, but they decided against the temporary table idea after I had already sunk a considerable amount of time into it. So, I guess I'll have to raise this issue again...thanks for the help. It really bugs me that there's no way to do something which seems so simple in Access, and you can understand how having to deal with problems similar to this as a first-time Access user has really increased my skills with Google data mining!

    Each relative can only have one kind of relationship with the person filling out the form, so each record/row for a relative would end after the third disease. Only one relative is represented by one row, so that should at least simplify things. Still, the issue of having combo boxes change values is what I have to deal with. Is that the complexity which you were referring to, or is it that you thought the relationship design would be confusing?

    And again, thanks for the trouble.
    The combos are relatively simple to do IF the relationship (and I mean here row to row) is simple. Your diagram did not display (from what I saw at least) that relationship. I couldn't tell if what I saw was 1 or more rows ...

    As for the temp table: UNLESS you're gonna have all the tables, forms, and reports in 1 db then temp is the way to go ... With a BE/FE scheme, you will have to do that ... As for the overwrite issue: REALLY/REALISTICALLY, how much of a chance is there that 2 or more people will be working on the same person at the same time???? Answer that for your design constraint ... Technically, yes - that overwrite can occur... Will it tho? Away around that can be to have a flag for each person that is set when editing/looking at so that others can either be blocked for editing/viewing or at least notified that other(s) are doing the same ... So, you can save at your own risk ... Even there, you could set up some mechanism to log who's viewing for pull up to confirm that overwrites will be minimized ...
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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