Which control should I use to display query data on a form?

[JohnM77 via AccessMonster.com]

I'm developing a project with a series of forms and unbound controls. Right
now I'm using the ListBox control and setting the .RecordSource to display
data from queries. I then use the ListBox_Click event to populate textboxes
with data from the selected row. The problem is that the ListBox loses the
flexibility of being able to resize columns and sort by fields, as is
possible when viewing data directly in a table or query.

I chose unbound so that I could have a "save" button on each form which
performs validation prior to any data manipulation. Is there a better control
to serve this purpose of allowing the user to sort and size columns while
keeping the textboxes (used for editing data) unbound?

Thanks for your help!

 

[Dale_Fye via AccessMonster.com]

Although I rarely use it, you might want to consider a datasheet or a
continuous form. You could set either of these up so that all the controls
in the main portion of the form are locked, so they cannot be edited. You
would have to do some pretty serious coding to allow sizing of fields in a
continuous form , but the datasheet would allow this.

You could then put the unbound controls in the forms footer and use the
Current event to populate those controls with the values from the currently
selected record.

HTH
Dale

 

[JohnM77 via AccessMonster.com]

Thanks for your reply, Dale. I'm unsure of how to add the controls to the
footer of my datasheet. Do I need to somehow nest the datasheet in a seperate
form? I've only used forms in Access in a similar manner to in VB6, where the
form is just one field (without header/footer).

Thanks!

Although I rarely use it, you might want to consider a datasheet or a
continuous form. You could set either of these up so that all the controls
in the main portion of the form are locked, so they cannot be edited. You
would have to do some pretty serious coding to allow sizing of fields in a
continuous form , but the datasheet would allow this.

You could then put the unbound controls in the forms footer and use the
Current event to populate those controls with the values from the currently
selected record.

HTH
Dale

I'm developing a project with a series of forms and unbound controls. Right
now I'm using the ListBox control and setting the .RecordSource to display

[quoted text clipped - 9 lines]

Thanks for your help!

 

[Dale_Fye via AccessMonster.com]

I fyou open the datasheet form in design view, then Right click anywhere
inside the form, you should see an option for "form header/footer" in the
popup menu. Just click that option and you will see the header and footer
areas.

HTH
Dale

Thanks for your reply, Dale. I'm unsure of how to add the controls to the
footer of my datasheet. Do I need to somehow nest the datasheet in a seperate
form? I've only used forms in Access in a similar manner to in VB6, where the
form is just one field (without header/footer).

Thanks!

Although I rarely use it, you might want to consider a datasheet or a
continuous form. You could set either of these up so that all the controls

[quoted text clipped - 14 lines]

 

[JohnM77 via AccessMonster.com]

It seems that design view will not display the datasheet. When I attempt to
go from Datasheet to Design, it automatically switches to design of Form view.
In design of Form view, I can add controls to the form header/footer, but
when I run the form, it loads in Form view (properly displaying footer
controls). Switching to Datasheet view hides the header/footer, thereby
hiding the related controls. In summary, it seems that header/footer controls
are only available in Form view. Am I missing something?

Here's a reference to what they're calling a "split form" in Access 2007.
It's very similar to what I'm trying to do. I'm gathering that it's not
possible in 2003?

http://office.microsoft.com/en-us/access/HA100759941033.aspx

I fyou open the datasheet form in design view, then Right click anywhere
inside the form, you should see an option for "form header/footer" in the
popup menu. Just click that option and you will see the header and footer
areas.

HTH
Dale

Thanks for your reply, Dale. I'm unsure of how to add the controls to the
footer of my datasheet. Do I need to somehow nest the datasheet in a seperate

[quoted text clipped - 8 lines]

 

[Dale_Fye via AccessMonster.com]

Correct, 2003 does not have split forms.

Didn't actually try the header and footer in datasheet view. Sorry.

With that in mind, you could create a subform that is in datasheet view, and
then add that subform to another form. Then add the controls you want to
edit to the main form.

Dale

It seems that design view will not display the datasheet. When I attempt to
go from Datasheet to Design, it automatically switches to design of Form view.
In design of Form view, I can add controls to the form header/footer, but
when I run the form, it loads in Form view (properly displaying footer
controls). Switching to Datasheet view hides the header/footer, thereby
hiding the related controls. In summary, it seems that header/footer controls
are only available in Form view. Am I missing something?

