Duplicate Record - Primary Key from Input Box

[melanie]

Hello all

Please could you assist me with the following:

The primary key is not autonumber, so when the duplicate button is activated on the form, I would like the user to insert the unique key for the new record into an input box.

If the key inserted already exists, to have a message telling them so.

Many thanks
Melanie

 

[Matt Greatorex]

You could add something like the following to the AfterUpdate event of the field the person is entering the number into.

If DCount("[I]name of field in table[/I]","[I]name of table[/I]","[I]name of field in table = [/I]" & [Forms]![[I]name of form[/I]]![[I]name of field[/I]]) > 0 Then
       Msgbox "That number already exists"
End If

 

[RuralGuy]

I would put Matt's code in the BeforeUpdate event of the control and set Cancel = True if DCount() > 0 to hold the user in the control.

 

[melanie]

Input Box - Primary Key

Thanks for a quick response, Matt

I will try the dcount to take care of msg to user warning them that the number already exists. Thanks.

How do I take care of the input box allowing user to input new unique number when duplicating the record? It's not an autonumber.

Many thanks
Melanie

 

[melanie]

Thank you, I will do that RuralGuy....

would the control be the entry field on the form or the input box. At the moment if I click "duplicate record" I receive the message saying that record cannot be duplicated because it requires a unique primary key. I would like to intercept it before this point with an input box which the user can use to insert the key.

Melanie

 

[RuralGuy]

What code do you have behind the "duplicate record" button?

 

[melanie]

Private Sub Command148_Click()
On Error GoTo Err_Command148_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Exit_Command148_Click:
Exit Sub

Err_Command148_Click:
MsgBox Err.Description
Resume Exit_Command148_Click

End Sub

I just used the copy record wizard.

Am stuggling to post messagess am continually asked to log on???

Thank you for your help.
Melanie

 

[melanie]

Dcount - duplicate record

I've tried this code on the beforeupdate event of my primary key field, but I don't receive the message, it accepts the entry and when I try and save the record I receive the usual access warning about duplicate entries, etc.

Where am I going wrong? The table is named "ValuationJob" and so is the form, both fields are named "IDNO".


Private Sub IDNO_BeforeUpdate(Cancel As Integer)
If DCount("IDNO", "ValuationJob", "IDNO = " & [Forms]![ValuationJob]![IDNO]) > 0 Then
MsgBox "That number already exists"
Cancel = True
End If

End Sub

Thanks
Melanie

 

[RuralGuy]

I think your [IDNO] field is a text field so you need quotes around your value.

Private Sub IDNO_BeforeUpdate(Cancel As Integer)
If DCount("[IDNO]", "ValuationJob", "[IDNO] = '" & Me.IDNO & "'") > 0 Then
MsgBox "That number already exists"
Cancel = True
End If

 

[melanie]

Duplicate record - input box

Thank you, that did the trick perfectly!!

Now for the duplicating of the record using the input box to insert the idno into the new record.

Tried, but get the same message from windows, including the message from the new dcount code.

A lot of these records are the same so it will be handy to have the copy feature to save data entry time.

Thank you
Melanie

 

[RuralGuy]

Hi Melanie,
If your Primary key was an AutoNumber, you could use this wizard code because it would create the Key for you. Since you have to provide the Key you will probably need to use an append query or a RecordSet so you can insert your new key. Why do you want to duplicate an entire record, if you don't mind my asking?

 

[melanie]

I'm not sure if this makes any difference, but this form is pulled from four tables with a one to one relationship.

 

[melanie]

RuralGuy, there is a lot of information needed for one record, but a lot of the records are so similar that it is easier just to copy them over and then change the fields which differ.

 

[melanie]

RG, if I can't go the "duplicate record" route, how do I proceed with the recordset method?

 

[RuralGuy]

I'm from the "a programmer should spend hours to save the user seconds" school of programming. Why not add some code to the Form's BeforeUpdate event that simply copies all of the .Value properties to the .Default properties so each record will have the previous value as its default. That would allow you to request a new [IDNO] before you saved the record.

 

[RuralGuy]

I'm sorry about the delay in responding Melanie, but my satellite internet connection decided to quit for about an hour and a half.

 

[melanie]

RG, yes, you have it right, I am trying to save the user time with giving them the option to copy the records over and then make adjustments, and it is taking me hours (long tired hours I should be spending finalising the reports ...sigh!).

They have been using excel spreadsheets till now and have copied and pasted a lot of the records where there have been duplications and don't want to loose that functionality.

I have been trolling the net for the past hours to see if I can find a 'simple' solution, but it's not to be. I have read up on what you are suggesting - setting to copy all the values over. This is going to be too limiting and may confuse them and may actually end up slowing down the process I'm attempting to speed up, I'll explain why.

What happens is they do batches of valuations for certain banks and then a lot of the details for that particular bank remains the same, some fields change according to the bank's branches and the valuator who does the valuation, but then there are often valuations done for many other different banks with completely different requirements and default field settings for which I have spent days building in validations - to again save the user time and to cut out on finger error. The records are all not so similar to warrant using code that carries over to the next record. There are quite a number of these entries in a month. The suggestion you are making would be fine if they were only dealing with the one bank with only that bank's requirements, not with a whole heap of variables.

I was hoping that there would be a quick clever little piece of code that would halt the record copy process, display an input box into which the user is required to enter a unique id number, the number is then transfered to the copied record.

Have you any ideas?

Melanie

 

[melanie]

Oh those PK's

RG, yes, you have it right, I am trying to save the user time with giving them the option to copy the records over and then make adjustments, and it is taking me hours (long tired hours I should be spending finalising the reports ...sigh!).

They have been using excel spreadsheets till now and have copied and pasted a lot of the records where there have been duplications and don't want to loose that functionality.

I have been trolling the net for the past hours to see if I can find a 'simple' solution, but it's not to be. I have read up on what you are suggesting - setting to copy all the values over. This is going to be too limiting and may confuse them and may actually end up slowing down the process I'm attempting to speed up, I'll explain why.

What happens is they do batches of valuations for certain banks and then a lot of the details for that particular bank remains the same, some fields change according to the bank's branches and the valuator who does the valuation, but then there are often valuations done for many other different banks with completely different requirements and default field settings for which I have spent days building in validations - to again save the user time and to cut out on finger error. The records are all not so similar to warrant using code that carries over to the next record. There are quite a number of these entries in a month. The suggestion you are making would be fine if they were only dealing with the one bank with only that bank's requirements, not with a whole heap of variables.

I was hoping that there would be a quick clever little piece of code that would halt the record copy process, display an input box into which the user is required to enter a unique id number, the number is then transfered to the copied record.

Have you any ideas?

Melanie

 

[melanie]

Oh those PK's

Hi, RG, seen your post about your sat being down. How nice of you to be so considerate, I just appreciate that you are taking your time out to help me. Thank you!!! There is no immediate urgency, I still have a few days to tidy the db up and do a few reports.

I'm having a struggle posting and I'm not sure why. It keeps looping around and making me log in again and again and again.

 

[RuralGuy]

I don't know what could be going on with your Forum log on. That has never happened to me.
As for your issue: How about a check box (probably unbound) that turns on/off the "carry" operations and disables it and clears each control when not checked. In either case you can force the user into the ID control on any new record and not let them out until you have a valid, non-duplicated value.