Requerying a list box

[LOUISBUHAGIAR54]

Good day ye wonderful people,

Can someone please help ?

I have a combobox which is based on a table containing doctors' specialties such as physician, surgeon etc. On the same form I have a list box based on a query. The list box is supposed to show all the doctors with the speciality shown in the combo box.

The query runs as follows:

SELECT Query2.appl, Query2.dname, Query2.surname, Query2.dctIdcard, Query2.Speciality FROM Query2 WHERE (((Query2.Speciality)=[Forms]![Appointments]![Combo44])) ORDER BY Query2.surname;

In the after update event of Combo44 I have added the following;

Me!List54.Requery.

However the list does not update with new choices in the Combo box.

If I change the form into the design view and back into the form veiw the list updates as expected.

Could anyone please find a diagnosis ? Thanks for trying.


Louis Buhagiar

 

[VilaRestal]

I would always set the listbox rowsource to do these sorts of things:

Me.List54.RowSource = "SELECT appl, dname, surname, dctIdcard, Speciality FROM Query2 WHERE Speciality = " & Me.Combo44 & " ORDER BY surname"

I know some people are quite happy with using expressions that refer to Forms!Form1!Control1 but, as you're finding, it's not very reliable and also not very efficient or easy to debug and can't handle exceptions.

 

[LOUISBUHAGIAR54]

The following is the code I am inserting now into the after update event of Combo44.

Private Sub Combo44_AfterUpdate()
Dim str1 As String
str1 = "SELECT appl, dname, surname, dctIdcard, Speciality FROM Query2 WHERE Speciality = " & Me.Combo44 & " ORDER BY surname"
Me.List54.RowSource = str1
End Sub

It does not seem to be altering the rowsouce property of List54. I am also having problems with other controls executing code.

Could there be something i am missing elsewhere ? Many thanks.


Louis Buhagiar

 

[LOUISBUHAGIAR54]

Hi,

It is possible that the whole problem arises from the fact that somehow my ACCESS 2007 is not executing code. Could I have (have not) done something in this particular database, causing the program not to execute code.

Another command button which I have inserted to close the form with
DoCmd.Close is not working and when i toggle in a breakpoint it does not function either.

It is not the first time I have carried out extensive code and had nothing like this before.

Mysterious! and quite nagging.

Louis

 

[VilaRestal]

That code will change the rowsource of List54 (or will produce a runtime error if there's a mistake in it) if it executes.

Is the database trusted (in the Trust Centre) so code is executing? (When you open the database do you get a warning that macros are disabled?)

Put a MsgBox "Test" in the code somewhere. Does the msgbox appear?

 

[LOUISBUHAGIAR54]

I think the problems lies here. I have another database which is quite large and each time I load it there a security warning saying ' certain content in the database has been disabled'. I then have to press options and to enable the database.

However in this new database which I am compiling I cannot get clues as to how to do this.

Many thanks. We seem to be getting somewhere now.


Louis

 

[LOUISBUHAGIAR54]

Hi VilaRestal,

I have changed the settings in the Trust Center and now the code is working fine and the list box is updating as planned.

Your're and expert indeed. Many thanks

Louis

 

[VilaRestal]

Nice one