Duplicate Record when Required Fields are Present

  • Thread starter Thread starter Stacey Crowhurst
  • Start date Start date
S

Stacey Crowhurst

Hi. I would like to use the duplicate record funciton for several of my
forms. The problem I am facing is how to adjust when I have required fields
in my forms. For example I get this error...

Run-Time Error 3314
The field 'tblPotentialChangeOrders.pcoPCOID' cannot contain a Null value
becuase the Required property for this field is set to True. Enter a value
in this field.

How do I get around this issue without changing my table properties? Here
is the code on my Copy Record command button (from the wizard):

Private Sub cmdCopyRecord_Click()
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
Exit_cmdCopyRecord_Click:
Exit Sub
End Sub

Any help is greatly appreciated. Thank you!
Stacey
 
Hi. I would like to use the duplicate record funciton for several of my
forms. The problem I am facing is how to adjust when I have required fields
in my forms. For example I get this error...

Run-Time Error 3314
The field 'tblPotentialChangeOrders.pcoPCOID' cannot contain a Null value
becuase the Required property for this field is set to True. Enter a value
in this field.

How do I get around this issue without changing my table properties? Here
is the code on my Copy Record command button (from the wizard):
Click to expand...

That depends. Do you want the field to be required? If not you must change the
table properties. If so, you must provide a value to be stored in that field.
What is the dataype of proPCOID?
 
Hi John. I do want the field to be required. pcoPCOID is a text field.
Thanks.
Click to expand...

In that case your code must provide some unique value to be put into that
field. I have no idea what that value might be, since I don't know anything
about your data!
 
Thanks John. I would probably want the value to be "PCO00". I have no
coding experience. What would the code be that I need to add? I appreciate
all your help!
 
Thanks John. I would probably want the value to be "PCO00". I have no
coding experience. What would the code be that I need to add? I appreciate
all your help!
Click to expand...

It CAN'T be PCO00 for every record.

Unique means *unique* - the value must occur once, and only once, in the
entire table. If you add a second record using your form, it would cause a key
error (since you'ld be putting the same value into two records).


Let's step back a bit. What is the purpose of this table? What (other than
PCO00) within the record can uniquely identify that record and distinguish it
from every other record? Where is the data that you're adding coming from?
 
Hi John:

I think I am confusing you a bit. The table "tblPotentialChangeOrders" from
which the form is based has twelve fields. Of these twelve only is a no
duplicates field. It is an autonumber field. Of the eleven remaining fields
seven are required but all allow for duplicate values.

The field we are discussing pcoPCOID is required but allows for duplicates.
So I belive your earlier suggestion about providing a value for that field
will serve my purpose to circumvent the error message. But then again,
you're the expert!

The purpose of the table is to track data on any potential change order that
a project manager forsees as an upcoming expense or deduct on their project.
The unique identifier is the autonumber field and the pcoPCOID is my
meaningful identifier. The data that I am adding comes from the project
manager. An example would be if the project manager found out that there was
asbestos that needed to be removed and it wasn't part of the original
contract. The potential change order table will assign and ID, dollar
amount, date and description. Once we contract with a vendor to have the
asbestos removed the potential change order status will be changed from
'projected' to 'executed' and a new contract will be entered.

Whew, hope that helps!
Thanks,
Stacey
 
Hi John:

I think I am confusing you a bit. The table "tblPotentialChangeOrders" from
which the form is based has twelve fields. Of these twelve only is a no
duplicates field. It is an autonumber field. Of the eleven remaining fields
seven are required but all allow for duplicate values.

The field we are discussing pcoPCOID is required but allows for duplicates.
So I belive your earlier suggestion about providing a value for that field
will serve my purpose to circumvent the error message. But then again,
you're the expert!

The purpose of the table is to track data on any potential change order that
a project manager forsees as an upcoming expense or deduct on their project.
The unique identifier is the autonumber field and the pcoPCOID is my
meaningful identifier. The data that I am adding comes from the project
manager. An example would be if the project manager found out that there was
asbestos that needed to be removed and it wasn't part of the original
contract. The potential change order table will assign and ID, dollar
amount, date and description. Once we contract with a vendor to have the
asbestos removed the potential change order status will be changed from
'projected' to 'executed' and a new contract will be entered.
Click to expand...

So it's ok if you have (say) 20 records all with exactly the same pcoPCOID?

Ok, just include it as a literal. Rather than the THOROUGHLY obsolete
DoMenuItem code I'd just run an Append query:

Dim strSQL As String
On Error GoTo Proc_Error
strSQL = "INSERT INTO tblPotentialChangeOrders " _
& "(pcoPCOID, thisfield, thatfield, otherfield) " _
& "VALUES('PCO00', " & Me!thiscontrol & ", " & Me!thatcontrol & ");"
Currentdb.Execute strSQL, dbFailOnError
Proc_Exit:
Exit Sub
Proc_Error:
Msgbox "Error " & Err.Number & ":" & Err.Description
Resume Proc_Exit
End Sub

Use the names of the fields in the table in the first parentheses, and the
names of the form control containing the values you want to copy in the
second.
 
So it's ok if you have (say) 20 records all with exactly the same pcoPCOID?
Click to expand...

To answer that question, yes it is ok for mutiples to have the same
pcoPCOID. When we only have an internal estimate and don't know who the
vendor will be to do the work, the pcoPCOID is Unknown. So there are quite a
few 'Unknown's hanging out there.

Anyway to ask a really simple (?) question... where do I put the code you
supplied? Do I put it with the copy record command button code?

Here is a list of all my required fields, their types and desired generic
value.
Required Field Data Type Desired Generic Value
pcoPCOID Text PCOxxx
pcoCCPID Text XXX00-00
pcoBudgetCodeID Text 000-000
pcoLineAmount Currency $0
pcoTypeID Text U
pcoUseUncommittedBudget Y/N Y
pcoPCODate Date/Time =Date()

With that information I tried to modify the code below. Let me know if it
seems right.

Dim strSQL As String
On Error GoTo Proc_Error
strSQL = "INSERT INTO tblPotentialChangeOrders " _
& "(pcoPCOID, pcoCCPID, pcoBudgetCodeID, pcoLineAmount, pcoTypeID,
pcoUseUncommittedBudget, pcoPCODate) " _
& "VALUES('PCOxxx', " & Me!pcoPCOID & ", ‘XXX00-00’, " & Me!pcoCCPID & "
, ‘000-000’, " & Me!pcoBudgetCodeID & ", “0â€, " & Me!pcoLineAmount & " ,
‘U’, " & Me!pcoTypeID & ", “-1â€, " & Me!pcoUseUncommittedBudget & ",
‘=Date()’, " & Me!pcoPCODate & ");"
Currentdb.Execute strSQL, dbFailOnError
Proc_Exit:
Exit Sub
Proc_Error:
Msgbox "Error " & Err.Number & ":" & Err.Description
Resume Proc_Exit
End Sub


THANKS :)
 
Back
Top