Beach Access Software
bchacc at san.rr.com
Fri Jan 5 12:41:38 CST 2007
AHA! Looks like a good weekend job.
Thanks
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:54 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] A Question of Timing
Hi Rocky
No, it is not so. No concatenation.
It is your index that needs two fields:
Go to tabledesign:
Create a new index, idxRocky
Assign it field fldLanguageForm
Move to next line.
Assign field fldLanguageControl
Make the index unique.
Save table.
Now, to seek for the entry for control cboCompany of form frmCustomer:
strSearchField1 = "frmCustomer"
strSearchField2 = "cboCompany"
and in the seek example:
' Set the index.
.Index = "idxRocky"
.Seek "=", strSearchField1, strSearchField2
/gustav
>>> bchacc at san.rr.com 05-01-2007 18:30 >>>
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-linehelp.
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