SQL Insert

[Adam McReynolds]

I have a field [record_num] in a table [tbl_batt_id] that can have duplicate record numbers in the table any number of times as a forgien key of sorts. So I would have a group of say 4 records with this same[record_num] in them and would like to duplicate the records but with a new[record_num] that is on my form.

I know I can find these records with Dlookup but I am not sure how to grab the whole group and SQL Insert them into the table with the new [record_num]. Long story short, I know how to do this with a single fixed record but not a variable multiple set. Any suggestions or solutions?

 

[Adam McReynolds]

UPDATE: I was able to get the first and last [prikey]'s of the table[tbl_batt_id] using DMin/DMax based on the [record_num]. I was thinking now some kind of loop to insert the values from looping from the first to the last record. Still at a loss though, just thought I would update my method as I try to find a solution. Thanks to anyone who takes the time to help!

 

[pbaldy]

Sounds like

INSERT INTO...
SELECT...
FROM...
WHERE record_num = Forms!...

You can get the new number from the form as well.

 

[Adam McReynolds]

Sounds like

INSERT INTO...
SELECT...
FROM...
WHERE record_num = Forms!...

You can get the new number from the form as well.

Thanks for the reply. I am a little confused here. Will this allow me to essentially duplicate a group of records based on the old record_num and insert them with the new record_num? Thanks again.

 

[Adam McReynolds]

I think I have a cleaner way of explaining:

1. I would like to query based on my old record_num

2. I would then like to insert that whole set of records back into the same table

3. The only difference is the new set would have the new record_num which is on my current form

I feel like this explains what I am attempting much better. Thanks for any help!

 

[Adam McReynolds]

Sounds like

INSERT INTO...
SELECT...
FROM...
WHERE record_num = Forms!...

You can get the new number from the form as well.

OK Paul, Here is my attempt to follow your advice(Of course the syntax is very wrong):

SQL = "INSERT INTO [" & tbl_batt_id & "] " & _
"(record_num,bat_id,float,under_load,siemans,manufacture,date_code,barcode,status) " & _
"SELECT " & Me.record_num & ", " & bat_id & "," & float & "," & under_load & "," & siemans & "," & manufacture & "," & Date_Code & "," & barcode & "," & Status & "" & _
"FROM " & tbl_batt_id & _
"WHERE record_num = '" & Me.txt_last_rec_id & "'"

DoCmd.RunSQL SQL
Debug.Print

I have the new record_num "selected" with the "Me.record_num". Is this correct? Also I think I screwed the pooch on the rest of the "SELECT" syntax.

 

[pbaldy]

Sorry, was having dinner. Only the form references need to be concatenated into the string. The fields you want copied should just be inside as part of the literal string.

 

[Adam McReynolds]

Sorry, was having dinner. Only the form references need to be concatenated into the string. The fields you want copied should just be inside as part of the literal string.

Thanks again for the reply Paul. Here is what I have but I still get a run time 3134 error on syntax:

SQLText = "INSERT INTO [tbl_batt_id] " & _
"(record_num,bat_id,float,under_load,siemans,manufacture,date_code,barcode,status) " & _
"SELECT " & Me.record_num & ", bat_id ,float,under_load,siemans,manufacture,Date_Code,barcode,Status " & _
"FROM  [tbl_batt_id]" & _
"WHERE ([record_num] = " & Me.txt_last_rec_id & ")"

Debug.Print SQLText
DoCmd.RunSQL SQLText

The debug window showed this:

INSERT INTO [tbl_batt_id] (record_num,bat_id,float,under_load,siemans,manufacture,date_code,barcode,status) SELECT 49652, bat_id ,float,under_load,siemans,manufacture,Date_Code,barcode,Status FROM  [tbl_batt_id]WHERE ([record_num] = 44574)INSERT INTO [tbl_batt_id] (record_num,bat_id,float,under_load,siemans,manufacture,date_code,barcode,status) SELECT 49652, bat_id ,float,under_load,siemans,manufacture,Date_Code,barcode,Status FROM  [tbl_batt_id]  WHERE [record_num] = 44574)

 

[pbaldy]

Did you notice in the debug that you don't have spaces between lines (words run together), a common error?

 

[Adam McReynolds]

Did you notice in the debug that you don't have spaces between lines (words run together), a common error?

I made spaces but I am still getting the same error code. Is this what you meant?

