Stop blank values from pasting into field

[dgreen]

I need a way to stop blank values from being pasted into my form/table. Simply, I'm copying and pasting a large group of text from Excel and in some cases there are blank values in the selection. I'd like something that recognized those values that are blank and skipped them and continued to execute the paste for the values.

Example data being selected.

Value 1

Blank/Null Value

Value 2

Here's the current code that I want to modify.

Private Sub GFEBS_Role_BeforeUpdate(Cancel As Integer)
Dim ClickResult As VbMsgBoxResultEx
'check to make sure I have a specific system selected.
If IsNull(Forms!f_E2E!f_E2E_System_Relationship!System_ID) = True Then
ClickResult = Dialog.RichBox("You need to select a system to populate this record", vbOKOnly + vbCritical, "System Linkage", , , 0, False, False, False)
If ClickResult = vbOK Then
Me.Undo
        End If

   'Checks the make sure I'm not pasting in a duplicate record.
    ElseIf DCount("*", "[t_E2E_GFEBS_Role_Relationship]", "[E2E_ID]= " & Me.[E2E_ID] & "and [System_ID]= " & Me.[System_ID] & " And [GFEBS_Role]= " & Chr(34) & Me![GFEBS_Role] & Chr(34)) > 0 Then
ClickResult = Dialog.RichBox("This is a duplicate record. " & "</p>" & _
"Click OK to remove it.", vbOKOnly + vbCritical, "Duplicate Entry", , , 0, False, False, False)
If ClickResult = vbOK Then
Me.Undo
End If
End If
End Sub

 

[theDBguy]

Hi. Without seeing exactly what you're doing, I'm not sure I follow. You're talking about "blank/empty" values and yet the code you posted seems to be about "duplicate" values. If you can't stop blank values from pasting, then maybe you could just clean them up after the paste operation, as a workaround.

 

[dgreen]

My current process is, after the values are pasted in, I sort for the blank/nulls and delete them out. Maybe I need a validation rule that says there must be a value?

 

[theDBguy]

My current process is, after the values are pasted in, I sort for the blank/nulls and delete them out. Maybe I need a validation rule that says there must be a value?

How about setting the field as required? Just a thought...

 

[dgreen]

The validation rule (is not null) didn't work. While it pasted in the 1st good result and recognized the 2nd value wasn't good, it stopped the paste after that point and the 3rd value wasn't pasted in.

The field is optional so making it a required field I don't think would work.

 

[theDBguy]

The validation rule (is not null) didn't work. While it pasted in the 1st good result and recognized the 2nd value wasn't good, it stopped the paste after that point and the 3rd value wasn't pasted in.

The field is optional so making it a required field I don't think would work.

Hi. Sounds like you're stuck with doing some cleanup work either before or after the data "import" process. Not sure what else to recommend to help you out. Sorry.

 

[dgreen]

Would there be any way to modify the duplicates formula to look for a blank value and at least not allow a 2nd or more blank value to be pasted in?

 

[theDBguy]

Would there be any way to modify the duplicates formula to look for a blank value and at least not allow a 2nd or more blank value to be pasted in?

Hi. You could certainly try. To check for an empty value, you could use something like:

If Me.FieldName & "" = "" Then

 

[zeroaccess]

I would clean up your data in Excel first – it has tools specifically designed for that.

 

[Gasman]

I need a way to stop blank values from being pasted into my form/table. Simply, I'm copying and pasting a large group of text from Excel and in some cases there are blank values in the selection. I'd like something that recognized those values that are blank and skipped them and continued to execute the paste for the values.

Example data being selected.

Value 1

Blank/Null Value

Value 2

Here's the current code that I want to modify.

Private Sub GFEBS_Role_BeforeUpdate(Cancel As Integer)
Dim ClickResult As VbMsgBoxResultEx
'check to make sure I have a specific system selected.
If IsNull(Forms!f_E2E!f_E2E_System_Relationship!System_ID) = True Then
ClickResult = Dialog.RichBox("You need to select a system to populate this record", vbOKOnly + vbCritical, "System Linkage", , , 0, False, False, False)
If ClickResult = vbOK Then
Me.Undo
        End If

   'Checks the make sure I'm not pasting in a duplicate record.
    ElseIf DCount("*", "[t_E2E_GFEBS_Role_Relationship]", "[E2E_ID]= " & Me.[E2E_ID] & "and [System_ID]= " & Me.[System_ID] & " And [GFEBS_Role]= " & Chr(34) & Me![GFEBS_Role] & Chr(34)) > 0 Then
ClickResult = Dialog.RichBox("This is a duplicate record. " & "</p>" & _
"Click OK to remove it.", vbOKOnly + vbCritical, "Duplicate Entry", , , 0, False, False, False)
If ClickResult = vbOK Then
Me.Undo
End If
End If
End Sub

Filter out the blank values in Excel before copying and pasting?

 

[dgreen]

In this case, the values are coming from merged cells. I'm deconstructing someone's Excel 'database' into multiple Access sub tables and spending the time to clean up the Excel isn't what I'd like to spend the time doing. I'm about 2/3rd's of the way through.

Filter out the blank values in Excel before copying and pasting?