Sub form with Grid and want to display detail on form above grid (1 Viewer)

[Laurad]

I want to be able to have a form that has a grid (datasheet view) below, using the same data (i.e., one query).

The idea is that the grid shows the entire query (all fields) other than the memo field which I want to display above the grid. When the user scrolls through the grid, the memo field changes according to which record is selected. Obvioulsy if I use the navigation buttons, then I move onto the next record/memo field. I would actually prefer to not have the navigation buttons at all, but rather require that records are selected from the grid.

I've tried creating a subform for the grid/datasheet view and putting it on the main form with just a memo field. When I scroll through the grid, the memo field does not "refresh" along with the grid. I want the user to be able to click on a record in the grid and immediately display the memo field for that record above it. Is this possible? I hope I've been clear.

Many thanks
Laura

 

[stopher]

See the attached example. Take a look at the form frmPaymentsReview and click on the different lines (bottom subform). The memo field (top subform should change accordingly.

I've just used the On Current event in the dataview subform to set a different filter for the memo subform.

You could easily add other subforms in the same way e.g. Customer details

I had to implement an error trap for 2455 because it seems that when the form is first opened, the On Current event seems to fire before the other form is ready.

hth
Chris

 

[vbaInet]

I had to implement an error trap for 2455 because it seems that when the form is first opened, the On Current event seems to fire before the other form is ready.

Hi Chris,

I've not looked at your db but from what you described the subform normally loads before the parent form. So what you can do is to remove [Event Procedure] from the Current event, and set it in the load event of the main form, i.e.

Me.SubformControl.Form.OnCurrent = "[Event Procedure]"

 

[stopher]

I've not looked at your db

Why not?

but from what you described the subform normally loads before the parent form.

That certain makes sense.

So what you can do is to remove [Event Procedure] from the Current event, and set it in the load event of the main form, i.e.

Me.SubformControl.Form.OnCurrent = "[Event Procedure]"

I chose the OnCurrent because it's firing code depending on the record selected (clicked, tabbed to, navigated to or however) from a datasheet list. I don't see how the OnLoad would provide that joy unless I'm missing your point.

The OnClick is another option but again it just does do what OnCurrent does i.e. caters for the different ways to select a record.

Chris

 

[vbaInet]

I will don't worry

Here's what I mean. The Current Event in the property sheet is cleared and it's set in the main form's On Load event.

There are other ways of filtering the subform though.

 

[stopher]

Here's what I mean. The Current Event in the property sheet is cleared and it's set in the main form's On Load event.

Cool. Not seen that before.

There are other ways of filtering the subform though.

Yes it would be interesting to see how different ways this problem could be solved.

 

[Laurad]

Thank you both so much. I have been working through your examples and the first will probably work - I am about to test it, but at the risk of showing my ignorance, I wonder what vbaInet means by

Me.Parent.sbfrmPaymentsList.Form.OnCurrent = "[Event Procedure]"

What is the "Event Procedure" in this case? I didn't understand.

ETA.. yes, it works (first option adding code to the On Current Event of the form withthe datasheet) and it's simple and slick and I'm so grateful. I was getting in a muddle trying to put the datasheet subform onto a main form with the memo field, but that's where I went wrong. I didn't create two sub forms to go on a main form which didn't have a datasource.

 

[vbaInet]

Yes it would be interesting to see how different ways this problem could be solved.

Hey Chris, I've attached the db showing three other methods.

I wonder what vbaInet means by

Me.Parent.sbfrmPaymentsList.Form.OnCurrent = "[Event Procedure]"

What is the "Event Procedure" in this case? I didn't understand.

That line simply "reactivates" the On Current event of the subform so it can fire only when the parent form has loaded. As already mentioned, a subform loads before the parent form and if you're trying to call a property or method of a subform from another subform it will fail. So in order to bypass the error we activate the On Current event of the subform only when the main form is loading. Notice that even there there's code in the On Current event of the subform, the word [Event Procedure] is not in the event on the property sheet.

 

[Laurad]

Thank you. I'm trying to get my head round your explanation of the "Event Procedure" method, hopefully it will sink in.

I've looked at each of your examples, thank you for taking the time to work them out and demonstrate them too. It would seem that Method 4 was what I was trying to achieve myself, but failed to do. I should have worked it out and got close to having done so, but just haven't got the right skills yet. I will now have to decide on the best one for my needs as I want to show a few other fields (not just the memo field), but it will all fall into place now.

Thank you again.
Laura

 

[vbaInet]

...

I will now have to decide on the best one for my needs as I want to show a few other fields (not just the memo field), but it will all fall into place now.

I think method 3 or 1 would be your best bet since they are much faster and you can house as many controls as you wish in the subform.

Good luck!

 

[Laurad]

I've been trying to apply the third example as you suggested and ran into one problem, the subform on the top containing the "comment" or memo field was not appearing with any data in it when I first load the form but does update correctly when I click on a record on the data sheet subform below. After numerous attempts at recreating everything and beginning to tear my hair out, I wondered if the order in which you place the two subforms on the main form is important, i.e., do they load according to the order you place them when designing?

I had put the "comment" subform on first. I removed it saved the form, closed it then added it to the top above the data sheet subform, ran the form and the data appeared as the form opened.

So I've made progress, thank you.

Not sure if I should open a new thread but following on from this, is it possible for the user to click on the top row of headings in the data sheet and instantly change the sort order?

Also, I was going to put this form with it's two sub forms on a tabbed control of another form, but I think I might be nesting too many sub forms, is there a limit on how many you can nest?

Thanks again
Laura

 

[vbaInet]

I had put the "comment" subform on first. I removed it saved the form, closed it then added it to the top above the data sheet subform, ran the form and the data appeared as the form opened.

By changing the Tab Order of that section of your form you could have achieved this more quickly.

Not sure if I should open a new thread but following on from this, is it possible for the user to click on the top row of headings in the data sheet and instantly change the sort order?

It is possible, but how do you determine whether it should be an ascending or descending sort? Are you using Datasheet View or Continuous?

What about using the built-in Sorting menus?

Also, I was going to put this form with it's two sub forms on a tabbed control of another form, but I think I might be nesting too many sub forms, is there a limit on how many you can nest?

http://support.microsoft.com/kb/214651

 

[Laurad]

Thanks I thought about the "tab order" after I had achieved success - in other words, at the time I didn't know why it wasn't working and was just trying different things.

Also, after I left my post I went hunting for a solution to the fact that I know that the person I am writing the database for will want to be able to click on the datasheet view (not continuous form) to sort it in any column they wish and discovered that it's quite easy - they just need to reight click and they get a sub menu with sort ascending or descending options - job done, no need to do any programming, so I'm happy about that too and they can learn to right-click.

Also, instead of using a main form and two sub forms, I just put the two sub forms on an empty tab control and it works well, I'm very pleased with the result, so a big thank you.

Laura

 

[vbaInet]

Good to hear you managed to get it all sorted.

Glad we could help!