Adding keys, that's a great idea, I'm going to add that to my change tracker.
I'm only recording the primary key for the table being edited, but I could have both membership records and payment records in the audit table. So if I want to look up a member's payment change history, I need to join the audit table to the payment table in order to find one member's changes.
By adding the foreign key to the audit table, I'll...
wvmitchell
Last Activity: Dec 2 '07, 08:41 PM
Joined: Oct 17 '07
Location:
-
Don't you think that DoCmd.Quit is required after a failed login in your post ( #4 ) ? Otherwise, the dialog is left open & the user has no way to cancel or close out from Access.
OTOH a savvy user could simply press Alt-F4 to close the dialog (unless the built-in Close button has been disabled on the form) and they would be granted access to the application. That's a security hole.
The OP has since added a boolean...Leave a comment:
-
Jim,
Sorry if I sounded rude, I meant no disrespect.
BillLeave a comment:
-
Jim, you said
"Your full SQL could potentially return any number of rows including duplicate entries (unless of course you have a composite primary key based on username and password.) "
But a user login table must always have a unique index on the username, therefore since you will never have a duplicate username, a composite index is not necessary. Examples - logging in to your email account, or online banking,...Leave a comment:
-
We're not supposed to be doing anybody's homework here, but here's a fix for the bad part of your code.
But after that point, you just close the form whether or not they've successfully...Code:Set rst = CurrentDb.OpenRecordset(strSQL) If (rst.BOF And rst.EOF) Then MsgBox ("Access Denied") Else ysnAdmin = rst!ysnAdmin End IfLeave a comment:
-
Mr. Browne's solution is very comprehensive, but here are a couple things to note:
1. A log table is required for every table that you wish to audit
2. Every edit will generate two rows that hold the entire record (before & after)
3. Failed edits are not logged
4. Quite a bit of code is required to be added to each form
Items 1 & 2 will grow your database file size considerably, especially...Leave a comment:
-
Johan,
To open the database exclusively in order to change the database password - launch Access first, then click File ~ Open, navigate to your Access file and then notice that the Open button has a small drop-down at the right edge - select Open Exclusive.Leave a comment:
-
Single quotes can create headaches, but you can put this in a standard module & then use it any time you're searching for strings. It doubles-up any single quotes as Rabbit said. It accepts a Variant, so NULLs won't break it, it just returns a zero-length string.
...Code:Function FixQuotes(text) As String If Len(Nz(text, "")) = 0 Then Exit Function FixQuotes = Replace(text, "'", "''")Leave a comment:
-
Are you connecting to a SQL Server database?Leave a comment:
-
I agree that reserved words should never be used for field names, but every new version of Access adds new reserved words that can break your application. Therefore, you need to adopt a best practice as far as naming your fields to be as specific as possible, i.e. instead of "Number" you might use "Order_Numb er" which avoids conflict with future Access versions, plus has the benefit of being self-documenting.
However,...Leave a comment:
-
Just a quick idea, if you display the form in datasheet view you can freeze the columns (Format ~ Freeze Columns). The downside is that it doesn't look as attractive & you lose the header / footer.Leave a comment:
-
Leave a comment:
-
Here's an idea:
1. Create a new table with the same structure.
2. Change the "Number" field in the new table to autonumber.
3. Append all the records from the old to the new table.
4. Rename your tables so the new one uses the original name.
5. Compact the database
Existing records will have the original number, and any new records added should then start with the next higher autonumber....Leave a comment:
-
Aha! The reason that Abs() works is that the bracketed expression evaluates to True or False, which in Access equals -1 or 0. So then Abs(True) = 1 and Abs(False) = 0 and when you Sum() that you get the right answer.Leave a comment:
-
Create a macro that will execute your code and close the database when it's finished. (Macros can execute a Function but not a Sub.) Make sure that your code has proper error-handling, if you don't then any error will halt the macro & leave the file open with a macro error dialog (not cool).
Then, you can use the command-line switch /x to launch Access, open the file, and execute the macro. You would create a Windows shortcut that...Leave a comment:
-
I misplaced a parenthesis in the IIf statement:
Column2
=Sum(IIf(([qryMonthlyRepor t]![ClearanceCode]="O") And (TheDate Between [Forms]![frmMonthlyRepor t]![Date1] And [Forms]![frmMonthlyRepor t]![Date2],1,0)))
should be
Column2
=Sum(IIf(([qryMonthlyRepor t]![ClearanceCode]="O") And (TheDate Between [Forms]![frmMonthlyRepor t]![Date1] And [Forms]![frmMonthlyRepor t]![Date2]...Leave a comment:
-
Robert,
Is it possible that the Oracle table is being changed? When you create an ODBC connection in Access, it remembers the table structure but does not automatically update if the underlying table changes. So for example, if you add a column to the table, you need to relink, otherwise you get error 3146 ODBC call failed.
Another thought, I'm not an Oracle person, but is it possible that the Oracle 8 driver is not...Leave a comment:
-
Hi Dan, glad I could help.
As far as your new question, I'm also fairly new to this forum, but I think if you start a new thread you'll get more views - others will see the highlighted "new post" tag and be more likely to read & respond. There's usually more than one way to solve a given problem in Access, maybe somebody else might have a better idea.
Back to your new question, the general approach could...Leave a comment:
-
Dan, is this what you want in the results:
Do you always want to see every record from tblOffenses.Off enses, along with a count of tblCaseManageme nt.Offense records that fall within the date range?
If that is the case, you would use two queries. The first query would filter for the records that fall inside your date range. Let's call it qryCaseFilter:
SELECT tblCaseManageme nt.Offense
FROM tblCaseManageme nt...Leave a comment:
-
Dan,
I don't fully understand the logical relationship between your two tables, but when you apply criteria to the tblCaseManageme nt table, the effect is that you're making it act like an INNER JOIN, because you are requiring a matching record in tblCaseManageme nt.
In any event, can you try this & see if it gives you the desired results:
SELECT tblOffenses.Off enses, Count(tblCaseMa nagement.Offens e)...Leave a comment:
No activity results to display
Leave a comment: