Gary Kjos
garykjos at gmail.com
Fri Jan 5 12:20:34 CST 2007
Rocky,
An index can contain one field OR SEVERAL FIELDS. Bring up the index
box for your table and you will see a column for the name of the index
and another column for the fields that make up that index and then a
ascending or desending option etc.
So you might have say an index called FullName that was made up of the
fields FirstName and LastName.
Try it.
GK
On 1/5/07, Beach Access Software <bchacc at san.rr.com> wrote:
> Gustav:
>
> So in this case they are seeking on the primary key - ProductID - which the
> user inputs. It looks like, if I want to use Seek I'll have to create a new
> field which is the concatenation of the form name and the control name,
> index it, and then set the .Index of the table to that new field. That
> would allow Seek to replace my current FindFirst which operates on two
> fields. Correct?
>
> TIA
>
> Rocky
>
>
> Rocky Smolin
> Beach Access Software
> 858-259-4334
> www.e-z-mrp.com
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
> Sent: Friday, January 05, 2007 9:01 AM
> To: accessd at databaseadvisors.com
> Subject: Re: [AccessD] A Question of Timing
>
> Hi Rocky
>
> If you have the Nortwind database, an example is provided in the on-line
> help.
> Pick Microsoft Visual Basic Help, type in Seek, select second item: Seek
> Method (DAO).
> This topic has an example:
>
> <code>
>
> Sub SeekX()
>
> Dim dbsNorthwind As Database
> Dim rstProducts As Recordset
> Dim intFirst As Integer
> Dim intLast As Integer
> Dim strMessage As String
> Dim strSeek As String
> Dim varBookmark As Variant
>
> Set dbsNorthwind = OpenDatabase("Northwind.mdb")
> ' You must open a table-type Recordset to use an index,
> ' and hence the Seek method.
> Set rstProducts = _
> dbsNorthwind.OpenRecordset("Products", dbOpenTable)
>
> With rstProducts
> ' Set the index.
> .Index = "PrimaryKey"
>
> ' Get the lowest and highest product IDs.
> .MoveLast
> intLast = !ProductID
> .MoveFirst
> intFirst = !ProductID
>
> Do While True
> ' Display current record information and ask user
> ' for ID number.
> strMessage = "Product ID: " & !ProductID & vbCr & _
> "Name: " & !ProductName & vbCr & vbCr & _
> "Enter a product ID between " & intFirst & _
> " and " & intLast & "."
> strSeek = InputBox(strMessage)
>
> If strSeek = "" Then Exit Do
>
> ' Store current bookmark in case the Seek fails.
> varBookmark = .Bookmark
>
> .Seek "=", Val(strSeek)
>
> ' Return to the current record if the Seek fails.
> If .NoMatch Then
> MsgBox "ID not found!"
> .Bookmark = varBookmark
> End If
> Loop
>
> .Close
> End With
>
> dbsNorthwind.Close
>
> End Sub
>
> </code>
>
> In your case these lines should be changed:
>
> .Index = "PrimaryKey"
> to
> .Index = "MyTwoFieldIndex"
> and
> .Seek "=", Val(strSeek)
> to
> .Seek "=", strSeekForFieldOne, strSeekForFieldTwo
>
> if you for the table have created a compound index named MyTwoFieldIndex
> with two fields.
> Then strSeekForFieldOne and strSeekForFieldTwo are the values for this index
> you wish to look up.
>
> Note that the valuetypes of the search variables must match those of the
> fields.
>
> Of course, you will also have to modify the InputBox part ...
>
> /gustav
>
>
> >>> bchacc at san.rr.com 05-01-2007 15:54 >>>
> Gustav:
>
> When you say 'index with these two fields' I'm not sure what you mean,
> unless I create a new field of the two Find fields concatenated and index
> that? I know I can index multiple fields but in the Seek I can only specify
> one field, yes?
>
> Rocky
>
>
> Rocky Smolin
> Beach Access Software
> 858-259-4334
> www.e-z-mrp.com
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
> Sent: Friday, January 05, 2007 6:17 AM
> To: accessd at databaseadvisors.com
> Subject: Re: [AccessD] A Question of Timing
>
> Hi Rocky
>
> Well, not exactly.
> What you will need is an index with these two fields.
>
> /gustav
>
> >>> bchacc at san.rr.com 05-01-2007 15:05 >>>
> Gustav:
>
> Well the table is relatively small - <2300 small records - but I am doing
> the current Find First on 2 fields - Form Name and Control Name. So, unless
> I create a concatenation of the two and use that as the Seek field, Seek
> won't work for this. Correct?
>
> TIA
>
> Rocky
>
>
> Rocky Smolin
> Beach Access Software
> 858-259-4334
> www.e-z-mrp.com
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
> Sent: Friday, January 05, 2007 2:55 AM
> To: accessd at databaseadvisors.com
> Subject: Re: [AccessD] A Question of Timing
>
> Hi Rocky
>
> Seek doesn't work without specifying the index to use. Thus, at least one
> index must exist for that table.
> In fact, that's the secret of Seek. It doesn't care about the records; it
> just looks up one record using the specified index if the seek criteria is
> met.
>
> /gustav
>
> >>> bchacc at san.rr.com 04-01-2007 20:00 >>>
> I'll look at that. Index on the field I'm using to Find First would be all
> that's necessary?
>
> Rocky Smolin
> Beach Access Software
> 858-259-4334
> www.e-z-mrp.com
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
> Sent: Thursday, January 04, 2007 8:08 AM
> To: accessd at databaseadvisors.com
> Subject: Re: [AccessD] A Question of Timing
>
> Hi Rocky
>
> You could use DAO and the Seek method on this local table if it is indexed
> properly.
> Seek is extremely fast but the syntax is a bit weird.
>
> /gustav
>
> >>> bchacc at san.rr.com 04-01-2007 16:50 >>>
> Dear List:
>
>
>
> In my manufacturing app I translate forms to various languages on the fly -
> when the form opens. The core of the translation routine looks at each
> control in a form and if it's a label or command button looks up the
> language record in a table (front end) and replaces the caption with the
> appropriate language. The core of the routine is:
>
>
>
> rstControls.FindFirst "fldLanguageForm = '" & argForm & "' and
> fldLanguageControl = '" _
>
> & ctl.Name & "'"
>
> If rstControls.NoMatch = False Then
>
>
>
> I have noticed, especially in the Chinese translation some noticeable delay
> and you can see the form repaint itself the first time it opens (the second
> time it seems to go much faster).
>
>
>
> So I'm wondering if I put all the translations into an array when the app
> starts up and search the array instead of using FindFirst on a DAO recordset
> if it will be significantly faster and maybe eliminate the flicker that
> occurs during translation. The translation is called from the Open event of
> the form, BTW.
>
>
>
> Another approach I have considered which would be a considerable re-write,
> would be to open all the forms hidden and, instead of opening them when
> needed, simply make them visible. There are about 85 forms and I don't know
> if having that many forms opne at one time will cause other problems.
>
>
>
> Any opinions appreciated.
>
>
>
>
>
> MTIA
>
>
>
>
>
> Rocky Smolin
>
> Beach Access Software
>
> 858-259-4334
>
> www.e-z-mrp.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.5.432 / Virus Database: 268.16.5/616 - Release Date: 1/4/2007
> 1:34 PM
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
Gary Kjos
garykjos at gmail.com