All of a sudden no controls will work on my form

[DavidGeorge]

I have an unbound form with three list boxes. The selections made in
these list boxes 'filter' the records that appear in an unbound
subform. The subform record source is a query which looks at the
values in the three list boxes.

It was working, but while tweaking it a little something went wrong
and now nothing on the form will respond to any mouse clicks. The
only way I can get out is to switch back to design view.

I suspect that I have done something simple, but I can't find the
cause. Does anyone have any ideas of what might casue the form to
suddenly stop responding?

David

[Allen Browne]

Make sure the form's AllowEdits property is Yes.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"DavidGeorge" <da*********@hotmail.comwrote in message
news:af**********************************@k1g2000p rb.googlegroups.com...

>I have an unbound form with three list boxes. The selections made in
these list boxes 'filter' the records that appear in an unbound
subform. The subform record source is a query which looks at the
values in the three list boxes.

It was working, but while tweaking it a little something went wrong
and now nothing on the form will respond to any mouse clicks. The
only way I can get out is to switch back to design view.

I suspect that I have done something simple, but I can't find the
cause. Does anyone have any ideas of what might casue the form to
suddenly stop responding?

David

[DavidGeorge]

My earlier reply seems not to be appearing. Apologies for the double
post if it does turn up.

Allow Edits is set to 'Yes' so I'm afraid that's not it Allen.
However, looking at the Documenter, it lists a property 'Allow
Updating' which is shown as 'No'. I can't find that property in the
Form's property list. Does this give you any clues?

David

[Allen Browne]

Very strange. AllowUpdating seems to be an artifact from the distant past:
http://support.microsoft.com/kb/120483

Unless the form was imported from an old version, or you are actually using
an old verion, I can't explain it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"DavidGeorge" <da*********@hotmail.comwrote in message
news:ab**********************************@s9g2000p rm.googlegroups.com...

My earlier reply seems not to be appearing. Apologies for the double
post if it does turn up.

Allow Edits is set to 'Yes' so I'm afraid that's not it Allen.
However, looking at the Documenter, it lists a property 'Allow
Updating' which is shown as 'No'. I can't find that property in the
Form's property list. Does this give you any clues?

David

[lyle fairfield]

On Nov 7, 2:57*am, DavidGeorge <[e-mail]:

My earlier reply seems not to be appearing. *Apologies for the double
post if it does turn up.

Allow Edits is set to 'Yes' so I'm afraid that's not it Allen.
However, looking at the Documenter, it lists a property 'Allow
Updating' which is shown as 'No'. *I can't find that property in the
Form's property list. *Does this give you any clues?

David

AllowUpdating seems to be a hidden, undocumented form property. I'm
astounded that you somehow or other set it to Not Allow(1).

My experimentation with it !!!!suggests!!! that when it's not messed
with, its value is zero, and we can update the form.
But when its set to 1 updating is not available.

Strangely, after it is set to 1, setting it back to zero does not seem
to restore updating capability. It must be set to 2 to achieve that.

Perhaps, other will explore this and report to expand or correct my
observations.

In the meantime, this !!!may!!! help with you problem.

Public Sub SetUpdating(ByVal FormName$, _
Optional ByVal CanUpdate As Boolean)
Dim Form As Form
DoCmd.OpenForm FormName, acDesign
Set Form = Forms(FormName)
Form.AllowUpdating = 1 + Abs(CLng(CanUpdate))
DoCmd.Close acForm, FormName, acSaveYes
End Sub

[Salad]

DavidGeorge wrote:

I have an unbound form with three list boxes. The selections made in
these list boxes 'filter' the records that appear in an unbound
subform. The subform record source is a query which looks at the
values in the three list boxes.

It was working, but while tweaking it a little something went wrong
and now nothing on the form will respond to any mouse clicks. The
only way I can get out is to switch back to design view.

I suspect that I have done something simple, but I can't find the
cause. Does anyone have any ideas of what might casue the form to
suddenly stop responding?

David

[Salad]

DavidGeorge wrote:

I have an unbound form with three list boxes. The selections made in
these list boxes 'filter' the records that appear in an unbound
subform. The subform record source is a query which looks at the
values in the three list boxes.

It was working, but while tweaking it a little something went wrong
and now nothing on the form will respond to any mouse clicks. The
only way I can get out is to switch back to design view.

