Insert with Select

[krzysiekk]

Hi I found problem with insert in vba.

DoCmd.RunSQL "insert into SeqNo (SequenceNo) Select seq From print_all WHERE line = '01' and reqd_date = [Forms]![Production Plan]![Text5] and seq = '1'"

is not possible to execute because in criteria where I specify same field.
But I need specify seq

Please advise

 

[VilaRestal]

You would do it like this:

DoCmd.RunSQL "INSERT INTO SeqNo (SequenceNo) SELECT seq FROM print_all WHERE line = '01' AND reqd_date = " & Forms![Production Plan]!Text5 & " AND seq = '1'"

However I'm guessing reqd_date is a date field and seq is a number field. In that case it would be:

DoCmd.RunSQL "INSERT INTO SeqNo (SequenceNo) SELECT seq FROM print_all WHERE line = '01' AND reqd_date = #" & Format(Forms![Production Plan]!Text5,"mm/dd/yyyy") & "# AND seq = 1"