SQL "Insert Into" question

[johnb]

I have the following SQL as part of a VBA Sub
strSql = "INSERT INTO [tbl_Parts] ( CPS_ID, FramePart, Mullpart, TransPart)
" & _
"SELECT " & lngID & " As CPS_ID, FramePart, Mullpart, TransPart " & _
"FROM [tbl_parts] WHERE CPS_ID = " & Me.CPS_ID & ";"
And it works great!

The "CPS_ID, FramePart, Mullpart, TransPart" part are only 4 fields but the
rest of the Form has another 50 or so fields. Do I have to type All the
fileds or is there another method of populating the 50 or so fields??

TIA
johnb

 

[Alex Dybenko]

Hi,
you can either go through all form's controls:

for each ctl in me.control

next ctl

check if control is textbox or combobox, and then build your sql based on
control names

OR
go through fields of tbl_parts table and get field names there

for each fld in tdf.fields


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[johnb]

Cheers Alex

Just what I wanted!

regards
johnb

Hi,
you can either go through all form's controls:

for each ctl in me.control

next ctl

check if control is textbox or combobox, and then build your sql based on
control names

OR
go through fields of tbl_parts table and get field names there

for each fld in tdf.fields


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

I have the following SQL as part of a VBA Sub
strSql = "INSERT INTO [tbl_Parts] ( CPS_ID, FramePart, Mullpart,
TransPart)
" & _
"SELECT " & lngID & " As CPS_ID, FramePart, Mullpart, TransPart " & _
"FROM [tbl_parts] WHERE CPS_ID = " & Me.CPS_ID & ";"
And it works great!

The "CPS_ID, FramePart, Mullpart, TransPart" part are only 4 fields but
the
rest of the Form has another 50 or so fields. Do I have to type All the
fileds or is there another method of populating the 50 or so fields??

TIA
johnb