Copy fields to next record

[Wayne Cramer]

I have a database where the majority of fields are the same record to record. For instance the only thing that changes is the line item number for a purchase order. I have a button that copies most of the fields to the next record (code is below). The problem is that if any field does not have content I get a debug error. Can code be added to accept blank (null) entries for some of the fields? Any help is greatly appreciated.

Private Sub Command230_Click()
Dim sVendor_Name As String
Dim sIssue_Generator As String
Dim sIssue_Type As String
Dim sIssue_Description_and_Resolution As String
Dim sOther_Explanation As String
Dim sRequestor As String
Dim sResponsible_Party As String
Dim sCell As String
Dim sQuote As String
Dim dtmDateAndTime As Date

sVendor_Name = Vendor_Name
sIssue_Generator = Issue_Generator
sIssue_Type = Issue_Type
sIssue_Description_and_Resolution = Issue_Description_and_Resolution
sOther_Explanation = Other_Explanation
sRequestor = Requestor
sResponsible_Party = Responsible_Party
sCell = Cell
sQuote = Quote
dtmDateAndTime = DateAndTime

DoCmd.GoToRecord , , acNewRec

Vendor_Name = sVendor_Name
Issue_Generator = sIssue_Generator
Issue_Type = sIssue_Type
Issue_Description_and_Resolution = sIssue_Description_and_Resolution
Other_Explanation = sOther_Explanation
Requestor = sRequestor
Responsible_Party = sResponsible_Party
Cell = sCell
Quote = sQuote
DateAndTime = dtmDateAndTime

sVendor_Name = ""
sIssue_Generator = ""
sIssue_Type = ""
sIssue_Description_and_Resolution = ""
sOther_Explanation = ""
sRequestor = ""
sResponsible_Party_Party = ""
sCell = ""
sQuote = ""

End Sub

 

[boblarson]

Use the NZ function to handle nulls like this:

sIssue_Generator = Nz(Issue_Generator,"")

 

[WayneRyan]

Wayne,

There are a lot of places where you can address this.

Also, your table design has attributes for each field such as "Required" and
"allow zero-length string".

You can check the field's entered on entry into the Command Button code and
exit, forcing them to provide valid entries.

If the table's fields don't allow for a "zero-length string", then you can
use the NZ function to provide a suitable value for the fields like:

Me.Issue_Generator = Nz(Me.Issue_Generator, "Not entered")

Address it at the table level,
Address it on entrance to the Command Button Code, Or
Address it with the Nz function.

Wayne

 

[Wayne Cramer]

Thank you both for the quick reply. Bob, you concise solution was exactly what I needed - once again. This will save a ton of data entry so I know the end users thank you also.