[AccessD] Combobox.oldvalue display text?

Hi Max, see comments in line.

On 8 Feb 2009 at 9:59, Max Wanadoo wrote:

> 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) .
> 

No, I know *why* I'm getting it.

All the comboboxes look something like this (just replace "FundingSource" with the relevant 
name for about 20 other lookups on the form)

Name = cboFundingSourceFK
ControlSource = FundingSourceFK
RowSource = Select FundingSourceDescr,FundingSourceID From tblFundingSources)
ColumnCount = 2
Bound Column = 2
ColumnWidth = 4,0

ctl.Value and ctl.Oldvalue return the FK ( a number)
In the Diary I want to log the Description associated with that FK.

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

I wouldn't - I always type my variables!  NZ() handles Nulls and gives me better control over 
the return value from a Null.  (e.g. NZ(ctl.Value, "BLANK")  below)

> 
> 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.
> 

I'm trying to avoid doing anything in individual controls event procedures - it's much cleaner 
to do it all in one generic function in the Form Before_update.

-- 
Stuart

> 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
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com