combo box on form to update different table

[Guest]

Access 2000, Windows2000

Several tables:
ratings has fields: TrxID*, name, id, game, and shift, plus 20 others
customers has fields: Cname,Cid, StAdd, City, and so on
GAmes includes fields: name*
Shift includes fields: shiftname*

I have created a form to update the ratings table.
problem fields on this form are lookups
I want the name field to allow selection form those entries in the customer
table and display the selected name and ID in two separate fields on the
form, the ID should then be stored in the ratings table. The name( on the
form is for user input selection only) since it is already stored in the
customer table.

My name field on the form is defined as a combo box, with its control;
source = [customers]![Cname]. The rowsource = SELECT [Customers].[Cname],
[Customers].[Cid] FROM Customers.
This currently displays the fields availble( from the table) but will not
allow me to select anything. The status row display the message " Control
can't be edited, it's bound to the expression [customers]![name]"

I have verifed that the enabled properties is set to yes, and teh locked
property is set to no.

How can I get this field to allow selection of a record form the displayed
table and also fill in the CID value on the form. This CID value must be
saved as part of the ratings table.

Thanks to anyone who can point me in the right direction.

 

[Jeff Boyce]

I may not be following, but it sounds like you have the combo box source set
up opposite of the way I usually see it.

My comboboxes that allow selection of a "customer" do so by putting the
CustomerID in the first column and the CustomerName (actually, [LastName] &
", " & [FirstName]) in the second column. I then hide the first column
(folks don't want to have to remember IDs, they're much better with names,
anyway!) by setting the width to "0" (zero). That way, the user selects a
name from the combo box, and the control stores the ID.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

Oh yes, and I set the bound column to 1 (the ID).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Have solved the initial question regarding the "control can't be edited..."

But am still struggling with getting the CID field to be populated based on
the customer selected in the customer name field combo box.

 

[Pat Hartman \(MVP\)]

If you are using a combo for searching, it should not also be bound. Use a
separate bound field that will appear only when the form is on a new record.
Put the following in the form's Current event:

If Me.NewRecord then
Me.SomeCombo.Visible = True
Else
Me.SomeCombo.Visible = False
End If

 

[Guest]

Thanks to both Pat and Jeff, and I must also acknowledge Ken Snell and his
post at http://www.mvps.org/access/forms/frm0058.htm. Between all three
resources, my issue has been satisfactorily resolved.

This is a great forum for all of us.

If you are using a combo for searching, it should not also be bound. Use a
separate bound field that will appear only when the form is on a new record.
Put the following in the form's Current event:

If Me.NewRecord then
Me.SomeCombo.Visible = True
Else
Me.SomeCombo.Visible = False
End If

Access 2000, Windows2000

Several tables:
ratings has fields: TrxID*, name, id, game, and shift, plus 20 others
customers has fields: Cname,Cid, StAdd, City, and so on
GAmes includes fields: name*
Shift includes fields: shiftname*

I have created a form to update the ratings table.
problem fields on this form are lookups
I want the name field to allow selection form those entries in the
customer
table and display the selected name and ID in two separate fields on the
form, the ID should then be stored in the ratings table. The name( on the
form is for user input selection only) since it is already stored in the
customer table.

My name field on the form is defined as a combo box, with its control;
source = [customers]![Cname]. The rowsource = SELECT [Customers].[Cname],
[Customers].[Cid] FROM Customers.
This currently displays the fields availble( from the table) but will not
allow me to select anything. The status row display the message " Control
can't be edited, it's bound to the expression [customers]![name]"

I have verifed that the enabled properties is set to yes, and teh locked
property is set to no.

How can I get this field to allow selection of a record form the displayed
table and also fill in the CID value on the form. This CID value must be
saved as part of the ratings table.

Thanks to anyone who can point me in the right direction.