Problem with AuditTrail

[daikaio94]

Good morning,

I did some changes and testing and found out something interesting. I added the required fields to the query so I can create a new record now.

Unfortunately the code does still throw the same error when I try to update the two combo boxes on existing entries. What surprised me was, that the audit code worked for the new entry, the OldValue was simply written to tblAuditTrail as NULL.


After that I did some more testing and this is were I simply do not understand things anymore . I created a second, identical form and did some changes on the code. I deleted the loop that goes through all the controls and set the controll to active control.

Set ctl = Screen.ActiveControl

After that I called the Sub using the Before_Update event of the controls I want to audit.

This is were things get strange: The two combo boxes now work and the correct values are written to tblAuditTrail. Now if I try to update the text boxes below next it throws the same error as the combo boxes before. And to make the mess perfect: If I edit the textboxes first and the combo boxes second everything works without problems.....

I attached the updated version of the example database with both versions so you can see the changes and test it out yourself.

BTW: English is not my native language, I hope you can understand my explanation of the Problem.

 

[Uncle Gizmo]

I tested your first example quite thoroughly and my conclusion was that there were problems with the query, and possibly the queries in the combo boxes. My suspicion is you still have those problems. I doubt very much that there is anything wrong with the Audit trail routine.

Sent from my SM-G925F using Tapatalk

 

[Uncle Gizmo]

The first test you can do is easy. Create a new form based on your query, without any combo boxes or anything, just basic text boxes. Now, can you add, update, modify the records without any problems?

Sent from my SM-G925F using Tapatalk

 

[daikaio94]

Yes, I can update and modify the records and create new ones without any Problems. The only thing I have to pay attention to is to use existing id's when the control is bound to an id.

This is regardless if I use combo- or textboxes, I tested both again just to be sure. And of course it works only without the AuditTrail routine.

 

[Uncle Gizmo]

Like I said, it's NOT the audit code at fault. You are making the wrong assumption, the audit code was, and is, working as designed.

You have some other problem.

I suspect your queries.

Sent from my SM-G925F using Tapatalk

 

[Uncle Gizmo]

I note you have lots of relationships setup, these can be a source of problems. I usually leave them out of development until I know what results I want.

I suggest you make a copy of your dB, delete the relationships, see if that helps.

Sent from my SM-G925F using Tapatalk

 

[daikaio94]

Hello,

sorry for not posting any updates, I was busy last week and could not work on the project.

I tried to delet the relationships but without any success, still the same errors. I understand that the error comes propably from the query but I dont understand WHY. As fas as I understand the code and the OldValue property it should work.....

I found a way the code works with the query. If I refresh the form after calling the routine (in the afterupdate event of each controll) everthing works as intended. BUT this creates Problems with existing code I'm using, for example my cancel button would not work...

Is there maybe any other way to display the n:m relationship then a query as the recordsource? To the user it should still look like it's one form like in the example though...

 

[daikaio94]

does no one else have any ideas?

 

[jdraw]

Can you be explicit --details -- of what exactly is not working as expected?

 

[jdraw]

I looked quickly at your database. I commented some code as per attached jpg.
You'll see it fails on line 121 ---the Ctl.oldValue????

AcControlType enumerations

I'd like to hear the details of what you are trying to do with this set up.

I hope the above is helpful.

Good luck.

 

[daikaio94]

Hello,

Backstory: I'm working on an invetory database to document all the pc's and their users. I want to implement the AuditTrail to my Edit_Form so i can later view a log on what was changed to the selected entry.


The code itself should not be the problem, as @Uncle Gizmo pointed out it has something to do with the query behind the form.

What confuses me is that if you remove the tag "Audit" from the textboxes so only the top two comboboxes are monitored the code works just fine. In return, if i only tag the textboxes without the comboboxes the code also works fine. Only if I tag both, the comboboxes and the textboxes I get the same error as you pointed out.

 

[jdraw]

Uncle Gizmo is probably correct. It may even be related to your data structure, I'm not sure.
Here is a free data model for the management of IT assets generally.

