Selection Problems

[Awes]

I am developing a form where eventually the user enters a surname into a combobox 'FindSurname' on the form, which is based on a query, and then clicks on a button 'Select'. This will then run some code and the, display all occurences of the selected surname in a subform 'subCustDets', which is set up to display in datasheet view.

As I haven't done this before I am building the form up slowly so
that any problems that occur are easier to find and correct.

To start with all I want to do is click on the 'Select' button, to
run the code, and display all the records from the table containing
the names 'T_Customers' which has the following fields:

Cust_Id
Title
Initial
Surname

The code I'm using is as follows:

Dim MyDb As Database
Dim Recset As Recordset

Set MyDb = CurrentDb
Set Recset = MyDb.OpenRecordset("SELECT ALL T_Customers.Cust_ID, " & _
"T_Customers.Title, T_Customers.Initial, T_Customers.Surname " & _
"FROM T_Customers")

Me!subCustDets!Cust_ID = Recset!Cust_ID
Me!subCustDets!Title = Recset!Title
Me!subCustDets!Initial = Recset!Initial
Me!subCustDets!Surname = Recset!Surname

The result is that the subform displays the first record from the
table 'T_Customers' but none of the other records, currently 9.

What am I doing wrong??

To complete the process what form should the WHERE element of the query take place so that it gets the name entered into 'FindSurname' and only displays the matching records in the subform??

I think it should be:

.....FROM T_Customers WHERE T_Customers.Surname = Me!FindSurname

but would like somebody to confirm that for me.

Many thanks for taking an interest and for any feedback you provide.

 

[Rich]

Why not just use a stored Query, set the criteria to the combo box and Requery the subform on the after update of the combo

 

[Awes]

Re: Selection problem

Hi Rich

I tried that but had problems defining the criteria statement as it kept giving me and input box in which to put my choice whereas I want it to take it from the form.

Perhaps you could be good enough to refresh my memory on how to refer to a text box/combobox on a form in a query.

Many thanks for your help

Awes.

 

[Rich]

[Forms]![MyFormName]![MyComboName]

 

[Awes]

Cheers Rich

I was missing the [Forms] bit.

It now works as required.

I would still be interested in how to do it all by VBA if anyone has the answer.

TTFN