[AccessD] Combobox.oldvalue display text?

Turns out Access didn't like the Ctl.Text  ( Only available when object has the focus)
So I ended up needing to grab both the old and new dispay values. - that makes the Tag 
storage way more complex.

I also really don't want to build a great block of value holder variables and have to step 
through them - I'm after some sort of generic process. :-(  

I've gone with the following for now, but if I anyone can see a way to  avoid hitting the 
lookup table twice for every combo box, I'd appreciate it :-)  

 Case "cbo"
       If Nz(ctl.Value, "") <> Nz(ctl.OldValue, "") Then
              strchanges = strchanges + Mid$(ctl.Name, 4, Len(ctl.Name) - 5) _
                   & " changed from '" & GetValue(ctl.Rowsource, Nz(ctl.OldValue, 0)) _
                   & "'-to-'" & GetValue(ctl.Rowsource, Nz(ctl.Value, 0)) & "'" & vbCrLf
       End If
....
Function GetValue(Rowsource As String, ID As Long) As String
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(Rowsource)
rs.FindFirst rs.Fields(1).Name & " = " & ID
If rs.NoMatch Then
GetValue = "BLANK"
Else
GetValue = rs(0)
End If
rs.Close
Set rs = Nothing
End Function




it didn't like the On 8 Feb 2009 at 9:48, Andy Lacey wrote:

> Sorry, you'd need to store in OnCurrent or you'd get the wrong thing if
> someone changed a combo twice.
> 
> Better idea perhaps might be still to use the same principle but instead of
> storing to varibales store to the combo control's Tag property. Your For
> Each loop would be neater that way.
> 
> Andy
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Andy Lacey
> Sent: 08 February 2009 09:31
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Combobox.oldvalue display text?
> 
> 
> Hi Stuart
> What about storing the ctl's old description to a form level variable in,
> say, OnGotFocus, and using that? Andy Lacey
> 
> -----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