ComboBox Clearing ONLY on new record, but NOT on viewing records

[BartonConstruction]

Greetings all,

These groups have been a lifesaver (not to mention the hair pulling
reduction offered by the insight within these messages). Having said that, I
have a question that I have been searching for answers to throughout the
groups (and elsewhere).

I have a form that I use to view and input client data. The form has a
combo box that users can select an insurance company from. Without going
into all the particulars, there are about 10 insurance companies to select
from. My problem is in the viewing AND adding clients. The combobox updates
a table (tblClients.InsuranceCompanyName). This I have working correctly.
What I need to have happen is that when I goto a new record, I DONT want the
combobox to show the last selected name in list of the combobox. On the
other hand, I cant use form_load or Current() to clear the combobox (by
setting its value to Null) because I want the form to show the currently
selected client's insurance company. So, to reiterate, I need the form's
combobox to clear ONLY on a new record, not on an already created record.

Windy? I hope I have offered enough info to be clear.

 

[tina]

the behavior you're describing - combobox shows last selected name, on a new
record - sounds like an unbound control. but you say that the combo box
updates a table (the table which the form is bound to?), which sounds like a
bound control. is the combobox control bound to the
tblClients.InsuranceCompanyName field in the form's RecordSource? or is the
combobox control unbound?

hth

 

[BartonConstruction]

Well, I have tried it both ways. I WANT it to show a choice when entering a
new client on the form. I ALSO want it to show the already selected (and
saved to the underlying table) insurance company name for clients already
created. There is currently nothing showing in Control Source for the
cboInsuranceName though the Row Source has this: SELECT
tblInsuranceCompany.InsuranceCompanyID, tblInsuranceCompany.[Company Name]
FROM tblInsuranceCompany;

There are two columns, only one of which (the second=[Company Name]) is
shown in the combobox. I guess what I cant figure out is how to

1) Show the underlying table info on the form (in the combo box) for those
clients already created.

AND

2) When creating a new client, have that same combobox be blank until a
selection is made (which would then update the underlying table field).

I think I may have just confused myself, so any advice would be greatly
appreciated.

Dave

 

[BartonConstruction]

An update--puting InsuranceCompanyName in the Control Source for the combobox
DOES show the correct company name for the selected client. Now all I need
is to clear that box when I goto a new (and blank except for the combobox
which is showing the last record's selection) record. I was thinking about
making the cboInsuranceCompanyName = Forms!tblClients.ClientID.Column(8).
Havent tried it yet and am sure the syntax is off, I can look that up later.
BTW, Column(8) is the Insurance Company Name column of the tblClients. Now,
looking at the above code, I already see a problem (since the syntax is all
screwed up). I'll play with it, again, any advice would be great and deeply
appreciated.

dave

 

[tina]

comments inline.

An update--puting InsuranceCompanyName in the Control Source for the combobox
DOES show the correct company name for the selected client.

right. by putting the fieldname in the control's ControlSource property, the
combobox is a "bound" control, meaning that it's bound to a field in the
form's RecordSource - whereas, before, it was an "unbound" control, meaning
(of course) that it was NOT bound to any field in the form's RecordSource.

Now all I need
is to clear that box when I goto a new (and blank except for the combobox
which is showing the last record's selection) record.

no, you don't need to do anything. a bound control always displays the value
in its' underlying field, for the current record in the form. so when you go
to a new record, there's no value in that field in the current (new) record,
so the control will be blank - because there's no value to display.

note that the only caveat to the above is if the Default property of the
control in the form, or of the field in the table, is set to something; then
that Default value will be displayed on a new record.

hth