Values in Combo boxes not selectable.

[klufkee]

Hello,

I'm working on form to allow the user to select a value from a combo
box and have the rest of the data fill in automatically. The DB I have
has 6 tables that link to each other through established relationships.
I want the user to be able to select a value from any of the different
combo boxes on the form and have the related records fill in
themselves. The form I've created has a combo box for all the different
fileds from the 6 tables. (There are 57 in total.)
When I try to select a value from the combo box, it drops down and
shows all the available values but I cannot click on any of them. (I
get a system beep but no error boxes or errors at the bottom of
Access.) Oldly, I can use the mouse wheel to scroll and it goes through
the records and reports the correct information from the 6 tables. I've
been sifting through the groups and on the MS Office site but after
trying many 'fixes' haven't been able to get this working. Any help
would be greatly appreciated.

Thanks,
Bill

 

[Guest]

This will happen if your form is based on a recordset that is not updatable
which will in turn be influenced by the relationships between your tables.
Also check that you have the right control source set for your combo box. For
instance if you have the combo box control source set to the autonumber
primary key field of your table, then you wont be able to make a selection as
this will try to change the primary key.

Delordson Kallon
http://www.instantsoftwaretraining.com/

 

[klufkee]

Thanks for your input. I'm still new to Access, (been useing it for 2
weeks) so thanks for the 'not-to-technical' answer. I checked the
recordset type as you suggested and found it set to 'Dynaset'. I tried
switching it to 'Dynaset (Inconsistent Updates)' and I was then able to
select different values but the other data did not change to reflect
the selection. It looked like it was creating a new record instead. (I
saw the little pencil icon in the upper left of the form.) When I
turned off 'AllowEdits','AllowDeletions' and 'AllowAdditions' I was
again unable to select a value from any of the fields. The data in the
tables is all archival and does not need to be changed or edited. I
would prefer to make it uneditable really. I just would like to have
the user able to select a value from a list and have all records that
share that value appear. (Essentially I want it to filter the 22000
records by what was selected and be able to filter even further when
selecting a value from a different combo box from within the filtered
results.) Thanks again for all the help here.

Bill

 

[John Vinson]

Hello,

I'm working on form to allow the user to select a value from a combo
box and have the rest of the data fill in automatically.

Normally you would NOT want to do this. Do you want to find and
display an existing record, or do you want to store data redundantly?
If the latter - reconsider! It's almost NEVER a good idea.

The DB I have
has 6 tables that link to each other through established relationships.
I want the user to be able to select a value from any of the different
combo boxes on the form and have the related records fill in
themselves. The form I've created has a combo box for all the different
fileds from the 6 tables. (There are 57 in total.)

If you have six tables, you really should consider using a Form for
the "one" (or "one-est") table, with subforms for the related tables.
Trying to base your Form on one monster query joining all six tables
is pretty much sure to be a mistake: it won't be updateable most
likely (your current symptom), and you'll see duplicates of the data.

Could you explain how your tables are structured, and what you're
using as the Recordsource of this form?

John W. Vinson[MVP]

 

[klufkee]

OK. Well I took your advice and make subforms for each of the tables.
Within each subform though I am having the same problem. The data is
all old and I don't want it to be update-able nor do I want the users
to be able to add any records. The 6 tables are related but not by a
common field accross all of them. Example; 'TBL_GIN' in table 1 is
matched to 'FORM_GIN' in table two while 'TBL_GIN' in table 2 is
matched to "BP_GIN' in table 3 and so on. Is there a whole other way I
should be going about this? I must say that I have only been using
Access for 2 weeks (since I started this project) and I do have the
'Inside Out' book from MS Press but this has me stumped. Seems like it
should be easy to just pull up records and all the relevant data appear
without being editable. It really needs to be searchable by any of the
fields though in cass the user doesn't have all the info for an account
and needs to narrow the search by selecting things like, 'type of
account' from one box, having the results filtered to only records that
contain that value in that field, then select another value such as
'creation date' and have the filtered results narrowed even more untill
the user is able to pinpoint the data they need. I really got thrown to
the wolves with this project. (They figured, He can do Excel, he must
be able to do Access too!) I'm willing to start from the ground up if
you think there is an easier way to go about what I'm trying to do.

Thanks,
Bill

 

[John Vinson]

Seems like it
should be easy to just pull up records and all the relevant data appear
without being editable.

It is.

Set the Form's Allow Updates property to Yes.

Use the Combo Box Wizard to put combo boxes on the form using the
option "Use this combo to find a record".

John W. Vinson[MVP]