Forms, subforms and embarrasment

[BigAl]

This is really embarrassing but, hey ho, I need an answer.

This should be incredibly simple.

Two tables.

First: (Type) holds only one field called type. This has 5 records each with
a mountain type.

Second (Mountains) has a number of fields including type which is a look up
from the above table. It also has a field called Mountain (holds the
mountain name).

All I want to do is create form/subform that has a combo box (Master Form)
with the type and then lists the all the mountains of that type (subform). I
can do this quite easily using the form wizard BUT the field type shows as a
Text Box. Change it to a combo box and all it does is list the first record
from the Type table. The ridiculous thing is that if I use the record
selectors to move to the next record the subform then picks up the different
mountain names quite happily and correctly. But I want the user to be able
to select the different mountain type from a combo-box.

If anyone can stop laughing lonmg enough to point out what I'm doing wrong
I'd be most grateful.

BigAl

 

[Ken Snell [MVP]]

You need to give the RowSource of the combo box a query to get all the
values of the Type table.

Open form in design view, click on combo box, and then right-click and
select Properties. In that window, click Data tab.

Click in box next to Row Source and type this:
SELECT [Type] FROM [Type] ORDER BY [Type];

In the Control Source box, delete any information in there (you don't want
to change the Type data in the table, just use it for the combo box
display). So you should have a blank box there.

Now the combo box will allow you to select a type.

 

[BigAl]

You need to give the RowSource of the combo box a query to get all the
values of the Type table.

Open form in design view, click on combo box, and then right-click and
select Properties. In that window, click Data tab.

Click in box next to Row Source and type this:
SELECT [Type] FROM [Type] ORDER BY [Type];

In the Control Source box, delete any information in there (you don't want
to change the Type data in the table, just use it for the combo box
display). So you should have a blank box there.

Now the combo box will allow you to select a type.

Cheers Ken

That worked and does what I wish it to ............................ but.

On closing the form I get an error message saying:
"The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship.
Change the data in the field or fields that contain duplicate data, remove
the index, or redefine the index to permit duplicate entries and try again."

Runninjg a quck test (having copied the database first) it is trying to
change the value in the the "Type" field in the "Type" table. Tried various
things like not allowing edits etc. but nothing worked. All I want to do is
view the list of mountains by Type. I can do this by creating a parameter
query but that would be messy and not very user friendly.

Any further advice?

TIA


BigAl

 

[Ken Snell [MVP]]

Be sure that the combo box is not bound to the Type field (see my comment
about the control source for the combo box).
Also, for the setup that you're using, the main form's RecordSource should
be "empty" (do not use the table), as you're not doing any data entry in the
main form.

--

Ken Snell
<MS ACCESS MVP>

You need to give the RowSource of the combo box a query to get all the
values of the Type table.

Open form in design view, click on combo box, and then right-click and
select Properties. In that window, click Data tab.

Click in box next to Row Source and type this:
SELECT [Type] FROM [Type] ORDER BY [Type];

In the Control Source box, delete any information in there (you don't
want to change the Type data in the table, just use it for the combo box
display). So you should have a blank box there.

Now the combo box will allow you to select a type.

Cheers Ken

That worked and does what I wish it to ............................ but.

On closing the form I get an error message saying:
"The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship.
Change the data in the field or fields that contain duplicate data, remove
the index, or redefine the index to permit duplicate entries and try
again."

Runninjg a quck test (having copied the database first) it is trying to
change the value in the the "Type" field in the "Type" table. Tried
various things like not allowing edits etc. but nothing worked. All I want
to do is view the list of mountains by Type. I can do this by creating a
parameter query but that would be messy and not very user friendly.

Any further advice?

TIA


BigAl

 

[BigAl]

Be sure that the combo box is not bound to the Type field (see my comment
about the control source for the combo box).
Also, for the setup that you're using, the main form's RecordSource should
be "empty" (do not use the table), as you're not doing any data entry in
the main form.

Cheers Ken

Left the combo-box bound to the table.

All is now well and working as required.

Many thanks

BigAl