Insert into problem

[MarionD]

Hi all, I need a bit of help please.
I have this sqltext that works fine but I want to add another field that is not in the source table, but in a box on the screen.

sqltext = "insert into tbl_Kett_Teilnehmer_Vorauszahlung([tbl_tour_id], [tbl_teilnehmner_id])" & _
" select tbl_Teilnehmer.[tbl_tour_id], tbl_Teilnehmer.[id]" & _
" from tbl_Teilnehmer " & _
" where tbl_tour_id =" & Me.Toursuchen
DoCmd.RunSQL sqltext

LIKE THIS:

sqltext = "insert into tbl_Kett_Teilnehmer_Vorauszahlung([tbl_tour_id], [tbl_teilnehmner_id], tbl_voraus_id)" & _
" select tbl_Teilnehmer.[tbl_tour_id], tbl_Teilnehmer.[id], (here a value on my screen) me.vorausID" & _
" from tbl_Teilnehmer " & _
" where tbl_tour_id =" & Me.Toursuchen
DoCmd.RunSQL sqltext

Can anyone tell me how to do this?

Thanks in anticipation
Marion

 

[plog]

You have done this in your query already--you used a field on your form in the WHERE clause. Use the method you used there.

 

[Pat Hartman]

You can't change the table definition this way. All the columns referenced by an Insert query must already exist.

You can add calculated columns in a Select query but not in an action query.

 

[MarionD]

Thanks everyone

It works like this:

DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * from tbl_Kett_Teilnehmer_Vorauszahlung where tbl_Tour_ID=" & Me.Toursuchen & " and " & "tbl_Voraus_ID=" & Me.Voraussuchen
Me.Refresh
sqltext = "insert into tbl_Kett_Teilnehmer_Vorauszahlung([tbl_tour_id], [tbl_teilnehmner_id],tbl_voraus_id)" & _
" select tbl_Teilnehmer.[tbl_tour_id], tbl_Teilnehmer.[id]," & Me.Voraussuchen & " from tbl_Teilnehmer " & _
" where tbl_tour_id =" & Me.Toursuchen
Debug.Print sqltext
DoCmd.RunSQL sqltext
Me.Refresh