I Give Up! Can't select name from drop down.

S

SVE

Hello everyone.

I have a table called "Site List" with multiple fields, but only 2 fields
called Site Status and FSM that I'm concerned with. I have created a query
to filter the Site Status on "Active" and have set the query properties
Unique Value to "Yes" as an FSM may have multiple Sites. (The FSM are entered
directly into this table, not their own table so no relationship issues)

I created a form bound to this query with 1 drop down box pulling the FSM
names. I can view the list and highlight a name, but when I click on it,
nothing happens, the combo box just keeps the initial blank field.

To get the names in the drop down box I had to remove the Site List filter
in the Form properties, otherwise, it would not display the names in the list
and I could only go to different names by using the Record Selectors at the
bottom (when I had them visible)

FYI--This form will be used to filter a report when all is said and done.

Please help and thank you for your time and expertise.
 
D

Dirk Goldgar

In
SVE said:
Hello everyone.

I have a table called "Site List" with multiple fields, but only 2
fields called Site Status and FSM that I'm concerned with. I have
created a query to filter the Site Status on "Active" and have set
the query properties Unique Value to "Yes" as an FSM may have
multiple Sites. (The FSM are entered directly into this table, not
their own table so no relationship issues)

I created a form bound to this query with 1 drop down box pulling the
FSM names. I can view the list and highlight a name, but when I
click on it, nothing happens, the combo box just keeps the initial
blank field.

To get the names in the drop down box I had to remove the Site List
filter in the Form properties, otherwise, it would not display the
names in the list and I could only go to different names by using the
Record Selectors at the bottom (when I had them visible)

FYI--This form will be used to filter a report when all is said and
done.

Please help and thank you for your time and expertise.
Click to expand...

Is your form's AllowEdits property set to Yes? If not, you won't be
able to modify the value of even unbound controls on the form.
 
R

RxDave

I believe I have the same problem. I have built a hospital pharmacy
inventory system using Access 2003 running in Access 2000 format. Every day
we record purchases. Items are selected with a combo box using the catalog
number. The query behind the combo box contains three columns, Primary key
(autonumber field) which is hidden, catalog number and item name. This has
been running for about four years now. On 9/25/07 it was running perfectly.
On 9/26, the catalog number disappeared. The entire column is blank, and we
cannot select items with the catalog number. I tried building a new combo
box. While running the wizard, the catalog number shows. In the completed
combo box, it is blank. I checked with our IT department and nothing has
changed on their end. I can copy the entire data base onto a flash drive and
run it on my machine at home under Access 2002. It runs perfectly. Catalog
number is a text field. AllowEdits is Yes. What is even stranger is that we
have another database for clinical monitoring. An almost identical combo box
is used to retrieve patient records using medical record number. This one is
running fine. Any suggestions would be appreciated. Thanks.
 
D

Dirk Goldgar

In
SVE said:
Thank you for your quick response. Yes it is set to allow edits.
Click to expand...

RxDave has posted, in this thread, a link to a new bug that I was only
vaguely aware of. Could that be the source of your problem?
 
S

SVE

Thanks. I tried this, but although the primary key is a text field, I had no
formatting for the field and Concatenation did not solve the problem. Unlike
RxDave, I can see my list of names and even highlight them, but I can't
select them. Any other ideas?

Thanks for all your help.
 
B

Bob Quintal

Thanks. I tried this, but although the primary key is a text
field, I had no formatting for the field and Concatenation did not
solve the problem. Unlike RxDave, I can see my list of names and
even highlight them, but I can't select them. Any other ideas?

Thanks for all your help.
Click to expand...
is it possible that the combobox locked property is set to yes?
or that the form's allow edits property is set to no?
 
S

SVE

No, I had already checked that. Locked is set to no and allow edits is set
to yes.

Thanks again. Any other thoughts?
 
B

Bob Quintal

No, I had already checked that. Locked is set to no and allow
edits is set to yes.

Thanks again. Any other thoughts?
Click to expand...
What is the control source of the combo box? Is it something that
Access thinks is not updateable?

