Duplicate Record - Primary Key from Input Box
- Thread starter melanie
- Start date
melanie
A Walking Contradiction
- Local time
- Today, 06:29
- Joined
- Jun 6, 2003
- Messages
- 101
R.G. that may be a do-able situation...how involved on a scale of 0-10? Joking!!! It still means they have to tab through the fields though.
I've played with creating a little form and calling that form up before the code for the duplicate where the user types in the id number, but I don't know how to tell visual basic to stop and take the id from that field and implement it in the new record. Am I being difficult to want to do it that way, am I hoping for the impossible.... I had thought that it was a simple thing to-do, now maybe I'm reconsidering even trying to give them the option of the copy record button.
How does the recordset option work, the one you suggested earlier?
Don't know either why I'm having to repeatedly log in, trying to get a post through is difficult.
I've played with creating a little form and calling that form up before the code for the duplicate where the user types in the id number, but I don't know how to tell visual basic to stop and take the id from that field and implement it in the new record. Am I being difficult to want to do it that way, am I hoping for the impossible.... I had thought that it was a simple thing to-do, now maybe I'm reconsidering even trying to give them the option of the copy record button.
How does the recordset option work, the one you suggested earlier?
Don't know either why I'm having to repeatedly log in, trying to get a post through is difficult.
melanie
A Walking Contradiction
- Local time
- Today, 06:29
- Joined
- Jun 6, 2003
- Messages
- 101
wow, that message went straight through!! Ok, so what I was thinking that if they want to do a batch of banks then if we can get the records to copy over, every field, with the sequential number then all the user needs to do is alter the property details, refno, erf no and address of the property where the valuation needs to take place and the valuator assigned to the job?
Can that be done maybe a little easier without turning on the field copy and they still need to tab over each section?
Can that be done maybe a little easier without turning on the field copy and they still need to tab over each section?
RuralGuy
AWF VIP
- Local time
- Yesterday, 21:29
- Joined
- Jul 2, 2005
- Messages
- 13,825
How good are your skills on a scale of 0-10?
Why? How are you getting to the next record? The defaults will store if you save the record, which Access will do if you move to a new record and the current record is Dirty. It should be Dirty because you have at least given it a new ID number!
We'll deal with that if we can't come up with a better solution. I'm starting to really like my "Record Duplicating Enabled" idea!
Actually quite trivial.
Could it be *your* ISP? Are you having problems anywhere else on the internet?
Don't give up. I think it is an excellent idea. We're simply working out the details!
What do you want to work on first!
melanie
A Walking Contradiction
- Local time
- Today, 06:29
- Joined
- Jun 6, 2003
- Messages
- 101
Lets copy them fields
R.G. Great lets give it a shot.
I don't give up too easily - I may have grown fur on my legs (and my teeth) and the dog is even getting a bit scared of the wild look, but I'm here and hunched over the keyboard and keen to make it work. I could pass for Einsteins not so clever daughter, got the looks (after weeks dribbling into the keyboard), missing the brain.
Probably on the scale about mid way, with a bit of hand holding.
I think what I read up was a bit different from what you're suggesting hence the reluctance. I was thinking you were on the line that each field carries a default (previously typed entry) which becomes available after a few keystrokes into data entry of the field, but if you're saying it will flood the whole form, well it sounds as good as the copy record button option to me.
Resolutions for 2012 - study vb seriously, make appointment for leg wax, feed the fish regularly!!
Are you always this helpful - or is it you being way too lonely out there in the middle of the Rocky Mountains?
R.G. Great lets give it a shot.
I don't give up too easily - I may have grown fur on my legs (and my teeth) and the dog is even getting a bit scared of the wild look, but I'm here and hunched over the keyboard and keen to make it work. I could pass for Einsteins not so clever daughter, got the looks (after weeks dribbling into the keyboard), missing the brain.
Probably on the scale about mid way, with a bit of hand holding.
I think what I read up was a bit different from what you're suggesting hence the reluctance. I was thinking you were on the line that each field carries a default (previously typed entry) which becomes available after a few keystrokes into data entry of the field, but if you're saying it will flood the whole form, well it sounds as good as the copy record button option to me.
Resolutions for 2012 - study vb seriously, make appointment for leg wax, feed the fish regularly!!
Are you always this helpful - or is it you being way too lonely out there in the middle of the Rocky Mountains?
melanie
A Walking Contradiction
- Local time
- Today, 06:29
- Joined
- Jun 6, 2003
- Messages
- 101
RG I scratched up this hectic piece of code and am still harping on about changing the part about the autoid, can you just take a peek at this and tell me what you think....nag nag nag
'To copy the fields of the form's recordsource
Public Sub sDuplicateCurrentRecord(strFormName As String)
'Duplicate the current record on a form
Dim frmAny As Form
Dim rst As Recordset
Dim aVarContents() As Variant
Dim iCount As Integer
Dim iFldCount As Integer
On Error GoTo Err_cmdDuplicateCurrent
Set frmAny = Forms(strFormName)
If MsgBox("Duplicate the current record?", _
vbYesNo, "Duplicate") = vbYes Then
'Save current record if needed
If frmAny.Dirty Then frmAny.Dirty = False
'Duplicate the current record on the form into an array
iFldCount = frmAny.RecordsetClone.Fields.Count - 1
ReDim aVarContents(iFldCount) 'resize the array
Set rst = frmAny.RecordsetClone
rst.Bookmark = frmAny.Bookmark
'Get contents of Current record
For iCount = 0 To iFldCount
aVarContents(iCount) = rst.Fields(iCount)
Next iCount
'This code relies on the Primary key being an autonumber field
With rst
.AddNew
For iCount = 0 To iFldCount
If .Fields(iCount).Attributes _
And dbAutoIncrField Then
'Autonumber field so Skip and let it fill
'automatically
Else 'This is not primary key so populate field
If IsNull(aVarContents(iCount)) = False Then
'check for nulls, else do it
.Fields(iCount) = aVarContents(iCount)
End If
End If
Next iCount
.Update
'Optional line to move form to new record
.Bookmark = .LastModified
End With
End If 'Do you want to duplicate
Exit_cmdDuplicateCurrent:
Exit Sub
Err_cmdDuplicateCurrent:
MsgBox Err.Number & ": " & Err.Description, , "sDuplicateCurrentRecord"
Resume Exit_cmdDuplicateCurrent
End Sub
'To copy the fields of the form's recordsource
Public Sub sDuplicateCurrentRecord(strFormName As String)
'Duplicate the current record on a form
Dim frmAny As Form
Dim rst As Recordset
Dim aVarContents() As Variant
Dim iCount As Integer
Dim iFldCount As Integer
On Error GoTo Err_cmdDuplicateCurrent
Set frmAny = Forms(strFormName)
If MsgBox("Duplicate the current record?", _
vbYesNo, "Duplicate") = vbYes Then
'Save current record if needed
If frmAny.Dirty Then frmAny.Dirty = False
'Duplicate the current record on the form into an array
iFldCount = frmAny.RecordsetClone.Fields.Count - 1
ReDim aVarContents(iFldCount) 'resize the array
Set rst = frmAny.RecordsetClone
rst.Bookmark = frmAny.Bookmark
'Get contents of Current record
For iCount = 0 To iFldCount
aVarContents(iCount) = rst.Fields(iCount)
Next iCount
'This code relies on the Primary key being an autonumber field
With rst
.AddNew
For iCount = 0 To iFldCount
If .Fields(iCount).Attributes _
And dbAutoIncrField Then
'Autonumber field so Skip and let it fill
'automatically
Else 'This is not primary key so populate field
If IsNull(aVarContents(iCount)) = False Then
'check for nulls, else do it
.Fields(iCount) = aVarContents(iCount)
End If
End If
Next iCount
.Update
'Optional line to move form to new record
.Bookmark = .LastModified
End With
End If 'Do you want to duplicate
Exit_cmdDuplicateCurrent:
Exit Sub
Err_cmdDuplicateCurrent:
MsgBox Err.Number & ": " & Err.Description, , "sDuplicateCurrentRecord"
Resume Exit_cmdDuplicateCurrent
End Sub
melanie
A Walking Contradiction
- Local time
- Today, 06:29
- Joined
- Jun 6, 2003
- Messages
- 101
"Don't give up. I think it is an excellent idea. We're simply working out the details!
What do you want to work on first! "
Erm, finding a clean cup so I can have some coffee, feeding the fish to the cat, the cat is hungry and the fish are dying anyway!!
No, seriously, sound a bit hillbillish there!! Where do you think I should start? Have heaps and heaps of field names, four tables, all one to one relationship, one valuationjob, one property, one set of commissions, one set of valuationfees.
Where do we go from there?
What do you want to work on first! "
Erm, finding a clean cup so I can have some coffee, feeding the fish to the cat, the cat is hungry and the fish are dying anyway!!
No, seriously, sound a bit hillbillish there!! Where do you think I should start? Have heaps and heaps of field names, four tables, all one to one relationship, one valuationjob, one property, one set of commissions, one set of valuationfees.
Where do we go from there?
melanie
A Walking Contradiction
- Local time
- Today, 06:29
- Joined
- Jun 6, 2003
- Messages
- 101
Hi there, I'm pleased to announce that duplicating a record which has a primary key which is NOT autonumber CAN be done using the inputbox method.
I still get the warning about it not wanting to paste etc, (I'm sure one of the forum gurus can tell me how to make it stop), but it does in fact copy the record with the new primary key typed into the inputbox.
This is the code behind the copy record button:
Private Sub Command151_Click()
On Error GoTo Err_Command151_Click
DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
Me.IDNO = (InputBox(IDNO, "Enter IDNO"))
DoCmd.SetWarnings True
Exit_Command151_Click:
Exit Sub
Err_Command151_Click:
MsgBox Err.Description
Resume Exit_Command151_Click
End Sub
I have the following code behind the actual primary key field on my form:
Private Sub IDNO_BeforeUpdate(Cancel As Integer)
DoCmd.SetWarnings False
If DCount("[IDNO]", "ValuationJob", "[IDNO] = '" & Me.IDNO & "'") > 0 Then
Me.IDNO.Undo
MsgBox "That number already exists", vbOKOnly
End If
End Sub
Ipexues: you mention having the same problem and that the combo boxes are interfering with the copy. I had that too, my combo boxes are record finders, but it only seems to happen if there are actual values in the fields, in other words I had done a search on a record before trying to copy. They are not interfering in the record copy if they are cleared.
I still get the warning about it not wanting to paste etc, (I'm sure one of the forum gurus can tell me how to make it stop), but it does in fact copy the record with the new primary key typed into the inputbox.
This is the code behind the copy record button:
Private Sub Command151_Click()
On Error GoTo Err_Command151_Click
DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
Me.IDNO = (InputBox(IDNO, "Enter IDNO"))
DoCmd.SetWarnings True
Exit_Command151_Click:
Exit Sub
Err_Command151_Click:
MsgBox Err.Description
Resume Exit_Command151_Click
End Sub
I have the following code behind the actual primary key field on my form:
Private Sub IDNO_BeforeUpdate(Cancel As Integer)
DoCmd.SetWarnings False
If DCount("[IDNO]", "ValuationJob", "[IDNO] = '" & Me.IDNO & "'") > 0 Then
Me.IDNO.Undo
MsgBox "That number already exists", vbOKOnly
End If
End Sub
Ipexues: you mention having the same problem and that the combo boxes are interfering with the copy. I had that too, my combo boxes are record finders, but it only seems to happen if there are actual values in the fields, in other words I had done a search on a record before trying to copy. They are not interfering in the record copy if they are cleared.
Last edited:
Similar threads
