If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to Append a duplicate record and just change the key?
I am trying to duplicate an entire record with a new key without listing
every field (too many). I tried this, but it didn't work. There must be a way! strSql=Select statement to select record to copy Set rstSaved = mydb.OpenRecordset(strSql, dbOpenSnapshot) With rstSaved Set rstNew = mydb.OpenRecordset("Proposal", dbOpenDynaset) rstNew.AddNew rstNew!Key1 = Key1 rstNew!Key2 = Key2 For Each fld In .Fields If fld.Name Key1 And fld.Name Key2 Then rstNew.Fields(fld.Name) = (fld.Value) End If Next rstNew.Update rstNew.Close .Close End With |
#2
|
|||
|
|||
How to Append a duplicate record and just change the key?
Your code is a little confusing.
You've got fields named Key1 and Key2, and you're assigning them the values contained in variables Key1 and Key2. However, your loop is comparing the field names to Key1 and Key2, which means the variables. I think you want If fld.Name "Key1" And fld.Name "Key2" Then -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Holly" wrote in message ... I am trying to duplicate an entire record with a new key without listing every field (too many). I tried this, but it didn't work. There must be a way! strSql=Select statement to select record to copy Set rstSaved = mydb.OpenRecordset(strSql, dbOpenSnapshot) With rstSaved Set rstNew = mydb.OpenRecordset("Proposal", dbOpenDynaset) rstNew.AddNew rstNew!Key1 = Key1 rstNew!Key2 = Key2 For Each fld In .Fields If fld.Name Key1 And fld.Name Key2 Then rstNew.Fields(fld.Name) = (fld.Value) End If Next rstNew.Update rstNew.Close .Close End With |
#3
|
|||
|
|||
How to Append a duplicate record and just change the key?
Sorry for the confusion, I just replaced the Key names for example purposes.
That part of the code is working, it's the code: rstNew.Fields(fld.Name) = (fld.Value) that I'm having a problem with. The record gets updated with only the keys filled in. All other fields are blank. |
#4
|
|||
|
|||
How to Append a duplicate record and just change the key?
It sounds as though that If statement isn't working for you. What do you
have in reality there? If your code is getting into that loop, try removing the parentheses around fld.Value. In reality, though, you'd be far better off using an INSERT INTO statement. Yes, you'd need to list all of the fields, but you only have to do that once. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Holly" wrote in message ... Sorry for the confusion, I just replaced the Key names for example purposes. That part of the code is working, it's the code: rstNew.Fields(fld.Name) = (fld.Value) that I'm having a problem with. The record gets updated with only the keys filled in. All other fields are blank. |
#5
|
|||
|
|||
How to Append a duplicate record and just change the key?
When I want to duplicate a record, I use "The 3 query method". It's my own
invention. I query the current record, modify whatever fields need to be changed in a 2nd query (such as key fields), then use a 3rd query to append this record to the table. Works! I only use VB when there isn't a SQL solution. "Holly" wrote in message ... I am trying to duplicate an entire record with a new key without listing every field (too many). I tried this, but it didn't work. There must be a way! strSql=Select statement to select record to copy Set rstSaved = mydb.OpenRecordset(strSql, dbOpenSnapshot) With rstSaved Set rstNew = mydb.OpenRecordset("Proposal", dbOpenDynaset) rstNew.AddNew rstNew!Key1 = Key1 rstNew!Key2 = Key2 For Each fld In .Fields If fld.Name Key1 And fld.Name Key2 Then rstNew.Fields(fld.Name) = (fld.Value) End If Next rstNew.Update rstNew.Close .Close End With |
#6
|
|||
|
|||
How to Append a duplicate record and just change the key?
There are too many fields, and more importantly the database is still
evolving, so here's what I did and it worked. I did this before I tried your parenthesis suggestion, so I'm not sure if it would work. For intTemp = 2 To rstSaved.Fields.Count - 1 rstNew(intTemp) = rstSaved(intTemp) Next (the first two fields are the keys) |
#7
|
|||
|
|||
How to Append a duplicate record and just change the key?
Don't be so lazy! g
It's almost always more efficient to use SQL than to use a recordset. INSERT INTO MyTable (Key1, Key2, Field3, Field4, ..., Fieldn) SELECT NewValue1, NewValue2, Field3, Field4, ..., Fieldn FROM MyTable WHERE Key1 = OldValue1 AND Key2 = OldValue2 It's also fairly trivial to generate the list of fields and write them to the Debug window, where you can then copy it into your code. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Holly" wrote in message ... There are too many fields, and more importantly the database is still evolving, so here's what I did and it worked. I did this before I tried your parenthesis suggestion, so I'm not sure if it would work. For intTemp = 2 To rstSaved.Fields.Count - 1 rstNew(intTemp) = rstSaved(intTemp) Next (the first two fields are the keys) |
Thread Tools | |
Display Modes | |
|
|