Combobox autonumber

[hongsaru]

Hi all

I made an audit trail based off the example from

http://www.techrepublic.com/article/a-simple-solution-for-tracking-changes-to-access-data/6166807

My comboboxes in my tblaudit table is storing autonumbers instead of the actual value. Is there way to change it so it stores the actual value instead?

 

[TheAceMan1]

How are ya hongsaru . . .

What does the comboxes have to do with the audit trail? None are mentioned in the reference you provided. Anyway, have a look below:

The Evils of Lookup Fields in Tables

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[hongsaru]

The audit trail tracks all changes on the form and makes a record in tblaudit table. My textbox fields are recorded just fine, but my comboboxes are recording the autonumber from my lookup table instead of the actual value.

I have read your link and if lookup fields are bad then what do you suggest using instead of a combobox?

 

[dhookom]

The actual value in the table is the autonumber. If you want to see the other value then you need to create a query that joins in the table with the other value.

Duane
Hook'D on Access
MS Access MVP

 

[hongsaru]

Sorry for late reply, got sidetracked with work.

I have tried creating a query that joins in my table but the audit table field (NewValue and PrevValue) is a text type of value and my lookup table primary key (UserID) is an autonumber type.

My Audit table
AuditID
UniqID
Field
NewValue (Text)
PrevValue (Text)

I'm getting "Type mismatch in expression." while trying to join my lookup table for my combobox.

Lookup table
UserID (autonumber)
Name

How would you approach this?
Thanks

 

[dhookom]

I expect you can go into the SQL view and change the UserID to text within the JOIN

Duane
Hook'D on Access
MS Access MVP

 

[hongsaru]

I used Format([User].[A_UserID],"@") to change my autonumber type to text format type, but adding it within the JOIN gave me no records.

My SQL View
SELECT tblAudit.Audit_ID, tblAudit.User, tblAudit.Field, tblAudit.Prev_Value, tblAudit.New_Value, User.A_UserID, User.Name
FROM [User] INNER JOIN tblAudit ON (Format([User].[A_UserID],"@") = tblAudit.New_Value) AND (Format([User].[A_UserID],"@") = tblAudit.Prev_Value);

I had tried to change the join to 'RIGHT JOIN' and I got all of the records from my Audit table, but none from my User Table. 'LEFT JOIN' does the exact opposite.

Is my join wrong?

 

[dhookom]

Your query seems to assume New_Value and Prev_Value are the same. That doesn't make sense to me. Try something like changing using Cstr():

SELECT tblAudit.Audit_ID, tblAudit.User, tblAudit.Field,
tblAudit.Prev_Value, tblAudit.New_Value, User.A_UserID, User.Name
FROM [User] INNER JOIN tblAudit ON (cstr([User].[A_UserID])  = tblAudit.New_Value) AND (cstr([User].[A_UserID])  = tblAudit.Prev_Value);

Duane
Hook'D on Access
MS Access MVP