Hindsight? Bound or Unbound forms 1

[BobJacksonNCI]

I'm having trouble storing the NT/2000 logon name of
the person who changes a record. I store the name
of the record's creator by setting the default value
of one of the fields to (suser_name()). I thought:

Forms!frm_Missed_PUNCH_STATUS.txtStatusChgBy = GetUser()

Function GetUser()
GetUser = Environ("Username&quot
End Function

in the form's BeforeUpdate event would work. But
that results in the run-time error "You can't
assign a value to this object".
Note that the form control is bound to a field
in the table.

So, this is a two-parter for folks who can assist me.
(Please!)

1. How can I store the name of the person who changes
a record in a form bound to an SQL table.

2. Would I have been better off using unbound forms?
(My reading suggested bound would be better, but
in this ADP using Access 2000 with SQL 7,
I have had a LOT of problems and workarounds.)

As always, Thank You for taking your time to help!
Bob

 

[cmmrfrds]

If you are using NT Authentication and the user is defined in sql server with permissions on the tables, then the id can be a default in the table. The suser_sname() will work for this.

I have never seen a need for unbound forms (there may be cases, but nobody has proved the need to me), since bound forms are easy to use and I have not seem any draw backs.

 

[BobJacksonNCI]

Thanks for your reply - and I DO appreciate your thoughts on bound versus unbound!

I am storing the name of the person who initially creates each record via default of suser_name(). There is a status field in each record indicating it's processing state. When someone changes the status, I'd like to store their name and a datestamp. I don't need to know everyone who changed the status, just the most recent one.

I thought I had it, but then got the error message. I'm suspecting I'll have to write an update query for this - which means for this purpose the bound form to table is not an advantage over unbound.

And, it's likely that the cause of some of my challenges with this ADP is the versions of Access and SQL I'm using.

Thanks again for your reply!
Bob

 

[cmmrfrds]

The best way to go about capturing changes to a record where you want to track the update time and user, is to put a trigger on the table. I do these myself, but many places restrict creating trigger to the DBA. You would need some knowledge of how sql server handles triggers. Here is an example.

Example of Mulitple record trigger.
Parent/Child relationship.

create trigger trig_ModDate_InvoiceDetail
on InvoiceDetail
for update
as
declare @ID int
select @ID = invoiceID from deleted
if @@rowcount = 1
BEGIN
UPDATE InvoiceDetail
SET modifyDate = getdate()
WHERE invoiceID = @ID
END
ELSE
BEGIN
UPDATE InvoiceDetail
SET modifyDate = getdate()
WHERE InvoiceDetail.invoiceID IN
(Select InvoiceDetail.invoiceID from inserted)
END

Example of Single record trigger.

create trigger trig_ModDate_InvoiceHeader
on dbo.InvoiceHeader
for update
as
declare @ID int
select @ID = invoiceHeaderID from deleted
UPDATE InvoiceHeader
SET modifyDate = getdate()
WHERE invoiceHeaderID = @ID