search results output in form

[Han]

I have successfully created a form with a text box (txtLastName) and command
button to execute a search. The command button executes a sub routine which
opens a connection to the database and executes a query using the value from
the text box:

"SELECT LastName FROM Contacts WHERE LastName LIKE """ & Me.txtLastName &
"*"""

I would now like to output all matching records in a list. Ideally this list
will appear in the same form, below the text box and command button. By
default, I don't want anything to appear in the list area until a search has
been executed. If a subsequent search is executed, the existing list will
clear and the new results will be displayed.

How can this be accomplished?

Thanks,
Han

 

[Ivar svedsen]

Hello.

Create a list box where you want the search results, and
set the RowSource to this query:

PARAMETER txtLastName TEXT; SELECT LastName FROM Contacts
WHERE LastName LIKE [txtLastName] & "*";


Then make the commandbutton call something like:

ResultList.Requery


Regards,
Ivar Svendsen.

 

[Emilia Maxim]

I have successfully created a form with a text box (txtLastName) and command
button to execute a search. The command button executes a sub routine which
opens a connection to the database and executes a query using the value from
the text box:

"SELECT LastName FROM Contacts WHERE LastName LIKE """ & Me.txtLastName &
"*"""

I would now like to output all matching records in a list. Ideally this list
will appear in the same form, below the text box and command button. By
default, I don't want anything to appear in the list area until a search has
been executed. If a subsequent search is executed, the existing list will
clear and the new results will be displayed.

Han,

you could create a subform in continuous form view. In the command
button's OnClick event you could then assign the above query SQL to
the subform's RecordSource property, something like this:

Dim strSQL As String

strSQL = "SELECT LastName FROM Contacts WHERE LastName LIKE """ &
Me!txtLastName & "*"""

Me![MySubformControl].Form.RecordSource = strSQL

To be able to set the ControlSource of the controls on the subform
form when creating it, you could set the RecordSource property of the
subform form in design view to something like:

SELECT LastName FROM Contacts WHERE LastName = "XXX"

or some other criteria returning 0 records.

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de