Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2006
    Posts
    3

    Unanswered: Dynamically generating subform

    Hi all,

    I am developping an application to allow doctors to enter medical records.
    For women, we are asking the number of pregnancies they have had.
    Depending on that number, I would like to open a subform with the exact number of records. Ideally, I would like to numbered each line automatically, and asked several questions for each pregnancies.
    Could anyone help me?
    Thanks in advance
    Ln

  2. #2
    Join Date
    Nov 2004
    Location
    Harrogate, N.Yorks, UK
    Posts
    83
    Why record the number of pregnancies in a field when you are entering a full record corresponding to each? The number of pregnancies would be derived later from the number of records for that person...

  3. #3
    Join Date
    Aug 2006
    Posts
    3
    it is the way the medical questionnaire has been set up.
    We are asking : have you ever been pregnant ?(Y/N)
    if yes, how many times have you been pregnant.
    Then we are asking details for each pregnancy.

    I didn't design the questionnaire and I have to develop an application to enter the corresponding data. I can't change anything in the design ...
    So I am looking for a way to be sure to have same number of records than the number of pregnancies.

  4. #4
    Join Date
    Nov 2004
    Location
    Harrogate, N.Yorks, UK
    Posts
    83
    You haven't designed the questionnaire but there is no need to design that question into your database. If you enter a full record for every pregnancy anyway then a count of those records will tell you how many times they have been pregnant- i.e. the data is derived. You can also specify that if there are no 'pregnancy records' for an individual that the count returns a zero value. Recording a yes/no is also pointless if the creation of a full pregnancy record depends on a 'yes' response.

    You can still get the results that you need without designing the questionnaire flaws into your database.

  5. #5
    Join Date
    Nov 2004
    Location
    Harrogate, N.Yorks, UK
    Posts
    83
    On second thoughts, I have found that entering a 'no' value may be of use to verify that the question has been answered. You may also consider a value of 'no response' when recording survey results so that you know the respondent failed to answer the question rather than the user failing to input the data.

  6. #6
    Join Date
    Aug 2006
    Posts
    3
    I agree with you. BUT what my chief is asking to me is to have on the screen (so in the forms) the same thing as on the paper questionnaire ....

  7. #7
    Join Date
    Nov 2004
    Location
    Harrogate, N.Yorks, UK
    Posts
    83
    In my experience, people who believe that they are capable of designing a lovely questionnaire rarely have the knowledge to organise information or to ask a question correctly. Bosses always seem to have acquired database design knowledge at birth...

    Auto generating records could get very messy. If you have no option but to follow your bosses advice then you are probably better off using some VBA to check the 'number of times pregnant' field value with your pregnancy sub-form record count once the pregnancy records have been created. Although this exercise is completely for the hell of it basically.

    I'm just moving away from macros and getting my VBA feet wet so someone else can no doubt advise you (safely) on the VBA code. Anyone?

  8. #8
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520
    Why not use a For Next loop for this one?

    Just pull the answer to the how many kids question from the form and insert it into the count.

    Severe Air Code

    Code:
    Dim Kids as integer
    Kids = me.howmanykidsanswer
    For counter = 1 to Kids
    <commands to display your form here>
    Next counter
    Darasen

  9. #9
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520
    Oops double posted, sorry.
    Last edited by Darasen; 01-23-07 at 17:44.
    Darasen

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    tempting to use a temporary table populated with something like
    dim strSQL as string
    dim ici as integer
    strSQL = "DELETE * FROM tmpPreg"
    currentdb.execute strSQL
    for ici = 1 To me.totPreg
    strSQL = "INSERT INTO tmpPreg(PregNo, FKfield) Values(" & ici & ", " & me.yourForeignKey & ")"
    currentdb.execute strSQL
    next

    gives you numbered records with the FK set.
    mess around with this in the subform, validate data entry, blah blah blah.
    INSERT into the real table when editing is done.

    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I like to use a tally table for stuff like this. Not sure if the query would be updatable though in this case.... hmmm...

    Anyway - have a table called Numbers. One integer field, numbers from 1 to 30, 000 (or so), Primary key. Cross join to your table

    Code:
     
    .....
    WHERE Numbers.number BETWEEN 1 AND Forms!MyForm!NoOfKids
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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