Hello forum!
Im pretty new to access and im trying to create a database for the company i work at so that to begin with they can log details on all hardware they have.
See the image attatched, at the moment i wish to store details on there computers and also on other hardware is it ok sofar?
http://img210.imageshack.us/my.php?image=erd9xa.jpg
Hi,
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?
Any advice would be greatly appreciated!
why is it that as soon as you fix one problem another one needs dealing with :mad:
OK, the problem i have is that i have a BE/FE configuration database in a multiuser environment. I have built all the tables and the relationships in the BE configuration. In the FE configurations i have built the main forms to input data. It has no come to the point where i have begun to create the queries to allow reporting and data searching. And this is where i have a problem. I am trying to create a simply query and i do mean simple (at the moment i simply want 2 fields from 1 table and 2 fields from another table) no calculations or anything i just want it to display the data. I have tried creating this query numerous times and i keep getting the same error.
"The wizard is unable to open your query in datasheet view, possibly because another user has a source table open in exclusive mode. Your query will be opened in design view"
the above is the error i get when i use a wizard to create the query, after getting this message and going into design view i try to view the results in datasheet view and get the following error "Type Mismatch in expression"
If i dont use a wizard and create it in design view, when i go to view the results i get the second error message everytime.
If i create a query from 1 table only the query works without errors, but i always get errors when i have more than one table in the query.
Also i am the only person with the database open so therefore it is total impossible for the database to be open by another user in exclusive mode.
Anyone know what the problem might be or how to fix. Your answers will be very much appreciated.
I am trying to create a simple audit trail (which I got directions online) in my Database (and have posted a copy here) but am getting an error on the second form (and this will be a subform eventually) -The Audit Trail instructions say to put four fields in my table which feeds the form - CreateBy=Text, CreateDate=Date/Time and default to =now(), ModBy=text, and ModDate=Date/Time.
Then to add code to the Current and BeforeUpdate events on the form as you will see in my forms attached. I created a test database with very little data (called AuditTrackingTest2DBLp-attached). I created the first table (tblCustomers) and then created the form (FormCustomerInput) and then I added code to this form on the Current and BeforeUpdate as I was instructed in my notes and it recorded my changes perfectly in the tblCustomers.
I then created a new table called tblCustomerOrders and then a form (FormCustomerSubOrders) based on this table and added the same code to this form and it is giving me an error message in the code Compile Error: Variable not defined.
I have inherited an Access project from a former employee. My school currently keeps all grades in an Access program. Right now we just assign numerical values and those are turned into grades (I cannot actually find this table). Overall it works well, however, we now have classes where we need to input Pass/Fail and Audit.
View 14 Replies View RelatedHi guys
Quick question. im running a planning database which has got 3 tables Objectors, Representations and Agents. Ive put these together using a one-to-many join matching up the index fields from Objectors and Representations. Ive just constructed a form which has a main form being the Representations and the sub forms with Objectors and Agents. When I want to update or add records a message saying " you cannot add or change a record because a related records is required in table Objectors.
Can any one tell me how I can get around this or possible solutions to this is? Any comments or feedback greatly appreciated!
Cheers
I have been given a project from work and although I have built multiple databases before, for some reason this one is stumping me. I need to create a database to capture data which was previously being captured on multiple spreadsheets to simplify the statistics program at my company. Each spreadsheet captured key pieces of information, I've attached an example of each. Each sheet needs to be set up so that an individual can enter their information for their location and that one person (me) can enter the full company information. For example the hours form, from this form I need to capture the employee count and manhours, whereas from the district sheet I need to capture contractor hours, vehicle data (second tab), and performance data on a monthly basis. The final entry needs to be able to enter all of the counts on the counts link form in order to create a table similar to the link form.
View 1 Replies View RelatedIs there way a to get this audit trail module to work with a form in Datasheet view? It works fine with a form in Single Form view; I can open the table where the audits are saved and everything looks good. But when implemented on a form in datasheet view, nothing happens. I don't get any errors and nothing is saved to the audit table.
Link : [URL] ....
Here's the vba code:
Sub AuditChanges(IDField As String, UserAction As String)
On Error GoTo AuditChanges_Err
Dim db As DAO.Database
Dim rsT As DAO.Recordset
Dim ctl As Control
Dim datTimeCheck As Date
Dim strUserID As String
[Code] ...
You call it in the BeforeUpdate event like so:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
'Track all changes made to the record
If Me.NewRecord Then
Call AuditChanges("Asset_ID", "NEW")
Else
Call AuditChanges("Asset_ID", "EDIT")
End If
End Sub
I've created and tested my database. What step or steps do I take now to make it so it's a program that only shows the forms and menu's I created, not all the Tables, Queries etc and the formatting options? Is there a link that explains what to do step by step?
View 2 Replies View RelatedI have a table that is a list of all of my events. Each record of events should have a child table that list all of the things that happened at the event. When a new record (event) is added how can I have a new child table created and linked to that record. Also I have a blank table to serve as a template for what each child should look like. How do I make sure this occurs? The child tables can have the same name as the index. I am just using numbers 1- for the index with 1 being the first event and so on?
View 8 Replies View RelatedI have a database on a network which is shared by other users.
These users are supposed to use this database monthly to montitor there budgets.
I want to be able to create an audit trail on each time they open the database. I am using the OSUserName function to get there netwotk login id.
Can someone give me some code to put in the db so that when it opens, it inserts the user,date,time in and audit table.
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.
Is there a way to keep track of the users and which form they updated and when? and to be able to view/print reports of it?
this is provided tt i use the group security feature with User ID and password.
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
Thanks for the hlep
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??
Help me, Thanks!
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:
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?
View 1 Replies View RelatedHi - 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...
Can upload a sample test of required??
Thanks anyone that is listening?
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.
I would be grateful for somehelp.
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?
View 1 Replies View RelatedHi
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"
Anybody got any clues, please
Thanks, in advance.
ajm
How would I archive all orders place by customer 'x' in to a new table called tblArchive?
something like:
CREATE INDEX tblArchive ON tblCustomers WHERE customername = "x" ?
Hi everyone,
Here is my problem:
I Have a form that shows bills in a continuous form. My problem is that I want to put a textbox or a label that will display a kind of counter for each bills. For example, If I have 3 bills to display, I want my label or textbox to display 1 for the first bill, 2 for the second, 3 for the third, and so on.
But I don't know how to do that with a continuous form.
Could someone help me, please...
Thanks in advance!
hi there,
i also need help in my form creation. as you can tell from my post, i am clueless.
anyways, here is what i want to do.
i have an input text as:
title1
name1
address1
these will grow based on how many people information one needs to enter. so say if i have 3 people i should have:
title1
name1
address1
title2
name2
address2
title3
name3
address3
and so on
the thing is i only want to show input text 2, 3, etc when i have several names to enter.
so what i want is, a button, such that when i click on that button, it creates input text 2, 3, based on the number of people i have.
i know how to do this easily with html/javascript, but i MUST do this in ms access, so i hope somebody can help.
TIA
chocho