Continuing before values returned from Query?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that is used to enter customer payments. My DB is set up such
that there is a customer table that holds the amount originally owed, with a
one to many relationship to a transactions table that has each entry of any
payments that have been received.

At any given time i can look at what is owed by subtracting the sum of what
has been paid from the customers original charge and I have it!

I have set up an interface so that someone can blindly enter transatcions by
simply entering a customer number and an amount paid. I then have it set up
so that I programatically find any over payment.

In doing this I have run into something where I think that my code is trying
to sometimes apply the paylent BEFORE the results of the query are returned
with the balance.

Is there some way to programatically wait until I know that a query has
finished and has returned a value before I continue to perform calculations
with the result?

Thanks

Rick
 
Rick said:
I have a form that is used to enter customer payments. My DB is set up such
that there is a customer table that holds the amount originally owed, with a
one to many relationship to a transactions table that has each entry of any
payments that have been received.

At any given time i can look at what is owed by subtracting the sum of what
has been paid from the customers original charge and I have it!

I have set up an interface so that someone can blindly enter transatcions by
simply entering a customer number and an amount paid. I then have it set up
so that I programatically find any over payment.

In doing this I have run into something where I think that my code is trying
to sometimes apply the paylent BEFORE the results of the query are returned
with the balance.

Is there some way to programatically wait until I know that a query has
finished and has returned a value before I continue to perform calculations
with the result?
Click to expand...


It would have helped clarify the issue if you had posted the
revelant code, but I'll guess that you are doing something
other than opening a recordset to run the query. If you
were using a recordset to retrieve the value, what you're
saying is happening would be impossible.
 
I have to break up customer charges into 2 categories Fees, Penalties, then
apply their payments following that priority:

There is some other stuff going on that is irrelevant to my challenge.


Dim mParm As Parameter
Dim deFees As Double
Dim dePenaltys As Double
Dim deRemainder As Double
Dim dePayment As Double

dePayment = CDbl(Me.txtPayment.Value)
Set dbs = currentDB
Set qdf = dbs.QueryDefs("PaymentEntry balances")

For Each mParm In qdf.Parameters
mParm.Value = Eval(mParm.Name)
Next

Set rst = qdf.OpenRecordset(dbOpenDynaset)
rst.MoveFirst


'my problem occurs HERE. Sometimes this will appear to be zero when it is
not. It is not consistent which is ‘why I am suspicious that the values are
not set yet from above. SOMETIMES there will be values, other times ‘zero
will be returned. There are sums being calculated in the queries used below.

deFees = rst!actFee- Nz(rst![Sum Of traAppFee], 0)
dePenaltys = rst!actPenaltys - Nz(rst![Sum Of traAppPenaltys], 0)

deRemainder = dePayment

'Distribute remainder
If deRemainder >= deFees Then
Me.txtAppFee = deFees
deRemainder = deRemainder - deFees
Else
Me.txtAppFee = deRemainder
deRemainder = 0
End If
 
Rick said:
I have to break up customer charges into 2 categories Fees, Penalties, then
apply their payments following that priority:

There is some other stuff going on that is irrelevant to my challenge.


Dim mParm As Parameter
Dim deFees As Double
Dim dePenaltys As Double
Dim deRemainder As Double
Dim dePayment As Double

dePayment = CDbl(Me.txtPayment.Value)
Set dbs = currentDB
Set qdf = dbs.QueryDefs("PaymentEntry balances")

For Each mParm In qdf.Parameters
mParm.Value = Eval(mParm.Name)
Next

Set rst = qdf.OpenRecordset(dbOpenDynaset)
rst.MoveFirst


'my problem occurs HERE. Sometimes this will appear to be zero when it is
not. It is not consistent which is ‘why I am suspicious that the values are
not set yet from above. SOMETIMES there will be values, other times ‘zero
will be returned. There are sums being calculated in the queries used below.

deFees = rst!actFee- Nz(rst![Sum Of traAppFee], 0)
dePenaltys = rst!actPenaltys - Nz(rst![Sum Of traAppPenaltys], 0)

deRemainder = dePayment

'Distribute remainder
If deRemainder >= deFees Then
Me.txtAppFee = deFees
deRemainder = deRemainder - deFees
Else
Me.txtAppFee = deRemainder
deRemainder = 0
End If
Click to expand...


Try opening the query directly in the query design window
(using the same parameter values) and double check its
dataset.

It looks like the query only return a single record, so I
can't imagine how there would be a timing issue. Maybe
there's a combination of parameters the doesn't return a
record at all??
 
You are correct, I AM returning only one record. I have an "account" table
that contains all of the customer information and a "Transactions" table that
contains all of the transactions on a given account. There is a one to many
relationship, one account can have many transactions.

The query that I am running has two data sources. ONE is the Account table.
The other is a query that is a summary of all the transactions. This
creates a One to One relationship between accounts and the sum of there
transactions if there are any.

It is NOT a matter of no record being returned at all as the problem is
sporadic. The end user will have the instance occur, give me a call, I will
try it and it will work! BUT when the end user does it I have it write a
record in another table (Create a transaction) and I can see that it has
misapplied the LAST transaction.

Maybe the explanation of the complexity of the query might explain?

Is there any way I can check to make sure that a value is returned before
performing the calculations?

Thanks so much for your help.

Rick

Marshall Barton said:
Rick said:
I have to break up customer charges into 2 categories Fees, Penalties, then
apply their payments following that priority:

There is some other stuff going on that is irrelevant to my challenge.


Dim mParm As Parameter
Dim deFees As Double
Dim dePenaltys As Double
Dim deRemainder As Double
Dim dePayment As Double

dePayment = CDbl(Me.txtPayment.Value)
Set dbs = currentDB
Set qdf = dbs.QueryDefs("PaymentEntry balances")

For Each mParm In qdf.Parameters
mParm.Value = Eval(mParm.Name)
Next

Set rst = qdf.OpenRecordset(dbOpenDynaset)
rst.MoveFirst


'my problem occurs HERE. Sometimes this will appear to be zero when it is
not. It is not consistent which is ‘why I am suspicious that the values are
not set yet from above. SOMETIMES there will be values, other times ‘zero
will be returned. There are sums being calculated in the queries used below.

deFees = rst!actFee- Nz(rst![Sum Of traAppFee], 0)
dePenaltys = rst!actPenaltys - Nz(rst![Sum Of traAppPenaltys], 0)

deRemainder = dePayment

'Distribute remainder
If deRemainder >= deFees Then
Me.txtAppFee = deFees
deRemainder = deRemainder - deFees
Else
Me.txtAppFee = deRemainder
deRemainder = 0
End If
Click to expand...


Try opening the query directly in the query design window
(using the same parameter values) and double check its
dataset.

It looks like the query only return a single record, so I
can't imagine how there would be a timing issue. Maybe
there's a combination of parameters the doesn't return a
record at all??
Click to expand...
 
Back
Top