Populating a Continuous Form with Unbound Fields

[gazzieh]

I have a continuous form where I have made all the fields unbound. I wish to populate these with data derived from a query.



I have the following code:

Private Sub Form_Load()

    Dim rst As Recordset
    Dim rst2 As Recordset
    
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM tbl_class WHERE course_id=1")
    
    course.Value = rst!course_id
    
    Set rst2 = CurrentDb.OpenRecordset("SELECT * FROM qry_students_by_course WHERE course_id=" & CInt(course.Value))
    
    txt_class.Value = rst2!txt_class_code
    txt_name.Value = rst2!Name
    txt_gender.Value = rst2!txt_gender
    txt_meg.Value = rst2!txt_meg
    txt_target.Value = rst2!txt_target
    txt_school_name.Value = rst2!txt_school_name
    txt_du.Value = rst2!DU
    txt_sen.Value = rst2!SEN
    txt_ethnicity.Value = rst2!txt_ethnicity
    
    rst2.MoveNext
  
End Sub

The form loads and the number of rows equals the number of records I am expecting. The form however is populated only with the first record from the query; so I have about 40 copies of the same record rather than the 40 individual records I am expecting.

I put in the last line (MoveNext) with the thought that the code is being accessed each time by the system but even if this were true, the system would be starting from scratch each time so my question is simple:

How do I make the system populate the entire form correctly?

[June7]

This requires 40 sets of unbound textboxes on unbound form. Name the textboxes like:

txt_class1, txt_class2, ...
txt_name1, txt_name2, ...
...

Then in loop structure:

If Not rst.EOF
For i = 1 to rst.RecordCount
Me.Controls("txt_class" & i) = rst2!txt_class_code
Me.Controls("txt_name" & i) = rst2!Name
...
If Not rst.EOF Then rst.MoveNext
Next
End If

Why do you want this approach as opposed to bound form?

[gazzieh]

Actually, I have opted not to since there is no benefit gained but a lot of headaches earned.

I have the form running based on a query but I wonder if you may be able to help in another issue (same form). In the top section of the continuous form (Form Header) I have a drop down that lists a selection of courses. I would like to have this filter the data in the continuous form based upon the selection but cannot get it to do so. Also, if I do select an item then I cannot deselect an option (I can delete everything but that's not ideal). I would prefer the system to list all the courses (taken from a query) but begin with either [All Courses] or * to allow the selection of all the classes in the course.

Any idea how to filter a continuous form based upon the dropdown data AND place an item at the top of a list taken from the results of a query.

[June7]

That combobox is unbound?

Is the subform linked to main form?

For one method of filtering form, review http://datapigtechnologies.com/flash...tomfilter.html

[Missinglinq]

...I have a continuous form where I have made all the fields unbound. I wish to populate these with data derived from a query.

The form loads and the number of rows equals the number of records I am expecting. The form however is populated only with the first record from the query...

An Unbound Form can only display one Record at a time! To display multiple Records from a Record Source, a Form has to have a Record Source, and a Form with a Record Source is a Bound Form!

Unbound Forms in Access can only be used for Data Entry, and while there are a few reasons that justify their use, the majority of the time Bound Forms should be used. There is very, very little that can be done with Unbound Forms that cannot be done with Bound Forms.

Several developers I know, experienced in Visual Basic database development and Access development, estimate that development, using Unbound Forms, by highly experienced developers, takes two to three times as long, using Unbound Forms, as it does when using Access and Bound Forms.

Anyone who insists on using Unbound Forms would be far better off using a straight VB or C++ front end with a SQL Server or Oracle back end.

• You can create an EXE file which gives total protection to your code/design
  
• You can distribute the db to PCs without a copy of Access being on board
  
• Your data security is far, far better than anything you can do in Access

Linq ;0)>

[gazzieh]

Thanks for all of this.

I use unbound for data entry and was going down a path that I did not need to. I use unbound to stop automatic updating of the data during data input. As a programmer I find it quick and easy to code for this and it puts me, the developer, in full control of what the user can and cannot do.

I am assuming that you are saying that there is a way of having the user update the data visible within the data entry form without this updating the actual data within the table until they have clicked on a Save button or some similar operation?

[June7]

Data entry/edit is committed to table when form closes, move to another record, or run code. Until commitment, entry/edit can be canceled. Explore the Undo method and BeforeUpdate event which has a cancel argument.