Problem on subform!

[ailyn]

I have a for for making orders called NordersMaken with a subform NordersDetails. Here is some info on my tables:

tblOrders (=form NordersMaken)
OrderId
OrderDate
Seller
Customer
...

tblOrderDetails (= Form NordersDetails)
OrderDetailId
OrderId
ProductId (from tblProducts)
BlowerId (from tblBlowers)
Price
...

tblProducts
ProductId
Ref
Serial
Avail (yes/no) /for item ordered = not available (value yes/true)
uit (yes/no) / for item invoiced = out of wharehouse (value yes/true)

tblBlowers
BlowerId
Type
Serial
Available (yes/no) /for item ordered = not available (value yes/true)
out (yes/no) / for item invoiced = out of wharehouse (value yes/true)

From the form Products(tblProducts) I send the ProductId intot he subform, NordersDetails and set the focus on the main form (NordersMaken) with this:

    DoCmd.OpenForm stDocName, , , , acFormAdd
    Forms!NordersMaken.Form!OrderDatectrl = Date
    Forms!NordersMaken.NordersDetails.Form!Serial = Me.ProductId

The problem: when I try to change details in the subform (NordersMaken) like Price I get an error message saying that the JETDB couldn't find the record OrderDetails.BlowerId. Just because I didn't select it. what if I don't need/want to? How could I leave it blank and/or avoid those errors?

 

[seth_belgium]

I'm afraid I don't quite get it...

Can you post the database again with the form that causes this included?

Greetz,

Seth

 

[ailyn]

Ok, here you have it. You'll have to open form Products and click order this item (when the order checkbox is not marked), it will open NordersMaken with the productId in its subform. The problem comes when you don't pick a blowerId.

 

[ailyn]

Anyone's got a clue on this error, please???

 

[ailyn]

Anyone that knows how to avoid filling in the second value? Pleeaaase

 

[ailyn]

...help, please?

 

[x0reset]

Is BlowerId part of a compound Primary Key in tlbOrderDetails?

 

[ailyn]

Yes, indeed. BlowerId and ProductId are primary keys from tables Products and Blowers. Although in OrderDetails they show the serial numbers with a normal table query.

 

[ailyn]

I tried catching the error on the subform on GotFocus and on LostFocus I added this:

If Err = 3101 Then Err.Clear

I already changed the relationships from one-to-many to normal for 'tblProducts' and 'tblBlowers' with 'tblOrderDetails'. But the error keeps appearing and doesn't let me do anything else if I don't select them both. Any suggestions?

 

[x0reset]

One way or the other this sounds like a primary key violation.

If BlowerId is part of a compound primary key in tblOrderDetails your getting a compound primary key violation. You can't leave a field that is part of a primary key blank! Try setting your primary key in tblOrderDetails to just OrderDetailId.

On the same note, if BlowerId is not part of a compound primary key, but it is a foreign key pointing to tblBlowers, it will require a reference to tblBlowers.

You could solve this by modifying the type of relationship between the two tables in the relationships screen (NOT recommended), or preferably, add a record to tblBlowers that can be used as a "Blank" reference, then set the "Default Value" (in the Data Tab of the properties) of the control bound to BlowerId to the primary key of the blank reference.

Hope this helps.

 

[ailyn]

Thanks a lot X0reset!!! I tried to avoid that because it included a lot of changes with all my queries and filters but it was worth it. Now it works perfectly, thanks!