updating Subform

[bassamsuha]

Hi,
I have 2 subforms in my main form each of them has its own Autoincrement ID number.
When I try to add new record and filling table fields It showing me an error :The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship.

I know the problem is with the auto increment field that is not updating, any way to do it ??

Thanks

[bassamsuha]

Hi,
I forgot to minsion that one subform is from query when i try to update or add new record i got this error (Field cannot be updated).

Thanks in Advanced.

[PEB]

This is an unupdatabale query! And you can't use it to update information by it!
You have to transform your query in updatable by using table / or linked tables that can be updated!

In function of the link the query may be updatable or not!

Take care what kind of link are you doing and which table you will edit, append or update with your select query!

[PEB]

And your child field in the properties window for the subform have to be a field that is indexed but allows dublicates!

And your master field have to be a key for the main form!

[bassamsuha]

Thanks Bep I really appreciate your queck help.

What I understood is that I need a select query to get my records and another update query and another append query to do both actions.

the question is: how to call these two queries?? from where??
I have Add new button and Save button.

Thanks in Advanced

[PEB]

In fact the only thing that I said is that:

YOU NEED TO USE AN UPDATABLE SELECT QUERY FOR YOUR SUBFORM /S

IF it isn't the case so you have to think how to do your queries updatable! It's NOT by Append or Update queries! It's by changing your SELECT query!

If you want you can describe your query : tables, links and fields from tables and see here what we can do together!

About the forms!

For each control that is displayed in the form have properties! The subform which is located in the form also has properties that you can see them clicking in the respective subform once pressing the right mouse bouton if you are with right handing mouse and choose properties!

In the properties you have to flook for Link master field and link chield field properties!

Those properties do the connection between Main and subform!

Principally In the Chield Field /s we enter information from the main form that concerns the subform! The relation is: the first field from Link Master Fields corresponds to the first field from Link Chield Fields!

You have to be carefull about the information that you type in those fields!

Hope that this time I've been more explicit! ;)

[bassamsuha]

Thanks again Peb, this problem has been solved.
I have another issue that i want one field in the subform to be a combo box when one of the value selected, rest of the fields should be changed to match the selected value.
any idea on how to do it.

[PEB]

In fact when the user choose a value from the combo box, Values in other columns in the combo box have to be placed in the fields of your subform, is this your question?

[comteck]

Not sure if this is what you are looking for, but here is what I did for a similar situation. What happens in my case is I make a selection from a pull-down list from the combo box, and the price appears in the text box.

I have 2 tables with following fields:

tblrefPartsList is just a list of all the parts and a second column listing the price for each part. It has the following fields:
PartID - Autonumber (PK)
refPart - Text
refCost - Currency

tblTBMPartsCost is the child table that stores all the parts used for each customer. It has the following fields:
TBMID - Number (PK)
PartID - Number (PK) - Same as PartID in tblrefPartsList
PartName - Text - Same as refPart in tblrefPartsList
PartCost - Currency

My subform is based on tblTBMPartsCosts. There are 3 controls on the subform - 1 combo box, and 2 text boxes. The combo box is tied to the PartID. Its RowSource property has the following:

SELECT DISTINCTROW [tblrefPartsList].[PartID], [tblrefPartsList].[refPart], [tblrefPartsList].[refCost] FROM [tblrefPartsList];

The following properties has to be set in the combo box as well:
Column Count - 3
Column Width - 0";0.5";0"
Bound Column - 1

PartName is another textbox included on the subform. It has no code associated with it.

The other textbox is PartCost. It has the following BeforeUpdate event procedure:

Private Sub Cost_BeforeUpdate(Cancel As Integer)
Me.PartCost = Me.PartName.Column(2)
End Sub

Hope this helps.
comteck