Could someone please help. I am in a serious time crunch and cannot figure out what I have done wrong. I have copied the module attached for the audit trail and have implemented it into my database. For some reason when I go to run the BeforeUpdate event I get a 13-Type Mismatch error. I have found a fix that will allow me to use the code without flagging an error, but I am not sure it is working properly now. Here is the fix.
1. Pass the form as an argument of the function
Function AuditTrail(frm as Form)
2. Comment out these lines in the code
'Dim frm as Form
'Set frm=Screen.ActiveForm
3. Call the function in the BeforeUpdate event of the form and or subform as follows:
Call AuditTrail(Me)
Hi Ive downloaded and installed the AuditTrail zip file as mentioned in a different thread. It works fine on my main form, but I also have a subform on that form. I am trying to pass "ME" as a parameter to a second function ((Audit_TrailSub(Frm as form)) on the Before Update command ie =Audit_TrailSub(ME) However when I attempt to change a record in the subform I get a message . . . "The object doesn't contain the automation object ME"
I hate to bring another Audit Trail question to the table, but I am scratching my head for thelast day trying to get to get this work.
Link to original post: http://www.access-programmers.co.uk/forums/showthread.php?t=44231&highlight=audit+trail
Problem: I have a main form/subform. The mainform is based on a query of 2 tables with a lookup field. The subform is based on a query referencing the PK for one of the main form's tables.
I have gotten the audit trail to work on the subform, no problems there.
But I get an Error 3251 - Operation is not supported for this type of object.
When I press ok, the record is changed and saved. The memo field logs that a change has been made, but does not list what was changed or what it was changed from.
I have tried the following: Changing the lookup fields names as Lookup1, Lookup2...etc the changing the line of code to skip those fields along with tbAuditTrail.
I have tried adding a second module named different/changed function name and calling it...no luck
Creating a just a new main form and tring it...zilch
I read something about recordsetclone, but do not know exactly how to add this in the module to try.
Does anyone have any other suggestions/or solutions that I may try?
I have searched this subject out and tried some of the solutions but have more questions.
All I really need is a table with the username, date/timeIn and date/timeOut of the "database" forms, reports and all that don't matter...yet.
I am able to get the user info to show on a form (that comes up on startup) but can't get the info from the form into a table. I have tried an append query but no luck. If I see it on a form shouldn't I be able to "grab" it? I have used all kinds of code found here to "get" the info and same problem, I can see but I can't touch.
I have created the following code to put the date and username into a table to track any changes made to a record. All I am interested is when the change was made and by who.
When I change a record the Now() command works fine and puts the date and time but the Username = User_FX does not grab the logged in to win nt domain user any Ideas
Private Sub Form_BeforeUpdate(Cancel As Integer) ' Log the user details to the table Me!SystemUsername = User_FX Me!RecordChanged = Now() End Sub
My audit trail is doing fine, but now my boss needed the "memo" to be highlighted or numbered so its easier to see whether the record was updated. (And the date wasn't enough:mad: ) So is there any possible way to have it highlighted/numbered after edited??
Hi, I am trying to use Audit Trail in Access2000. I tried to look at this snippet from Microsoft. But I am getting an error when I save the record. Actually I have a form with 8 tab pages. It tracks that the change is made, even though it gives error message, but does not track which field was changed. I would appreciate if someone can help me. Regards K
Function AuditTrail() On Error GoTo Err_Handler
Dim MyForm As Form, C As Control, xName As String Set MyForm = Screen.ActiveForm
'Set date and current user if form has been updated. MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _ "Changes made on " & Date & " by " & CurrentUser() & ";"
'If new record, record it in audit trail and exit sub. If MyForm.NewRecord = True Then MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _ "New Record """ End If
'Check each data entry control for change and record 'old value of Control. For Each C In MyForm.Controls
'Only check data entry type controls. Select Case C.ControlType Case acTextBox, acComboBox, acListBox, acOptionGroup ' Skip Updates field. If C.Name <> "Updates" Then
' If control was previously Null, record "previous ' value was blank." If IsNull(C.OldValue) Or C.OldValue = "" Then MyForm!Updates = MyForm!Updates & Chr(13) & _ Chr(10) & C.Name & "--previous value was blank"
' If control had previous value, record previous value. ElseIf C.Value <> C.OldValue Then MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _ C.Name & "==previous value was " & C.OldValue End If End If End Select Next C
TryNextC: Exit Function
Err_Handler: If Err.Number <> 64535 Then MsgBox "Error #: " & Err.Number & vbCrLf & "Description: " & Err.Description End If Resume TryNextC End Function
I know this has been asked many times and I have tried following all the threads but I have been struggling and cannot get the audit trail to work on a subform. Please, does anyone have a working example, or can point me to one, of the audit trail working on a subform ? I've been tearing my hair out trying to get it to work. :confused:
I have a form that is based on a parameter query. The user inputs an item number and the form opens in edit mode. The user clicks the "duplicate" command button and then changes the item number. I've incorporated GHudson's "A Better Mouse Trap" logic and when clicking "save" I get the type mismatch error.
I don't understand why Access is not returning the 3022 duplicate entry error.
When I click "debug" Access highlights the line as shown below:
Err_bSave_Click: If Err = 2046 Then 'The command or action Undo is not available now Exit Sub Else MsgBox Err.Number, Err.Description Resume Exit_bSave_Click End If
I am trying to do a basic query and I keep getting a "Type Mismatch" error and the query will not run. If I only do a query on one table, it works no problem so I know it must be related to my Join between tables.
For the two tables that are joined (it is one-to-many)- the first table is a clients table and I created a field called ClientNumber that is an AutoNumber. The second table is called TrainingRequests. This will store the training requests for each client and each client can have multiple training requests. I created a field called ClientNumber in it as well (this is what field I linked the tables by). But I set it to text instead of AutoNumber.
Is there a way to do a query with the two tables? Or will I have to change something in table design? I already have some data in the tables so I am not sure what direction to take.
Thanks for any help someone can provide. It would be greatly appreciated.
I am getting the following error when I try to present some information from a database:
Microsoft JET Database Engine error '80040e07' Data type mismatch in criteria expression. /tribute2.asp, line 168
The code relating to this is: The error line it is talking about is: Code:rsGuestbook.Open SQLstr, adocon
Code: <%Dim adoCon 'Holds the Database Connection ObjectDim rsGuestbook'Holds the recordset for the records in the databaseDim SQLstr'Holds the SQL query for the databaseSet adoCon = Server.CreateObject("ADODB.Connection")adoCon.Open = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=c:inetpubftprootlocalusericlay emember ing.co.nzdb emembering.mdb;"strcustomerID = Request.QueryString("remID")Set rsGuestbook = Server.CreateObject("ADODB.Recordset") sqlstr = "select * from guestbook where guestbook.mid =" & strcustomeridrsGuestbook.Open SQLstr, adoconDo While not rsGuestbook.EOF'Write the HTML to display the current record in the recordsetResponse.Write ("<font face='Arial' size='2' color='#000080'><i>A tribute provided by ")Response.Write (rsGuestbook("confirstname"))Response.Write (" ")Response.Write (rsGuestbook("conlastname"))Response.Write ("</i></font><font face='Arial' size='1' color='#000080'><i> ")Response.Write (rsGuestbook("conrelationship"))Response.Write ("</i></font><br><br>")Response.Write (rsGuestbook("conmessage"))Response.Write ("<br>")'Move to the next record in the recordsetrsGuestbook.MoveNextLoop'Reset server objectsrsGuestbook.CloseSet rsGuestbook = NothingSet adoCon = Nothing%>
Can anyone see where I have gone wrong ... could it be something to do with the fact that 'remid' relates to another table in the database. Though when I did a reponse write on the sqlstr it is presenting the remid.
Got a problem with that audit trail that has been posted on here.
Basically I can get it to work on all put one subform, and I don't know why.
I have a main form with a tab control placed on it containing all of the subforms in my db. On the form it doesn't work on it seems to be inputting into the updates control incorrectly. With all the others it works fine, and displays the changes as follows in the updates control:
Record added on 26/10/05
Changes made on 26/10/05 10:23:55 by johns; Date: was previously Null, New Value: 20/03/05
But using the subform that is not working, it shows something like:
Changes made on 26/10/05 10:23:55 by johns;Date: was previously Null, New Value: 20/03/05
Can anyone help with this, I'm not sure if it is because it is getting confused between the tab control and the main form.
Anyone any ideas, let me know if you want me to post the code. I can come back and post one that works, and the one that doesn't.
i created an audit trail in my DB... i do need to submit reports reflecting the relevant changes made.... however, i notice that the report can be very lengthy if not renewed every month.... can i archive these trails.. on a mthly basis? if yes.. how ? if not... is there anything else i can do?
Hi - This is what I am trying to do, any help greatly appreciated... i.e. Customer Table Customer History Table Import Table
1 Insert New Cust from Import Table into Cust table - Yes can do 2 Insert New Cust History from Import Table into Cust History Table - Yes can do 3 Update changes in customer table from Import table - yes can do
Now comes the tricky part
4 Next step is to insert a record into the customer history table where customer changes are happening - yes can do for the first time
HOWEVER this will keep updating same changes!!! If I then run the query again - see below
I want to take the SQL into vb and put all together and click button to run import as the import table will come from another database every day
Once I have run step 4 i don't want it to update the customer history table again next time that I run above said button...
There are other issues but this is my first stuck step and having been banging my head for two days...
I have search the forum for a solution but I having a little difficulty understand some of soultions provided.
Getting the Audit trail, created g G Hudson work magnificently on a main form, to work on my subform called subformMovements is giving me a little grief.
I dont really know how to fix my problem. I have attached a copy of my DB is someone who like to give me a hand to figure it out.
The Audit Trail function (GHudson) on my form is still working wonderfully. But I was wondering, will it add alot to the overall size of the DB? It seems to record a lot of information (which is what it is made for lol). Has anyone had problems with it growing too large?
common error in VBA imports. It has happened with spreadsheets and text files I have users importing. An error message pops up "Type Mismatch." But the import appears to work correctly. So I added code that On Error, if error Like "*Type mismatch*" continue to next step. Now the user gets a longer error message: "The expression On Click entered as the event property setting produced the following error: Type mismatch." Again, the import still works, but this message is annoying! The even stranger thing is that I do not get this error message on my machine. Just the other users.
I've been trying to get a query to run but I keep the "type mismatch in expression" error message.It's the Invoice-Product Query in the attached file.I have tried changing the field types, the primary keys and messed around with the relationships but noting seems to work.
I have discovered a problem with my database and wonder if anyone here can point me to the best solution...
The problem is this... when I took our company database over about 3 years ago it was around 45MB... now it is almost 350MB and growing steadily.
I have today discovered the main cause of this... the audit trails on a couple of the tables. If I remove these 2 fields only from the database it cuts it down to under 100MB. The Audit trail is quite important for us and so I'd like to keep it if I can. It is stored in a memo data type (the data is well over the 255 char limit of a text box). Is there any other better way to store this data or is there anything I can do to reduce the size of the memo data type?
Hi All, i need some more help. I have an access application which has 6 different forms. The application will be accessed by 10-12 people. some can have an access to to all the forms and some may have an access to a few of them. How can I set up the password security for the users at application and form level? I also want to capture the audit trail when the user users edit form and makes changes to a record. I would apprecaite help on this one. Regards K
I have a form with a few tabs and 3 subforms. I used the following Audit Trail [URL] .... to complete what I have so far and it's been really great. Simple, easy to use and works well. The only issue that I have is it doesn't read the edits from the sub forms. It tells me when a new record is created or deleted but I can't see if anything was changed or what was input into the new record.
I believe it's something to do with Screen.ActiveForm but I can't be certain.
Below is the code that I use in a module and the code on the event section of the forms and the module that I use. Also all the controls I want audited have Audit in the tag of the Other menu.
BadAudit Module
Sub AuditChanges(IDField As String, UserAction As String) On Error GoTo AuditChanges_Err Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim ctl As Control
[Code] ....
The After Del and Before Up are also on the forms SuspensionsSubFrm, ReviewsSubFrm and Framework which are all subforms.
The parameters @subject and @type are simply strings selected from dropdown values.
The @date parameter is set by using a dropdown to select a timeframe to search in, specifically "Within 1 month", "Within 3 months", "Within 6 months". These options have the values "1", "3" and "6".
When the form is submitted the value of the selected option is passed into the following routine:
Dim valSelectedDate AsInteger Dim dateParameter AsDate valSelectedDate = ddlDate.SelectedValue dateParameter = DateAdd("m", valSelectedDate, (Date.Now)) cmdSelectEvents.Parameters("@date").Value = dateParameter
When I run the debugger the right date value seems to be being passed to the sql query (i.e. if the user selects "within 6 months" and today's date is 5th May 2005, then #05/11/2005# is passed as the parameter) but I get the error data type mismatch. The column eventDates.date is a Date/Time column.
