ACCESS FEED: RE: [MS_AccessPros] Re: Combo Box Cannot Be Updated

David

I'm going to guess that the problem was using fields that were bound in such a
way as to force Access to update them out of order. It kept trying until it
worked itself into a corner. Hence, the record locking.

When you have a form it is best to bind the form to just one table and use sub
forms for any subsequent tables. Crystal is famous for pointing this out. It's
her creed and one that should be followed in all but the most difficult
situations. You were mixing customer data with the data from the repair order.

Regards,

Bill

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of David Tolson
Sent: Saturday, August 27, 2011 7:21 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Re: Combo Box Cannot Be Updated

Bill, Yes my earlier response was the problem. After removing the customer
contact information from the form and placing it in a subform, the combo
boxes started to work. I don't have the skill to know why that is, but
suffice it to say that it works. Thanks for reaching out and have a great
day!

From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of David Tolson
Sent: Saturday, August 27, 2011 8:40 AM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: RE: [MS_AccessPros] Re: Combo Box Cannot Be Updated

I think I have found a solution. It appears if I remove customer contact
info and vehicle info from frmRepairOrder, and then create a subform with
that information, then the combo boxes work. I'll report back after
finishing the subform.

From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of David
Tolson
Sent: Saturday, August 27, 2011 7:12 AM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: RE: [MS_AccessPros] Re: Combo Box Cannot Be Updated

Hi Bill, Thanks for sending me a lifeline J. I was not sure which Select
clause you were referring to (the form or the
control(tblRepairOrder_RepairTypeID)), so I tried changing both of them.
Results:

1. When I changed the Record Source of the Form -
tblRepairOrder.RepairTypeID AS tblRepairOrder_RepairTypeID, I received the
following error when running the query: Duplicate output alias
'tblRepairOrder_RepairTypeID'.

2. So I changed the form SQL to read tblRepairType.RepairTypeID AS
tblRepairOrder_RepairTypeID, and I received the same error as above.

3. So I changed the form SQL to read tblRepairOrder.RepairTypeID AS
tblRepairType_RepairTypeID, and low and behold, the query ran, but I still
received the error on the combo box.

4. So I changed the Row Source accordingly and received a dialogue box
that asks me to enter a Value.

I sure it is something simple, but I can't find it yet.

From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Bill Mosca
Sent: Friday, August 26, 2011 5:47 PM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: [MS_AccessPros] Re: Combo Box Cannot Be Updated

David

I'm not sure if this will fix it but try this:

Is the RepairTypeID in tblRepairOrder? If so, try using it instead of
tblRepairType_RepairTypeID in your SELECT clause.

That goes for all fields in tblRepairOrder. Use the foreign key instead of
the primary key of the related table.

Using the primary keys in the other tables is probably causing the locking.

Regards,
Bill Mosca, Founder - MS_Access_Professionals
http://www.thatlldoit.com
Microsoft Office Access MVP
https://mvp.support.microsoft.com/profile/Bill.Mosca

--- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com> , "djsdaddy531"
<drtolson@...> wrote:
>
> I have two combo boxes in a form for Repair Orders in an automotive repair
shop: One is for the Repair Type (RepairTypeID) and the other is for the
Repair Technician (EmployeeID). If I the Repair Order combo box first when
the form is opened, I get the following Access error:
>
> "Field Cannot be updated"
>
> and when I click onto the Help button, I get the following:
>
> "The field cannot be updated because another user or process has locked
the corresponding record or table. (Error 3164)
>
> Possible causes:
>
> You tried to update a field in a record or table that is currently locked
by another user. Wait for the other user to finish working with the record
or table, and then try the operation again.
> In Microsoft Access, you tried to change the value of a control whose
Locked property is set to Yes."
>
> But both locked properties are set to No.
>
> If I click Ok a few times in the Access error dialogue box, the value
enters into the field, and the Repair Technician combo box is okay, but if I
reverse the process by clicking the Repair Technician combo box I get the
same access error - "Field cannot be updated", and then I can click on the
Repair Type Combo Box withe no problem.
>
> Here is the form's Record Source:
>
> SELECT tblRepairOrder.RepairOrderID, tblRepairOrder.CustomerID AS
tblRepairOrder_CustomerID, tblRepairOrder.CustomerVehicleID AS
tblRepairOrder_CusVehicleID, tblRepairOrder.RepairTypeID AS
tblRepairOrder_RepairTypeID, tblRepairOrder.RepairOrderPartsID,
tblRepairOrder.PartCost, tblRepairOrder.QtyParts, tblRepairOrder.LaborCost,
tblRepairOrder.DescribeRepair, tblRepairOrder.DateIn,
tblRepairOrder.DateOut, tblRepairOrder.DateOfWarrantyWork,
tblRepairOrder.MilageIn, tblRepairOrder.MileageOut,
tblRepairOrder.EmployeeID AS tblRepairOrder_EmployeeID,
tblMasterCustomer.CustomerID AS tblMasterCustomer_CustomerID,
tblMasterCustomer.FirstName, tblMasterCustomer.LastName,
tblMasterCustomer.[Street Address-Line 1], tblMasterCustomer.[Street
Address-Line 2], tblMasterCustomer.City, tblMasterCustomer.State,
tblMasterCustomer.ZipCode, tblMasterCustomer.HomePhone,
tblMasterCustomer.BusinessPhone, tblMasterCustomer.MobilePhone,
tblMasterCustomer.EmailAdress, tblCustomerVehicles.CustomerVehicleID AS
tblCustomerVehicles_CusVehicleID, tblCustomerVehicles.Make,
tblCustomerVehicles.Model, tblCustomerVehicles.ModYear,
tblCustomerVehicles.Color, tblCustomerVehicles.VIN,
tblCustomerVehicles.[Tag-State], tblRepairType.RepairTypeID AS
tblRepairType_RepairTypeID, tblMasterEmployee.EmployeeID AS
tblMasterEmployee_EmployeeID, tblRepairType.RepairType
> FROM tblRepairType INNER JOIN (tblMasterEmployee INNER JOIN
(tblMasterCustomer INNER JOIN (tblCustomerVehicles INNER JOIN tblRepairOrder
ON tblCustomerVehicles.CustomerVehicleID = tblRepairOrder.CustomerVehicleID)
ON tblMasterCustomer.CustomerID = tblCustomerVehicles.CustomerID) ON
tblMasterEmployee.EmployeeID = tblRepairOrder.EmployeeID) ON
tblRepairType.RepairTypeID = tblRepairOrder.RepairTypeID;
>
>
> The Repair Type cpmbo box control source is tblRepairOrder_RepairTypeID
>
> and the Row Source is
>
> SELECT tblRepairType.RepairTypeID, tblRepairType.RepairType,
tblRepairType.RepairCost FROM tblRepairType ORDER BY
tblRepairType.RepairType;
>
>
> the Repair Technician Combo Box control source is
tblRepairOrder_EmployeeID
>
> and the row source is
>
> SELECT tblMasterEmployee.EmployeeID, [LastName] & ", " & [Firstname] AS
Name FROM tblMasterEmployee ORDER BY [LastName] & ", " & [Firstname];
>
>
> Any assistance will be greatly appreciated. Thanks in advance.
>
> r/David
>

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]