You can test your model using pencil and paper and some realistic test data.

According to my test of your Form the error 3251 occurs when you attempt to use the OldValue of the Hostname control.

Good luck.

 

[daikaio94]

That is correct, but if you remove tag Audit (see screenshot attached) from every control but the Hostname the OldValue works.


I'll test the suggested data model tomorow and let you know any results.

 

[jdraw]

As Gizmo said, and i agree, there is something in your design that is causing the issue.
Could be related to bound fields and adding new records??
I don't think the error is with martin green's code.
Can you tell us in simple terms, what exactly you are trying to do?
There may be some options for "how" to achieve it, that preclude the 3251 error.

Since the error seems to be with HOW you implemented your requirements, let's step back and see if there are options.

 

[daikaio94]

I want to search for pc's either by using the Servicetag, Username or the Usage (Verwendung).

Example:
The user "John Doe" has 3 laptops and 1 workstation
The user "Max Mustermann" has 1 workstation
10 laptops are used as "Pool" laptops (they do not have a user assigned, only a usage)
2 laptops are used as "Office" laptops (also, no user assigned only usage)

I have my main project split into two forms: the main form with the search function and the sub form looking like the one I uploaded. In the main form I can select a user (e.g. John Doe) and the sub form loads all pc's that are assigned to John Doe using the query in my example database and filter it.

I hope you can understand my explanation of what I'm trying to do. One other thing though: The database itself is finished and works very well, I'd prefer to Keep changes to the data model as minimal as possible :O

 

[jdraw]

Just opened table tblUser_rechner

ID	User_ID	Rechner_ID	Verwendung_ID
1		6	2
2		7	2
3	4	1	1
4	3	2	1
5	1	3	1
6	4	4	1
7	2	5	1
11	3	11	1
13		14	1
14		15	1
15	3	16	1
16	3	17	2

and see a lot of "empty" User_ID which is confusing.

Where are records 8, 9 10?

Main and subform??

Perhaps you should do a compact and repair, then attach a copy of your database in zip format.

 

[daikaio94]

Just opened table tblUser_rechner and see a lot of "empty" User_ID which is confusing.

That can happen when there is a pc without a user assigned. In reality the pc would then be stored in our office untill we decide what to do. If there is no user there has to be something entered for "Verwendung" (I handled this with vba in my main Project, in the example i uploaded it's not relevant)

Where are records 8, 9 10?

They contained personal information so i deleted them before uploading the example.

Main and subform??

Again, this was related to my main project. The form in the example database I uploaded is a subform in my main project that is filtered if loaded. Should not be relevant to the current problem though.


I attached the current version of the example database.

 

[jdraw]

Based on you latest post, I see these facts. I added the link to Model which was an orphan.

There are many Computers (rechner)
There are Many Users
Each Computer has a designated Model
Each Computer has an defined Usage
A Usage(verwendung) may be 1)User/Office OR 2)Pool


Do ALL Users have an assigned Computer?

My feeling is that verwendung should be Joined to Rechner , not UserRechner, but I'm still trying to understand what you are trying to do.

Please review and update the facts.

 

[Uncle Gizmo]

I was thinking that this problem should be assigned to its own thread, however I'm on the fence because although it isn't a problem with the audit code itself, it is a problem with the audit procedure in finding the "OldValue". (I'm inclined to believe, until known for sure)

Sent from my SM-G925F using Tapatalk

 

[daikaio94]

Hello,

I reconsidered my data model and built a new one from scratch. Actually I did not need the extra table "tblUser_Rechner". Because I only Need to know the current user and not past (or future) users of a pc I can simply put the User_ID and Verwendung_ID in tblRechner. That also solves the Problem with the Audit Process as I can bind the Form to the tblRechner and with that everything works fine. Thank you very much for all the help!

I am still curious though, have you any ideas how to make this work with a m:n (many to many) relationship? For example if I want to see past users of the pc (that would be possible with the data model in the example database).