Macro to do a search

[Guest]

Yes, I'm back again. I'll relay what I need rather than relay all the ways I
have been trying to do this. I have a many-to-one data base with a
form-subform arrangement related by an auto number and using a combo box to
search on name which brings up matching records, if any, before entering the
data. I'm using the form exclusively for editing and data input. The forms
to be keyed may already be in the file, but because of almost illegible hand
writing, I want to make a search on phone number and retireve the data before
I decide to key a new record. Soooo, what I need is a command button on my
form which would invoke a Search Macro on the file and give me the choice of
pulling up any matching phone numbers into the main form/subform. If no
match, then tell me no match and return to where I was. One problem I had in
trying this was that my macro didn't wait until after I keyed the phone
number before starting the search. I'm afraid I need help. Thanks.

 

[tina]

hi again, Tom. here's a suggestion: just add a second combo box right
below, or right beside, your existing cboFindClient combo box. use the
wizard to build it, as before; only this time, select the PhoneNo. field to
search on, instead of the name field(s). i've often used more than one "find
a record in my form" combo box on a single form, to give my users more than
one search option.

hth

 

[Guest]

You came through again tina. Now why didn't I think of that. You saved me
another day at least. I played all afternoon trying to figure that one out.
By now, you have given me a pretty good input form. I did give up on the
sequencing of my child records on date. Of course there was no row source
that I could easily use like we did on the parent, and then I tried using a
query as a table for my child subform, but that didn't work either.
Actually, I'm pretty well satisfied now with the whole thing. Thanks again
and I'll give you a well done also.
Tom

 

[tina]

hmm, a little bit of VBA code should get a sort to work for you in the
subform. try adding the following code in the subform's module; try it first
in the OnLoad event, and if that doesn't work, then try it on the OnOpen
event instead, as

Me.OrderBy = "NameOfDateField"
Me.OrderByOn = True

the above will give you an Ascending sort. if you want a Descending sort,
change the code to

Me.OrderBy = "NameOfDateField DESC"
Me.OrderByOn = True

if you don't know how to create an event procedure in the form's module,
post back and i'll give you directions (it's easy to do).

hth

 

[Guest]

hmm. I really didn't expect a response to my last question, but thought i
would just check back in case there was something there. I had given up on
the sorting of the activity records because it is not a necessity, but just a
convenience. But I'll try your advice anyway and I'm sure it will work! Now
I am trying to do something else! Here I go again. My LastName and FirstName
are separate fields in my combo box to search for names. Is it possible to
concatenate those two separate fields into a single field in my combo box.
Here again, it is not a necessity, but just an easier way to see if I have
the correct person - I would still want to key only the first few letters as
I do now. Now I won't feel bad if you skip this one. But thanks loasds for
all your prior help.
Tom

 

[Guest]

OOps. I should have read your complete post before I signed off on my
previous comment. I thought I might have known how to do this event, but I
don't guess I do. I guessed that I would go to the Activity subform and
click on the form properties where I do see the Onopen. I also see the ...
which takes me to build expression, macro expression or code builder. I
don't know what to do next. Help again. Thanks.

 

[tina]

comments inline.

OOps. I should have read your complete post before I signed off on my
previous comment. I thought I might have known how to do this event, but I
don't guess I do. I guessed that I would go to the Activity subform and
click on the form properties where I do see the Onopen. I also see the ....
which takes me to build expression, macro expression or code builder.

good, you got it right so far - especially making sure that you're in the
Activity (sub)form, and *not* in the main form. one thing: go to the OnLoad
property first (we won't try OnOpen unless OnLoad doesn't work).

I
don't know what to do next.

now, in the Choose Builder dialog box, click on Code Builder and click OK.
Access creates the event procedure, and takes you to it, in the VBA window.
paste in the code i gave you in previous post; when you've done that, it
should look like below:

Private Sub Form_Load()

Me.OrderBy = "NameOfDateField"
Me.OrderByOn = True

End Sub

remember to substitute the correct name of your date field.

hth

 

[tina]

