Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parent/Child SubForms, etc. (2000/2002)

    I'm finding Access application development a little overwhelming, as I'm not able to author code (sometimes I can piece it together though). I would describe myself as beginner to intermediate and I'm currently using Alison Balter's Mastering Access 2002 Desktop Development and Access Cookbook as resources but I find the former full of secret handshakes when it comes to basic functionality; Ms. Balter seems real good at surveying a landscape in a very broad way but it's not specific enough for my needs. The Cookbook is much better but doesn't cover all of my needs (at least not that I'm aware given my lack of knowledge). I'm thinking of ordering the Developer's Handbook Set but often find this forum is the best resource available. My lack of knowledge always leaves me feeling like I'm over complicating the simplest things. Methodology is not my strong point. (Yet!)

    I'm trying to add a Notes register to two separate forms: frmSubmissions and frmViewCommissioners. Each should have a separate Notes register so I've created and linked tables tblSubNotes and tblCommNotes. I'm just working with the frmSubmissions object now. tblSubNotes contains the following fields: SubmissionID, NoteID, DateCreated, UserCreated, DateModified, UserModified with a combination primary key of SubmissionID and NoteID. tblSubmissions and tblSubNotes are meant to be linked via the shared SubmissionID field which is an AutoNumber in the former and a Number in the latter. Originally, I wanted to add two command buttons, Add Note and View Notes. For View Notes, I thought I would use a subform in datasheet view displaying all the tblSubNotes records that shared the same SubmissionID as the parent record, sorted by DateCreated (I was thinking this would be continuous?). For Add Note, I thought I would launch a separate form which seems easy enough to do. (Famous last words.) I'm so overwhelmed with this, right now I can't even think about how to Edit a note record.

    Though there are probably many problems with this scenario, the most obvious to me right now is passing an established AutoNumber from the tblSubmissions parent record to a Number field in the tblSubNotes child record. There must be some code or event required here because, having created the basic forms, I'm unclear as to how a click on a command button would create a new record, much less pass a string from an AutoNumber field into the new record as a Number. I'm confused, obviously.

    Also, for the View Notes command button, I was thinking it might possibly be easier to launch a Report that queries the tblSubNotes for all records containing the same SubmissionID and then sorts it via DateCreated. This seemed like it would be much easier to read or print. Once created, I don't think the Notes records will be modified much as the user will always have the ability to create a new note, rather than update old ones. I prefer for each note to be in it's own record and then query the table by the shared SubmissionID.

    Sorry to be so long winded but a lot of this is about thinking through a solution and, for better or for worse, I wanted to present my thoughts about this. I'd like to think more like a developer! Any clues, hints, links or solutions would be greatly appreciated. Also, any opinions on the Developer's Handbook Set and it's suitability for me would be welcome; I do like the clarity of Ken Getz's solutions in the Cookbook.

    Thanks!

    S.O.

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

    Re: Parent/Child SubForms, etc. (2000/2002)

    You mention subforms, but from your description it seems you're not really using a subform. Still, a subform on frmSubmissions is by far the easiest way to accomplish this. The idea is as follows:
    <UL><LI>Create a new form based on tblSubNotes.
    <LI>This form can be single or continuous, both will work well.
    <LI>While designing it, keep in mind that it will have to fit on the main form.
    <LI>Save it, say as sbfSubNotes.
    <LI>Open frmSubmissions in design view.
    <LI>If you have enough room, you can place the subform now. It you don't, create a tab control on the main form. Cut the existing controls and paste them into the first page of the tab control, then move the tab control to the upper left corner of the form and resize the form to remove unneeded space. Activate the second page of the tab control and put the subform there.
    <LI>Set the Link Master Fields and Link Child Fields properties of the subform as a control on main form to SubmissionID.[/list]The linking of the main form and subform through Link Master Fields and Link Child Fields is the essential part. As soon as the user types something in the notes subform, the linking field will be copied from the main form into the subform automatically. No code needed at all!

  3. #3
    Star Lounger
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parent/Child SubForms, etc. (2000/2002)

    Hans, you're the best. Thanks for pointing the way. I'm going to get to work on this now.

    Thank you.

    S.O.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Parent/Child SubForms, etc. (2000/2002)

    It looks like Hans has pointed you in the right direction with the subform for Notes, so I'll just comment on books. The Access Developer's Handbook would probably be more frustrating for you at this stage than the Balter books. It is an advanced book, while her's is an intermediate to advanced. As you become more experienced, you will definitely want to add the ADH to your collection, but you might want to wait a bit before you acquire it. You might want to look into one of the Que "Using" books that is beginning to intermediate covering topics like subforms, combo boxes, reports and the like. If you want to dig into VBA, I like the WROX book "Beginning Access XXXX VBA" as it gives you a strong introduction to things like recordsets, DAO and ADO. Hopefully others can suggest books that are focused on the beginner to intermediate transition.
    Wendell

Posting Permissions

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