record set fill out form

[Arrowx7]

Hello,
I have a record set I open with SQL, and I have lots of fields that are assigned control source names (which are columns in my table)
When I open the record set from a table, science the control source names agree is there a way I can fill all those out instead of going:
Me!fieldOne = myrecordset("fieldOne")
Me!fieldTwo = myrecordset("fieldTwo")
etc etc

That is very tedious and very hard to update the code. Is there a way?

 

[pono1]

I won't pretend to have a solid sense of what you're doing, exactly, but I'll throw out a thought. Bind the form and the controls to the data source, using the query you've created (to create a recordset) in the form's RecordSource property.

Regards,
Tim

 

[RoyVidar]

dim lngCounter as long
for lngCounter=0 to rs.fields.count-1
me.controls(rs.fields(lngCounter).name).value=rs.fields(lngCounter).value
next lngCounter

rs - the recordset, typed not tested...

This means the control names must agree with the field names, not the controlsurce. Since you're unbound, I don't think you should have any controlsource. Might throw in an on error resume next, in case there are more fields in the recordset than there are controls on the form.

btw - how is the progress with
http://www.access-programmers.co.uk/forums/showthread.php?t=82131

 

[Arrowx7]

Thanks Roy,

Pono1, I tried doing that
Me.RecordSource = recordset

It says type mismatch

 

[pono1]

Sorry, Arrow, I wasn't very clear. For future reference, I meant something more like this (not literal):

Me.Recordsource = _
"SELECT MyField1, MyField2, MyField3 " & _
"FROM MyTable " & _
"WHERE "MyField1=" & 100

Or, easier, create the query with the Access Query grid, save it, and then...

Me.Recordsource = MySavedQueryName

Depending on what you're doing exactly, you might also be able to just type the saved query name into the form's RecordSource property when the form is in design mode. No code necessary.

Regards,
Tim