Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2003
    Posts
    10

    Unanswered: Automatically create records

    first i would look at my attachment to see my table structure and relationships.

    there are 20 questions in tblQuestion, i want the user to open a form, enter the caseno, click a button and a subform (control source = tblMain) will poulate with the 20 questions. then my users can add their answers.

    this all sounds so simple yet i havent a clue on how to go about doing this, would i need to use a query?

    i hope i have made my problem clear, does anyone have any advice or solutions?
    Attached Thumbnails Attached Thumbnails relationships.jpg  

  2. #2
    Join Date
    May 2002
    Location
    London
    Posts
    87
    Well firstly, that image might be a bit much for those on dial-up so and you could edit it down to be a bit smaller or use a text description.

    Secondly, the simplest approach is to create a form with all the records for all of the caseno and then use a combobox to filter it for the current case.

    The filter code would be

    Private Sub ctlSelectCaseNo_AfterUpdate()
    Me.Filter = "[CaseNo] =" & ctlSelectCaseNo
    Me.FilterOn = True

    You should also stick something in the Form_Load to ensure a filter is loaded.

    Sub ctlSelectCaseNo.Value = Sub ctlSelectCaseNo.ItemData(0)
    Sub ctlSelectCaseNo_AfterUpdate

    Would do the trick

  3. #3
    Join Date
    Feb 2003
    Posts
    10
    but how does this code enter the 20 questions from tblQuestions into tblMain for the caseno that the user enters?

  4. #4
    Join Date
    May 2002
    Location
    London
    Posts
    87
    Oh I see.

    You need and insert query

    qryAddQs:

    Parameters parCASENO
    INSERT INTO tblMain (CASENO,QUESTIONID, ANSWER)
    SELECT parCASENO as CASENO, tblQuestions.QuestionID, Null as ANSWER
    FROM tblQuestions

    Then

    Private Sub ctlSelectCaseNo_AfterUpdate()

    Dim dbs as DAO.Database
    Dim qdf as DAO.QueryDef

    Set dbs = CurrentDb
    Set qdf = CurrentDb.QueryDefs("qryAddQs")

    'This will add any missing questions to the Case
    With qdf
    .Parameters("parCASENO") = ctlSelectCase
    .Execute
    End With
    set dbs = Nothing
    Me.Requery
    Me.Filter = "[CaseNo] =" & ctlSelectCaseNo
    Me.FilterOn = True

    End Sub

  5. #5
    Join Date
    Feb 2003
    Posts
    10
    thanks for the reply, i tried that but it still doesnt work, it puts the caseno into the first record in the subform but it doesnt put all the questions in

    here is the database if it helps
    Attached Files Attached Files

Posting Permissions

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