Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2004
    Posts
    6

    Unanswered: Beyond me... open new form and auto-populate

    Dear all,

    I'm new to databases, and hope that you can help me.

    I'm creating a database for a medical research study, looking at surgical complications. It's the first database I've worked with properly, let alone designed from the ground up. It's also getting quite complicated, so I could do with a bit of help :P

    I have 3 tables:
    1. Patient details - contains patient demographics
    2. Primary operation - contains details of the patient's first operation. There may be two instances for each patient (left and right side)
    3. Re-operation - contains details of a subsequent operation (e.g. due to a complication arising from the first operation).
    There may be multiple secondary operations for a given patient, each of which relate specifically to
    one side (left or right). The most operations in my population is 11.

    I've created a one-to-many relationship between table 1 and table 2, and a one-to many relationship between table 2 and table 3. I think this is the right way to go about this, but if anyone wants to correct me I'd love to hear suggestions.

    My question is this:
    I've created 3 basic forms to correspond to the tables, but at the moment there is no logical progression from one form to the next. Since each patient (record) will require at least one of all 3 tables, I'd like to set it up so that when the user finishes entering the data into form1 (patient details table), he/she can press a button which will close that form and open the next one (form2 - primary procedure) for that patient. In addition, it would be beneficial to pre-populate the newly opened form with some of the data already entered in the previous form e.g. the patient's medical record number (MRN) and/or name.

    A friend of mine suggested that what I need to ask about is how to pass arguments from one form to another in order to pre-populate the next form, but wasn't sure how to go about it. Any suggestions would be greatly appreciated.

    Oh aye, and if anyone needs questions answered on the subject of building cages for chinchillas, I'd be happy to reciprocate :P

    Thanks in advance,

    Lee

  2. #2
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313

    Just a thought...

    Welcome,

    Others may have some more detailed advice for you. However, from what you're describing, it sounds as though you can use subforms for what you're trying to accomplish.

    You can you a wizard to get you pretty close to the desired result.

    If you start the form wizard, then choose all the fields you need to include (provided your one-many relationships are set up) the wizard will set up sub forms or linked sub forms.

    Something you may want to try if you're just getting started in Access.

    HTH,

    Chris

  3. #3
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Hi Lee,

    Also on your table when you set up your relationships be sure to set the Referential Integrity also. Also to get better help, create a copy of your database filled with dummy info. Zip and Post it here, that might get some better results, along with a good explanation of what you want to be in the forms as well.

    Bud

  4. #4
    Join Date
    Jun 2004
    Posts
    6
    Hi all,

    I should also mention that I have the forms all pretty much created (which took me quite a while to do, being new to databases), and would rather not re-create them if at all possible.

    As such, I'm leaning away from sub-forms at the moment (unless anyone knows of a quick/easy way of migrating the contents of the existing forms into a new subform setup).

    Bud, thanks for the offer of looking at my stuff. I'll get on it and post it here asap.

    Cheers,

    Lee

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    try this on a copy of your db and see how easy subforms are.

    open a "mother" form in design view... for fun, make it a new, empty, form
    drag one of your other forms (drag it's name from the database window: forms) into the "mother"

    voila - a subform with no form redesign!

    for all my stuff, i use an almost empty mother form, and one or two subform "holes" in the mother. no more opening forms (...with the startled user hunting around the screen for what happened when he clicked something). all subforms come off the same template so everything is visually unified. all subforms can work out what record(s) they should be playing with by asking mother or (faster) being told by mother.

    to wander amongst the candidate subforms, mother executes
    me!theNameOfTheSubFormHole.SourceObject = "nameOfAForm"

    the mother holds maybe a combo or similar which explains to the daughter what she should be playing with.

    the daughters can "see" anything on the mother with
    xyz = Forms!MothersName.thisTextboxNameOnMother

    the mother can mess with the daughter using
    Me!theNameOfTheSubFormHole.thatTextBoxOnDaughter = "Don't be late"

    the only place you could get stuck is working out the name of the subform object "hole" on the mother - it may not be what you expect! properties/other/name on mother in design view with one click on the "hole" tells you. the "hole" name stays the same when you switch .SourceObject and it is a good idea to edit the name away from the A's default to something that makes sense.

    really, you should check out subforms! best thing since sliced bread (beughhh )

    izy
    Last edited by izyrider; 06-19-04 at 14:20.
    currently using SS 2008R2

  6. #6
    Join Date
    Jun 2004
    Posts
    6

    Posted DBase

    Izy, thanks for that advice, it sounds like it might make things a bit easier. I'm going to have a go at that later this afternoon.

    In the meantime, here is the database, minus real data. I've included a couple of dummy records, which are complete for the "Details and Risk Factors" table/form only. The idea is that the user will enter this data in first, then click a button to continue entering related data for that patient (i.e. it opens the next form - Primary Procedure - and automatically fills in the necessary fields on that form. In this case, it would be "Hospital ID" which is the patient's medical record number.

    The way it is currently set up, the user will have to manually close the first form, open the "primary procedure" form from the database window, and then fill in the correct "Hospital ID" number - a surefire way of creating mistakes.

    It doesnt help that no-one in my department has used a database before (myself included), so I need to make it as foolproof as possible (I hope to do that as I go along... I realise its ugly atm).

    If Bud, or anyone else would like to take a look and make some suggestions, I'd be willing to give you my first-born child. Twice...

    In the meantime, I think I'll see about this subform thingy..

    Thanks in advance all,

    Lee
    Attached Files Attached Files

  7. #7
    Join Date
    Jun 2004
    Posts
    6

    quick question about subforms?

    Hi again,

    can anyone (Izy?) tell me how to get rid of the navigation toolbar (for skipping between records) at the bottom of a subform?

    I've dropped one of my already created forms into the motherform, as Izy suggested, which looks great! However, it is possible to navigate between records on the subform, while the motherform remains on the original (first) record. I'd like to get rid of the subform navbar, and to synchronise the motherform and subform, so that when one form skips records, it automatically skips to the correct record (ie same patient) on the subform. Is this possible/simple?

    Thanks again,

    Lee

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    in design view of the form:
    properties/format/Navigation Buttons: No

    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    and the second question: getting the right record showing on the subform.

    there are many ways, here is one:

    is your subform recordsource a saved query? if not - fix it.

    once the subform's recordsource is a query, you can set criteria in the query that look at the mother form... perhaps at a combo the user uses to select the record of interest.
    be lazy and use the wizard.
    click in the criteria box of the column corresponding to the bound column in the combo and click the wizard magic-wand thingie. in the ensuing window navigate forms/all forms/mother form and then double-click the combo.

    your query now returns the record "selected" by the user using the combo


    another way is to directly attack the .recordsource in code, for example in the subform's _Load() event
    me.recordsource = "SELECT this, that FROM myTable WHERE aField=" & Forms!Mother.myCombo & ";"

    here you need
    aField=" & Forms!Mother.myControl & ";" for numbers
    aField='" & Forms!Mother.myControl & "';" for text
    aField=#" & Forms!Mother.myControl & "#;" for dates


    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Sorry lee,

    Tried to check out your sample but you have a dfferent version than me. I have A2K.

    Bud

Posting Permissions

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