Here's a reference to what they're calling a "split form" in Access 2007.
It's very similar to what I'm trying to do. I'm gathering that it's not
possible in 2003?

http://office.microsoft.com/en-us/access/HA100759941033.aspx

I fyou open the datasheet form in design view, then Right click anywhere
inside the form, you should see an option for "form header/footer" in the

[quoted text clipped - 9 lines]

 

[JohnM77 via AccessMonster.com]

Dale, thanks again for your thoughts on this. I'm working with the subform
concept now. I'm trying to figure out how to duplicate the Current even
behavior in the subform, but the subform only has Enter/Exit events. Do you
have any ideas on how I would populate my main form controls with the subform
table values when a record is selected in the subform table?

Many thanks,
John

Correct, 2003 does not have split forms.

Didn't actually try the header and footer in datasheet view. Sorry.

With that in mind, you could create a subform that is in datasheet view, and
then add that subform to another form. Then add the controls you want to
edit to the main form.

Dale

It seems that design view will not display the datasheet. When I attempt to
go from Datasheet to Design, it automatically switches to design of Form view.

[quoted text clipped - 15 lines]

 

[Dale_Fye via AccessMonster.com]

John,

If you set the SourceObject of the subform control, then you will actually
see your subform inside the main form. Click the small square in the upper
left corner of the subform to put the focus on that "form". Then display the
Event properties for the subform. Click inside the Current event and set
that to [Event Procedure]. Then click on the ...

Once inside the code window for the Current event you can do something like:

Private Sub Form_Current

me.parent.txt_UnboundField1 = me.txtField1
me.parent.txt_UnboundField2 = me.txtField2

End Sub

Then, on the main form, if you have an "Update" button, you can do something
like:

Private Sub cmd_Update

'Put some field validation code here

'If field validation code is passed, then write the new values to those
fields
me.subformControlName.Form.txtField1 = me.txt_UnboundField1
me.subformControlName.Form.txtField2 = me.txt_UnboundField2

'Another way to do this would be to build a SQL Update string that
'you would execute to update the particular record from the subform.
'If you do that, then you would need to requery the subform and move
'the record pointer to the record you just finished editing.

End Sub

You might even want to consider disabling the subform once you start editing
text in any of the unbound controls of your main form. Have Cancel and
Update buttons on that form. If the users selects the Cancel button, clean
out the unbound controls and enable the subform. If the user selects Update,
enable the subform, and write the changes to it.

HTH
Dale

Dale, thanks again for your thoughts on this. I'm working with the subform
concept now. I'm trying to figure out how to duplicate the Current even
behavior in the subform, but the subform only has Enter/Exit events. Do you
have any ideas on how I would populate my main form controls with the subform
table values when a record is selected in the subform table?

Many thanks,
John

Correct, 2003 does not have split forms.

[quoted text clipped - 11 lines]

 

[JohnM77 via AccessMonster.com]

I have it working just as you described. Thanks so much for your help! I
appreciate your time.

-John

John,

If you set the SourceObject of the subform control, then you will actually
see your subform inside the main form. Click the small square in the upper
left corner of the subform to put the focus on that "form". Then display the
Event properties for the subform. Click inside the Current event and set
that to [Event Procedure]. Then click on the ...

Once inside the code window for the Current event you can do something like:

Private Sub Form_Current

me.parent.txt_UnboundField1 = me.txtField1
me.parent.txt_UnboundField2 = me.txtField2

End Sub

Then, on the main form, if you have an "Update" button, you can do something
like:

Private Sub cmd_Update

'Put some field validation code here

'If field validation code is passed, then write the new values to those
fields
me.subformControlName.Form.txtField1 = me.txt_UnboundField1
me.subformControlName.Form.txtField2 = me.txt_UnboundField2

'Another way to do this would be to build a SQL Update string that
'you would execute to update the particular record from the subform.
'If you do that, then you would need to requery the subform and move
'the record pointer to the record you just finished editing.

End Sub

You might even want to consider disabling the subform once you start editing
text in any of the unbound controls of your main form. Have Cancel and
Update buttons on that form. If the users selects the Cancel button, clean
out the unbound controls and enable the subform. If the user selects Update,
enable the subform, and write the changes to it.

HTH
Dale

Dale, thanks again for your thoughts on this. I'm working with the subform
concept now. I'm trying to figure out how to duplicate the Current even

[quoted text clipped - 10 lines]