Audit Trail Code Type Mismatch

[sstiebinger]

Let me start by saying thank you for taking the time to read my question.

I have an audit trail module called from my forms.

Sub AuditChanges(IDField As String)    On Error GoTo AuditChanges_Err
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim ctl As Control
    Dim datTimeCheck As Date
    Dim strUserID As String
    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
    rst.Open "SELECT * FROM tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic
    datTimeCheck = Now()
    strUserID = Environ("USERNAME")
    For Each ctl In Screen.ActiveForm.Controls
        If ctl.Tag = "Audit" Then
            If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
                With rst
                    .AddNew
                    ![AuditDateTime] = datTimeCheck
                    ![AuditUser] = strUserID
                    ![AuditForm] = Screen.ActiveForm.Name
                    ![AuditRecord] = Screen.ActiveForm.Controls(IDField).Value
                    ![AuditField] = ctl.ControlSource
                    ![AuditOld] = ctl.OldValue
                    ![AuditNew] = ctl.Value
                    .Update
                End With
            End If
        End If
    Next ctl
AuditChanges_Exit:
    On Error Resume Next
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub
AuditChanges_Err:
    MsgBox Err.Description, vbCritical, "ERROR!"
    Resume AuditChanges_Exit
End Sub

It works great, but when I tried to add a multi-select combo box, I get a type mismatch error.
Basically the form is for customers, and there's a field for status (Customer, Prospect, Suspect) and the customer can be 1 or more of these... the field works fine and as expected, but my audit trail code doesn't like it... any ideas?



Let me know what else you would need.

[June7]

Don't use multi-value fields. I NEVER do. Most experienced developers won't.

I don't think this will work with multi-value.

[rpeare]

preach it june, preach it

multi value fields and calculated fields in tables are a bad, bad, bad, bad thing.

[CJ_London]

I also wouldn't touch a multivalue field, too many restrictions, too slow, cannot be indexed etc.

However if you really want to keep it then take a look at this code which handles multivalue fields. You might be able to adapt it to your requirements.

http://allenbrowne.com/func-concat.html

I suspect your main problem will be identifying which 'record' has changed, you will also probably need another field in your audit recordset.

[sstiebinger]

Thanks for the advice... I'm new to Access so I used the field out of ignorance.

Can you give me an idea of what I should be using instead of a multi-value field?

I chose that type because there may be multiple statuses for each customer, and we may invent more statuses later and adding an additional entry to the status table seemed easier than adding a new field in the customer table every time.

[rpeare]

Look into the topic of normalization.

If you have multiple possible records that you want to reference from a single record (for instance a business with multiple locations) you want a 'main' table with all the fields related to the business (tax ID, business name, etc) and a 'sub' table storing their address information that would also carry the FK (foreign key) to the 'main table'

for example

tblCustomers
Cust_ID  Cust_InternalID  Cust_Name  Cust_TaxID ---> company specific data
1        X4056J3412       Company A  111111
2        X7364K7862       Company B  222222

tblCustomerLocations
CustLoc_ID  Cust_ID  Cust_Add      Cust_City  Cust_State  Cust_Zip  ---> location specific data
1           1        12th Ave NE   Lincoln    NE          44444
2           1        10th Ave NW   Bangor     ME          11111

[sstiebinger]

I understand the concept of normalization, but I don't think this applies the way that you think....

The customer has fields like Name, Address, Primary contact (which is foreign key to contacts table), Parent Company (Foreign Key to company table), and STATUS.

The status is what they are... a current customer, a sales prospect, etc...

The customer may be one or more of those statuses (They may be a current customer, but also a prospect for another product or service.)

My initial thought was to create a table for the possible statuses, then link the customer table to the status table.

This way, if the end users need to create a new type of status, the end user can add an entry to the status table and all's good...
The end users would not have access to the back end, so using additional fields for each possible status would require a developer to modify the table design every time the end-user wants to create a new status.

Again, I'm fairly new to Access, so please tell me the better way to do this, without using multi-select fields... Thoughts?

[CJ_London]

They may be a current customer, but also a prospect for another product or service.

you'll get in a horrible mess if you try to combine these, multivalue or not.

to not use a multivalue field

1. have a separate table with the status in (you may already have this)
2. if status is not mutually exclusive then you need a second link table with two fields, one for customerid and one for status
3. if you want to include product status as well, add a third field to the link table for productID

On your form, have a continuous subform for this link table (instead of the multiselect combo). Depending on your level of expertise you can use a disconnected ADODB recordset to mimic a multiselect combo

One of the benefits of this is you can set rules, for example if the status's are alive/dead/pregnant, you can set a rule in VBA that prevents a customer being selected as both alive and dead, but alive and pregnant is OK

[rpeare]

You're not expanding your thinking enough I don't believe.

In your setup you would have

1. A table for you customer (with a form to edit, table must have a PK, primary key)
2. a table for your statuses (with a form to edit the list, table must have a PK)
3. a junction table connecting the customer to a status that may be relevant to that customer (table must have a PK and a FK, foreign key) to both the customer and the status table.

You can take this one step further and define which product a customer is a 'prospect' for by having an additional table that references the PK of the junction table and stores your a product FK to a product table.

[sstiebinger]

Ahh, I didn't even think of using a join table (Already using this for products tied to customer.)

All part of my learning curve I guess... thanks for the tips on best practices, I'm an Excel pro, but still learning to think 3 dimensionally in Access.
Tips like this are helpful