Query save issue

[Ryan Chamberlin via AccessMonster.com]

I have a form designed for bills of materials for hotel work we complete. I have multiple queries that list our inventory on this form.

When I open the form normally, I can see all of our stock name listed.

If I enter the information for an item directly into the table, I can see everything fine, as well as save changes.

My problem is that if I enter into the form, (the Quantity on hand, quantity ordered, etc..) I get an error message as soon as i attempt to leave the row. The error is: "You cannot add or change a record because a related record is required in table 'tblBOMmain'"

Does anybody have any iea on what I'm missing out on here?

*****************************************
* This message was posted via http://www.accessmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.accessmonster.com/Uwe/Abuse.aspx?aid=a6226aac1d784e35985bb36629ee4a78
*****************************************

 

[Ken Snell [MVP]]

The error message means that when you create a new record via the form,
you're not giving the record the value of the foreign key field (the field
that is related to the main table tblBOMmain). Is the query joined to that
table? You'll need to give us more info about your query and the form's
setup / recordsource.

--

Ken Snell
<MS ACCESS MVP>

I have a form designed for bills of materials for hotel work we complete.

I have multiple queries that list our inventory on this form.

When I open the form normally, I can see all of our stock name listed.

If I enter the information for an item directly into the table, I can see

everything fine, as well as save changes.

My problem is that if I enter into the form, (the Quantity on hand,

quantity ordered, etc..) I get an error message as soon as i attempt to
leave the row. The error is: "You cannot add or change a record because a
related record is required in table 'tblBOMmain'"

 

[Ryan Chamberlin via AccessMonster.com]

Ok, I'm not sure exactly what information is needed, but I'll give it a shot.

My form is based off of 4 tables.
TblBOMmain:
BOMid, HotelId

TblPartsList:
ID, PartNum, StockNum, Description, ItemCode

tblInventoryCount:
rowID, ItemID, OnHand, MaxHold, ReOrderNum, OnOrder, Remarks

tblBOMdetails:
RowID, BOMid, ItemID, QuantityRequested, QuantityShipped

My setup is that I have 1 form open that specifies the BOMid, and HotelId (in tblBOMmain). So I match this criteria when I open the troubled form. Each query on the form filters the items by their ItemCode (in tblPartsList).

The tables relationships are linked by:
TblBOMmain/BOMid -1 to many- tblBOMdetails/BomID
TblInventoryCount/Itemid -1 to many- tblBOMdetails/ItemID
TblInventoryCount/Itemid -1 to 1- tblPartsList/ID

The SQL code for each query is similar to this, with the exception of the item code being different:

SELECT tblPartsList.strPartNum, tblPartsList.StrStockNum, tblPartsList.strDescription, tblPartsList.Manufacturer, tblBOMdetails.QuantityRequested, tblInvCount.OnHand, tblBOMdetails.QuantityShipped, tblInvCount.OnOrder, tblInvCount.Remarks
FROM (tblPartsList LEFT JOIN tblBOMdetails ON tblPartsList.ID = tblBOMdetails.ItemID) LEFT JOIN tblInvCount ON tblBOMdetails.ItemID = tblInvCount.ItemID
WHERE (tblPartsList.ItemCode= 2) AND (tblBOMdetails.BomID= [frmBOMmainSheet].[BOMID]
Or tblBOMdetails.BomID Is Null);

This issue has stumped me for 2 weeks now.
Hoping you can help.

*****************************************
* A copy of the whole thread can be found at:
* http://www.accessmonster.com/uwe/Forum.aspx/access-queries/16409
*
* Report spam or abuse by clicking the following URL:
* http://www.accessmonster.com/Uwe/Abuse.aspx?aid=366efb6e1d824335b578d27c3d414bb2
*****************************************

 

[Ken Snell [MVP]]

The SQL statement that you posted does not include the BomID field from
tblBOMdetails. This field must have a value in it, and that value must be
one that is in the BOMid field in TblBOMmain table. You need to add the
tblBOMdetails.BomID field to the query, and give it a value.

--

Ken Snell
<MS ACCESS MVP>

Ok, I'm not sure exactly what information is needed, but I'll give it a shot.

My form is based off of 4 tables.
TblBOMmain:
BOMid, HotelId

TblPartsList:
ID, PartNum, StockNum, Description, ItemCode

tblInventoryCount:
rowID, ItemID, OnHand, MaxHold, ReOrderNum, OnOrder, Remarks

tblBOMdetails:
RowID, BOMid, ItemID, QuantityRequested, QuantityShipped

My setup is that I have 1 form open that specifies the BOMid, and HotelId

(in tblBOMmain). So I match this criteria when I open the troubled form.
Each query on the form filters the items by their ItemCode (in
tblPartsList).

The tables relationships are linked by:
TblBOMmain/BOMid -1 to many- tblBOMdetails/BomID
TblInventoryCount/Itemid -1 to many- tblBOMdetails/ItemID
TblInventoryCount/Itemid -1 to 1- tblPartsList/ID

