search help

[eddy3459]

I have a form which contain 2 unbound text boxes and a subform. 1st
text box is where I input client's ID and 2nd textbox is for client's
name. I have set the child and master field for subform to clients ID
on subform and 1st testbox on main form. This works fine if I only
have to search by client's ID.

My problem is how do I make the subform to requery if ,say the client
forget his ID # or search using ID produce no result, and I have to
search by name using 2nd textbox as a search string.
I tried changing the master and child field link using code but that
didn't work...like:
me.subform.Form.MasterField = me.txtbox1

there must be a simpler solution to this but I now I don't have a
clue...

any help on this is very much appreciated....thank you

 

[Al Campagna]

Eddy,
Use just one 2-column combobox. (ex. cboFindClient). Fisrt coiumn = ClientID, second
= ClientName.
(ClientName can be the concatenation of LastNme & ", " & FirstName)

Combo setup... (Using an Unbound combo)...
RowSource = All the ClientIDs and Names from your Client Table
NoOfColumns = 2
ColumnWidths = 0" ; 2" (adjust 2" to suit)
ListWidth = 2"
LimitToList = True

This combo will display the ClientName, allow the user to select a Client Name, and
display that name after selection. But... it really stores the ClientID in the unbound
field cboFindClient.
Now, using the AfterUpdate event of cboFindClient...

DoCmd.GoToControl "ClientID"
DoCmd.FindRecord cboFindClient

This will go to the Client Record selected in cboFindClient.

On my website (below) in Access Tips, I have a sample 97 and 2003 file that
demonstrates this technique. (QuickFindCombo)
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[eddy3459]

Eddy,
Use just one 2-column combobox. (ex. cboFindClient). Fisrt coiumn = ClientID, second
= ClientName.
(ClientName can be the concatenation of LastNme & ", " & FirstName)

Combo setup... (Using an Unbound combo)...
RowSource = All the ClientIDs and Names from your Client Table
NoOfColumns = 2
ColumnWidths = 0" ; 2" (adjust 2" to suit)
ListWidth = 2"
LimitToList = True

This combo will display the ClientName, allow the user to select a Client Name, and
display that name after selection. But... it really stores the ClientID in the unbound
field cboFindClient.
Now, using the AfterUpdate event of cboFindClient...

DoCmd.GoToControl "ClientID"
DoCmd.FindRecord cboFindClient

This will go to the Client Record selected in cboFindClient.

On my website (below) in Access Tips, I have a sample 97 and 2003 file that
demonstrates this technique. (QuickFindCombo)
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVPhttp://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

thank you for your reply.....

I've looked at your sample db and I think it can work fine if you have
tens or hundred of clients,but in my case i have a table with
thousands of clients. It could be very tedious to scroll thru and find
the clients by their name like that....

So what I want to do is display result in subform which is base on a
query as its record source. When a user input clients ID theres no
problem. if clients have registered before the result will be
displayed, if not clients is registerd as new. Problem is sometimes
client forget their ID # so user has to find the clients ID through
their name using wildcard search string...the result displayed could
be many and user has to scroll thru until they find the right
one...otherwise client is registerd new

hope you get the picture..thanks again anyway

 

[Al Campagna]

Eddy,
You don't have to "scroll" to find a name in a combo box. As you type each character
the Auto Expand feature starts "drilling down" to find the correct name.
I've used this method with >28,000 records, and the auto expand feature keeps pace
faster than I can type.
You're call....
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[eddy3459]

Eddy,
You don't have to "scroll" to find a name in a combo box. As you type each character
the Auto Expand feature starts "drilling down" to find the correct name.
I've used this method with >28,000 records, and the auto expand feature keeps pace
faster than I can type.
You're call....
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVPhttp://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Okay I'll give a try and I'll let you know if it works in my case....I
have to record around that much as you have stated...if if it works in
your case, it should work with mine....thanx again

 

[Al Campagna]

Can't hurt... doesn't take long to test, and beats creating an individual subform just
for that...
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[eddy3459]

Can't hurt... doesn't take long to test, and beats creating an individual subform just
for that...
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVPhttp://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

yes it works just like you said...
sorry it took this long to reply, its just that only this past days i
had a chance to look at it again
Thanx again