The Value cannot be added...until...committed.

[mjdemaris]

Hi all!
Working on a dual-purpose form for adding new or editing existing items. The form's Record Source is :

SELECT [T-Parts].MasterNum, [T-Parts].Item, [T-Parts].CategoryID_FK, [T-Parts].WinterLevel, [T-Parts].SummerLevel, [TPartLocations].LocationID_FK
FROM ([T-Categories] INNER JOIN [T-Parts] ON [T-Categories].CategoryID = [T-Parts].CategoryID_FK) INNER JOIN [T-PartLocations] ON [T-Parts].MasterNum = [T-PartLocations].PartID_FK;

The problem lies with the LocationID_FK text box. I get the message in the status bar, when I click on a value: The Value cannot be added to the new row until the value has been committed. Commit the row first, then try adding the value.

LocationID_FK text box Row source is a query that combines the warehouse and bin location to one field. The control source is that field in the PartLocations table.

The tables are editable, the form is in add new mode. The query is also used as a row source for another table, to provide the same info (combined locations).

Mike

Just tried using the form in the edit mode and I was able to change the Part location just fine.

AddNewRecord VBA:
TempVars!tmpRecEntry = 1
DoCmd.OpenForm "F1_PartsEntry", acNormal, , , acFormAdd, acDialog

Tempvars used to set visible properties for other boxes.

EditRecord:
TempVars!tmpRecEntry = 2
DoCmd.OpenForm "F1_PartsEntry", acNormal, , , acFormEdit, acDialog

 

[llkhoutx]

Are you sure that the form has an updateable recordset? With 2 joins in your SQL, I doubt that it is.

 

[mjdemaris]

This is what opens the Add New form:

DoCmd.OpenForm "F1_PartsEntry", acNormal, , , acFormAdd, acDialog

This opens the Edit form:

DoCmd.OpenForm "F1_PartsEntry", acNormal, , "[MasterNum]= [TempVars]!tmpMaster", acFormEdit, acDialog

It is the same form, just setup for different operations. The Edit feature works fine, just not the New.

I removed the join on the Category table, and still no dice.

 

[llkhoutx]

I can't tell what's going on with the information you've provided.

 

[mjdemaris]

Right, looks like we have a lack of communication.
So, frmMain has a list box. I click on a line in the list box, then click Edit button. A frmParts opens with the selected record's data in the text boxes. All fields are editable. No problem.

If I click the Add New button, vs. Edit, on frmMain, the same frmParts opens, only in Add mode, and all text boxes are blank, except for the PK. I can type data into each text box, except the Location combo box. I cannot change this value when I open frmParts in Add mode.

The Control source for this combo is PartLocations table, has 3 fields: ID, PartID_FK, and LocationID_FK. This last field is a combo as well, using a query to take a value from two tables and combines them onto the PartLocations table.

 

[mjdemaris]

Here is a diagram, think I got it all here.

 

[mjdemaris]

Houston: does this diagram make sense?