***********
I Googled your original post.You say that you have a distinct clause
in the query, That makes the query non-updateable.

You said
FYI--This form will be used to filter a report when all is said and
done.

If this is the case, you do not want to bind the combo box to the
query's field, you simply want to use it as criteria for a report.

I think you don't need to bind the form to the query at all,.

Q
Bob Quintal said:
is it possible that the combobox locked property is set to yes?
or that the form's allow edits property is set to no?
Click to expand...
Click to expand...
 
S

SVE

Here's the situation. The table that halds the FSM information has numerous
fields. The 3 of most concern are the Site (Text but the primary key) the
Site Status (must filter on Active) and the FSM. An FSM can be used with
more than one site, so if I don't use Unique Values, then each FSM is listed
multiple times in my drop down (for each site the FSM is listed with). The
report, when run, I want the user to be able to choose from the drop down the
FSM. That way all the records for that FSM will be displayed in the report.
The report is based on a chain of queries and none of them have the FSM as a
field, otherwise it would be much easier to filter; but the queries it does
use eventually have a table that links with this Sites table and subsequent
query. I realize that a table with the FSMs is a better way to go, but I did
not design the dbase and it would be quite tasking to try and fix it now.
So, with all that said, do you have any other thoughts of how I can do what I
am trying to do?

Thanks for all your help.

Bob Quintal said:
No, I had already checked that. Locked is set to no and allow
edits is set to yes.

Thanks again. Any other thoughts?
Click to expand...
What is the control source of the combo box? Is it something that
Access thinks is not updateable?

***********
I Googled your original post.You say that you have a distinct clause
in the query, That makes the query non-updateable.

You said
FYI--This form will be used to filter a report when all is said and
done.

If this is the case, you do not want to bind the combo box to the
query's field, you simply want to use it as criteria for a report.

I think you don't need to bind the form to the query at all,.

Q
Click to expand...
 
B

Bob Quintal

Here's the situation. The table that halds the FSM information
has numerous fields. The 3 of most concern are the Site (Text but
the primary key) the Site Status (must filter on Active) and the
FSM. An FSM can be used with more than one site, so if I don't
use Unique Values, then each FSM is listed multiple times in my
drop down (for each site the FSM is listed with). The report,
when run, I want the user to be able to choose from the drop down
the FSM. That way all the records for that FSM will be displayed
in the report. The report is based on a chain of queries and none
of them have the FSM as a field, otherwise it would be much easier
to filter; but the queries it does use eventually have a table
that links with this Sites table and subsequent query. I realize
that a table with the FSMs is a better way to go, but I did not
design the dbase and it would be quite tasking to try and fix it
now. So, with all that said, do you have any other thoughts of
how I can do what I am trying to do?

Thanks for all your help.
Click to expand...

There is nothing unusual about your situation.

Use the Combobox on an unbound form. Its Row Source should be your
distinct query that calculates your FSM. You may have an unbound
checkbox If you wish to enable the report to print active/inactive
rows. You'll also need a command button to run the report.

Build your report using a query that returns all records.

There are two options you can use to actually run the report.

Option 1: In the query, add references to the unbound form's
combobox (and checkbox) in the criteria row against the columns you
need to filter like Forms!formname!controlname
Then you simply open the form by clikcing the command button.

Option 2: Instead of putting the references to the form into the
query, you put them into the vba code which opens the report

stWhereClause = "FMS= """ & me.cboFMS & """"
Docmd.Openreport stDocname,,,stWhereClause

Bob Quintal said:
What is the control source of the combo box? Is it something that
Access thinks is not updateable?

***********
I Googled your original post.You say that you have a distinct
clause in the query, That makes the query non-updateable.

You said
FYI--This form will be used to filter a report when all is said
and done.

If this is the case, you do not want to bind the combo box to the
query's field, you simply want to use it as criteria for a
report.

I think you don't need to bind the form to the query at all,.

Q
Click to expand...
Click to expand...
 
Top