Evaluate Varible and field for input Loop

[damienwjking]

I am having problems evaluating a varible and field for input.
Seems simple in other languages like Java etc. I want to loop through a record set and assign the values to different fields. (i.e. ID1,ID2,ID3,ID4 etc) Code below

i=1
Do While Not rs.EOF
Eval ("Me.ID" & i & " = rs('ContactID')&quot
Eval ("Me.FirstName" & i & " = rs('FirstName')&quot
Eval ("Me.LastName" & i & "= rs('LastName')&quot
rs.MoveNext
i = i + 1
Loop

Does anyone know the syntax for VB or am I going about this the wrong way. Suggestions

 

[p27br]

I would do it like this :

with rs
.movefirst
do
.edit
!field1 = yourvalue
.update
.movenext
loop until .eof

 

[damienwjking]

Thanks for that thats helpful.
I don't really want to edit the data though.
I just want to display all the results on my form.
At the moment it only shows one row.
So the idea is to loop through the record set and put the values into fields on the screeen.(ie. formfeild1,formfield2,formfield3,etc)

with rs
.movefirst
do
formfield & i = rs("Field&quot
i = i + 1
.movenext
loop until .eof

 

[p27br]

hello

I don't really know what you would like to do but if you want the form to display records matching certain criteria ,then you would be better off using an SQL sentence as the form row source.

Another thing, why do you increment the i variable ? It doesn't seem necessary with the movenext method.

 

[damienwjking]

Well, I am a bit new to using recordsets in access.
At the moment a have a form that produces a recordset using SQL.
PROBLEM: if 4 records are returned. How do I display them on the form. I thought of making 4 groups of fields and then looping through the recordset and putting the values to the individual fields (i.e. FieldID1,FieldName1,FieldID2,FieldName2) thus the increment.

Im sure there must be an easy way to display the records returned from the recordset in a form.

Does this make sense????

 

[p27br]

the way to display a recordset on a form is to set the form's recordsource.
Go to form design view. Click Form properties. In the Data tab click in recordsource.then click on the three dot button to the right of this field, it brings up the query builder.
Here you can define your recordset by creating a query. Close the query screen and answer Yes to the question.When you are back in form design, you can place the fields on the form as you wish and then save the form
Go to form view and you should see the recordset.
BE SURE TO SELECT CONTINUOUS FORMS in FORMAT > DEFAULT VIEW
in form design

hope this helps

 

[damienwjking]

Thanks for your help. But Im really trying to use an SQL DAO recordset not a query. Otherwise it wouldn't be a problem.

 

[GHolden]

Hi,

What exactly do you want to do with these records once you've filtered them out.

 

[damienwjking]

Just display them in a form. So if there are 6 records returned the form displays 6 rows of data. If theres only 2 records then it just show 2.
Does this help???

 

[GHolden]

The main problem would seem to be that you don't know how may records are going to be returned. Therefor you need a control that you can dynamically add the SQL results to. There are plenty of these in VB (MSFlexGrid etc.) but Access seems lacking.

You could add these items to a listbox which require building the rowsource in code.

something like

dim strRowSource as string

do while not rs.eof

If strRowSource <> &quot;&quot; Then
strRowSource = strRowSource & &quot;;&quot; & rs!Field1& &quot;;&quot; & rs!Field2 & &quot;;&quot; & rs!Field3
Else
strRowSource = rs!Field1 & &quot;;&quot; & rs!Field2 & &quot;;&quot; & rs!Field3
End If

loop

lstListBox.RowSource = strRowSource

you need to make sure listbox column count is 3

This will display each record as a separate line in a list box

Let me know if this is any help, if not there are other possibilities!!

 

[damienwjking]

Cool. That seems like one possiblity. But I really would prefer it display in seperate text fields as there is quite a few fields.
Maybe there is another way of doing this.
I have now got a recordset returns a number of records then moves to the first record and display it in one set of unbound fields on my form.

I put a movenext and moveprevious button using the code.

rs.MoveNext
and rs.MovePrevious
But I comes up with the error &quot;object required&quot;.

Im trying to either display the returned records in multiple sets of fields or just have a single set of fields and use movenext moveprevious buttons.

Am I making any sense yet.
Helplessly suicidal

 

[GHolden]

You beat me to my next possibility. Single set of boxes, movenext, moveprevious buttons.

It sounds from what you say that it's possible that your recordset is not global so has gone out of scope in your movenext etc, click events.

You need to dim your recordset and db outside of the sub so they are public to all subs in the form.

 

[damienwjking]

Cheers, Thanks everyone for your help. I have got the recordset working by returning one record and then using the movenext moveprevious.
That will do pig, that will do.