Cannot requery combobox from another form (1 Viewer)

[ColinH]

Good afternoon,

It's a hot day here in the UK, but I've promised to finish some Access programming for the database that handles bookings for our training courses at work, and I can't update a combobox one one form from a button on another form.

Form A is a waiting list of people who want to attend one of our several courses.
Form B is a bookings form where the operator selects a course from a list, a person's name from a combobox then clicks a button to make the booking.

I'm trying to make things easier for the operators by allowing them to highlight the entry for person X in form A, then click a button on form A that opens form B and writes SQL code (which contains a where statement that identifies the ID number of person X) to the recordsource property of the combobox.
The code then requeries the combobox to populate the rest of the columns and other controls on form B.

The problem is the combobox on form B doesn't seem to requery. I'm writing to the bound column but after .requery the columns contain nulls, but if I click the combo box it drops down to shows only one person correctly identified by the ID value I've written to it.
I can write values to text boxes on form B easily from one form to another, but the combobox has 16 columns (don't ask) so I don't want to go down the route of adding many extra hidden text boxes to form B.

DoCmd.OpenForm "BookCourses"
Forms!bookcourses!cmbFindDelegate.RowSource = strSQL1 & strSQL2
Forms!bookcourses!cmbFindDelegate.Requery

Any suggestions gratefully received, thanks in advance.

 

[vbaInet]

What kind of form does the combo box reside? A continuous or Single form?

 

[ColinH]

It's a single form...

 

[vbaInet]

Can you upload a sample db so we can see what you're doing.

 

[ColinH]

Well, yes I could, but it's pretty big (10MB for the data file and the application is about the same) and contains company sensitive data. I started it in 1998 when our existing one was declared 'not Y2K compliant'. I guess the best thing would be to cut down the app to just a few forms. I'll also have to find out how to upload a db to the forum.
This will take a little time but bear with me...

 

[vbaInet]

It doesn't have to be the exact form, just a test form with the combo box and some test data.

 

[ColinH]

Hi vbaInet, I've now chopped down the three forms involved, and merged the data and application into a single file which is attached.

I suggest you run up Booking.mdb and open form frmCourses.
Select Course B (which has vacancies) from the combobox 'Find existing course'.
Click 'Wait list' which opens frmQbookings and filters the entries for 'Course B'.
Highlight one name and click 'Bookings' which will open up the main booking form and should (but doesn't) refresh cmbFindDelegate with the delegate's details you highlighted.
I've trapped the point at which the code fails to refresh with a message box.
Many thanks for looking at this problem.

 

[bob fitz]

ColinH
I can't see any attached file.

 

[boblarson]

Where do the parts in red below come from? How are you assigning them?

Forms!bookcourses!cmbFindDelegate.RowSource = strSQL1 & strSQL2
Forms!bookcourses!cmbFindDelegate.Requery

What is the code which assigns their values?

 

[ColinH]

I thought I had uploaded this yesterday, but apparently not.
ColinH

 

[ColinH]

strSQL1 and strSQL2 are two text strings that make up an SQL query. strSQL1 is a standard part, strSQL2 is a part that changes according to which entry in the wait list is highlighted. You'll see the code in my database, but it picks up the DelegateID from the first column in the list box. I could have done it all in a single string, but it's a long string and it seemed neater to do it in two steps.

 

[ColinH]

Well, I've found a solution to my problem of a couple of weeks ago so I thought I'd update this post.

The aim is for a value selected in a list box on one form to filter and update a combo box on another form. The filtering worked fine but I couldn't fire the AfterUpdate event on the combo box on the second form. This means the user still has to pick the single, filtered record from the combo drop down, which is what I was trying to avoid.
The solution I've found is to shift the focus to another control using VBA. This fires the OnLostFocus event in the combo box. I've copied the code that was originally on the AfterUpdate event of the combo box to the OnLostFocus event. I've also added a few checks to make sure everything works smoothly.

As I said this all works well but it seems rather clumsy to me and I wondered if anyone could suggest a more elegant way of accomplishing this.

ColinH

 

[vbaInet]

All you have done is filter the combo boxes row source but you haven't actually set the Value of the combo box. So all you needed was just one line of code to set the value.

See attached.

By the way, when last did you Compile your code? Your project doesn't compile.

 

[ColinH]

Many thanks for your reply vbaInet.

I did try setting .value to the DelegateID, but this doesn't trigger the combobox AfterUpdate event.
In the original app. when the administrator selects a delegate the AfterUpdate event triggers a search for comments about the delegate and his parent company. For example, these could be "this delegate requires a vegetarian meal" or "this company is on the hold list", these are then displayed. I had to strip my application right down to get it within the 2 MB upload limit and I removed this feature. I'm sorry if this has clouded the issue.

I'm certain the AfterUpdate event doesn't fire. I've added a messagebox to the AfterUpdate event in the attached file and you can see that it doesn't fire when the BookCourses form is called from the WaitList form, but it does fire if you then select the name from the combobox in the normal way.

All this is to save our administrators a single click! But they are making scores of bookings a day so I'm trying to cut down their work.

The original app. does still compile, I'm sure this version doesn't because I've deleted some variables (probably Public) when I cut it down.

Incidentally, I've found a much shorter way of selecting the delegate than generating a long SQL string; simply send
"DelegateID = " & strDelegateID to the combobox.

ColinH

 

[vbaInet]

AfterUpdate doesn't fire when you set the value through code. If you want to raise that event you simply call it as you would any other function or sub.

 

[ColinH]

I tried that vbaInet, but couldn't get it to work.

Both the next two lines fail with syntax error:
Forms!frmBookCourses!cmbFindDelegate_AfterUpdate()
or
Call Forms!frmBookCourses!cmbFindDelegate_AfterUpdate()

VBA asks for an "=" either before or after the main text. I guess its treating it as a function and returning either true or false for OK or error.

So I tried
Dim Result as boolean
Result = Forms!frmBookCourses!cmbFindDelegate_AfterUpdate()
VBA replies
"Cannot find field cmbFindDelegate_AfterUpdate()", now it appears to be interpreting that text as a table field rather than a control event.

If I try to create an event handler with
Public Event cmbFindDelegate_AfterUpdate()
again, it fails the syntax checker before I write any code.

So how do I write VBA code to trigger an event on another form?

Colin

 

[vbaInet]

Can you upload a db with the amended code?

Also the one with the keyword Call doesn't need parentheses.

 

[boblarson]

If you need to call it from another form, you need to remember to change it from:

Private Sub Form_AfterUpdate()

to

Public Sub cmbFindDelegate_AfterUpdate()

Then you should be able to use the

Forms!frmBookCourses!cmbFindDelegate_AfterUpdate

without an equals sign or the parens.

 

[ColinH]

Got it working at last!
Many thanks Bob for pointing out my Private/Public error. I'm so used to Access automatically generating the Subs' framework I didn't think of the Private/Public issue, but like a lot of things, it's obvious when pointed out.

The following line finally worked:
Call Forms.bookcourses.cmbFindDelegate_AfterUpdate
and that has solved my problem.

Some other syntax which I understand should work didn't. I usually use the ! character to specify Forms!FormName!ControlName, but this gives a compile error. Also the following line still fails with the error "Expected: ="
Forms.bookcourses.cmbFindDelegate_AfterUpdate()

Anyway thanks again to everyone who helped me.
ColinH

 

[vbaInet]

Some other syntax which I understand should work didn't. I usually use the ! character to specify Forms!FormName!ControlName, but this gives a compile error. Also the following line still fails with the error "Expected: ="
Forms.bookcourses.cmbFindDelegate_AfterUpdate()

Did you put this directly inside the Event box?