[AccessD] Combobox.oldvalue display text?

Hi Stuart (or G'day PNG)

The reason you are probably getting "6" instead of "Description" is probably
because the Index is pointing to position 1 instead of position 0.  Most
people would probably have the rowsource set to "Select ID, Descr from
tblLookup", so to get at "Description" at position 0 you could try
fldname.column(0) .

I would also change the variable to a variant, ie strchanges to varchanges
so that it handles nulls etc.

I would also have a look at storing the control.value onentry then onexit do
a "me.dirty=false" forcing a save and then checking contol.value again and
if different writing it away by calling a function.

All of the above is off the top of my head.

Max


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: 08 February 2009 09:00
To: Access Developers discussion and problem solving
Subject: [AccessD] Combobox.oldvalue display text?

I'm logging record changes on a form into an audit trail/diary.
Text boxes are simple:

strchanges = ""
For Each ctl In Me.Controls
Select Case Left(ctl.Name, 3)
    Case "txt"
       If Nz(ctl.Value, "") <> Nz(ctl.OldValue, "") Then
          strchanges = strchanges + Mid$(ctl.Name, 4) & " changed from '" &
Nz(ctl.OldValue, 
"BLANK") & "'-to-'" & Nz(ctl.Value, "BLANK") & "'" & vbCrLf
       End If
    Case Else
End Select
Next
If strchanges > "" Then
strchanges = Replace(strchanges, "'", "''")
strSQL = "Insert into tblDiary (DTG,TenderID,creator,comments) values
(Now()," _
         & TenderID & ",'" & GetUserName() & "','" & strchanges & "')"
End If
CurrentDb.Execute strSQL

There are a number of Combo Boxes (cboFieldName) as well.  
In each case, the row source is in the form:
Select Descr,ID from tblLookup

If I use the same method as per "txt", I get the Indexes in the diary
ie Fieldname changed from "6"-to-"4"

I can use use ctl.text to get
 Fieldname changed from "6"-to-"New Description"

Getting "Old Description" insead of "6" is the problem

I've considered
Building Dlookup()s from cbo.Rowsource with a cbo.Oldvalue filter
Using a DAO,.Recordset to do the same, again based on the cbo.Rowsource
(easier than 
Dlookup, because I can use Fieldnumbers in the returned record)

Both seem a bit of a PITA since there are a lot of fields on the form.

Ideally I'd like something like ctl.oldvalue.text but that doesn't work.

Anyone got any other ideas?

-- 
Stuart



-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com