The SQL code for each query is similar to this, with the exception of the item code being different:

SELECT tblPartsList.strPartNum, tblPartsList.StrStockNum,

tblPartsList.strDescription, tblPartsList.Manufacturer,
tblBOMdetails.QuantityRequested, tblInvCount.OnHand,
tblBOMdetails.QuantityShipped, tblInvCount.OnOrder, tblInvCount.Remarks

FROM (tblPartsList LEFT JOIN tblBOMdetails ON tblPartsList.ID =

tblBOMdetails.ItemID) LEFT JOIN tblInvCount ON tblBOMdetails.ItemID =
tblInvCount.ItemID

WHERE (tblPartsList.ItemCode= 2) AND (tblBOMdetails.BomID= [frmBOMmainSheet].[BOMID]
Or tblBOMdetails.BomID Is Null);

This issue has stumped me for 2 weeks now.
Hoping you can help.

*****************************************
* A copy of the whole thread can be found at:
* http://www.accessmonster.com/uwe/Forum.aspx/access-queries/16409
*
* Report spam or abuse by clicking the following URL:
* http://www.accessmonster.com/Uwe/Abuse.aspx?aid=366efb6e1d824335b578d27c3d414bb2
*****************************************

 

[Ryan Chamberlin via AccessMonster.com]

Ok, this almost works. When I use this, I now get the field you listed, and you were correct. The issue is that it is not populating the BOMID field with any info. My only issue now is how to have the system fill this information in after the query is completed. (It should pull up around 30 records). I have tried to set the default value for the BOMID field, but this limits the results to only those that have already been entered. Is there an easy way to set all of the records BOMID fields to match Me.BOMID after all the results are located?


New Query:
SELECT tblPartsList.strPartNum, tblPartsList.strStockNum, tblPartsList.strDescription, tblPartsList.Manufacturer, tblBOMdetails.QuantityRequested, tblInvCount.OnHand, tblBOMdetails.QuantityShipped, tblInvCount.OnOrder, tblInvCount.Remarks, tblBOMdetails.BomID, tblPartsList.ItemCode
FROM tblPartsList LEFT JOIN (tblBOMdetails LEFT JOIN tblInvCount ON tblBOMdetails.ItemID = tblInvCount.ItemID) ON tblPartsList.ID = tblBOMdetails.ItemID
WHERE (((tblBOMdetails.BomID)=[frmBOMmainSheet].[BOMID] Or (tblBOMdetails.BomID) Is Null) AND ((tblPartsList.ItemCode)=1));

By the way, thanks for all your time and help on this!

*****************************************
* A copy of the whole thread can be found at:
* http://www.accessmonster.com/uwe/Forum.aspx/access-queries/16409
*
* Report spam or abuse by clicking the following URL:
* http://www.accessmonster.com/Uwe/Abuse.aspx?aid=12fe5499ab1b45b4b682e51b3d58a50c
*****************************************

 

[Ken Snell [MVP]]

Your query is pulling existing records. So I assume that you're editing the
records on the second form? Or are you adding new records via the second
form, too?

The easiest way to have the field be "auto-filled" would be to NOT use a
second form, but rather use a subform to display the records from the query.
That way, the mainform will take care of filling in the value of BOMID field
in the child table for you. Can you modify your setup to do this?

If not, then I need answers to a few more questions:

1) Is the original form (? frmBOMmainSheet ?), on which the value of BOMID
is found, still open when the second form is open?

2) Are you just editing data in existing records? Or are you also adding new
records in the second form?
--

Ken Snell
<MS ACCESS MVP>

Ok, this almost works. When I use this, I now get the field you listed,

and you were correct. The issue is that it is not populating the BOMID field
with any info. My only issue now is how to have the system fill this
information in after the query is completed. (It should pull up around 30
records). I have tried to set the default value for the BOMID field, but
this limits the results to only those that have already been entered. Is
there an easy way to set all of the records BOMID fields to match Me.BOMID
after all the results are located?

New Query:
SELECT tblPartsList.strPartNum, tblPartsList.strStockNum,

tblPartsList.strDescription, tblPartsList.Manufacturer,
tblBOMdetails.QuantityRequested, tblInvCount.OnHand,
tblBOMdetails.QuantityShipped, tblInvCount.OnOrder, tblInvCount.Remarks,
tblBOMdetails.BomID, tblPartsList.ItemCode

FROM tblPartsList LEFT JOIN (tblBOMdetails LEFT JOIN tblInvCount ON

tblBOMdetails.ItemID = tblInvCount.ItemID) ON tblPartsList.ID =
tblBOMdetails.ItemID

WHERE (((tblBOMdetails.BomID)=[frmBOMmainSheet].[BOMID] Or

(tblBOMdetails.BomID) Is Null) AND ((tblPartsList.ItemCode)=1));