Pasting Overrides Combobox Lookup Field List Validation?

[jjsande1492]

Hi, everyone.



Edit: Subject should say Value List, not Field List.

I have a table with a combo box that is using a value list lookup. Values permitted are: 'Y' and 'N' (note: I cannot use the Yes/No field Access has provided). I have set 'Limit to List' to yes and 'Allow Value List Edits' to No, but if I copy and paste some values from Excel or even another field in the same table, it is allowing me to put these values in. I have duplicated these validation rules in a form and it won't catch it there either.

Here is my lookup setup:

Here is my field after pasting in two values.

How can I prevent this behavior?

Thanks,
jj

[Minty]

Don't paste directly into the table, it's dangerous and the table should never be exposed directly to end users.
Use an import routine and sanitize the data before committing it to the table.

Also Lookup fields in tables are very confusing to use, see here for some explanation http://access.mvps.org/access/lookupfields.htm

If you must go this route use a validation rule on the table field, not the lookup.

[jjsande1492]

Thanks, minty. As a rule, I don't paste directly into the table. I only did so as a test. I have a data sheet form that handles all of my validation requirements in a user-facing environment. However, as mentioned, pasting data ignores the limit to list property of the field. Interestingly, this is only an issue on value lists. Other fields that have lookup values from other tables will reject entries that are not in the list even when pasted in.

[Minty]

Did you try setting validation rules on the actual table field, rather than the lookup?

[jjsande1492]

I haven't put validation on the table field yet. I didn't want to because if I update the value list, I also have to remember to update the validation. It shouldn't need to have the extra step of complication. I guess if that's my only option, then ok, but I'm confused and disappointed that a table lookup maintains data integrity while a value list cannot.

[davegri]

When you update the table directly, the value list (which is associated with a form) is bypassed.

[jjsande1492]

Interesting. I didn't know that. However, it is happening in my form as well. Table lookups are fine, value lists get overridden.

[jjsande1492]

I've had another look at my tables and forms and I still don't understand how this is happening. There's one table field from a linked table that is being overwritten. It has hundreds of options that change frequently.

Here is that field in the table:

Here is my combobox in my datasheet form:

'Product Types' is a linked table with one field (there is no ID field - don't ask me why, I didn't build it and I can't do anything about it). Copying / pasting from Excel into my form is still overriding the lookup values. I don't particularly want to also use validation here. Surely, there is some way to fix this?

[davegri]

Here's code to read table Product Types and apply the contents to the validation rule for the other table.
I think the rule is limited to 2048 characters.
Any time table Product Types is changed, run this again to update the validation rule.
This will save you the trouble of manually adjusting the validation rule.
Adjust your table names and the field name as required.
Delete the existing field lookup.

Option Compare Database
Option Explicit

Private Sub cmdSetValidationRule_Click()
    Dim sVal As String
    Dim dq As String
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("Prod Types")      'source for validation fields
    rs.MoveLast
    rs.MoveFirst
    sVal = "="
    dq = Chr$(34)     'double quote
    While Not rs.EOF
        sVal = sVal & dq & rs!ptype & dq & " or "      'field name in Prod Types
        rs.MoveNext
    Wend
    Set rs = Nothing
    sVal = Left(sVal, Len(sVal) - 3)
    'Debug.Print sVal
    Call fcnFieldValidation("tblQuantum", "Quantum_PRO", sVal, "Not valid Type")    'target table and field name in that table
End Sub

Function fcnFieldValidation(strTblName As String, strFldName As String, strValidRule As String, strValidText As String) As Integer
    Dim db As DAO.Database
    Dim tdf As TableDef
    Dim fld As Field
    Set db = CurrentDb
    Set tdf = db.TableDefs(strTblName)
    Set fld = tdf.Fields(strFldName)
    fld.ValidationRule = strValidRule
    fld.ValidationText = strValidText
End Function

[jjsande1492]

Thanks, Davegri. I appreciate it.

Do you have any insight as to why some fields properly restrict to the lookup list and others do not?

[davegri]

Do you have any insight as to why some fields properly restrict to the lookup list and others do not?

It appears that the lookup list is only honored if the field is being created/changed via the keyboard.
However, the validation rules are always honored.
Did you try the code in post #9 that will build the validation rules for you?

[jjsande1492]

Thanks again, davegri. I got your code to work. It's not ideal because the product type table is a linked table that can and will be updated without me knowing, but it's better than nothing.

I'm still frustrated by Access allowing you to paste values that are not in the lookup list. I can't find a single thing about this on the net.

[davegri]

jj, you could run the code when the database is opened by including it in the FORM_OPEN event of the first form opened, or use the autoexec macro.
Maybe also store the recordcount of the linked table in a tempvar at BOJ. Then recheck the recordcount at various times to see if the recordcount has changed.

[jjsande1492]

Good suggestion. Thank you.