Insert Into og tilføj værdi

http://www.eksperten.dk/spm/296424

Jeg kan ikke ud fra de se en løsning.
der skal oprettes en ny record i tabel1 indeholdene én værdi fra tabel2 samt en værdi fra det sql træk der er lavet fra en anden tabel.
rs1 indeholder den eller de værdier der skal med.

men hvis jeg laver en insert skal jeg da "fortælle" hvilket felt værdien '"&rs1&'" skal ind i...

INSERT INTO table1 (field1, field2) SELECT 'YourValue', field2 FROM table2

I get a key violation..
this is what I have...

Dim strFldName1 As String
Dim strfldName2 As String
Dim i As Integer

Dim rs1 As ADODB.Recordset
Dim rs2 As ADODB.Recordset

  Set rs1 = New ADODB.Recordset
  Set rs2 = New ADODB.Recordset

rs1.Open "Select SerieNr from IT_tilmeld", CurrentProject.Connection, _
            adOpenKeyset, adLockOptimistic
rs2.Open "Select DISTINCT SerieNr from support", CurrentProject.Connection, _
            adOpenKeyset, adLockOptimistic
    For i = 0 To rs1.Fields.Count - 1
    strFldName1 = rs1.Fields(i).Value
    strfldName2 = rs2.Fields(i).Value
      If strFldName1 = strfldName2 Then
            MsgBox "match"
            'MsgBox rs1
            'MsgBox strFldName1
            'MsgBox strfldName2
      Else
            MsgBox (strFldName1)
            DoCmd.RunSQL "INSERT INTO support (SerieNr, Historik) SELECT  'strFldName1', Historik FROM DummySupport"
            'DoCmd.RunSQL "INSERT INTO support Select Historik from DummySupport VALUES,('"&rs1&"')"
            'MsgBox "no match"
            'MsgBox strFldName1
            'MsgBox strfldName2
      End If
    Next i
   
rs1.Close
rs2.Close

the idea is that I match each record from IT_tilmeld with support. If there is a record in IT_tilmeld, that has a SerieNr that is not represented in support. It takes that SerieNr plus the value in a "dummytable" with a default infoField and add´s that to support, and then cycle to next record that is NOT represented in support

If strFldName1 = strfldName2 Then
MsgBox "match"

Efter denne kører koden jo videre og forsøger at oprette posten. Prøv at indsætte en Exit Sub:

If strFldName1 = strfldName2 Then
MsgBox "match"
Exit Sub

der kan jo også være flere poster der ikke matcher så den skal jo helst cykle gennem alle poster der ikke står i support

Så kan du evt. oprette en fejlhåndtering der går til næste post:

On error GoTo Errorhandler
din kode
errorhandler:
If Err.number = 123 Then
Resume Next
End If

jeg tror jeg er galt på den...
det må kunne gøres med et SQL statement....

I'm just guessing but I thingk your problem is that you are trying to insert a string and not the contents of a variable..

DoCmd.RunSQL "INSERT INTO support (SerieNr, Historik) SELECT  " & 'strFldName1' & ", Historik FROM DummySupport"

Oops!
DoCmd.RunSQL "INSERT INTO support (SerieNr, Historik) SELECT  " & strFldName1 & ", Historik FROM DummySupport"

Thats still not correct

DoCmd.RunSQL "INSERT INTO support (SerieNr, Historik) SELECT  '" & strFldName1 & "', Historik FROM DummySupport"

hey Terry...it works..it inserts well one record...this meens my loop aint working...Im going to make another ? on that...hope u could take a look at it too

Thanks, I'll take a lokk at your new question if I have time :o)