INSERT INTO tbl_batt_id (record_num, bat_id, float, under_load, siemans, manufacture, date_code, barcode, status) SELECT 49653, bat_id , float, under_load, siemans, manufacture, Date_Code, barcode, Status FROM  tbl_batt_id  WHERE record_num = 44546)

Thanks again for your help.

Also, record_num is an auto number in another table and just a regular field in the tbl_batt_id

 

[pbaldy]

Yes, you corrected the one I saw (before WHERE). The only thing I see now is a trailing parentheses at the end that shouldn't be there.

 

[Adam McReynolds]

Yes, you corrected the one I saw (before WHERE). The only thing I see now is a trailing parentheses at the end that shouldn't be there.

Took those out but same error. Could it be the fact there is an auto number?
Here is what I have in the debug:

INSERT INTO tbl_batt_id record_num, bat_id, float, under_load, siemans, manufacture, date_code, barcode, status SELECT 49654, bat_id , float, under_load, siemans, manufacture, Date_Code, barcode, Status FROM  tbl_batt_id  WHERE record_num = 44547

 

[pbaldy]

I meant just the one at the end; the ones around the target table's fields need to be there. I'm not sure regarding the autonumber, but yes that could be a problem. I'll test something.

 

[pbaldy]

I just did a quick and dirty test and was able to insert specifying the autonumber field (I normally work with SQL Server data, so wasn't sure).

 

[Adam McReynolds]

I just did a quick and dirty test and was able to insert specifying the autonumber field (I normally work with SQL Server data, so wasn't sure).

So I put the parenthesis back in on the Into section but still same error:

INSERT INTO tbl_batt_id (record_num, bat_id, float, under_load, siemans, manufacture, date_code, barcode, status) SELECT 49655, bat_id , float, under_load, siemans, manufacture, Date_Code, barcode, Status FROM  tbl_batt_id  WHERE record_num = 44700

So it should be working? Could this be it, this is my bosses DB I am trying to find a solution for and he has the WORST naming of objects. Here it is:

1.The field [record_num] on the form is a bound object but renamed [Record #] and I don't know why. Should I change this back? I am afraid it will screw up something else.

2.The form itself has for a record source a query that is composed of 2 tables and a query within it. The renamed [record_num] bound field comes from [tbl_ps_maint_pm] which is a table in the record source union query.

 

[pbaldy]

The names have to be whatever the actual name in the table is. If you take the SQL from the Immediate window and paste it into a new query and try to run it, what error do you get? Can you post the db here?

 

[Adam McReynolds]

The names have to be whatever the actual name in the table is. If you take the SQL from the Immediate window and paste it into a new query and try to run it, what error do you get? Can you post the db here?

OK, I did what you said and it highlighted the field [float]. I took it out and the query ran. I looked at the data type in the table and it is regular text. So I don't see what the issue could be with that field.

Also, I noticed that [Date_Code] is a date format. Do I need to format it in SQL? I tried but I think my syntax for date format was off(Go figure).

Thanks for all of your help.

 

[pbaldy]

That's the actual field name? It's a reserved word, so try adding brackets around it in both clauses:

[float]

Are you seeing a problem with the inserted date? If you were concatenating a value into the code, you'd have to worry about formatting and delimiters, but since you're just selecting fields, you shouldn't have to.

 

[Adam McReynolds]

That's the actual field name? It's a reserved word, so try adding brackets around it in both clauses:

[float]

Are you seeing a problem with the inserted date? If you were concatenating a value into the code, you'd have to worry about formatting and delimiters, but since you're just selecting fields, you shouldn't have to.

Ha, that was it. OMG 4 hours later. My boss and his naming....well I guess he's paying for it$$$. I will attempt to test it out and post if I find any errors but here is what got now that is working:

'''''''''''''''''''''''''''''''''''''
'SQL INSERT INTO tbl_batt_id
''''''''''''''''''''''''''''''''''''''''
Dim SQLText As String

If Not IsNull(Me.txt_last_rec_id) Then
SQLText = "INSERT INTO tbl_batt_id " & _
"(record_num, bat_id, [float], under_load, siemans, manufacture, date_code, barcode, status) " & _
"SELECT " & Me.record_num & ", bat_id , [float], under_load, siemans, manufacture, Date_Code, barcode, Status " & _
"FROM  tbl_batt_id  " & _
"WHERE record_num = " & Me.txt_last_rec_id & ""

Debug.Print SQLText
DoCmd.RunSQL SQLText
End If

Thank you so much for all of your help. You are shooting 100%!!!!! Cheers!

 

[pbaldy]

Happy to help!