Search for a record by typing in a text box

[Trina]

Hi

I Had found the solution to my problem through the forums but then
accidently deleted the macro that made it all work and now cant find the
solution that I orignally found so I am hoping some one can enlighten me.

The problem:-

to be able to search for a record by typing in part of the name and having
the "like" names being returned in a list box which when selected opens up a
form displaying all the details for that record.

The solution so far:-
I have a form called [Extra Search Module] which has an unbound text box
which the user types in the name they are looking for. called txtsearch.

I have an unbound list box whose criteria is set to:

SELECT [Account Table].[Account Number], [Account Table].[Account Name]
FROM [Account Table] WHERE ((([Account Table].[Account Name]) Like "*" &
Forms![Extra Search Module]!txtsearch & "*"));

This works just fine.

When I select a record in the list box and click a command button on the
form it opens up the account detail form as required.

The problem:-

The macro that I deleted returned the results in the list box based on the
text typed in the text box and I have tried to rebuild but cant seem to get
it. this is built in Access 2000

Can someone please let me know how to rebuild the macro.

 

[ken]

I wouldn't bother with a macro for this as it only needs one line of
code in the AfterUpdate event procedure of the txtsearch text box:

Me.[YourListBoxNameGoesHere].Requery

To do this select the txtsearch control in form design view and open
its properties sheet if its not already open. Then select the After
Update 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 line of code between these two existing lines.

BTW deleting an object accidentally does show how important it is to
back up your files regularly and incrementally, so that even if you've
backed up since the deletion you can go back to an earlier back up to
retrieve the file containing the lost object. Any decent back up
product provides means of automating this.

Ken Sheridan
Stafford, England

 

[Trina]

Thank you so much for your reply that works brilliantly.

And yes ... great folly not having a back up, I shall get on to it straight
away.

I wouldn't bother with a macro for this as it only needs one line of
code in the AfterUpdate event procedure of the txtsearch text box:

Me.[YourListBoxNameGoesHere].Requery

To do this select the txtsearch control in form design view and open
its properties sheet if its not already open. Then select the After
Update 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 line of code between these two existing lines.

BTW deleting an object accidentally does show how important it is to
back up your files regularly and incrementally, so that even if you've
backed up since the deletion you can go back to an earlier back up to
retrieve the file containing the lost object. Any decent back up
product provides means of automating this.

Ken Sheridan
Stafford, England

Hi

I Had found the solution to my problem through the forums but then
accidently deleted the macro that made it all work and now cant find the
solution that I orignally found so I am hoping some one can enlighten me.

The problem:-

to be able to search for a record by typing in part of the name and having
the "like" names being returned in a list box which when selected opens up a
form displaying all the details for that record.

The solution so far:-
I have a form called [Extra Search Module] which has an unbound text box
which the user types in the name they are looking for. called txtsearch.

I have an unbound list box whose criteria is set to:

SELECT [Account Table].[Account Number], [Account Table].[Account Name]
FROM [Account Table] WHERE ((([Account Table].[Account Name]) Like "*" &
Forms![Extra Search Module]!txtsearch & "*"));

This works just fine.

When I select a record in the list box and click a command button on the
form it opens up the account detail form as required.

The problem:-

The macro that I deleted returned the results in the list box based on the
text typed in the text box and I have tried to rebuild but cant seem to get
it. this is built in Access 2000

Can someone please let me know how to rebuild the macro.

 

[JAMBROZ]

Hi

I Had found the solution to my problem through the forums but then
accidently deleted the macro that made it all work and now cant find the
solution that I orignally found so I am hoping some one can enlighten me.

The problem:-

to be able to search for a record by typing in part of the name and having
the "like" names being returned in a list box which when selected opens up
a
form displaying all the details for that record.

The solution so far:-
I have a form called [Extra Search Module] which has an unbound text box
which the user types in the name they are looking for. called txtsearch.

I have an unbound list box whose criteria is set to:

SELECT [Account Table].[Account Number], [Account Table].[Account Name]
FROM [Account Table] WHERE ((([Account Table].[Account Name]) Like "*" &
Forms![Extra Search Module]!txtsearch & "*"));

This works just fine.

When I select a record in the list box and click a command button on the
form it opens up the account detail form as required.

The problem:-

The macro that I deleted returned the results in the list box based on the
text typed in the text box and I have tried to rebuild but cant seem to
get
it. this is built in Access 2000

Can someone please let me know how to rebuild the macro.