open the form in design view and click on the combo box control once to
select it. in the Properties box, click in the RowSource line, then click
the ellipsis (...) button at the right. now you're in the design grid that
looks like query design. i'm guessing that right now, you have the two
fields for FirstName and LastName in the grid. remove them. in the first
empty "column", enter the following on the Field: line, as

FullName: LastName & ", " & FirstName

this is called a calculated field. you can sort it ascending or descending,
if you like. close the grid window and save the changes. reminder: you now
have one less column in the combo box than you did before, so you need to
adjust the ColumnCount property to reflect the change.

hth

 

[Guest]

tina,
Thanks again and again and again. You have helped me immensely. I miss the
office environment where there were individuals around who could answer
questions. Sitting here in a vacuum, the inspirations do not come through.
But with this website, I do have a warm, cozy, comfy feeling that I can get
the answers. Thanks to you and all the others who have helped me.
Tom

 

[Guest]

tina,
I'm sorry to bother you again, but I noticed that I have a problem with new
records. Remember, we put a name sort in rowsource of the combo box to
display the names in sequence. But when I key a new record, it doesn't get
sorted back with the others until after I close the file. The potential
problem is that I could key duplicate records in one session. Is there an
easy remedy for this? Thanks.
"tom"

 

[Guest]

tina,
the Onload worked great! Access is great but you are the greatest in
finding all these solutions for me. Thanks again.
tom

 

[tina]

add either a macro or an event procedure to the form's OnCurrent event, as

[macro]
Action: Requery
Control Name: cboFindClient

or
[procedure]
Me!cboFindClient.Requery

in either instance, use the correct name of the combo box, of course.
if you want the new record to show up in the phone number combo box also,
simply add a second macro action, or second line of code, using the name of
that combo box.

hth

 

[Guest]

thanks tina. I'll try this when I get back from a short visit.
tom

add either a macro or an event procedure to the form's OnCurrent event, as

[macro]
Action: Requery
Control Name: cboFindClient

or
[procedure]
Me!cboFindClient.Requery

in either instance, use the correct name of the combo box, of course.
if you want the new record to show up in the phone number combo box also,
simply add a second macro action, or second line of code, using the name of
that combo box.

hth

tina,
I'm sorry to bother you again, but I noticed that I have a problem with new
records. Remember, we put a name sort in rowsource of the combo box to
display the names in sequence. But when I key a new record, it doesn't get
sorted back with the others until after I close the file. The potential
problem is that I could key duplicate records in one session. Is there an
easy remedy for this? Thanks.
"tom"

 

[tina]

you're welcome
btw, don't be afraid to explore macro actions. an easy way is to open a
macro in design view, choose an Action from the droplist, leave the cursor
in the Action field, and press F1. takes you right to the topic in Access
Help. i taught myself Access and developed databases for a few years before
taking Access courses in night school - and i spent tons of time in Help,
reading up on macro actions, form properties, everything really, trying to
figure out how to use them to accomplish what i wanted. this was years
before i ever knew anything about newsgroups, or even the Internet. there's
a wealth of information right at your fingertips, it just takes time and
determination to use it.

hth

thanks tina. I'll try this when I get back from a short visit.
tom

add either a macro or an event procedure to the form's OnCurrent event, as

[macro]
Action: Requery
Control Name: cboFindClient

or
[procedure]
Me!cboFindClient.Requery

in either instance, use the correct name of the combo box, of course.
if you want the new record to show up in the phone number combo box also,
simply add a second macro action, or second line of code, using the name of
that combo box.

hth

tina,
I'm sorry to bother you again, but I noticed that I have a problem

with
new

records. Remember, we put a name sort in rowsource of the combo box to
display the names in sequence. But when I key a new record, it

doesn't
get

sorted back with the others until after I close the file. The potential
problem is that I could key duplicate records in one session. Is there an
easy remedy for this? Thanks.
"tom"

:

comments inline.

OOps. I should have read your complete post before I signed off on my
previous comment. I thought I might have known how to do this

event,
but

I
don't guess I do. I guessed that I would go to the Activity

subform
and

click on the form properties where I do see the Onopen. I also

see
the

....
which takes me to build expression, macro expression or code builder.

good, you got it right so far - especially making sure that you're

in
the

Activity (sub)form, and *not* in the main form. one thing: go to

