[AccessD] Find First in an Array?

Rocky,
This is a simple coding solution.  I have no idea of how fast it might be
viz-a-viz the Collections/Class solutions, but might give a reasonable
response time.
Called on the form's onload

Public Function pfTranslateLabels(frm As Form, strLanguage As String)
' EG:- strLanguage = "fldLanguageChineseComplex"
  Dim sql As String, rst As DAO.Recordset, ctl As Control, strFind As String
  sql = "Select * from tblLanguages Where fldLanguageForm ='" & frm.Name &
"'"
  Set rst = CurrentDb.OpenRecordset(sql)
  If Not rst.EOF Then
    For Each ctl In frm.Controls
      If ctl.Type = acLabel Then
        strFind = "fldLanugageControl='" & ctl.Name & "'"
        rst.FindFirst strFind
        If Not rst.NoMatch Then
          ctl.Caption = rst(strLanguage)
        End If
      End If
    Next ctl
  End If
exithere:
  Set rst = Nothing: Set ctl = Nothing
  Exit Function
End Function


If you have lots of forms doing this, you might want to open the RecordSet
as a persistent one and not close it down after each form.

Max


On Thu, Feb 19, 2009 at 7:01 AM, Rocky Smolin at Beach Access Software <
rockysmolin at bchacc.com> wrote:

>
> Oops, just checked - those fields are indexed.
>
> Rocky Smolin
> Beach Access Software
> 858-259-4334
> www.e-z-mrp.com
> www.bchacc.com
>
>
>
> -----Original Message-----
> From: Rocky Smolin at Beach Access Software [mailto:rockysmolin at bchacc.com
> ]
> Sent: Wednesday, February 18, 2009 10:57 PM
> To: 'Access Developers discussion and problem solving'
> Subject: RE: [AccessD] Find First in an Array?
>
> John:
>
> The table layout is:
>
> fldLanguageID             Autonumber
> fldLanguageForm           Text Name of the form that the control is on
> fldLanguageControl        Text Name of the control
> fldLanguageControlType    Text Not Used
> fldLanguageEnglish        Text
> fldLanguageChineseComplex Text
> fldLanguageChineseSimple  Text
> fldLanguageSpanish        Text
> fldLanguageFrench         Text
>
> In the translate routine I use:
>
> Set frm = Forms(argForm)
> For Each ctl In frm
>    rstControls.FindFirst "fldLanguageForm = '" & argForm & "' and
> fldLanguageControl = '" _
>        & ctl.Name & "'"
>
> Where argForm is the form name passed to the function.  So I think I might
> get a big boost in response time by indexing fldLanguageForm and
> fldLanguageControl.
>
> There are only about 2500 records in the table so I'm thinking that the
> whole recordset is probably in memory anyway.
>
>
> Rocky Smolin
> Beach Access Software
> 858-259-4334
> www.e-z-mrp.com
> www.bchacc.com
>
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Wednesday, February 18, 2009 5:46 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Find First in an Array?
>
> Does this imply a table structure of:
>
> TR_ID           Auto
> TR_CtlName      Text
> TR_L1           text (or memo?) for language 1
> TR_L2           text (or memo?) for language 2
> Etc
>
> I would suggest that you add a form (or container) field.  A control name
> is
> unique on a form, however it could be the same on different forms but have
> a
> different language string.  By having a form field you could pull subsets
> of
> records based on the form name, then use the control name (now guaranteed
> to
> be unique), then obtain the language string.
>
> It appears from your email however that you already have this stuff set up,
> so you might be resistant to modifying how it works.
>
> If this is all just one big table with control names guaranteed to be
> unique
> then you could just stash it in one big collection.  There is a problem
> however which is that classes get pretty slow as they get large.  If you
> get
> up past 10,000 strings (I don't know the exact point) it would bog back
> down.  If you broke it down into controls on a form, then you would ensure
> that the total strings in any given collection is pretty small.
>
> Again I would build a class for all the code and the collection to load one
> form.  then a supervisor class to hold instances of this form class keyed
> on
> form name.
>
> John W. Colby
> www.ColbyConsulting.com
>
>
> Rocky Smolin at Beach Access Software wrote:
> > Max:
> >
> > That would work except the table driven approach is so much more easier.
> > Easy to add a language (got French and Spanish now in addition to
> > traditional and simplified Chinese), too, or make a change to a
> translation.
> > To add a language I just add a column for that language to the Control
> > and Messages tables and send them to the translator.  Add that
> > language to the language selector combo on the Preferences form, and
> > walla! Another language.
> >
> >
> > Rocky Smolin
> > Beach Access Software
> > 858-259-4334
> > www.e-z-mrp.com
> > www.bchacc.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
>