IF HELP

[jerseygirl54]

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!

 

[Ken Snell \(MVP\)]

You'll need to post your code so that we can see how you're doing the
search, and then we can provide a suggestion for how to trap for "no such
person".

 

[jerseygirl54]

I have attached a macro to the switchboard that is called "LastNameSearch":
OpenForm
Form Name: Students
Filter Name: OfficerRoster Query

I have created a Query - "OfficerRoster Query" that has:
Officer ID LastName FirstName

and in the "Last Name" Column it is sorted Ascending and then in its
"Criteria" I have the following:

Like "*" &[Enter Last Name]& "*"

 

[Ken Snell \(MVP\)]

Your setup does not lend itself to an easy trapping when the user enters a
name that is not in the table / query. That's because you're having the
query ask for the name via a parameter, and the query cannot trap the error.

However, what you could do is use a macro on the "Students" form's Load
event that tests to see if the form has a record or not, and if not, close
the form and provide a message to the user.

Condition: Forms!Students.Recordset.RecordCount = 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>

I have attached a macro to the switchboard that is called
"LastNameSearch":
OpenForm
Form Name: Students
Filter Name: OfficerRoster Query

I have created a Query - "OfficerRoster Query" that has:
Officer ID LastName FirstName

and in the "Last Name" Column it is sorted Ascending and then in its
"Criteria" I have the following:

Like "*" &[Enter Last Name]& "*"

You'll need to post your code so that we can see how you're doing the
search, and then we can provide a suggestion for how to trap for "no such
person".

 

[jerseygirl54]

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! :-(

Your setup does not lend itself to an easy trapping when the user enters a
name that is not in the table / query. That's because you're having the
query ask for the name via a parameter, and the query cannot trap the error.

However, what you could do is use a macro on the "Students" form's Load
event that tests to see if the form has a record or not, and if not, close
the form and provide a message to the user.

Condition: Forms!Students.Recordset.RecordCount = 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>

I have attached a macro to the switchboard that is called
"LastNameSearch":
OpenForm
Form Name: Students
Filter Name: OfficerRoster Query

I have created a Query - "OfficerRoster Query" that has:
Officer ID LastName FirstName

and in the "Last Name" Column it is sorted Ascending and then in its
"Criteria" I have the following:

Like "*" &[Enter Last Name]& "*"

You'll need to post your code so that we can see how you're doing the
search, and then we can provide a suggestion for how to trap for "no such
person".

--

Ken Snell
<MS ACCESS MVP>


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!

 

[Ken Snell \(MVP\)]

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.

 

[jerseygirl54]

All I get is another VB code window where I have to enter code. I don't see
the "Condiion" column you are referring.

 

[jerseygirl54]

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\)]

What version of ACCESS are you using?

I assume that you did find the Condition column?

 

[jerseygirl54]

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 Sheridan]

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

 

[Ken Sheridan]

Oops, missed a closing parenthesis:

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

Ken Sheridan
Stafford, England

 

[Ken Snell \(MVP\)]

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

 

[jerseygirl54]

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!

 

[jerseygirl54]

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))

 

[John W. Vinson]

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])

Missing a period:

If Not IsNull(Me.[cboOfficer]) Then
<rest of the code>

 

[jerseygirl54]

I still get an error. This time it's:

Compile error: Expected: Then or GoTo

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])

Missing a period:

If Not IsNull(Me.[cboOfficer]) Then
<rest of the code>

 

[jerseygirl54]

Don't know where I went wrong, but I don't have the Roster to pick. I have
the "SELECT DISTINCT" in the unbound combobox Row Source. What could I be
doing wrong?

 

[John W. Vinson]

I still get an error. This time it's:

Compile error: Expected: Then or GoTo

Correct the error in your code. If you would like help doing so please post
your code (multiple lines).

My suggestion was how to correct the *syntax of part of one line*. What I
posted was not the code in its entirity (since I don't know what you're trying
to do it would be a bit hard to do so...)

 

[jerseygirl54]

Dim strCriteria As String

'only do the search if a name has been selected
If Not IsNull(Me.[cboOfficer]) <---This is where I get an error "COMPILE
ERROR: THEN OR GOTO
'build criterion for search
strCriteria = "[Officer ID]=" & 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