the
OnLoad

property first (we won't try OnOpen unless OnLoad doesn't work).

I
don't know what to do next.

now, in the Choose Builder dialog box, click on Code Builder and

click
OK.

Access creates the event procedure, and takes you to it, in the VBA window.
paste in the code i gave you in previous post; when you've done that, it
should look like below:

Private Sub Form_Load()

Me.OrderBy = "NameOfDateField"
Me.OrderByOn = True

End Sub

remember to substitute the correct name of your date field.

hth


Help again. Thanks.

:

hmm, a little bit of VBA code should get a sort to work for you

in
the

subform. try adding the following code in the subform's module;

try
it

first
in the OnLoad event, and if that doesn't work, then try it on

the
OnOpen

event instead, as

Me.OrderBy = "NameOfDateField"
Me.OrderByOn = True

the above will give you an Ascending sort. if you want a Descending
sort,
change the code to

Me.OrderBy = "NameOfDateField DESC"
Me.OrderByOn = True

if you don't know how to create an event procedure in the form's module,
post back and i'll give you directions (it's easy to do).

hth


You came through again tina. Now why didn't I think of that. You
saved me
another day at least. I played all afternoon trying to figure that
one
out.
By now, you have given me a pretty good input form. I did give

up
on

the
sequencing of my child records on date. Of course there was

no
row

source
that I could easily use like we did on the parent, and then I tried
using
a
query as a table for my child subform, but that didn't work either.
Actually, I'm pretty well satisfied now with the whole thing. Thanks
again
and I'll give you a well done also.
Tom

:

hi again, Tom. here's a suggestion: just add a second combo box
right
below, or right beside, your existing cboFindClient combo

box.
use

the
wizard to build it, as before; only this time, select the PhoneNo.
field
to
search on, instead of the name field(s). i've often used

more
than

one
"find
a record in my form" combo box on a single form, to give my users
more
than
one search option.

hth


Yes, I'm back again. I'll relay what I need rather than

relay
all

the
ways I
have been trying to do this. I have a many-to-one data

base
with

a
form-subform arrangement related by an auto number and using a
combo
box
to
search on name which brings up matching records, if any, before
entering
the
data. I'm using the form exclusively for editing and data input.
The
forms
to be keyed may already be in the file, but because of almost
illegible
hand
writing, I want to make a search on phone number and

retireve
the

data
before
I decide to key a new record. Soooo, what I need is a command
button
on my
form which would invoke a Search Macro on the file and

give me
the

choice
of
pulling up any matching phone numbers into the main form/subform.
If
no
match, then tell me no match and return to where I was. One
problem I
had
in
trying this was that my macro didn't wait until after I

keyed
the

phone
number before starting the search. I'm afraid I need help.
Thanks.

 

[Guest]

I'm back. OK, I want to include the Me!cboFindClient.Requery in the event
procedure in the OnCurrent properties of the main form. (My combo box name
for finding name is Combo50.) I looked at the OnCurrent event ...Code
Builder and it has some other procedures already there, I guess from the
inclusion of Combo Box's. Anyway, my question is do I include the procedure
"Me!Combo50.Requery" between the statements "Private Sub Form_Current () and
End Sub"??? Now I can also do it as a macro but I haven't looked at that yet.
Thanks for all the other technical advice.
tom

you're welcome
btw, don't be afraid to explore macro actions. an easy way is to open a
macro in design view, choose an Action from the droplist, leave the cursor
in the Action field, and press F1. takes you right to the topic in Access
Help. i taught myself Access and developed databases for a few years before
taking Access courses in night school - and i spent tons of time in Help,
reading up on macro actions, form properties, everything really, trying to
figure out how to use them to accomplish what i wanted. this was years
before i ever knew anything about newsgroups, or even the Internet. there's
a wealth of information right at your fingertips, it just takes time and
determination to use it.

hth

thanks tina. I'll try this when I get back from a short visit.
tom

add either a macro or an event procedure to the form's OnCurrent event, as

[macro]
Action: Requery
Control Name: cboFindClient

or
[procedure]
Me!cboFindClient.Requery

