Data entry in subform flows through to other figures

[dallnsn]

Hi Everyone,

Please excuse my inability to work with Access, but I am trying. I have a subform in my database that pulls data from an access query. I have 3 fields on the subform, Product Description, Due and Dispatch. Its a order entry subform.

The problem is, just say there was 4 items with due figures of 4, 3, 2, 1.
When I enter data in the dispatch column for item 1 of 4, the 4 is carried down to the rest of the items, thus displaying an error that I have coded. Basically If Dispatch > Due, then "Display error"

My question is, how can I stop this figure from being carried through down the dispatch column affecting the other records.

Any assistance is greatly appreciated.

 

[Banana]

I'm kind of confused because I don't know the value for Dispatch and why would the '4' get carried down.

If you can elaborate a bit, especially about what Dispatch contains (I'm guessing it's a quantity of items to be sent, but not entirely sure here), and how it is used with Due (I'm guessing that it's to verify that all quantity has been sent or?), and what error you have coded and how is this calculated?

 

[dallnsn]

Hi there,

The subform contains 3 data items. Description for a title of a book.
Once a book has been ordered and there is no stock, that figure goes over to due - how many books are due to the customer.
Dispatch is once items are received in stock, how many items are dispatched to the particular customer.

Okay, so if 2 different books have been ordered.
And both books have got a due amount of 2.

Just say that I only have enough stock to dispatch 1 particular title, but I can dispatch 2 of the other title. When I put the 1 in the top dispatch record, the 1 carries down to the below record, but I want 2 in that box.

This is the vba that I have used to code this particular section.

Private Sub Dispatch_AfterUpdate()
Dim strMessage As String, strTitle As String
Dim intStyle As Integer
If Me.Dispatch > Me.Due Then
strMessage = "Enter a number between 0 and " & Me.Due
strTitle = "Error"
intStyle = (vbCritical + vbOKOnly)
MsgBox strMessage, intStyle, strTitle
Me.Dispatch.SetFocus
Me.Dispatch.Requery
End If

 

[MagicMan]

It sounds like the sub-form is a continuous form, and the entry field is unbound. When an entry is made, it will cascade into all the records. You have to bind the Due field to a table.

 

[dallnsn]

Hi MagicMan,

Thanks for the reply. I have changed those details you suggested, The form looks best in data sheet and I have bound the entry to the table, however, I can't enter any figures in there. I have allowed additions, deletions, and yes to data entry, however, the figures are right, but I cannot edit them.

Any suggestions?


dallnsn

 

[MagicMan]

1) Is the data source a table or a query? A query depending on it's construct may not allow updates.
2) The subform (datasheet) Data Entry Property should normally be No if you wish to do updates to existing data.

 

[dallnsn]

Hi MagicMan,

The data source of the subform is based on a query, described below

SELECT DISTINCTROW Description, NbrRequested - NbrDispatched AS Due, NbrDispatched AS Dispatch, tblOrderItem.OrderNbr, tblItem.StockHolding
FROM tblOrderItem, tblItem, tblCustomer, tblOrder
WHERE tblItem.ItemID=tblOrderItem.ItemID AND tblCustomer.CustomerID=tblOrder.CustomerID
ORDER BY Description;

Problem is, it just won't let me edit the figure, I can click on it, but can't put a different figure in.

Regards,


Dallnsn

 

[MagicMan]

Select Distinctrow omits data based on Duplicate Records. If you were to change a value on the results set, Access does not know if it should change just the record selected, or any or all of the duplicates. This query is most likely not updatable. To change the value, you may have to create one or more unique forms which present all the records containing the data item you wish to change. This could possibly be done using popup subforms, or part of the the main form. Hopefully, you understand your data and can figure out how to effect a change.
Smiles
Bob