Recent content by gaffonso

Er, feeling a little dumb, just answered this for myself. The NewRecord property of the form will return true if the form is currently displaying a new record. The code would look like this: If Me.NewRecord Then ' do some stuff based on having a new record End If - Gary

I need to implement some default-setting behavior in a form. For various reasons, I can't use standard field defaults or the DefaultValue property of my controls so I'll need to program the solution. What's the best way to tell if the current record on a form is a "new" record...

I've got a modal pop-up form that bound to a table. The cancel button should close the form and ignore any changes. Problem is, changes made to fields on the pop-up form are committed after the user unfocuses on the field being edited. If a user makes a bunch of changes to various fields in...

I've got a text box that maps back to a decimal field data type in SQL Server (in an Access Project). I'd like to offer the user the ability to type in an integer from 0 - 100 (the percent they desire) and have that be translated into the corresponding decimal value. I can do this with code...

In my quest to write a "global" audit trigger (one that does not contain any table-specific code), I need to determine the name of the table that fired my trigger programatically. In my audit code, I log the table name being modified. Without a way to determine that name...

I've got a trigger written (thanks Terry) that's logging INSERT, UPDATE, and DELETE activity on my table. I'd like to re-use this trigger code on all my tables so I thought that putting it into a stored procedure and passing table-specific data from the trigger would be the ideal way to do...

I'm trying to implement a simple auditing feature by creating a trigger that writes a LastModifiedID and LastModifiedDate to columns on each table in my database. The LastModifiedID and LastModifiedDate columns are not directly edited by a user, they rely on the triggers to be populated. In a...

I'm writing a trigger to write audit data to my table, it automatically inserts the LastModifiedID and LastModified date into fields on that table. I've put a Not NULL constraint on the LastModifiedID and LastModified columns. The problem is that a simple insert into the other columns (not the...

Ok, I know how to use the Advanced tab to add user-defined fields into my Advanced Search criteria. But the real bummer here is that I just want an easy way to search on *all* fields in a form, including user-defined fields. Is there any way to do this? If not, is there a way to extend the...

A big, big thanks to you! - Gary

I'm designing a database in which I track changes made to each record. I've currently got a scheme whereby each table has a "LastModifedID" and a "LastModifiedDateTime" column. I intend to populate these with insert and update triggers. Is there a better way to do this? I...

I've seen several places now that a trigger cannot be used to generate a non integer Primary Key. I'm good with that but would like to read up on the details behind *why*. A cursory check of Books Online doesn't turn up this limitation. Where is it documented? Is there a discussion I can...

I would add that ANSI style joins can be faster than "natural" joins (doing joins using only the WHERE clause). Presumably this is because the specialized join statements allow the query processor to do optimization that it otherwise cannot. - Gary

Thanks for the feedback. Could someone please point me to the documentation describing the limitation of triggers and non integer primary keys? Terry recommended using a stored procedure. This makes perfect sense but it would eliminate the automatic checks when directly modifying the base...

I've fallen into the habit of assigning Surrogate (artificial) keys when doing my relational designs. I've got a client who needs a database with an "Intelligent" primary key on one of the tables. More specifically he would like the Primary Key to be a "Product Number" that...