Combo Box

[PHisaw]

Question

Hi,

I have a form that opens to specific record when a check box is selected on
another form. It is based on a query with SQL as follows:

SELECT tWarrantyInfo.TotalCost, tWarrantyInfo.WarrantyType,
qGeneralInfo.JobNumber, tStatus.Warranty, tStatus.Invoiced,
tStatus.Completed, qGeneralInfo.CustomerName, qGeneralInfo.Address,
qGeneralInfo.City, qGeneralInfo.State, qGeneralInfo.Zip, qGeneralInfo.Phone,
qGeneralInfo.ContactName, qGeneralInfo.PumpType, qGeneralInfo.Fax,
qGeneralInfo.Model, qGeneralInfo.PurchaseOrderNumber,
qGeneralInfo.SerialNumber, tWarrantyInfo.WarrantyComments,
qGeneralInfo.MachineAssignment, qGeneralInfo.JobAssignment
FROM (tStatus INNER JOIN qGeneralInfo ON tStatus.JobNumber =
qGeneralInfo.JobNumber) INNER JOIN tWarrantyInfo ON qGeneralInfo.JobNumber =
tWarrantyInfo.JobNumber
WITH OWNERACCESS OPTION;

There is a combo box on this form "WarrantyType" and has two options,
Factory or House. The selections are there in the drop down, but I can't
select either to place in the combo box.
At first it was bound to the WarrantyType field on the
table. I read thru other posts and thought I had corrected it by making the
combo box
unbound, row source type was a value list and row source was "Factory";
"Value". Then I put a text box "txtWarrantyType" bound to WarrantyType as a
hidden field and had the AfterUpdate of the combo box set to

Me.TxtWarrantyType = Me.CboWarrantyType

This only returns a "recordset not updateable" error message. I only have
two tables on the query for the form. I can't figure out what's causing this.

If anyone can help, it would be appreciated!
Thanks,
Pam

 

[Barry Gilbert]

It's not clear to me what you are doing with the selected value in the
combobox. Do you want to use it to update the WarrantyType field in the
form's table? If so, the rowsource should be the value list you mentioned and
the combobox should be bound to the WarrantyType field. If things aren't
showing up correctly, take a look at the combobox's Column Widths, Bound
Column, and Column Count properties.

Barry

 

[PHisaw]

Barry,
Thanks for the reply. I want the value I selected from the combo drop down
to be stored in the table for WarrantyType field. Please see following for
combo box:
ControlSource: WarrantyType
RowSourceType: ValueList
RowSource: "Factory";"House"
ColumnCount: 1
ColumnHeads: No
ColumnWidths: 1"
BoundColumn: 1

What am I missing to make this work?
Thanks for your help.
Pam

 

[Barry Gilbert]

The other things to verify are that the WarrantyType field in your table is
set to a Text data type and that it will accept at least 7 characters. Also,
need to make sure that the combobox control's Locked property is set to False.

Are you getting an error or does it just sit there and not accept the value?

Barry

 

[PHisaw]

Barry,
The table field is Text set to 50 and the combo's Locked property is set at
No.
It doesn't give a message, but the two values are in the drop down. They
can't be selected. There is just a sound when they are selected and the box
remains empty.

Pam

 

[Barry Gilbert]

Can other fields in the table be changed through the form?

If you unbind the combobox (set it's control source to blank), can you
select a value?

Barry

 

[PHisaw]

Barry,

I was only concentrating on the combo box, but no I can't change anything
else on the form - same error sound. If I unbind the combo box, I can select
a value, but I get a VB error msg "Recordset is not updateable".

Pam

 

[Barry Gilbert]

It sounds like an issue with the underlying table/query. If it's bound to a
query, it needs to be an updateable query. I would suggest opening the query
directly and seeing if you can add/modify records. In the form, look at the
properties on the form's data tab. Allow Edits, Allow Additions, Data Entry,
and Recordset Type could all impact your ability o work with data.

Barry

 

[PHisaw]

Barry,

The query isn't updateable. I looked at all you suggested and still can't
figure out why.
Do you have any other suggestions?
Pam

 

[John Vinson]

The query isn't updateable. I looked at all you suggested and still can't
figure out why.

Take a look at the Access Help for "updateable". Some common issues:

- no Totals query, or any query containing any totals operation, is
ever updateable
- No UNION query is updateable
- If the Query's Unique Values property is set, it won't be updateable
- If you have two or more tables joined, there must be a unique Index
(such as a primary key) in the "one" side table's join field, and a
relationship defined between the tables. Not all multitable queries
are updateable, and they can be confusing, giving multiple
partially-duplicated records. It's generally best not to use
multitable queries as form recordsources.

John W. Vinson[MVP]

 

[PHisaw]

Barry and John,
Thank you both for the help and insight. I've read the updateable help
topics and have checked much of the info you've provided. At this point, I'm
going to put the combo box on a subform based only on the table needed and
see if it will work any better.
Thanks again for your time and help. As always, very much appreciated!
Pam