set combobox value

[AccessNewbie]

Access 2003

I have a form that is based on an Order table. The records in the table
have already been entered. This form is being used to edit the existing
records.

When the record is initially entered the user can choose a Client from a
populated combobox. when the record is saved the client is written to the
OrderTable.

I now want to edit the Order records but also giving the user the option to
choose a different client from the combo box.

How can i set the combo box to the value that is already stored in the
orders table thus allowing them to change it from a list?

Thanks

 

[Fred Boer]

Dear AccessNewbie:

Bind the combobox to the "client" field in the table. Open the properties
sheet for the combobox control, and set the "Control Source" of the combobox
to the field in which the "client" data is stored..


HTH
Fred Boer

 

[Klatuu]

There is a bit of a problem with that, Fred.

When you bind a combo box to a field in the record source, you really can't
use it to navigate. As soon as you select something different from the
current record, it will try to update the value selected in the current
record. This becomes problematic.

I don't fully understand the OP's question, but I am wondering if he is
really asking how to get the new value in the row source of the combo. If
that is the question, the answer is to requery the combo.

If that is not the question, perhaps you could restate the question, please,
Access Newbie

 

[Fred Boer]

Hi Dave:

Well, if the combobox was used to "find a record", then, yes, that would not
work. But I thought that the OP wanted to use the combobox for data
entry/edit, not record navigation. So, that's why I suggested binding the
combobox to the field.

Of course if there is existing record navigation code in the combobox, this
*would* mess things up. But perhaps I am wrong?

Fred

 

[Klatuu]

Fred:

I re read the OP a couple of times, and it is still unclear what the real
question is.
AccessNewbie, if you are following this thread, please restate your question.

 

[AccessNewbie]

ok here goes:

I have two forms frmEnter and frmUpdate.

The frmEnter is bound to the Orders table and the cbxClient has a rowsource
based on the Client table. The user selects the client from the combobox and
this is stored in the Orders table.

The user then wants to Amend the records that have been entered using the
frmUpdate form. This form is bound to the Orders Table.

The problem that I have is that the user wants to change the client
(originally entered via a combobox option).

Is there a way of having a combobox that is based on the client table but
the item that is selected by default in the combobox is the value that is
already stored in the client field in the Orders Table?

 

[Klatuu]

If what you are asking is how to show the Client in a combo box that is the
client for the current Order record, then it can be done a couple of ways.
If the combo is bound to the Client field in the Order record, that it
should always show the client for the current record.
If the combo is unbound and used for looking up a client, then you can use
the form Current event to keep the combo in sync. You would need a text box
(it can be invisible) that is bound to the client field. Then in the Current
event:

Me.cboClient = Me.txtClient

 

[Fred Boer]

Dear Access Newbie:

It still seems to me that what you want is to bind the combobox to the field
in the form's recordset. Be sure that the correct field in the rowsource of
the combobox is selected as the "bound" column. In any case, try the
suggestions you've been given and let us know what worked or not.

Cheers!
Fred

 

[AccessNewbie]

Thanks for all the responses.
I couldn't get it to work so in the end I think I have done a variation of
what was suggested.

I have a textbox (txtClient) that shows the current Client as per the Orders
Table (this is locked). If the user wishes to change it they have to press
the Change button which inturn enables a combobox bound to the Client Table.
In the AfterUpdate event of the combobox it updates the value in the
txtClient textbox.