Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    INSERT INTO on a subform (A2K, Win2K Pro)

    I have a db where I am attempting to create a set of records using multiple sub-forms. Everything seems to be working alright but there is one part where I am running into problems. The user can enter a new survery, survey sections, questions for those sections, and, if required, a set of responses to a given question on a given section of a given survey. (You may see why I am doing a bunch of subforms here...)

    any way, I wanted to allow the user to select either a 4 or 6 point Likert scale set of response options if desired. I have a drop-down and code behind to attempt to insert the pre-defined Likert scale choices from a table. The preferred method would seem to be to do a SELECT INTO [TargetTable].(column) FROM [SourceTable].(column) but when I try to do it I get the error

    "Number of query values and desitination fields are not the same."

    I'm taking this to mean, perhaps, that as there are not already 4 or 6 rows in the target table the SELECT INTO from a source table won't run? Or am i missing something... hard to say because this error isn't associated with a help section.

    Here's the Code

    Select Case LikertChoice

    Case "4-Point Scale"
    strSQL = "INSERT INTO QuestionResponses (Response) SELECT LS_4.* FROM LS_4"

    Case "6-Point Scale"
    strSQL = "INSERT INTO QuestionResponses (Response) SELECT LS_6.* FROM LS_6"

    Case Else
    MsgBox "An error has occurred."
    Exit Sub

    End Select

    I've tried moving the drop-down to the subform itself and the same error occurs. I don't want to do that as the subform in this case should ideally be in datasheet format...

    TIA!

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

    Re: INSERT INTO on a subform (A2K, Win2K Pro)

    This is rather confusing - you mention both SELECT INTO (which corresponds to a make-table query) and INSERT INTO (which corresponds to an append query).

    Try

    strSQL = "INSERT INTO QuestionResponses (Response) SELECT LS_4.FieldName FROM LS_4"

    i.e. explicitly name the field instead of using *.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: INSERT INTO on a subform (A2K, Win2K Pro)

    Sorry - I meant INSERT INTO <TargetTable> SELECT <SourceTable>

    and...your suggestion worked!

    thanks!!

Posting Permissions

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