in either instance, use the correct name of the combo box, of course.
if you want the new record to show up in the phone number combo box also,
simply add a second macro action, or second line of code, using the name of
that combo box.

hth


tina,
I'm sorry to bother you again, but I noticed that I have a problem with
new
records. Remember, we put a name sort in rowsource of the combo box to
display the names in sequence. But when I key a new record, it doesn't
get
sorted back with the others until after I close the file. The potential
problem is that I could key duplicate records in one session. Is there an
easy remedy for this? Thanks.
"tom"

:

comments inline.

OOps. I should have read your complete post before I signed off on my
previous comment. I thought I might have known how to do this event,
but
I
don't guess I do. I guessed that I would go to the Activity subform
and
click on the form properties where I do see the Onopen. I also see
the
....
which takes me to build expression, macro expression or code builder.

good, you got it right so far - especially making sure that you're in
the
Activity (sub)form, and *not* in the main form. one thing: go to the
OnLoad
property first (we won't try OnOpen unless OnLoad doesn't work).

I
don't know what to do next.

now, in the Choose Builder dialog box, click on Code Builder and click
OK.
Access creates the event procedure, and takes you to it, in the VBA
window.
paste in the code i gave you in previous post; when you've done that, it
should look like below:

Private Sub Form_Load()

Me.OrderBy = "NameOfDateField"
Me.OrderByOn = True

End Sub

remember to substitute the correct name of your date field.

hth


Help again. Thanks.

:

hmm, a little bit of VBA code should get a sort to work for you in
the
subform. try adding the following code in the subform's module; try
it
first
in the OnLoad event, and if that doesn't work, then try it on the
OnOpen
event instead, as

Me.OrderBy = "NameOfDateField"
Me.OrderByOn = True

the above will give you an Ascending sort. if you want a Descending
sort,
change the code to

Me.OrderBy = "NameOfDateField DESC"
Me.OrderByOn = True

if you don't know how to create an event procedure in the form's
module,
post back and i'll give you directions (it's easy to do).

hth


You came through again tina. Now why didn't I think of that. You
saved me
another day at least. I played all afternoon trying to figure
that
one
out.
By now, you have given me a pretty good input form. I did give up
on
the
sequencing of my child records on date. Of course there was no
row
source
that I could easily use like we did on the parent, and then I
tried
using
a
query as a table for my child subform, but that didn't work
either.
Actually, I'm pretty well satisfied now with the whole thing.
Thanks
again
and I'll give you a well done also.
Tom

:

hi again, Tom. here's a suggestion: just add a second combo box
right
below, or right beside, your existing cboFindClient combo box.
use
the
wizard to build it, as before; only this time, select the
PhoneNo.
field
to
search on, instead of the name field(s). i've often used more
than
one
"find
a record in my form" combo box on a single form, to give my
users
more
than
one search option.

hth


Yes, I'm back again. I'll relay what I need rather than relay
all
the
ways I
have been trying to do this. I have a many-to-one data base
with
a
form-subform arrangement related by an auto number and using a
combo
box
to
search on name which brings up matching records, if any,
before
entering
the
data. I'm using the form exclusively for editing and data
input.
The
forms
to be keyed may already be in the file, but because of almost
illegible
hand
writing, I want to make a search on phone number and retireve
the
data
before
I decide to key a new record. Soooo, what I need is a command
button
on my
form which would invoke a Search Macro on the file and give me
the
choice
of
pulling up any matching phone numbers into the main
form/subform.
If
no
match, then tell me no match and return to where I was. One
problem I
had
in
trying this was that my macro didn't wait until after I keyed
the
phone
number before starting the search. I'm afraid I need help.
Thanks.

 

[tina]

if you already have VBA code running in the form's OnCurrent event, then
you'll need to add the Requery there, not in a macro. yes, just add it
between the Private Sub and End Sub lines. since i don't know what else is
running in the procedure, i can't say with certainty, but with a fair degree
of confidence, that you can add it after the last line of code in the
procedure.

hth

