Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2004
    Posts
    126

    Unanswered: Updating from a Form with a Multi-Table Recordsource

    I am filling a bound form in an ADP with a multi-table join query, and I want to update a few fields in one of those tables. I am violating a few of the criteria to have Access handle updating just that one table, and that is fine. I am perfectly capable with VB and T-SQL to write my own update query.

    However, I have been unable to find a way to manually update that table since Access won't let me even enter values into the the fields since the recordset isn't updateable. I need bound fields since I need to display information that is in those fields, but then I just need somewhere to enter the data that I can then use to run a manual update query. Preferably as seemlessly as possible to the user.

    Unfortunately, I can't use unbound fields and just fill them with info from the query because I am using a continuous form and there is only one instance of each unbound control.

    Any suggestions? I'd just like a direction to go in please. I will happily respond with what worked and didn't work when I am finished.

  2. #2
    Join Date
    Dec 2004
    Posts
    277
    have you tried showing the information on a subform?

  3. #3
    Join Date
    Feb 2004
    Posts
    126
    First off, I like your name

    I have toyed around with a few things such as having a listbox to select the records which would populate editable fields in the footer, but this still isn't an ideal situation.

    What did you have in mind with the subform? Put the editable or the non-editable fields on the subform?

  4. #4
    Join Date
    Feb 2004
    Posts
    126
    I kinda got it. Let's say I have two text boxes, one bound to a field in my query, one unbound that I will use for data entry to update that field. Let's call them:

    Field1Bound
    Field1UnBound

    Then what I ended up doing is place the bound text box directly over top of the unbound one and putting this in the GotFocus event of the Field1Bound text box

    Private Sub Field1Bound_GotFocus()
    Me.Field1Unbound = Me.Field1Bound
    Me.Field1Unbound.SetFocus
    End Sub

    And now I will just put my manual update procedures in the LostFocus event of the Unbound text box and I should be good to go.

    I will just repeat this with the 4 or 5 fields I need to update and the end user won't even notice what's going on.

    Does anyone have any suggestions or improvements to this "hack"?

  5. #5
    Join Date
    Dec 2004
    Posts
    277
    Since you have multi tables why not have the MasterForm be the viewable information and have subforms where you could edit information and have it set to be visible based on the subject you are workin

  6. #6
    Join Date
    Feb 2004
    Posts
    126
    Is there ANY way that I can populate the fields in the rows in a continuous form and allow editing of the text boxes and then use something like the Before Update event to manually handle the updating?

  7. #7
    Join Date
    Dec 2004
    Posts
    277
    anytime a form is based on three or more tables it will not allow editing..

    the only solution that i have is the one I described earlier.. but then again I have no idea of what continuous forms are and all that...

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by grrr223
    I am filling a bound form in an ADP with a multi-table join query, and I want to update a few fields in one of those tables. I am violating a few of the criteria to have Access handle updating just that one table, and that is fine. I am perfectly capable with VB and T-SQL to write my own update query.

    However, I have been unable to find a way to manually update that table since Access won't let me even enter values into the the fields since the recordset isn't updateable. I need bound fields since I need to display information that is in those fields, but then I just need somewhere to enter the data that I can then use to run a manual update query. Preferably as seemlessly as possible to the user.

    Unfortunately, I can't use unbound fields and just fill them with info from the query because I am using a continuous form and there is only one instance of each unbound control.

    Any suggestions? I'd just like a direction to go in please. I will happily respond with what worked and didn't work when I am finished.
    How I do this is to make my form bound to a "template" table and query for the data and put it into a temporary table that I bind the form to ... Then the controls are updateable. Then depending on what column is changed, you update the corresponding table ...
    Back to Access ... ADO is not the way to go for speed ...

  9. #9
    Join Date
    Feb 2004
    Posts
    126
    DackJaniels, a continuous form is simply one that shows many records on the page at once (usually as rows) instead of just one page for each record. It causes a few issues because unbound controls only have one instance per page (yes, this seems counter-intuitive) which means if you set the value of an unbound control on one record, it appears on all the records which makes this all very complicated.

    Thanks M Owen, I think I like that idea as only 1 person should be updating this information at a time. Do you have more ideas on how I should go about handlign the updating procedures? Do you do it after every field update, row update, or do you let them make all the changes they need and then update all the rows? Do you use stored procedures, vb, ado, etc.? Again, I am just looking for a direction I should be able to figure it out from there.

    Can you think of any drawbacks to using the temp table option such as updating and stuff like that?

  10. #10
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by grrr223
    DackJaniels, a continuous form is simply one that shows many records on the page at once (usually as rows) instead of just one page for each record. It causes a few issues because unbound controls only have one instance per page (yes, this seems counter-intuitive) which means if you set the value of an unbound control on one record, it appears on all the records which makes this all very complicated.

    Thanks M Owen, I think I like that idea as only 1 person should be updating this information at a time. Do you have more ideas on how I should go about handlign the updating procedures? Do you do it after every field update, row update, or do you let them make all the changes they need and then update all the rows? Do you use stored procedures, vb, ado, etc.? Again, I am just looking for a direction I should be able to figure it out from there.

    Can you think of any drawbacks to using the temp table option such as updating and stuff like that?
    It depends on what and where you're updating ... If the displayed data is from 1 table you can get away with an update once a row ... My persona preference is to have a separate Update and Save buttons that the user clicks to perform all the updates/saves for the data that has been added/changed. Now, how to implement updates: Add hidden columns to the underlying table for each column you wish to have a trigger for an update on (at least 1 for the row). You can then either perform the update automatically on the Current event of the form (when you move from 1 record to another) or update eternal to the controls. You can't update after the edit until the record pointer has moved off the row (when Access writes the changes to the table) or you force the update by clicking on the record selector directly.

    Drawbacks? Yes. The data may be entered but not saved. You will have to make detection code and safety code to handle those issues.
    Back to Access ... ADO is not the way to go for speed ...

  11. #11
    Join Date
    Feb 2004
    Posts
    126
    Thanks M Owen, I like the idea of using triggers, that really prevents a lot of possible issues.

    I think after looking at all of these options, I have decided to use a listbox to display the query and when the user clicks to select a record in the listbox, it populates text boxes below the listbox, and then they can make changes. This solves a lot of the issues that arise due to continuous forms by using an unbound single form instead. It also makes updates at the record level very easy.

    Thank you for your help. I hope this helps other people possibly too. Listboxes are really excellent things, especially when you learn how to harness the power of multiple selections. They are also very simple to program and fill, esp. if you have already done most of the work in a query.

  12. #12
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by grrr223
    Thanks M Owen, I like the idea of using triggers, that really prevents a lot of possible issues.

    I think after looking at all of these options, I have decided to use a listbox to display the query and when the user clicks to select a record in the listbox, it populates text boxes below the listbox, and then they can make changes. This solves a lot of the issues that arise due to continuous forms by using an unbound single form instead. It also makes updates at the record level very easy.

    Thank you for your help. I hope this helps other people possibly too. Listboxes are really excellent things, especially when you learn how to harness the power of multiple selections. They are also very simple to program and fill, esp. if you have already done most of the work in a query.
    Just as an aside, I made a customer contact entry form that has the contacts as a subform - a continuous forms at that ... I do not allow editing within the form. The user selects a contact to edit and up pops an edit form with that contact's info ... Upon saving, the changes are applied to the subform and to the actual contact table ... There are many ways to solve this issue - it's mostly a user interface/interaction issue.
    Back to Access ... ADO is not the way to go for speed ...

  13. #13
    Join Date
    Feb 2004
    Posts
    126
    I use methods like that one too in many places, mainly where the information doesn't get changed much once the record is created. But I am replacing what is currently done in a spreadsheet with this application in Access and I had no idea it was going to be this complicated to get the information I needed and then to update it as this is turning out to be. At least the listbox allows me to make multiple selections which will help speed up data entry.

    But I guess that's just how it goes. If you want the formalization of a rigid system, you have to accept that it's not going to be a flexible as being able to enter stuff at will on a spreadsheet.

Posting Permissions

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