Continuous Forms & Recordsets

[matzos]

Hello all!

I have a recordset that I need to populate into a continuous form. When I try the normal methods to set the textboxes equal to the recordset fields in the VBA I'm only getting the last record on the form, not the 200+ records that are in the recordset.

I'm sure there is a line of code I am missing to "movenext" in the detail section of the form but I have no idea what it is and the help is useless.

Please help. Thanks!

 

[Aerin]

Check the default view property of the form. Probably you forgot to change it from the single form to Continuous form?

Why do you think that VBA method is normal?

 

[matzos]

The form is set to Continuous. If I put the SQL query into an ACCESS query and make that the RecordSource property on the form, it shows me all of the query result records with no problem. However, I can't update any of the records because of the query links. My ultimate goal is to be able to show the query results, allow for changes and then save the changes back to the main data table involved in the query.

That's why I was looking to use a recordset to just fill in the data without locking it to a table/query, allow updates and then save them.

If anyone has a better suggestion on how to do this, I would REALLY appreciate it.

 

[willir]

matzos

recordset that I need to populate into a continuous form.

Two things here...
- Have you used a MoveNext until the end of the file - see sample code.
- More imporatantly, are you sure you can use a record set to populate a form. You may be just over-writing the from with each successive value.

With rst
     .MoveFirst

     Do While Not .EOF
         'Action stuff

         .MoveNext
     Loop
End With

I can't update any of the records because of the query links.

I think this is the real issue. With Access, assuming you have a one-to-many relationship (the most common type of relationship), you usually can only update the ONE side or the MANY side but not both.

You may wish to re-think your approach. Perhaps have a couple of subforms kept in synch, one for each "query link".

You can change the RecordSource of the a subform quite easily, espcially from the main form...

Me.YourSubForm.Form.RecordSource = YourNewSQL

This type of approach allows you to re-populate the subform per specific requirements.

Richard

 

[matzos]

I've tried all of these suggestions without any clear improvment.

What aobut this...is there a way to create a control array on the fly based on the number of records I have in a recordset? I know you can do this in VB6 but can it be done in Access 2000?

 

[willir]

Matzos

Access VBA does support arrays, but using an array to populate an unbound contineous subform would be problematic.

Why does the RecordSource not work? I have always been able to "come up with something".

Since you have not had success yet, why not post your query, your curent (appropriate) form(s), your requriements and perhaps some details on your table structure...

Richard

 

[abig99]

Best solution for this is to make a temporary table which the form is bound to, dump all your data into the temporary table. This gives you an editable version of your records, when you have finished making changes edit the actual table and add your changes back in.