Query results not matching data entered on ORDERS form.

[weightinwildcat]

I am working on a function that will cause a email messages to be sent when payment is received for an order. There can be more than one line item for each order, so I have to use queries rather than get values from checkboxes or other objects on my order form. The four fields I need to reference are boolean, and my code gets totals on each of them. So a True value is actually less than 0. The fields represent the following:

Payment Required to Start Work
Payment Received to Start Work
Payment Required to Ship
Payment Received to Ship

If the first two boolean values are True (less than 0) and have an equal value, and Payment Required to Ship does not equal Payment Received to Ship, a message should be sent that a down payment has been received. If all the values are equal and less than 0, a message should be sent that payment has bee received for the whole order.

The problem I am running into is that, even after I enter a checkmark in a checkbox, the new value does not get piacked up by my query. So I get a 0 when I should get a number less than 0, and the criteria do not get met.

I have tried DoCmd.RunCommand acCmdSaveRecord and Me.Dirty = False in the Click subroutines and the AfterUpdate subroutines for the Payment Received to Start Work checkbox and the Payment Received to Ship checkbox. So far, no luck.

Here is the query:

Set rs3 = db.OpenRecordset("SELECT ORDERS.OrderNumber, REPRESENTATIVES.Initials, " & _
"Sum(Not IsNull([PayStartReqDate])) As PayStartReq, Sum(Not IsNull([PayShipReqDate])) AS PayShipReq," & _
"Sum(Not IsNull([PayStartRecvdDate])) As PayStartRecvd, Sum(Not IsNull([PayShipRecvdDate])) AS PayShipRecvd " & _
"FROM REPRESENTATIVES INNER JOIN (ORDERS LEFT JOIN Payments ON ORDERS.OrderID = Payments.OrderID) " & _
"ON REPRESENTATIVES.RepID = ORDERS.RepID " & _
"WHERE ORDERS.OrderNumber = " & OrderNumber & _
" GROUP BY ORDERS.OrderNumber, REPRESENTATIVES.Initials", dbReadOnly)

Any thoughts on what else I should look at?

Thank you in advance.

 

[LaurieHamlin]

You have no criteria in your query to pull where not equal. I must be missing something. You say Payment Required to Start Work and
Payment Received to Start Work are booleans. If Payment Required to Ship and Payment Received to Ship are not booleans, why would they be <0?

 

[weightinwildcat]

A Boolean is essentially a Yes/No, zero or not zero, with a not zero actually being a minus one. Just to let you know, I tried simply counting values (as opposed to summing them), and ran into the same sort of problem: my query results do not match the entries I make on my form. If each sales order had only one line item I could just get values directly from my form, but there can be multiple line items for each sales order.

 

[LaurieHamlin]

Exactly. That does not answer my question. What data type are the Payment Required to Ship and Payment Received to Ship fields?

 

[weightinwildcat]

Those are boolean.

 

[LaurieHamlin]

I would put this in the After Update:
If [Payment Required to Ship] <> [Payment Received to Ship] Then
MsgBox("down payment has been received")
Else
If [Payment Required to Ship] = True And [Payment Received to Ship] = True Then
MsgBox("payment received for the whole order")
End If
End If

 

[weightinwildcat]

That still will not tell me why I can have a check box checked off on my form, but when I query the underlying tables I do not get information that matches

 

[Andrzejek]

I guess you need to find out exactly when your checkbox's state gets transfered as data change to your table.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Defactio]

Maybe I'm missing something obvious, but you seem to expect the underlying table (that stores a checkbox value) to immediately reflect a change on the form, and then basing logic on 'reflected' changes in tables.

Is that correct?

If so, then, it doesn't work that way; it's not like typing a value directly into a table - you've got to force the data update.
If you moved OFF that record (after ticking a checkbox), then back to that record, THEN the underlying table would reflect the data change.

Try: clicking a checkbox, moving to a previous record, then moving back to the edited record.
Does the 'query' NOW reflect that change?

Either way, I've got a strong feeling that your logic and / or table designs are not correct (if check boxes are being used to indicate 'numbers of records' and are the basis for event triggers etc).

Please explain in more detail what your requirement is - you may be about to produce 1,000 lines of code for something that can be done in 10.

Def