Combo Code Help Please

[golfinray]

I am using:
Me.filter = "[district name] = """ & Me.combo22 & """"
Me.filteron = true
to filter a combo for school district. The user can pick a district and
projects for that district comes up. If a district has no project, I get a
blank screen. I tried stuff like If isnull [district name] then
msgbox "District has no projects", vbokcancel
end if
I tried a bunch of different syntax and can't quite get it to work. Help!!!
Thanks, a bunch!!!

 

[Jeff Boyce]

You dont mention whether you are working in a form or in a report. I'll
assume in a form.

If you are trying to use the combobox to limit the number of records
displayed to those for a given school district, and you are using the
"filter" approach, then the form would have to load ALL the records first,
before the school district is selected. That consumes network resources (if
on a network) and time.

An alternate approach would be to have a query that returns the project
information, but that uses a selection criterion of a specific school
district. And in that selection criterion, you can "point" to the combobox
on the form.

On the form, your combobox lists school districts. In the AfterUpdate event
of the combobox, add code that requeries the form.

With this approach, when you first load the form, the query sees nothing in
the combobox and loads "that" record (i.e., nothing).

After selecting a school district in the combobox, the AfterUpdate event
fires, provides the query with a school district, and loads ONLY that school
district's Projects.

Is this what you were after?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[golfinray]

Thanks a bunch, Jeff, but this form runs off a table. I also looked at Allen
Browne's Why is my form blank and I tried both his solutions and couldn't get
either to work. I finally just put the combo filter in the header so that
users could make another choice. But I'd rather not do it that way. I'd
rather just have a msgbox come up and say "that district has no projects."
But I wasn't able to get that to work. Thanks!

You dont mention whether you are working in a form or in a report. I'll
assume in a form.

If you are trying to use the combobox to limit the number of records
displayed to those for a given school district, and you are using the
"filter" approach, then the form would have to load ALL the records first,
before the school district is selected. That consumes network resources (if
on a network) and time.

An alternate approach would be to have a query that returns the project
information, but that uses a selection criterion of a specific school
district. And in that selection criterion, you can "point" to the combobox
on the form.

On the form, your combobox lists school districts. In the AfterUpdate event
of the combobox, add code that requeries the form.

With this approach, when you first load the form, the query sees nothing in
the combobox and loads "that" record (i.e., nothing).

After selecting a school district in the combobox, the AfterUpdate event
fires, provides the query with a school district, and loads ONLY that school
district's Projects.

Is this what you were after?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am using:
Me.filter = "[district name] = """ & Me.combo22 & """"
Me.filteron = true
to filter a combo for school district. The user can pick a district and
projects for that district comes up. If a district has no project, I get a
blank screen. I tried stuff like If isnull [district name] then
msgbox "District has no projects", vbokcancel
end if
I tried a bunch of different syntax and can't quite get it to work.
Help!!!
Thanks, a bunch!!!

 

[Jeff Boyce]

For your information, forms don't have to "run off a table". In fact, you
might find you have greater selectivity and functionality if you first
create a query, then base the form on that query instead.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks a bunch, Jeff, but this form runs off a table. I also looked at
Allen
Browne's Why is my form blank and I tried both his solutions and couldn't
get
either to work. I finally just put the combo filter in the header so that
users could make another choice. But I'd rather not do it that way. I'd
rather just have a msgbox come up and say "that district has no projects."
But I wasn't able to get that to work. Thanks!

You dont mention whether you are working in a form or in a report. I'll
assume in a form.

If you are trying to use the combobox to limit the number of records
displayed to those for a given school district, and you are using the
"filter" approach, then the form would have to load ALL the records
first,
before the school district is selected. That consumes network resources
(if
on a network) and time.

An alternate approach would be to have a query that returns the project
information, but that uses a selection criterion of a specific school
district. And in that selection criterion, you can "point" to the
combobox
on the form.

On the form, your combobox lists school districts. In the AfterUpdate
event
of the combobox, add code that requeries the form.

With this approach, when you first load the form, the query sees nothing
in
the combobox and loads "that" record (i.e., nothing).

After selecting a school district in the combobox, the AfterUpdate event
fires, provides the query with a school district, and loads ONLY that
school
district's Projects.

Is this what you were after?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am using:
Me.filter = "[district name] = """ & Me.combo22 & """"
Me.filteron = true
to filter a combo for school district. The user can pick a district and
projects for that district comes up. If a district has no project, I
get a
blank screen. I tried stuff like If isnull [district name] then
msgbox "District has no projects", vbokcancel
end if
I tried a bunch of different syntax and can't quite get it to work.
Help!!!
Thanks, a bunch!!!