|
|
|
date: Thu, 3 Jul 2008 13:50:00 -0700,
group: microsoft.public.access.gettingstarted
back
Re: IF HELP
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>
"jerseygirl54" wrote in message
news:0DFB44CE-5713-4D02-A8A2-6966A4F3F839@microsoft.com...
> 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!
date: Thu, 3 Jul 2008 17:03:58 -0400
author: Ken Snell \(MVP\) etl
Re: IF HELP
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)" wrote:
> 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>
>
>
> "jerseygirl54" wrote in message
> news:0DFB44CE-5713-4D02-A8A2-6966A4F3F839@microsoft.com...
> > 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!
>
>
>
date: Thu, 3 Jul 2008 14:22:01 -0700
author: jerseygirl54
Re: IF HELP
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>
"jerseygirl54" wrote in message
news:B3F64A74-9B8B-4ADE-A36B-E72B79293D43@microsoft.com...
>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)" wrote:
>
>> 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>
>>
>>
>> "jerseygirl54" wrote in message
>> news:0DFB44CE-5713-4D02-A8A2-6966A4F3F839@microsoft.com...
>> > 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!
>>
>>
>>
date: Thu, 3 Jul 2008 17:43:15 -0400
author: Ken Snell \(MVP\) etl
Re: IF HELP
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! :-(
"Ken Snell (MVP)" wrote:
> 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>
>
>
>
> "jerseygirl54" wrote in message
> news:B3F64A74-9B8B-4ADE-A36B-E72B79293D43@microsoft.com...
> >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)" wrote:
> >
> >> 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>
> >>
> >>
> >> "jerseygirl54" wrote in message
> >> news:0DFB44CE-5713-4D02-A8A2-6966A4F3F839@microsoft.com...
> >> > 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!
> >>
> >>
> >>
>
>
>
date: Thu, 3 Jul 2008 16:32:01 -0700
author: jerseygirl54
RE: IF HELP
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!
date: Fri, 4 Jul 2008 15:46:00 -0700
author: Ken Sheridan
Re: IF HELP
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" wrote in message
news:8D54409E-4A3D-4272-90F3-73DE276B9B23@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" wrote in message
>> news:D5252D34-08C8-41FA-B964-56B5A7E12F3D@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" wrote in
>> >> message
>> >> news:786BEA34-FF5F-421F-93AA-05294F0E915F@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! :-(
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>
date: Fri, 4 Jul 2008 21:14:40 -0400
author: Ken Snell \(MVP\) etl
Re: IF HELP
I don't have an error any more, BUT...(for testing purposes) I entered part
of a name. It then asks me for an officer ID, which one would not have if
they are searching by name. I don't enter a number, then I get a list of
possible matches. Problem is that I am unable to select any name from the
list. I have to reenter the name, spelled correctly in order for it to pull
it up. Also, if you misspell a name or enter a name that is not on the list,
it does not give notification (message box) suggesting that you might have
spelled the name wrong as there is no such name in the list.
"John W. Vinson" wrote:
> On Sun, 13 Jul 2008 21:53:01 -0700, jerseygirl54
> wrote:
>
> Thanks. You need a THEN, just as the error message suggested. Try
>
> Dim strCriteria As String
>
> 'only do the search if a name has been selected
> If Not IsNull(Me.[cboOfficer]) Then
> 'build criterion for search
> strCriteria = "[Officer ID]=" & Me.[cboOfficer]
> ' This assumes that Officer ID is a Number datatype
> ' if it is Text instead use
> ' 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
> --
>
> John W. Vinson [MVP]
>
date: Mon, 14 Jul 2008 14:52:02 -0700
author: jerseygirl54
|
|