I'm back. OK, I want to include the Me!cboFindClient.Requery in the event
procedure in the OnCurrent properties of the main form. (My combo box name
for finding name is Combo50.) I looked at the OnCurrent event ...Code
Builder and it has some other procedures already there, I guess from the
inclusion of Combo Box's. Anyway, my question is do I include the procedure
"Me!Combo50.Requery" between the statements "Private Sub Form_Current () and
End Sub"??? Now I can also do it as a macro but I haven't looked at that yet.
Thanks for all the other technical advice.
tom

you're welcome
btw, don't be afraid to explore macro actions. an easy way is to open a
macro in design view, choose an Action from the droplist, leave the cursor
in the Action field, and press F1. takes you right to the topic in Access
Help. i taught myself Access and developed databases for a few years before
taking Access courses in night school - and i spent tons of time in Help,
reading up on macro actions, form properties, everything really, trying to
figure out how to use them to accomplish what i wanted. this was years
before i ever knew anything about newsgroups, or even the Internet. there's
a wealth of information right at your fingertips, it just takes time and
determination to use it.

hth

thanks tina. I'll try this when I get back from a short visit.
tom

:

add either a macro or an event procedure to the form's OnCurrent

event,
as

[macro]
Action: Requery
Control Name: cboFindClient

or
[procedure]
Me!cboFindClient.Requery

in either instance, use the correct name of the combo box, of course.
if you want the new record to show up in the phone number combo box also,
simply add a second macro action, or second line of code, using the

name
of

that combo box.

hth


tina,
I'm sorry to bother you again, but I noticed that I have a problem with
new
records. Remember, we put a name sort in rowsource of the combo

box
to

display the names in sequence. But when I key a new record, it doesn't
get
sorted back with the others until after I close the file. The potential
problem is that I could key duplicate records in one session. Is there an
easy remedy for this? Thanks.
"tom"

:

comments inline.

OOps. I should have read your complete post before I signed

off
on my

previous comment. I thought I might have known how to do this event,
but
I
don't guess I do. I guessed that I would go to the Activity subform
and
click on the form properties where I do see the Onopen. I

also
see

the
....
which takes me to build expression, macro expression or code builder.

good, you got it right so far - especially making sure that

you're
in

the
Activity (sub)form, and *not* in the main form. one thing: go

to
the

OnLoad
property first (we won't try OnOpen unless OnLoad doesn't work).

I
don't know what to do next.

now, in the Choose Builder dialog box, click on Code Builder and click
OK.
Access creates the event procedure, and takes you to it, in the VBA
window.
paste in the code i gave you in previous post; when you've done that, it
should look like below:

Private Sub Form_Load()

Me.OrderBy = "NameOfDateField"
Me.OrderByOn = True

End Sub

remember to substitute the correct name of your date field.

hth


Help again. Thanks.

:

hmm, a little bit of VBA code should get a sort to work for

you
in

the
subform. try adding the following code in the subform's

module;
try

it
first
in the OnLoad event, and if that doesn't work, then try it

on
the

OnOpen
event instead, as

Me.OrderBy = "NameOfDateField"
Me.OrderByOn = True

the above will give you an Ascending sort. if you want a Descending
sort,
change the code to

Me.OrderBy = "NameOfDateField DESC"
Me.OrderByOn = True

if you don't know how to create an event procedure in the form's
module,
post back and i'll give you directions (it's easy to do).

hth


You came through again tina. Now why didn't I think of

that.
You

saved me
another day at least. I played all afternoon trying to figure
that
one
out.
By now, you have given me a pretty good input form. I did

give
up

on
the
sequencing of my child records on date. Of course there

was
no

row
source
that I could easily use like we did on the parent, and then I
tried
using
a
query as a table for my child subform, but that didn't work
either.
Actually, I'm pretty well satisfied now with the whole thing.
Thanks
again
and I'll give you a well done also.
Tom

:

hi again, Tom. here's a suggestion: just add a second

combo
box

right
below, or right beside, your existing cboFindClient

combo
box.

use
the
wizard to build it, as before; only this time, select the
PhoneNo.
field
to
search on, instead of the name field(s). i've often used more
than
one
"find
a record in my form" combo box on a single form, to give my
users
more
than
one search option.

hth


Yes, I'm back again. I'll relay what I need rather

than
relay

all
the
ways I
have been trying to do this. I have a many-to-one

