Accessing the correct record quickly

Hi

I am using ms Access 2007
A while ago I outlined my problem.................

Basically I have thousands of names in a table.
I wish the enquirer to type in 2 or 3 letters of a surname and go straight
to the first record satisfying those letters.

I received some answers including reference to Allen Browne's code and I
thank you for that.

Unfortunately I am really new to all of this and although I tried - I could
not get the code to work.

Is there a simply solution - without screeds of code, please
If there is please explain the solution, step by step.

Many thanks

Bob

Bob Matthews wrote:I do not know what you think a "screed of code" is

I do not know what you think a "screed of code" is but,
although it is not efficient, the simplest way I can think
of is along these lines.

First you need to create a form to display the record(s?).
Add a text box for users to enter the start of a name in the
form's header section.  The code in the text box's Change
event procedure would look something like:

With Me.RecordsetClone
If Not IsNull(Me.textbox) Then
.FindFirst "namefield Like """ & Me.textbox & "*"" "
If .NoMatch Then
Beep
Else
Me.Bookmark = .Bookmark
End If
End If
End With

Be sure to repllace the dummy names textbox and namefield
with the real names you used.

If you are not familiar with anything in the code, check VBA
Help (not Access Help).

--
Marsh
MVP [MS Access]

BobFrom my perspective, Allen's solution IS a simple solution.

Bob

From my perspective, Allen's solution IS a simple solution.

I usually advise folks that there are 4 learning curves they will need to work
their way up to build good (i.e., get used) apps in Access:

1) understand normalization and relational database design (this is first
and foremost)
2) understand how Access does things (the only way to learn the
tips/tricks is, well, to learn them!)
3) understand graphical user interface design (if the user does not get it,
he will not use it)
4) understand application development (would you try building a bridge
without knowing how to?)

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

One is to use a combo with AutoExpand set to Yes.

One is to use a combo with AutoExpand set to Yes.
--
Build a little, test a little.

Thank you MarshallWhat is the control source of the textbox?Bob

Thank you Marshall

What is the control source of the textbox?

Bob

Bob Matthews wrote:ControlSource is the name of a property of the data

ControlSource is the name of a property of the data related
controls, including text boxes.  Look it up in VBA Help.

--
Marsh
MVP [MS Access]

Hi MarshallSorry I was not clear enoughIn the example you previously sent,

Hi Marshall

Sorry I was not clear enough

In the example you previously sent, what is .......

Bob M

wrote:A Combo Box (with the surname as the first visible field) does exactly

A Combo Box (with the surname as the first visible field) does exactly this.

You can use the Combo Box Wizard in form design to create a combo box using
the "Use this combo to find a record" option; leave its default AutoExpand
property set to Yes, use the primary key of the name table as the bound
column, and use the surname as the first visible column.

Making some guesses at your fieldnames, the rowsource of the combo might be

Select PersonID, [Surname] & ", " & [Forename] AS Fullname
FROM peopletable
ORDER BY [Surname], [Forename];

The combo's ColumnCount would be 2, BoundColumn would be 1, and ColumnWidths
would be something like

0.0 cm;5.0 cm

The combo's AfterUpdate event (which the combo wizard will build for you) will
find the record and display it on the form.

--

John W. Vinson [MVP]

Thank you John.........................

Thank you John.........................

I am almost there :)

Three points that are still not right...............................

a) you say "use the primary key of the name table as the bound column"
I do not see what question I answer in the wizard to get that right

b) when I type in "Gillies" the combo box positions itself on the first
Gillies and as I shift the slider down we reach the end of the Gillies and
continue with other names beginning with G - is that normal?

c) the combo's AfterUpdate Event is blank (i.e. it was not built by the
wizard)

Clearly with the above situation, the form fields are not being updated

Thank you for your patience

Bob

Bob Matthews wrote:If you would complete that sentence I might be able

If you would complete that sentence I might be able to
explain it.  And when you do that, use Copy/Paste so I do not
have to interpret any typos or misunderstandings of what you
think I posted.

--
Marsh
MVP [MS Access]

wrote:You need to know the fields in your table; you especially need to know

You need to know the fields in your table; you especially need to know the
Primary Key. That's not one of the questions (I do not think) - you just need
to design the combo correctly.


Exactly. it will go down throug Zybrowski if you want.


Sounds like you used the wrong wizard, or you are using 2007 which would build
a Macro rather than code.


Please post the relevant fieldnames from your table, the RowSource SQL of the
combo box, and indicate if there is *anything* in the After update event.
--

John W. Vinson [MVP]

Hi JohnTable Field Names:-ID Autonumber [Primary Key]Last Name

Hi John

Table Field Names:-
ID    Autonumber    [Primary Key]
Last Name    Text
First/Initials    Text
Title            Text
Maiden        Text
Ship             Text
Year            Text
Photo Title    Text
ImagePath    Text

I am using Access 2007

SELECT [Portraits].[ID], Portraits.[Last Name] & ", " &
Portraits.[First/Initials] AS Fullname FROM Portraits ORDER BY [Last Name],
[First/Initials];

After Update under the Event tab is completely blank

Bob