Calculation Issues (1 Viewer)

NearImpossible

Registered User.
Local time
Today, 08:10
Joined
Jul 12, 2019
Messages
225
For some reason I can't think straight today, its been one of those weeks.

I need to be able to calculate some numbers based off of a couple entries on my current form and its parent form, but I am getting the following error and can't seem to figure out what i'm doing wrong, even though it should be easy to figure out :banghead::banghead::banghead::banghead:

"The Expression After Update you entered as the event property setting produced the following error: A problem occured while <Database Name> was communicating with the OLE server or ActiveX Control"
* The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]
* There may have been an error evaluating the function, event, or macro



I am basically building a form to tell me how many devices I need to provide based off of 2 fields.

1. Tablet Ratio (Field on TabletCalculator Parent form)
2. Participants (Field on Subform I'm calculating on)

The user will select a facility and enter the desired Tablet Ratio.

On the subform there are several fields with one of them being Number of Participants where the user will enter that number, so in the After Update event of the Participants field, I have the following code to tell me how many tablets I need to provide in the CurrentTablets field, which is unbound

Code:
Private Sub Participants_AfterUpdate()
    [CurrentTablets] = Participants / [Forms]![TabletCalculator]![Tablet Ratio]
End Sub

Currently I have both the Tablet Ratio and Participants data types set to Int on the SQL side, I've also tried Numeric and Decimal and get the same error.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:10
Joined
Oct 29, 2018
Messages
21,358
Hi. Have you tried doing a C&R? If so, you might then try decompiling your project.
 

NearImpossible

Registered User.
Local time
Today, 08:10
Joined
Jul 12, 2019
Messages
225
Hi. Have you tried doing a C&R? If so, you might then try decompiling your project.

C&R didn't do anything and what do you mean by decompiling?

I did a Debug > Compile Database with the same result as well.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:10
Joined
Oct 29, 2018
Messages
21,358
C&R didn't do anything and what do you mean by decompiling?

I did a Debug > Compile Database with the same result as well.
I mean by using the /decompile command switch. Let me see if I can find a link for you...
 

NearImpossible

Registered User.
Local time
Today, 08:10
Joined
Jul 12, 2019
Messages
225
I just tried to select a different facility and enter the information just as a test and got the following:

A Problem occurred while <Database Name> was communicating with the OLE server or ActiveX Control.

Close the OLE server and restart it outside of <Database Name>. Then try the original operation again in <Database Name>
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:10
Joined
Oct 29, 2018
Messages
21,358
I just tried to select a different facility and enter the information just as a test and got the following:

A Problem occurred while <Database Name> was communicating with the OLE server or ActiveX Control.

Close the OLE server and restart it outside of <Database Name>. Then try the original operation again in <Database Name>
Sounds like you may have a corrupted file. Can you try a good backup copy just to see if the problem is also there?
 

NearImpossible

Registered User.
Local time
Today, 08:10
Joined
Jul 12, 2019
Messages
225
Sounds like you may have a corrupted file. Can you try a good backup copy just to see if the problem is also there?

Actually I just deleted the parent and subforms and recreated them and all is working as intended, like I said, its been one of those weeks so I can't say i'm surprised a new form I just made didn't work until I deleted it and started over, lol....

thanks again !!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:10
Joined
Oct 29, 2018
Messages
21,358
Actually I just deleted the parent and subforms and recreated them and all is working as intended, like I said, its been one of those weeks so I can't say i'm surprised a new form I just made didn't work until I deleted it and started over, lol....

thanks again !!
Hi. Congratulations! Glad to hear you got it sorted out. Unfortunately, corruption can creep in from anywhere at anytime. Good luck with your project.
 

NearImpossible

Registered User.
Local time
Today, 08:10
Joined
Jul 12, 2019
Messages
225
could you tell me how to make it so the result is rounded up to the nearest whole number?

Ex: I am getting 0.2, would like to round it up to 1 or if its 1.4, I would like to round it up to 2
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:10
Joined
Oct 29, 2018
Messages
21,358
could you tell me how to make it so the result is rounded up to the nearest whole number?

Ex: I am getting 0.2, would like to round it up to 1 or if its 1.4, I would like to round it up to 2
Hi. Based on your sample data, if I understand it correctly, you could try this:
Code:
int(1.4)-(1.4-int(1.4)>0)
There's probably a better way though...
 

NearImpossible

Registered User.
Local time
Today, 08:10
Joined
Jul 12, 2019
Messages
225
Hi. Based on your sample data, if I understand it correctly, you could try this:
Code:
int(1.4)-(1.4-int(1.4)>0)
There's probably a better way though...

the problem is i wouldn't be able to guestimate the outcome so it would need to be dynamic

Code:
Private Sub Participants_AfterUpdate()
    [CurrentTablets] = Participants / [Forms]![TabletCalculator]![Tablet Ratio]
    [CurrentChargers] = CurrentTablets/5
End Sub
CurrentTablets and CurrentChargers would always need to round up to the nearest whole number
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:10
Joined
Oct 29, 2018
Messages
21,358
the problem is i wouldn't be able to guestimate the outcome so it would need to be dynamic

Code:
Private Sub Participants_AfterUpdate()
    [CurrentTablets] = Participants / [Forms]![TabletCalculator]![Tablet Ratio]
    [CurrentChargers] = CurrentTablets/5
End Sub
CurrentTablets and CurrentChargers would always need to round up to the nearest whole number
Right, so just do it like this:
Code:
Private Sub Participants_AfterUpdate()
    [CurrentTablets] = Participants / [Forms]![TabletCalculator]![Tablet Ratio]
    [COLOR=red][CurrentTablets] = Int([CurrentTablets])-([CurrentTablets]-Int([CurrentTablets])>0)[/COLOR]
    [CurrentChargers] = CurrentTablets/5
    [COLOR=red][[/COLOR][COLOR=red]CurrentChargers] = Int([[/COLOR][COLOR=red]CurrentChargers[/COLOR][COLOR=red]])-([[/COLOR][COLOR=red]CurrentChargers[/COLOR][COLOR=red]]-Int([[/COLOR][COLOR=red]CurrentChargers[/COLOR][COLOR=red]])>0)[/COLOR]
 End Sub
 

NearImpossible

Registered User.
Local time
Today, 08:10
Joined
Jul 12, 2019
Messages
225
Right, so just do it like this:
Code:
Private Sub Participants_AfterUpdate()
    [CurrentTablets] = Participants / [Forms]![TabletCalculator]![Tablet Ratio]
    [COLOR=red][CurrentTablets] = Int([CurrentTablets])-([CurrentTablets]-Int([CurrentTablets])>0)[/COLOR]
    [CurrentChargers] = CurrentTablets/5
    [COLOR=red][[/COLOR][COLOR=red]CurrentChargers] = Int([[/COLOR][COLOR=red]CurrentChargers[/COLOR][COLOR=red]])-([[/COLOR][COLOR=red]CurrentChargers[/COLOR][COLOR=red]]-Int([[/COLOR][COLOR=red]CurrentChargers[/COLOR][COLOR=red]])>0)[/COLOR]
 End Sub

Makes sense seeing it put in play, however i'm not getting desired results. It appears as it is rounding per the laws of rounding, where >5 goes up, < 5 goes down, however I need anything over 0 to always round up, i.e.

1.0 stays at 1
1.1 rounds up to 2
1.4 rounds up to 2
2.2 rounds up to 3
2.5 rounds up to 3

Is this possible?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:10
Joined
Oct 29, 2018
Messages
21,358
Makes sense seeing it put in play, however i'm not getting desired results. It appears as it is rounding per the laws of rounding, where >5 goes up, < 5 goes down, however I need anything over 0 to always round up, i.e.

1.0 stays at 1
1.1 rounds up to 2
1.4 rounds up to 2
2.2 rounds up to 3
2.5 rounds up to 3

Is this possible?
Hi. Paul's link to Allen's version is simpler, but I thought I tested my version earlier.

If you take the original expression I gave you, the one with the fixed value of 1.4, and type it in the Immediate Window, do you get the expected result? If so, try replacing the 1.4 with all the other values you gave as sample data above. Do they return the expected results? If so, double check the code to make sure it's the same as the original expression.

Otherwise, I'll have to check when I get in front of a computer. Or, switch to using Allen's version.

Sent from phone...
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:10
Joined
Feb 28, 2001
Messages
27,001
Try this. Put it in a general module. I made it a double only because that gives it the widest possible precision.

Code:
Public Function RoundUp( X As Double ) As Double

  Dim Y As Double

  Y = Int( X )
  If Y <> X Then Y = Y + Sgn( X )
  RoundUp = Y

End Function
 

isladogs

MVP / VIP
Local time
Today, 13:10
Joined
Jan 14, 2017
Messages
18,186
Try this
Code:
Function RoundUp(N) As Integer

RoundUp = IIf(Int(N) <> N, Int(N) + 1, Int(N))
End Function

For example
Roundup(3)=3
Roundup(3.001)=4
Roundup(3.9999)=4
Roundup(4)=4
Roundup(4.000001)=5

Also works for negative values
 

NearImpossible

Registered User.
Local time
Today, 08:10
Joined
Jul 12, 2019
Messages
225
Thanks for the tips, i've now got the calculations working, however I am unable to loop through and update all the records on my subform.

I am using the following code in the Tablet_Ratio After Update but it only runs if I select a record in the subform prior to updating the tablet ratio on the main form, and then it only updates the selected record.

Code:
Private Sub Tablet_Ratio_AfterUpdate()

Dim rs As Recordset
        
Set rs = CurrentDb.OpenRecordset("Select * from [dbo_TabletCalculator]", dbOpenDynaset, dbSeeChanges)
                
    While Not rs.EOF

            Me.[TabletCalculator Subform].[Form]![CurrentTablets] = RoundUp(Me.[TabletCalculator Subform].[Form]![Participants] / [Tablet Ratio])
            Me.[TabletCalculator Subform].[Form]![CurrentChargers] = RoundUp(Me.[TabletCalculator Subform].[Form]![CurrentTablets] / 5)

        rs.MoveNext
    Wend

rs.Close

End Sub

NOTE: I've changed the CurrentTablets and CurrentChargers unbound fields to bound fields as well if that makes any difference
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 13:10
Joined
Jan 14, 2017
Messages
18,186
As they are bound fields, you should be using an update query rather than looping through a recordset
 

Users who are viewing this thread

Top Bottom