• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 794
  • Last Modified:

How / can I validate data pasted into combo box field of form in datasheet view

How / can I validate data pasted into combo box field of form in datasheet view.  We have a subform that user like to paste data into.  Some of the fields are combo boxes that I limit to list to restrict when entering data on the form.
In some cases users prefer to paste data into the form / datasheet as rows and columns from and xls sheet to ease data entry and I'd like to validate the data against the same list of values while still allowing users to paste the data as rows / columns.
0
conardb
Asked:
conardb
  • 7
  • 4
1 Solution
 
Jeffrey CoachmanCommented:
You can "Validate" anything you want...
The issue is how this system will deal with the validations
(Multiple items pasted, Nulls, ...etc)

So you must tell us "exactly" what you want to happen for every contingency.

You have not told us anything about this field or what is storing...? (Text, Numbers?)
You ave also not told us the properties of the combobox
(How many columns, Bound Column, rowsource, ...any code, ...etc)
0
 
Jeffrey CoachmanCommented:
This is why posting a sample database is always helpful.

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Compile the code. (From the VBA code window, click: Debug-->Compile)
10. Run the compact/Repair utility.
11. Remove any Passwords and/or security.
12. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
13. Post the explicit steps to replicate the issue.
14. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.

JeffCoachman
0
 
conardbAuthor Commented:
The form / datasheet has 6 fields, five are displayed.  The fifth field is a combo box for staffname that stores the last name of the staff member as the control source.  The row source is a select query of 5 fields, 3 of the 5 having column width >0.   The first column is the lastname and the bound column.

I want the user to be able to paste 5 fields into the form / datasheet and have the data for the for the 5th field / combo box validated against the row source.  If the user enters a record and tries to type a value other than the row source / bound column an indication that the value is not in the list is displayed but regardless of how I've configured the combo box values other than the row source are able to pasted into the field without validation.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
Jeffrey CoachmanCommented:
Set the "Limit to list" property of the combobox to Yes.

...However, the kicker here is that this validation will only work if you try to "Enter or Select" a wrong value.
"Pasted" values bypass this functionality

You can try working around this limitation bu using code like this on the combobox AfterUpdate event:

Dim strVal As String
strVal = Me.LastName
If DCount("LastName", "tblemployees", "lastName=" & "'" & strVal & "'") = 0 Then
    MsgBox strVal & " is not in the list, you must select a value from the list.", vbInformation
    Me.LastName = ""
    Me.LastName.SetFocus
End If

But ultimately remember that Copy and Paste is not the most reliable method for entering data into an Access database.

JeffCoachman
0
 
Jeffrey CoachmanCommented:
Complete code for reference:

'Code on the Last name combobox
Private Sub cboLastName_AfterUpdate()
Dim strVal As String
strVal = Me.cboLastName
If DCount("LastName", "tblemployees", "lastName=" & "'" & strVal & "'") = 0 Then
    MsgBox strVal & " is not in the list, you must select a value from the list.", vbInformation
    Me.cboLastName = ""
    Me.cboLastName.SetFocus
End If
0
 
conardbAuthor Commented:
Ok, thanks.  Will try... out yesterday (having a baby :-) ...)
0
 
conardbAuthor Commented:
Thanks, Looks like this will work well.  I started down this road of using after update but didn't get there / Didn't use Dcount.  

Basically, we have  form/datasheet that is used to enter task time tracking where the week#, hours, taskname, and staff member are entered.   The list of rows/columns gets pretty long and many have got into the practice of cutting and pasting or entering various values into xls and then pasting into the form as the preferred practice to ease data entry vs. tab, select, etc... in the form.    Exploring methods & redevelopment to continue to allow users to somehow enter percieved large lists of task assignments and hours and reducing data entry is something we want to do. Presently, I just want to be sure that the staff assigned are consistent so reporting is comparable.  Any thoughts / suggestions on how to enable entry of large lists and reduce entry and copy and paste procedures?   Thanks again...
0
 
Jeffrey CoachmanCommented:
You did not post a sample of these "large lists", so I can't post anything definitive...

But in a very broad sense, large amounts of data may perhaps need to be broken down into smaller parts ("normalized")

The issue with any validation using an aggregate Function (Dcount, DMax, Dlookup, ...ect), ...is that as the list gets longer and longer, it takes the Aggregate function, longer and longer to search through all the records to check for a match.
For example, imagine "validating" if a SS# exists...
...Your Database would have to search over 300 million records!

This is compounded even more if the size of the data to be checked is large.
For example:
It is faster to check for: "Joe"
...Than it is to check for: "Joe is a very fast runner and he will probably win the race"

Now, this all being said, ..."Large" (a relative term here) Lists, are not typically "validated".
There is simply too much room for misspellings, missing data, ...ect)
I know that every situation is different, and I may not know the full scope of your system here...
I am just bringing this up as s general observation.

;-)

JeffCoachman
0
 
Jeffrey CoachmanCommented:
Other issues with pasting data manually from other sources:
Non-printable characters: Carriage Return, Line Feed, Trailing/leading spaces
...sometimes cause problems.
0
 
conardbAuthor Commented:
Ok, thanks alot
0
 
Jeffrey CoachmanCommented:
;-)
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now