Requery subform with user input

[James Houston]

Here's what I want to do: I have a unbound form that has a text box for
user input and a command button, and a subform that is bound to a paramater
query that retrieves all the records in a table based on a last name field.
I want the user to be able to enter in a last name, say Smith, and click on
the command button to retrieve all the Smiths in the table and display the
results as a datasheet view in the subform. The user could then double
click on the subform to edit a record.

My question is this: How do I pass the value of the text box on the main
form to the underlying query for the subform? Thanks in advance.

Jim

 

[John Vinson]

Here's what I want to do: I have a unbound form that has a text box for
user input and a command button, and a subform that is bound to a paramater
query that retrieves all the records in a table based on a last name field.
I want the user to be able to enter in a last name, say Smith, and click on
the command button to retrieve all the Smiths in the table and display the
results as a datasheet view in the subform. The user could then double
click on the subform to edit a record.

My question is this: How do I pass the value of the text box on the main
form to the underlying query for the subform? Thanks in advance.

One handy way to do so is to make the Textbox the Master Link Field
for the subform, and use the corresponding subform recordsource field
as the Child Link Field. No code and no command button needed!

You won't be offered the names of controls by the subform wizard, but
you can simply type the name of the textbox into the Master Link Field
property of the subform.

John W. Vinson[MVP]

 

[Albert D. Kallal]

I actually don't use a parameters, and just stuff the sql right into the
sub-form.
(this is MORE flexible, as then you can change what field is searched by in
the main form).

Build a unbound form. Place a text box near the top. Lets call the control
txtLastName. Turn just about everything off for this form (record selector,
navigation buttons etc). This form is NOT attached to a table, nor any data
(we call this a un-bound form).

In addition to the above, you an build nice continues form that displays the
columns of data in a nice list. you can then put this form as a sub-form
into the above form.

Then, in the txtLastName after update event you simply stuff the results
into that sub-form.

dim strSql as string

strSql = "select * from tblCustomer where " &
" LastName like '" & me.txtLastName & "*'"

me.MySubFormname.Form.RecordSource = strSql.

for the "buttion to click on, you simply put a buttion on the continues
sub-form. The code behind that buttion is:


docdm.OpenForm "frmCustomers",,,"id = " & me!id

There is some screen shots that use the above exact code here:

http://www.members.shaw.ca/AlbertKallal/Search/index.html