data
base

with
a
form-subform arrangement related by an auto number and using a
combo
box
to
search on name which brings up matching records, if any,
before
entering
the
data. I'm using the form exclusively for editing and data
input.
The
forms
to be keyed may already be in the file, but because of almost
illegible
hand
writing, I want to make a search on phone number and retireve
the
data
before
I decide to key a new record. Soooo, what I need is a command
button
on my
form which would invoke a Search Macro on the file and give me
the
choice
of
pulling up any matching phone numbers into the main
form/subform.
If
no
match, then tell me no match and return to where I

was.
One

problem I
had
in
trying this was that my macro didn't wait until after

I
keyed

the
phone
number before starting the search. I'm afraid I need help.
Thanks.

 

[Guest]

tina,

Thanks tina. It worked fine. I hope this will be the last time I bother
you all on this particular project. As you can tell, I don't do this for a
living. I'm retired and I volunteered to do this in connection with a data
entry project at my Church. Thank you for getting me through this. Your
efforts have been above and beyond, and I sincerely thank you very, very
much. May you and all your co-workers have a wonderful Holiday Season.
tom

if you already have VBA code running in the form's OnCurrent event, then
you'll need to add the Requery there, not in a macro. yes, just add it
between the Private Sub and End Sub lines. since i don't know what else is
running in the procedure, i can't say with certainty, but with a fair degree
of confidence, that you can add it after the last line of code in the
procedure.

hth

I'm back. OK, I want to include the Me!cboFindClient.Requery in the event
procedure in the OnCurrent properties of the main form. (My combo box name
for finding name is Combo50.) I looked at the OnCurrent event ...Code
Builder and it has some other procedures already there, I guess from the
inclusion of Combo Box's. Anyway, my question is do I include the procedure
"Me!Combo50.Requery" between the statements "Private Sub Form_Current () and
End Sub"??? Now I can also do it as a macro but I haven't looked at that yet.
Thanks for all the other technical advice.
tom

you're welcome
btw, don't be afraid to explore macro actions. an easy way is to open a
macro in design view, choose an Action from the droplist, leave the cursor
in the Action field, and press F1. takes you right to the topic in Access
Help. i taught myself Access and developed databases for a few years before
taking Access courses in night school - and i spent tons of time in Help,
reading up on macro actions, form properties, everything really, trying to
figure out how to use them to accomplish what i wanted. this was years
before i ever knew anything about newsgroups, or even the Internet. there's
a wealth of information right at your fingertips, it just takes time and
determination to use it.

hth


thanks tina. I'll try this when I get back from a short visit.
tom

:

add either a macro or an event procedure to the form's OnCurrent event,
as

[macro]
Action: Requery
Control Name: cboFindClient

or
[procedure]
Me!cboFindClient.Requery

in either instance, use the correct name of the combo box, of course.
if you want the new record to show up in the phone number combo box
also,
simply add a second macro action, or second line of code, using the name
of
that combo box.

hth


tina,
I'm sorry to bother you again, but I noticed that I have a problem
with
new
records. Remember, we put a name sort in rowsource of the combo box
to
display the names in sequence. But when I key a new record, it
doesn't
get
sorted back with the others until after I close the file. The
potential
problem is that I could key duplicate records in one session. Is
there an
easy remedy for this? Thanks.
"tom"

:

comments inline.

OOps. I should have read your complete post before I signed off
on my
previous comment. I thought I might have known how to do this
event,
but
I
don't guess I do. I guessed that I would go to the Activity
subform
and
click on the form properties where I do see the Onopen. I also
see
the
....
which takes me to build expression, macro expression or code
builder.

good, you got it right so far - especially making sure that you're
in
the
Activity (sub)form, and *not* in the main form. one thing: go to
the
OnLoad
property first (we won't try OnOpen unless OnLoad doesn't work).

I
don't know what to do next.

now, in the Choose Builder dialog box, click on Code Builder and
click
OK.
Access creates the event procedure, and takes you to it, in the VBA
window.
paste in the code i gave you in previous post; when you've done
that, it
should look like below:

Private Sub Form_Load()

Me.OrderBy = "NameOfDateField"
Me.OrderByOn = True

