Display query results in a table bound form

[gebli]

Hi,

I searched the forum and found lots os thereads discussing how-to display query results in forms, sub-forms, etc. But I still can't figure it out.

I have a form which is bound to a table. I want it this way because at form-open I don't have the query. The query is actually built using the form.
The query works ok because I tested it as standalone.

I guess I am missing some code tying the recordset to the form or something like this.

Trying to display the results in a split form, I noticed I was getting all records in the table, not just the ones that matched the query.

I changed something and now I am also getting a recordset not updateable error!

Can someone please check my code below and point me all my mistakes?

Thanks,

Gerry

[SIZE=3][FONT=Calibri]Private Sub Audit_Click()[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim wrkspc As Workspace[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim WorkBase As Database[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim WorkRS As Recordset[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim SQLstr As String[/FONT][/SIZE]
 
 
[SIZE=3][FONT=Calibri]Set wrkspc = CreateWorkspace("", "admin", "")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Set WorkBase = wrkspc.OpenDatabase("Test.accdb")[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]SQLstr = "SELECT name, id FROM employees WHERE location=”London”[/FONT][/SIZE]
 
 
[SIZE=3][FONT=Calibri]Set WorkRS = WorkBase.OpenRecordset(SQLstr)[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]Do Until WorkRS.EOF[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]  'the following are text boxes to which I try to assign the values[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]  TextName.Value = WorkRS.Fields(0)[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]  TextID.Value = WorkRS.Fields(1)[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]  WorkRS.MoveNext[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Loop[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]WorkRS.Close[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]WorkBase.Close[/FONT][/SIZE]
 
 
[SIZE=3][FONT=Calibri]End Sub[/FONT][/SIZE]

 

[gebli]

(UPDATE)

I managed to display the values, I realized the text boxes needed to be unbound... that's why I was seeing all the records in the table, since the text box was bound to the table.

Now the problem is that the loop goes assigning values to the same variable (the text box) so when I run the report it loops over the same box and I just see the last valid value returned by the query.
I guess the problem now is how to display each value in a diferent row...

Set Forms("Formname").Recordset = WorkRS
Do Until WorkRS.EOF
'text13 is an unbound text box
    Text13.Value = WorkRS.Fields(2)
    WorkRS.MoveNext
Loop

Any suggestions?