Accessing the correct record quickly

[Bob Matthews]

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

 

[Marshall Barton]

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.

I don't 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).

 

[Jeff Boyce]

Bob

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

I usually advise folks that there are 4 learning curves they'll 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 doesn't get it,
he won't 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.

 

[KARL DEWEY]

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

 

[Bob Matthews]

Thank you Marshall

What is the control source of the textbox?

Bob

 

[Marshall Barton]

What is the control source of the textbox?

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

 

[Bob Matthews]

Hi Marshall

Sorry I was not clear enough

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

Bob M

 

[John W. Vinson]

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.

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.

 

[Bob Matthews]

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

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.

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.

 

[Marshall Barton]

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

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

 

[John W. Vinson]

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

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 don't think) - you just need
to design the combo correctly.

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?

Exactly. It'll go down throug Zybrowski if you want.

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

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

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

Thank you for your patience

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.

 

[Bob Matthews]

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