End Sub

remember to substitute the correct name of your date field.

hth


Help again. Thanks.

:

hmm, a little bit of VBA code should get a sort to work for you
in
the
subform. try adding the following code in the subform's module;
try
it
first
in the OnLoad event, and if that doesn't work, then try it on
the
OnOpen
event instead, as

Me.OrderBy = "NameOfDateField"
Me.OrderByOn = True

the above will give you an Ascending sort. if you want a
Descending
sort,
change the code to

Me.OrderBy = "NameOfDateField DESC"
Me.OrderByOn = True

if you don't know how to create an event procedure in the form's
module,
post back and i'll give you directions (it's easy to do).

hth


You came through again tina. Now why didn't I think of that.
You
saved me
another day at least. I played all afternoon trying to figure
that
one
out.
By now, you have given me a pretty good input form. I did give
up
on
the
sequencing of my child records on date. Of course there was
no
row
source
that I could easily use like we did on the parent, and then I
tried
using
a
query as a table for my child subform, but that didn't work
either.
Actually, I'm pretty well satisfied now with the whole thing.
Thanks
again
and I'll give you a well done also.
Tom

:

hi again, Tom. here's a suggestion: just add a second combo
box
right
below, or right beside, your existing cboFindClient combo
box.
use
the
wizard to build it, as before; only this time, select the
PhoneNo.
field
to
search on, instead of the name field(s). i've often used
more
than
one
"find
a record in my form" combo box on a single form, to give my
users
more
than
one search option.

hth


Yes, I'm back again. I'll relay what I need rather than
relay
all
the
ways I
have been trying to do this. I have a many-to-one data
base
with
a
form-subform arrangement related by an auto number and
using a
combo
box
to
search on name which brings up matching records, if any,
before
entering
the
data. I'm using the form exclusively for editing and data
input.
The
forms
to be keyed may already be in the file, but because of
almost
illegible
hand
writing, I want to make a search on phone number and
retireve
the
data
before
I decide to key a new record. Soooo, what I need is a
command
button
on my
form which would invoke a Search Macro on the file and
give me
the
choice
of
pulling up any matching phone numbers into the main
form/subform.
If
no
match, then tell me no match and return to where I was.
One
problem I
had
in
trying this was that my macro didn't wait until after I
keyed
the
phone
number before starting the search. I'm afraid I need
help.
Thanks.

 

[tina]

you're very welcome, and Merry Christmas!

tina,

Thanks tina. It worked fine. I hope this will be the last time I bother
you all on this particular project. As you can tell, I don't do this for a
living. I'm retired and I volunteered to do this in connection with a data
entry project at my Church. Thank you for getting me through this. Your
efforts have been above and beyond, and I sincerely thank you very, very
much. May you and all your co-workers have a wonderful Holiday Season.
tom

if you already have VBA code running in the form's OnCurrent event, then
you'll need to add the Requery there, not in a macro. yes, just add it
between the Private Sub and End Sub lines. since i don't know what else is
running in the procedure, i can't say with certainty, but with a fair degree
of confidence, that you can add it after the last line of code in the
procedure.

hth

I'm back. OK, I want to include the Me!cboFindClient.Requery in the event
procedure in the OnCurrent properties of the main form. (My combo box name
for finding name is Combo50.) I looked at the OnCurrent event ...Code
Builder and it has some other procedures already there, I guess from the
inclusion of Combo Box's. Anyway, my question is do I include the procedure
"Me!Combo50.Requery" between the statements "Private Sub Form_Current

()
and

End Sub"??? Now I can also do it as a macro but I haven't looked at

that
yet.

Thanks for all the other technical advice.
tom

:

you're welcome
btw, don't be afraid to explore macro actions. an easy way is to open a
macro in design view, choose an Action from the droplist, leave the cursor
in the Action field, and press F1. takes you right to the topic in Access
Help. i taught myself Access and developed databases for a few years before
taking Access courses in night school - and i spent tons of time in Help,
reading up on macro actions, form properties, everything really,

trying
to

figure out how to use them to accomplish what i wanted. this was years
before i ever knew anything about newsgroups, or even the Internet. there's
a wealth of information right at your fingertips, it just takes time and
determination to use it.

