subform setup

[TheShabz]

Hi all


I'm working on a customer service ticket application and need a little help.

controls involved:
cmbRequestType: Combobox that holds the request types
lstClosedReq: Listbox that displays all closed request by request type from cmbRequest
subViewClosed: subform that displays the notes per request highlighted in lstClosedReq

The purpose is for a user to be able to go back to all the old data requests submitted and review the comments and progress as needed. This works fine until I get to a request type that does not contain any closed requests. lstClosedReq will display no records as it should. However, subViewClosed will show the notes of the last valid closed request. What I am trying to do is have subViewClosed be empty as well.

Me.subViewClosed.Requery is what I tried but the query that it is bound to has lstClosedReq as criteria. If there is nothing selected, it is not changing the requestID.

My current idea is on the after update event for the combobox to check the rowcount of the listbox and write the query for the subform on the fly. However, I'd prefer to have as little SQL in VBA as possible. Any other ideas?

Thanks.

[Rod]

Shame on us; four days and no response!

In the following I assume that cmbRequestType and lstClosedRequest are on the same form. It doesn't make much difference if they're not except how you address them. (BTW FYI the standard Reddick prefix for a combobox is 'cbo.' It doesn't matter too much if you're consistent.)

In my experience when the bound value of a listbox is used as the Link Master Fields value for a subform then, after a requery of the listbox that results in no rows, the bound value retains its previous value, all other columns are set to null. (Bit of a mouthful - you may have to read it a couple of times. ) This explains the behaviour you are experiencing but does not help much in giving you the user interface you want.

So you need:

1. Some mechanism that traps the condition on the master form;
2. Some action that hides or 'blanks-out the subform.

For 1: Testing lstClosedRequest for null doesn't work since as explained above the listbox retains its previous value. Testing other columns, if there are any, for null will work but the most intuitive way is to see how many rows are in the recordset associated with the listbox. If coding in the master form module you can test the following for being greater than zero.

Me.lstClosedRequest.Recordset.RecordCount

(To give you all the information - that you don't need right now - the above does not necessarily give you an accurate count of the rows; it gives you only the number of rows that Access has so far retrieved. To make sure the number is accurate you must issue a MoveLast and then examine the record count. However I can't image the number of rows of a listbox being so large that Access uses tranches.)

So, in the AfterUpdate event coding of the combobox I imagine there is something like Me.lstClosedRequest.Requery. Immediately after this insert:

If Me.lstClosed.Request.Recordset.RecordCount > 0 then
<take some special action with the subform>
Else
<undo special action if necessary>
End If

For 2: So what is this special action? The answer depends upon what is acceptable to you and/or your users. By far the simplest action is to hide the subform and I will assume this solution. Hence the completed code fragment is:

If Me.lstClosed.Request.Recordset.RecordCount > 0 then
Me.subViewClosed.Visible = False
Else
Me.subViewClosed.Visible = True
End If

If you want guidance on more complex actions then post back.

Some parting thoughts:

a. The standard Reddick prefix for a subform control is 'sfr.'

b. The code you used to requery the subform actually requeries the control that holds the subform. The correct code is

Me.subViewClosed.Form.Requery

not that it will do much good in this particular case.

[TheShabz]

Similar to what I was thinking. At least I was on the right track. This will be very helpful. Thanks.

And as far as the prefixes go, I use different ones for a few things to identify my code. I've had people try to pass off work that they screwed up on to me claiming it was my code. I have a few built-in measures to disprove things like that.

[Rod]

IMO as long as you're consistent it doesn't really matter. I use 'btn' for command buttons and reserve 'cmd' for ADO commands.

An alternative action is to force lstClosedReq to null. It may be a good idea to disable the subform if this is not already done. This solution preserves the 'look' of your composite form and has the effect you want.