Prev: Auto-Fill a Form
Next: Relationships and lookup tables.
From: Ken Sheridan on 4 Jul 2008 18:46
You can make life a lot easier for users by using a combo box from which they
simply have to select a name from a list of those which the OfficerRoster
Query returns. That way they cannot enter a non-existent name or misspell
one. First you'll need to create an unbound dialogue form and add an unbound
combo box to it. Once you've done that change the name of the combo box in
its properties sheet to something meaningful like cboOfficer. Set the
RowSource property of the combo box so it lists all the LastName values
alphabetically, e.g.

SELECT DISTINCT LastName FROM [OfficerRoster Query] ORDER BY LastName;

It the query only returns one row per person you don't need the DISTINCT.

Set the combo box's LimitToList and AutoExpand properties to 'Yes'.

You can remove the Like "*" &[Enter Last Name]& "*" parameter from your
OfficerRoster Query as you'll be filtering the form to the selected name. To
do this add a button to the dialogue form and in its Click event procedure
enter the following code:

Dim strCriteria As String

' only do the search if a name has been selected
If Not IsNull(Me[cboOfficer]
' build criterion for search
strCriteria = "[LastName] = """ & Me.[cboOfficer] & """"

' open form filtered to selected person
DoCmd.OpenForm "frmOfficerRoster", WhereCondition:=strCriteria

' close dialogue form
DoCmd.Close acForm, Me.Name
Else
' otherwise inform user
MsgBox "No officer selected.", vbInformation, "Invalid Operation"
End If

where frmOfficerRoster is a form bound to the OfficerRoster query. You
enter the code in the event procedure like this:

Select the button in form design view and open its properties sheet if its
not already open. Then select the Click event property in the properties
sheet. Click on the 'build' button; that's the one on the right with 3 dots.
Select 'Code Builder' in the dialogue, and click OK. The VBA window will
open at the event procedure with the first and last lines already in place.
Enter the lines of code between these two existing lines.

Back in your switchboard, change it so that it opens the dialogue form
you've just created rather than the form bound to your query. When the
dialogue form opens a user simply has to select a name from the list and then
click the button. If the user types a name into the combo box rather than
scrolling down the list it will, because the AutoExpand property is True, go
to the first matching name as each character of the name is typed.

This will find people with the selected last name, but names can be
duplicated. To find a specific individual its much better to use a unique
numeric ID, your Officer ID. To make it easier to identify a particular
individual you can list the full names, but order the list by the last name
by setting up the combo box like so:

RowSource: SELECT DISTINCT [Officer ID], FirstName & " " & LastName AS
FullName FROM [OfficerRoster Query] ORDER BY LastName, FirstName;

BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm
AutoExpand Yes
LimitToList Yes

If your units of measurement are imperial rather than metric Access will
automatically convert the ColumnWidths. The important thing is that the
first dimension is zero to hide the first column and that the second is at
least as wide as the combo box.

As you are now searching on the numeric Officer ID column you'd need to
change the criterion for opening the form in the Click event of the button on
your dialogue form:

strCriteria = "[Officer ID] = " & Me.[cboOfficer]

Ken Sheridan
Stafford, England

"jerseygirl54" wrote:

> Thanks to all in advance for all the help you provide! :-)
>
> I am "green" when it comes to any code so, once again, need real simple
> instructions.
>
> I have a "SearchLastName", which searches a list of names and then opens up
> a person's information. Right now, if the person is not on the list, or if
> the name is misspelled, the error macro code comes up or it brings up a blank
> information box.
>
> I would like to know what and where to put a code so that if someone types a
> name that is not on the list or it is misspelled, a message box will pop up
> to state there is no such name, check the spelling.
>
> Thanks!

From: Ken Sheridan on 4 Jul 2008 18:51
Oops, missed a closing parenthesis:

' only do the search if a name has been selected
If Not IsNull(Me[cboOfficer]

Ken Sheridan
Stafford, England

From: Ken Snell (MVP) on 4 Jul 2008 21:14
Let's change the setup a bit, then, to avoid the Sandbox problem (the error
message about using Recordset in the macro).

On the form that you open from the first macro, put a hidden textbox; name
it txtRecordCount. Make its Control Source expression this:
=[RecordsetClone].[RecordCount]

Now, in the new macro that I suggested you create, do this:

Condition: Forms!Students!txtRecordCount = 0
Action: MsgBox
Message: There is no such person in the database.

Condition: ...
Action: Close
Object Type: Form
Object Name: Students
Save: No

--

Ken Snell
<MS ACCESS MVP>



"jerseygirl54" <jerseygirl54(a)discussions.microsoft.com> wrote in message
news:8D54409E-4A3D-4272-90F3-73DE276B9B23(a)microsoft.com...
> i'm using Access 2007. I actually could not find the macro in the form, it
> was all vb even when I found Tools - Macro. I didn't know what to do, so
> I
> tried to add to my "LastNameSearch" macro. As soon as I entered the last
> line to close and not save, a little yellow triangle with an exclamation
> point appeared on the left. When I tested it, I received the error
> message
> below.
>
> "Ken Snell (MVP)" wrote:
>
>> What version of ACCESS are you using?
>>
>> I assume that you did find the Condition column?
>>
>> --
>>
>> Ken Snell
>> <MS ACCESS MVP>
>>
>>
>> "jerseygirl54" <jerseygirl54(a)discussions.microsoft.com> wrote in message
>> news:D5252D34-08C8-41FA-B964-56B5A7E12F3D(a)microsoft.com...
>> >I place this code onto the "LastNameSearch" Macro and I get this error
>> >when
>> > run:
>> >
>> > "Access failed to eveluate one or more expressions because 'Recordset'
>> > was
>> > referenced in an expression. Only functions and properties that are
>> > considered to be safe are allowed in expressions when Access runs in
>> > sandbox
>> > mode."
>> >
>> > ???
>> >
>> >
>> > "Ken Snell (MVP)" wrote:
>> >
>> >> Yes. Be sure that your macro view is showing you the Condition column
>> >> so
>> >> that you can enter the information for it. Also, the "..." that I
>> >> typed
>> >> for
>> >> the second step's Condition column is exactly what you type. The
>> >> ellipsis
>> >> means that the step is using the same condition as the previous step.
>> >>
>> >> --
>> >>
>> >> Ken Snell
>> >> <MS ACCESS MVP>
>> >>
>> >>
>> >> "jerseygirl54" <jerseygirl54(a)discussions.microsoft.com> wrote in
>> >> message
>> >> news:786BEA34-FF5F-421F-93AA-05294F0E915F(a)microsoft.com...
>> >> > Just a quick question -
>> >> > When I click - "On Load" he VB screen appears. I click on Tools,
>> >> > then
>> >> > Macros and it is allowing me to create a macro. Do I write the
>> >> > macro
>> >> > as
>> >> > you
>> >> > wrote it below? Dumb question, I know! :-(
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>


From: jerseygirl54 on 8 Jul 2008 13:38
Sorry this has taken me so long to answer, but I presented it yesterday at a
staff meeting (went quite well, I may add!) :-)

This actually sounds better than what I had originally wanted. What I did
to get it to work the original way was to turn off the macro security. The
warning does pop up but, it sometimes runs an error.

I am going to try this and will be back to you as soon as I can get it
entered to let you know how it goes.

You are wonderful! Thank you so much! :)

"Ken Sheridan" wrote:

> Oops, missed a closing parenthesis:
>
> ' only do the search if a name has been selected
> If Not IsNull(Me[cboOfficer]
>
> Ken Sheridan
> Stafford, England
>
From: jerseygirl54 on 9 Jul 2008 01:53
I keep getting an error message after this statement. I tried to put he
parenthesis after, before, and still get error. Compile Error: Expected:
list separator or )

I did:
If Not IsNull(Me[cboOfficer])

Then I tried:

If Not IsNull(Me(cboOfficer))

"Ken Sheridan" wrote:

> Oops, missed a closing parenthesis:
>
> ' only do the search if a name has been selected
> If Not IsNull(Me[cboOfficer]
>
> Ken Sheridan
> Stafford, England
>
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: Auto-Fill a Form
Next: Relationships and lookup tables.