Takes 2 presses on nav bar to move to previous record

[tgbyhn10]

Hi

I just added a currency field to a table 2 decimal places default value 0.

I am displaying that value on a form as a bound field. I am calculating a value in a currency type field in my code and moving that value to the bound database field. I don't get an error but I know that system does not like the value I am moving to the bound field as it takes 2 presses on the navigation bar to move to the previous / next record.

I know this field is the problem as if I unbind it I can move around the table without problems. Also if I don't move a value to the field I can move around the table OK.

This is driving me crazy. If there is a problem why can't access display a message and tell me what it is - access is so pants.

Please tell me what I'm doing wrong.

thanks

Pete

Here is the code:
Private Sub calcTotals()

Dim intControlIndex As Integer
Dim dQuoteTotal As Currency

On Error GoTo Err_calcTotals

dQuoteTotal = 0
For intControlIndex = 1 To 23
If Not IsNull(Me.Controls("unitprice" & intControlIndex)) And Not IsNull(Me.Controls("Quantity" & intControlIndex)) Then
If IsNumeric(Me.Controls("unitprice" & intControlIndex)) And IsNumeric(Me.Controls("Quantity" & intControlIndex)) Then
dQuoteTotal = dQuoteTotal + Val(Me.Controls("unitprice" & intControlIndex)) * Val(Me.Controls("Quantity" & intControlIndex))
End If
End If
Next

Me!QuoteTotal.Value = dQuoteTotal

Exit_calcTotals:
Exit Sub

Err_calcTotals:
MsgBox Err.Description
Resume Exit_calcTotals

End Sub

 

[missinglinq]

What exactly is intControlIndex and what is "unitprice"?

 

[RuralGuy]

Linq,
It looks like the OP has 23 pairs of controls on their form named:
unitprice1 to unitprice23 and Quantity1 to Quantity23

 

[missinglinq]

I'm confused because he appears to be concatenating "unitprice" and intControlIndex and then trying to extract a number out of it, which seems strange!

 

[Rabbie]

TbHygb10, You might find it useful to set a breakpoint in your code and step through it line by line so you can see what you are generating

 

[tgbyhn10]

I'm confused because he appears to be concatenating "unitprice" and intControlIndex and then trying to extract a number out of it, which seems strange!

This works in the above code. You can concatenate the string with the number and access the field name it creates - may be strange but works fine.

Rabbie, thanks for your suggestion. I have tried breakpoints but where should I put the breakpoint to see what happens when I press the nav bar (to save the current record and move to the next reocrd.) It's going through automated dodgy access code I have no control over at that point - it's so annoying the system decides not to accept / save the record the first time without any explaination. I've always hated coding in languages where you only have partial control of what is happening.

Any help appreciated - I accept it's probably my mistake somewhere in the code but it's unforgivable for any language not to give any explaination of it's behaviour.

 

[Tim L]

tgbyhn10, thanks for introducing me to this method of referencing controls. I don't find it 'strange' at all, they have, afterall provided the 'Controls' statement to use, and accept strings as control references, so it actually seems to be perfectly logical to me.

Have you considered enforcing checking of validity of the data at the point the data is entered, rather than ignoring false inputs? If you prevent non-numeric entries at the time you can correct the user at the time. Instead of checking for nulls, use Nz(Me.Controls(<control reference>),0), which will automatically give the control a value of 0 if the field is Null (you can, of course, change the 0 for any other value that you would prefer).

Another thought, if you want all of the fields to contain a number, do a seperate check on all of the fields and inform the user that they have failed to provide all of the data, and indicate which field is missing data somehow, perhaps by changing the background colour of the field.

Additionally, why are you using Val, when you have already confirmed that the contents of the controls is a number? I think the use is superfluous and you could have used:

dQuoteTotal = dQuoteTotal + _
             (Me.Controls("unitprice" & intControlIndex) * _
              Me.Controls("Quantity" & intControlIndex))

Finally, I am not convinced that the error is actually in the bit of code you posted. Is there an event assocated with the QuoteTotal control that could be interferring? Also, what code does the navigation button use? And how/on what event is the calcTotals sub called?

Tim