I suspect that I have done something simple, but I can't find the
cause. Does anyone have any ideas of what might casue the form to
suddenly stop responding?

David

How can a subform be unbound with a recordsource?

Is it possible the recordsource is not updateable? Open up the query
for the subform's recordsource and see if you can modifiy a field.

Can you open the subform by itself (you supply the filtering if needed?
If so, can you modify any data?

Or is your problem that events (like click/dbl-click) don't fire?

[DavidGeorge]

Things are getting even more confusing.

Lyle: Thank you for your ideas. I tried your code but it didn't seem
to change things. However, I have since noticed that all the forms in
the three applications I have running in various organisations all
show AllowUpdating as No, and yet they are working. I agree that the
AllowUpdating is intriguing but I'm not sure that it's the issue here.

Salad: Yes, I'm sorry, my terminology is a bit suspect. The subform
does of course have a recordsource - a query which takes data from a
table and another query. If I run the query outside of the form, the
data items from the table are updatable, but not the computed items
from the sub-query.

The weird thing is that even after I removed the subform from the
form, the main form is not responding to any keys or mouse clicks;
and the form now comprises only 2 listboxes, 1 set as Extended
MultiSelect and 1 as no multiselect.
I have the form set as a single form with no record source. The
interesting thing is that if I set Record Selectors on AND select the
record, it works fine. I know this a simple problem .... there is
something fundamental that I have set wrong but I can't for the life
of me work out what it is.

[DavidGeorge]

I have managed to work out what is happening, but not why. To recap,
I have an unbound form with a number of ListBoxes which I use to
'filter' a subform. The subform appears to be irrelevant. The
problem I think lies in the first two list boxes.

I have an extended multi-select listbox, 'lstTypes', which contains
types of events such as 'TRAINING', 'LUNCH', 'PLAY GROUP' etc. When I
make a selection from lstTypes I create a string in a text box
delimited with "#" containing the event types selected (eg
#TRAINING#PLAY GROUP#).

I then requery a second listbox 'lstEvents' containing all events of
the types listed in the string. ( InStr('#TRAINING#PLAY GROUP#',
[tblEvents].[eType]) with the criteria >0)

If I select multiple items in lstTypes everything works fine. I can
then select any item from the second list and everything else is fine.

However, if I select a single item in lstEvents, it lists the events
correctly in lstEvents and selects the first row. However, every time
I click on an event the list flickers briefly and the selection
returns to the first item in the list.

The work-around, which is cumbersome and which I don't understand, is
to turn Record Selectors on. If I then click on the record selector
everything works, until I make another single selection from lstTypes.

This is totally beyond my experience. Can anyone suggest a
solution .... please???

David

[Salad]

DavidGeorge wrote:

I have managed to work out what is happening, but not why. To recap,
I have an unbound form with a number of ListBoxes which I use to
'filter' a subform. The subform appears to be irrelevant. The
problem I think lies in the first two list boxes.

I have an extended multi-select listbox, 'lstTypes', which contains
types of events such as 'TRAINING', 'LUNCH', 'PLAY GROUP' etc. When I
make a selection from lstTypes I create a string in a text box
delimited with "#" containing the event types selected (eg
#TRAINING#PLAY GROUP#).

I then requery a second listbox 'lstEvents' containing all events of
the types listed in the string. ( InStr('#TRAINING#PLAY GROUP#',
[tblEvents].[eType]) with the criteria >0)

If I select multiple items in lstTypes everything works fine. I can
then select any item from the second list and everything else is fine.

However, if I select a single item in lstEvents, it lists the events
correctly in lstEvents and selects the first row. However, every time
I click on an event the list flickers briefly and the selection
returns to the first item in the list.

The work-around, which is cumbersome and which I don't understand, is
to turn Record Selectors on. If I then click on the record selector
everything works, until I make another single selection from lstTypes.

This is totally beyond my experience. Can anyone suggest a
solution .... please???

David

I might have a multi-select listbox lstTypes with a command button
underneath it to requery lstEvents. The user selects the items from
lstTypes (1 or many) then presses the command button. The command
buttons OnClick event creates a Where clause for lstEvents.

IOW, scan thru all items in lstTypes for the Selected property as True.
Ex:

Dim varItem As Variant
Dim strW As String
Dim strSQL As String
For Each varItem In Me.lstTypes.ItemsSelected
'use column number necessary
strW = strW & "Etype = '" & Me.lstTypes.Column(0, varItem) & "' Or "
Next
If strW "" THen
'remove the trailing "Or"
strW = "Where " & Left(strW,Len(strW)-4)
Endif
strSQL = "Select etypeid, etype from Events " & strWhere
Me.lstEvents.RowSource = strSQL

In the OnClick event of lstEvents do something like
Forms!MainFormName!SubFormName.Form.Filter = _
"EtypeID = '" & lstEvents.Column(0) & "'"
Forms!MainFormName!SubFormName.Form.FilterOn = Trye

I know you have a third listbox but don't know where it applies. You
could also do a
msgbox Forms!MainFormName!SubFormName.Form.Filter
to see if you have a weird filter you are passing to the subform.

[DavidGeorge]

Thanks again Salad for your ideas. I tried adding a command button to
initiate the selection rather than the on-click event of the listbox,
but this made no difference.

However, I have discovered what is causing the problem. I created a
new database file with one very simple form with only two list boxes
and one text box. There are no user-defined tables. The ListBox1 uses
a value-list rather than a query to populate it. I used the On-Click
event to step through ListBox1 and concatenate the selected rows into
a string (eg 'One';'Two';'Three'; etc) and set the rowsource of
ListBox2 to that string. This is all fairly useless but so far
everything works.

However, I want to automatically select the first item in ListBox2
each time the rowsource changes. So I added the line,
"Me.lstSecond.Selected(0) = True" immediately after setting the
rowsource from the On-Click event of ListBox1. This is where my
problem starts.

Now, every time I click on ListBox2 it fires the On-Click event of
ListBox1. This of course, resets the RowSource of ListBox2 and resets
the selected row to 0. I have a msgbox which would show me if
ListBox2's On-Click event was firing, but is definitely not - a click
on ListBox2 only fires the On-Click event for ListBox1.

So far I can't find a workaround. This behaviour hardly seems
normal. Do you have any ideas?

David

[Salad]

DavidGeorge wrote:

Thanks again Salad for your ideas. I tried adding a command button to
initiate the selection rather than the on-click event of the listbox,
but this made no difference.

However, I have discovered what is causing the problem. I created a
new database file with one very simple form with only two list boxes
and one text box. There are no user-defined tables. The ListBox1 uses
a value-list rather than a query to populate it. I used the On-Click
event to step through ListBox1 and concatenate the selected rows into
a string (eg 'One';'Two';'Three'; etc) and set the rowsource of
ListBox2 to that string. This is all fairly useless but so far
everything works.

However, I want to automatically select the first item in ListBox2
each time the rowsource changes. So I added the line,
"Me.lstSecond.Selected(0) = True" immediately after setting the
rowsource from the On-Click event of ListBox1. This is where my
problem starts.

Now, every time I click on ListBox2 it fires the On-Click event of
ListBox1. This of course, resets the RowSource of ListBox2 and resets
the selected row to 0. I have a msgbox which would show me if
ListBox2's On-Click event was firing, but is definitely not - a click
on ListBox2 only fires the On-Click event for ListBox1.

So far I can't find a workaround. This behaviour hardly seems
normal. Do you have any ideas?

David

No. Perhaps you could post the code you have for lstTypes and lstEvents
from your new form. Perhaps you are using OnGetFocus or OnLostFocus and
its doing things you don't want to occur. Maybe your code is incorrect.
It's hard to determine what you are doing.

[DavidGeorge]

Reduced to it's simplest form, the only code is in ListBox1. For test
purposes ListBox1 has a value-list rather than a query as it's
RowSource

Private Sub ListBox1_Click()
Dim i As Integer
Dim sString As String

For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
If Len(sString) 0 Then
sString = sString & " ,"
End If
sString = sString & "'" & Me.ListBox1.Column(0, i) & "'"
End If
Next
Me.ListBox2.RowSource = sString
Me.ListBox2.Selected(0) = True
End Sub

With the line "Me.ListBox2.Selected(0) = True" in the code it is
impossible to made a selection from ListBox2 since clicking on
ListBox2 (or anywhere else) results in an On-Click event in ListBox1
(which then resets the RowSource of ListBox2 and selects the first
row.

Without that line it works fine. I would prefer to select the first
row automatically as in the real application I use this selection to
filter a subform.

[Salad]

DavidGeorge wrote:

Reduced to it's simplest form, the only code is in ListBox1. For test
purposes ListBox1 has a value-list rather than a query as it's
RowSource

Private Sub ListBox1_Click()
Dim i As Integer
Dim sString As String

For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
If Len(sString) 0 Then
sString = sString & " ,"
End If
sString = sString & "'" & Me.ListBox1.Column(0, i) & "'"
End If
Next
Me.ListBox2.RowSource = sString
Me.ListBox2.Selected(0) = True
End Sub

With the line "Me.ListBox2.Selected(0) = True" in the code it is
impossible to made a selection from ListBox2 since clicking on
ListBox2 (or anywhere else) results in an On-Click event in ListBox1
(which then resets the RowSource of ListBox2 and selects the first
row.

Without that line it works fine. I would prefer to select the first
row automatically as in the real application I use this selection to
filter a subform.

I created 2 listboxes. List0 was a 2 column list derived from a table
and is Extended, 2nd column a text field. List2 is a listbox with a
ValueList as its row source and set as None, Simple, or Extended...it
doesn't matter which. BTW, I use a ";" instead of "," to present the
list in List2. Here's my code.

Option Compare Database
Option Explicit
Const QT = """"
Private Sub List0_LostFocus()
Dim var As Variant
Dim strRow As String

For Each var In Me.List0.ItemsSelected
strRow = strRow & QT & Me.List0.Column(1, var) & QT & ";"
Next
If strRow "" then strRow = Left(strRow, Len(strRow) - 1)
Me.List2.RowSource = strRow
If strRow "" then Me.List2.Selected(0) = True
End Sub

Maybe the above code will give you a hint.

[DavidGeorge]

Thanks yet again for your help Salad.

Your code does of course work, but it doesn't set the RowSource for
List2 until List1 loses the focus whereas the style I have used
throughout my current application is to continually refine the
selection in the attached subform as the selection criteria are
progressively refined. In the full form I have a total of 4 ListBoxes
and two toggles which are being used to effectively filter the form
(although they do it by redefining the record source for the subform
rather than by using a filter).

It took me a while to progressively simplify the form until I reached
the situation in my post (two above). I am very curious about the
behaviour I experienced (Click on List2 triggers On-Click event on
List1).

Have you ever come across this before? It goes against everything I
had believed, but I am largely self-taught (and mainly through trial-
and-error backed up with searches on this group). It appears to me to
be a bug (or undocumented feature), but perhaps someone with more
knowledge than me might have an answer?

[Salad]

DavidGeorge wrote:

Thanks yet again for your help Salad.

Your code does of course work, but it doesn't set the RowSource for
List2 until List1 loses the focus whereas the style I have used
throughout my current application is to continually refine the
selection in the attached subform as the selection criteria are
progressively refined. In the full form I have a total of 4 ListBoxes
and two toggles which are being used to effectively filter the form
(although they do it by redefining the record source for the subform
rather than by using a filter).

It took me a while to progressively simplify the form until I reached
the situation in my post (two above). I am very curious about the
behaviour I experienced (Click on List2 triggers On-Click event on
List1).

Have you ever come across this before? It goes against everything I
had believed, but I am largely self-taught (and mainly through trial-
and-error backed up with searches on this group). It appears to me to
be a bug (or undocumented feature), but perhaps someone with more
knowledge than me might have an answer?

I don't know. I moved my code to the OnClick event and it worked fine.
I have no event code or macro for the second listbox to execute. I
don't know what you have as far as code and other listboxes and
textboxes and what calls you make to filter a subform. I don't even
know if you reset the listboxes and textboxes if you start a new search.
Maybe its your version of Access. Maybe your version of Access
doesn't like you. :).

I did notice you used a , instead of a ; to separate the values in the
second listbox. If I type in a rowsource for a ValueList it may look
something like
"One";"Two";"Three"

What version of Access are you using?

[DavidGeorge]

I'm using Access 2003. I've tried converting to 97 and 2000 formats
but it doesn't affect this behaviour.

I don't usually use commas to delimit items in the RowSource. It was
a mistake I made when constructing the simplified demo version of the
form. Strangely it worked, but I have now changed it to use the semi-
colon.

I used your code exactly (cut and paste) into the on-click event and
changed control names to suit. But I'm still getting the same
problem.

I think maybe my version of Access does hate me but I have to move
on. I think I'll settle for the first item in List2 not being auto-
selected. Thanks for all your help and ideas!