[AccessD] Combobox.oldvalue display text?

Hi Stu,


>>ctl.Value and ctl.Oldvalue return the FK ( a number)
>>In the Diary I want to log the Description associated with that FK.
Sure, that is what I was trying to say but probably didn't explain myself
properly.  The reason you are getting the FK is because it is bound to that
position in the rowsource.  Asking it to return a value will give you what
it is point to; in this case the FK value. Similarly for .oldvalue and
.value.


Using the form before update is fine, then looping through as you have
previously said. But get the "Description" requires you to position the
pointer to position 0 whereas you have it positioned (bound) to position 1
in the rowsource.
Viz,
 strchanges = ""
for Each ctl In Me.Controls
Select Case Left(ctl.Name, 3)
    Case "cbo" ' value will be a long but we want the description
        If ctl.Value <> ctl.OldValue Then
          strchanges = strchanges + Mid$(ctl.Name, 4) & " changed from '" &
ctl.OldValue.column(0)  & "'-to-'" & ctl.Value.column(0) & "'" & vbCrLf
       End If
     Case Else
 End Select
 Next

Can't try it myself at the moment, but give it a go.

Max


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: 08 February 2009 10:25
To: Access Developers discussion and problem solving
Subject: Re: [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


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