form startup problem

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Access Training and help » Form startup problem

Form startup problem

resolvedResolved · Low Priority · Version 2007

Robert has attended:
Access Advanced course

Form startup problem

A problem has appeared when starting up a form for the first time.

There are three related lists on the form that the user uses to home in on data. I have written some VBA that requeries two of the lists when the user clicks on the other two. The row source query for these two lists has the value from the previous list in its selection criteria.

When I start the form up I am asked to enter the value of these lists as if they were parameters (eg Forms!Main!GroupList). I can cancel both of these and the first list populates. However, when I click on the first list, an error box appears with the message: Object or class does not support the set of events. Ie I am being told that my VBA can't be found.

There is a solution. When I open up the VBA editor the problem goes away and the lists populate perfectly. It carries on working even if I close the editor and indeed when I close and reopen the form.

Any ideas?

Thanks,

Bob

RE: Form startup problem - solved, but I don't know why

I tried to find out which VBA function was triggering the problem and put a msgbox at line 1 of the one that was firing at startup.

I then closed down Access and started it again. The MsgBox fired and the form started up with no errors.

I have now taken out the MsgBox line and the form works perfectly. It seems that the problem went away without me doing anything!

There is undoubtedly a rational explanation but, as with acupuncture, I'm blowed if I know what it is!

RE: Form startup problem

Hi Robert

Thanks for your question

In order to resolve this it would be useful if I could see a copy of the relevant code. Could you please post it to this thread?

Thanks

Stephen

RE: Form startup problem

Hi Stephen,

I've pasted the functions below. The first one was throwing up errors when the form was first loaded. It doesn't of course fire at startup as I might have inferred but when clicking on the list. This went away when I opened the VBA editor as described in my initial post. Now the problem has gone completely!

So you can make sense of it, there are three lists: GroupList; GroupSectionList; PersonList. The query that populates GroupSectionList has the value of GroupList as a selection criteria. PersonList does the same with GroupSectionList.

The intention of the VBA is that when someone clicks on GroupList, the related Sections are displayed in GroupSectionList and also the members of PersonList that relate to the first record in GroupSectionList. This works only partially because SetFocus doesn't seem to do the same as physically clicking on the list and therefore the second function doesn't fire until GroupSectionList is clicked on. This is probably something for another thread but if you could throw some light on that as well that would be great. (ie is there a vba function that explicitly clicks on an object?).

Here is the code:

Private Sub GroupList_Click()
Me!GroupSectionList.Requery
Me!GroupSectionList.Selected(0) = True
Me!GroupSectionList.SetFocus
Me!PersonList.Requery
Me!PersonList.Selected(0) = True
End Sub

Private Sub GroupSectionList_Click()
Me!PersonList.Requery
Me!PersonList.Selected(0) = True
Me!PersonList.SetFocus
End Sub

Thanks,

Bob

RE: Form startup problem

I have refined my code and made it populate the two subordinate lists as I mentioned I was trying to do in my previous post.

I spent ages going down a blind alley trying to use sendkey to do a left click. Setting the default value achieved the same thing. My new code is here:

Private Sub GroupList_Click()
Me.GroupSectionList.Requery
Me.GroupSectionList.DefaultValue = Me.GroupSectionList.Column(0, 0)
Call GroupSectionList_Click
End Sub

Private Sub GroupSectionList_Click()
Me.PersonList.Requery
Me.PersonList.DefaultValue = Me.PersonList.Column(0, 0)
End Sub

When someone clicks on a value in GroupList, the other two lists are queried using related values.


 

Access tip:

Convert A Form Into A Report

If there is a form that you want to to save as a report:

1. Open that form in Design View
2. Select File and Save As
3. In the Save As Dialog box Select Report

The system creates a report based on the form.

View all Access hints and tips


Server loaded in 0.07 secs.