Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jul 2003
    Posts
    33

    Question Unanswered: Subforms on an unbound form

    I initially had the parent form bound but because I need the ability only to write to the tables when the user used the save button I had to use an unbound form. Unfortunately I'm now not sure how to handle my subform which has a one to many relationship with the parent table and form.

    Any suggestions would greatly be appreciated.
    thank you,
    gina

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Subforms link to the control, not the data. They will link to unbound controls as easily as bound controls, though you may have to set the Child and Master links yourself.

    There are probably better ways around restricting user's ability to save data than creating an unbound form. Have you thought of adding code to the form's insert and update events to interupt or redirect processing?

    blindman

  3. #3
    Join Date
    Jul 2003
    Posts
    33
    I'm not all that talented with Access. This was the only way I knew to work around writing directly to the tables.

    How is it done? This will probably be easier than opening a recordset because I need to have the subform changes rolledback as well if the entire form is not saved.

    Thanks,
    Gina

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What does your Save button do that you have to require the user to use it? Can you cut and paste the code for me?

    blindman

  5. #5
    Join Date
    Jul 2003
    Posts
    33
    Code for the save button ( on unbound form):

    Private Sub SaveButton_Click()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    'Dim qdf As DAO.QueryDef

    Set db = CurrentDb()
    'Set qdf = db.QueryDefs("qrySubCOID")
    'qdf.Parameters(0) = Forms!SubCO!txtSubCOID
    'Set rst = qdf.OpenRecordset()
    Set rst = db.OpenRecordset("select * from tblSubCO where SubCOID='" & txtSubCOID & "'")

    rst.Edit

    rst!SubCODate = txtSubCODate
    rst!Subcontractor = txtSubcontractor
    rst!ContractCSI = txtContractCSI
    rst![Allocated Funds] = txtSubCOFunds
    rst!Scope = txtScope
    rst.Update
    rst.Close
    Set rst = Nothing

    End Sub


    What I was attempting to create was a way that the user could open a form enter a new record or edit an existing record but I didn't want the changes or new form to write to the tables unless specified by the user. Basically I wanted to begintrans when the form was opened and only committrans if the user chose to save. Unfortunatley with bound tables I was finding this impossible.....again I don't know that much about Access. So I decided to try this work around.

    thanks for your help.
    gina

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Access saves changes every time you move to a new record, so yes it is going to be difficult to do what you want to do. It is only in the subform where you want to do this, or are you requiring users to explicitly save changes in the main form as well? Are you allowing users to create or edit several records before committing their work, or do they have to save one record before they can move on to another?

    Like I said, this isn't a simple thing to do, so make sure the functionality is worth the effort.

    A simpler solution would be to have a message box pop up to confirm changes or additions to each record. This would just require a few lines of VB code in the subform's BEFORE UPDATE event.

    blindman

  7. #7
    Join Date
    Jul 2003
    Posts
    33
    Unfortunately the functionality is critical to the application. The parent form only has one record but the subform(containing accounting items) needs to have several records per parent form. This is why I can't add a save / undo method to the BEFORE update. It gets very annoying working with multiple records in a form.

    Thank you for your help.
    Gina

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i don't know that this directly addresses your question, but here it comes anyway:

    i hate, loathe, and despise A's standard form setup: it's too easy for a stupid user to create/delete records and otherwise screw up the data. navigation is killed in ALL my forms. allow add/del is killed in almost all.

    for your new-with-confirmation issue, i make a button New which opens a pop-up/modal unbound form with textboxes for all the appropriate fields from whatever tables are involved in a new record + Create & Cancel button. the user can mess with this form for hours without hurting any tables.

    when the user hits the Create button, i have all the time in the world to go through each textbox and make sure the wannabe-data fits, send warning messages etc etc. then if everything really is OK it's a simple matter to make the recordset(s) and .addnew

    it really is safer & more flexible this way.

    izy

  9. #9
    Join Date
    Jul 2003
    Posts
    33
    Thank you, I agree I'm not fond of bound forms just for the reasons you stated.

    I basically have the same set up that you have described but what I need is to add a subform to the unbound form and have it filter the detail records that have the same ID as the parent ID.

    Any ideas????

    thanks

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    apart from the "filter" word (i don't use A filters) YES.

    if your subform is based on a query (if it isn't, change it anyway!) you can grab the ID from any edit open form (including the parent form) in query design grid. click in the criteria box of ID (doesn't need to be retrieved, but it needs to be on the grid) and then the magic-wand builder button on the main menu. navigate forms; all forms; your parent form and then double-click the ID field.

    izy
    Last edited by izyrider; 07-18-03 at 15:05.

  11. #11
    Join Date
    Jul 2003
    Posts
    33
    I'm sorry you will have to excuse me, I'm not that experienced with Access.

    I have a main form ( frmSubCO) which is unbound and the fields are populated when the user selects a SubCOID from a dropdown box. Upon saving the field changes/additions are written to the tables using an OpenRecordsrc.

    I did not understand how I would add a subform and have it link to the SubCOID on the main form. I also still need to be able to open add new records in the detail/subform.

    the Subform is based on a qry with the SubCOID being the parameter.

    I have a feeling I'm going about this all wrong.

    Thanks for being patient with me.
    gina

  12. #12
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you have a combo Forms!frmSubCo.SomeName which allows users to select from existing SubCOID. the combo works (???)

    you need to get the literal value
    Forms!frmSubCo.SomeName
    (with the real name, of course) into the criteria box of a query in the column that contains the field SubCOID (the query also needs to contain anything else that you want retrieved). the magic-wand can add the criteria, or you can manually type it.

    please do this with a new query, and please don't mess with parameters

    select something in the combo, keep frmSubCo open and
    now run the query: with luck the query will display what you want your subform to display.

    if YES: base your subform on this query
    if NO: open the query in SQL view and paste the SQL into this site.

    izy

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    afterthought: if YES but your subform wont behave, add the following in the afterupdate event of the combo Forms!frmSubCo.SomeName

    Forms!frmSubCo!TheSubFormName.requery

    izy

  14. #14
    Join Date
    Jul 2003
    Posts
    33
    Thank you. The only problem is that I am prompted for the criteria befor the main form will open.

    Thxs

  15. #15
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    one fix (there are others):

    make yourself a null form: a form containing a caption saying "select something from the combo", or your company logo, or the joke of the day, or all three.

    in design view, set this null form as the subform. still in design view, in properties/other set the name of the subform to something useful like "mySubForm"

    in the afterupdate event of the combo

    Forms!frmSubCo!MySubForm.sourceobject = "theNameOfYourSubform"

    izy

Posting Permissions

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