hth


thanks tina. I'll try this when I get back from a short visit.
tom

:

add either a macro or an event procedure to the form's OnCurrent event,
as

[macro]
Action: Requery
Control Name: cboFindClient

or
[procedure]
Me!cboFindClient.Requery

in either instance, use the correct name of the combo box, of course.
if you want the new record to show up in the phone number combo box
also,
simply add a second macro action, or second line of code, using

the
name

of
that combo box.

hth


tina,
I'm sorry to bother you again, but I noticed that I have a problem
with
new
records. Remember, we put a name sort in rowsource of the

combo
box

to
display the names in sequence. But when I key a new record, it
doesn't
get
sorted back with the others until after I close the file. The
potential
problem is that I could key duplicate records in one session. Is
there an
easy remedy for this? Thanks.
"tom"

:

comments inline.

OOps. I should have read your complete post before I

signed
off

on my
previous comment. I thought I might have known how to do this
event,
but
I
don't guess I do. I guessed that I would go to the Activity
subform
and
click on the form properties where I do see the Onopen. I also
see
the
....
which takes me to build expression, macro expression or code
builder.

good, you got it right so far - especially making sure that you're
in
the
Activity (sub)form, and *not* in the main form. one thing:

go
to

the
OnLoad
property first (we won't try OnOpen unless OnLoad doesn't work).

I
don't know what to do next.

now, in the Choose Builder dialog box, click on Code Builder and
click
OK.
Access creates the event procedure, and takes you to it, in

the
VBA

window.
paste in the code i gave you in previous post; when you've done
that, it
should look like below:

Private Sub Form_Load()

Me.OrderBy = "NameOfDateField"
Me.OrderByOn = True

End Sub

remember to substitute the correct name of your date field.

hth


Help again. Thanks.

:

hmm, a little bit of VBA code should get a sort to work

for
you

in
the
subform. try adding the following code in the subform's module;
try
it
first
in the OnLoad event, and if that doesn't work, then try

it
on

the
OnOpen
event instead, as

Me.OrderBy = "NameOfDateField"
Me.OrderByOn = True

the above will give you an Ascending sort. if you want a
Descending
sort,
change the code to

Me.OrderBy = "NameOfDateField DESC"
Me.OrderByOn = True

if you don't know how to create an event procedure in

the
form's

module,
post back and i'll give you directions (it's easy to do).

hth


You came through again tina. Now why didn't I think

of
that.

You
saved me
another day at least. I played all afternoon trying

to
figure

that
one
out.
By now, you have given me a pretty good input form. I

did
give

up
on
the
sequencing of my child records on date. Of course

there
was

no
row
source
that I could easily use like we did on the parent, and then I
tried
using
a
query as a table for my child subform, but that didn't work
either.
Actually, I'm pretty well satisfied now with the whole thing.
Thanks
again
and I'll give you a well done also.
Tom

:

hi again, Tom. here's a suggestion: just add a

second
combo

box
right
below, or right beside, your existing cboFindClient combo
box.
use
the
wizard to build it, as before; only this time,

select
the

PhoneNo.
field
to
search on, instead of the name field(s). i've often used
more
than
one
"find
a record in my form" combo box on a single form, to

give
my

users
more
than
one search option.

hth


Yes, I'm back again. I'll relay what I need

rather
than

relay
all
the
ways I
have been trying to do this. I have a many-to-one data
base
with
a
form-subform arrangement related by an auto number and
using a
combo
box
to
search on name which brings up matching records,

if
any,

before
entering
the
data. I'm using the form exclusively for editing

and
data

input.
The
forms
to be keyed may already be in the file, but because of
almost
illegible
hand
writing, I want to make a search on phone number and
retireve
the
data
before
I decide to key a new record. Soooo, what I need is a
command
button
on my
form which would invoke a Search Macro on the file and
give me
the
choice
of
pulling up any matching phone numbers into the main
form/subform.
If
no
match, then tell me no match and return to where I was.
One
problem I
had
in
trying this was that my macro didn't wait until

after
I

keyed
the
phone
number before starting the search. I'm afraid I need
help.
Thanks.