A calculate button?

[bknight]

I have a Db, that needs to be run code to calculate values every time records are imported from Excel workbooks. what would be the best way to do that. Currently I open the modules copy the sub name into the immediate window and do the calculation. Is there an easier way to accomplish the task?

[HansV]

You can create a command button on a form, and in the On Click event procedure of that button call the procedure (sub) or procedures that you want to run:

Code: Select all

Private Sub cmdRun_Click()
    Call Procedure1
    ' Optional: call more procedures
    Call Procedure2
End Sub

[bknight]

My Access doesn't have the create Command Button.

[HansV]

Open a form in design view.
Activate the Form Design tab of the ribbon.
Button should be in the Controls group.

S2513.png

[bknight]

Ok, now refresh my short term memory, where are the controls to disable scroll through records and both slider bars?
Found them when I selected properties of the form.

[HansV]

Those are properties of the form. Activate the Property Sheet (if you don't see it, press F4), then activate its Format tab.

S2514.png

[bknight]

Um, this is not what I wanted.

[bknight]

In from view it is over another form, I attempted to move it but that failed.

[bknight]

I got it, just make the form design a bit larger and then move it. Thanks

[bknight]

I attached a docmd to another form opening form and got an error message.

[HansV]

Compare the offending line with the DoCmd.OpenForm line above it. Use either

DoCmd.OpenForm "frmCalculate", acNormal

or

DoCmd.OpenForm FormName:="frmCalculate", View:=acNormal

(with := instead of = )

[bknight]

Sloppy of me, thanks.

[bknight]

I had an interesting occurrence today. After pushing the calculate button, everything populated in the table correctly. I have a summation query with attached form. Once the calculation the summation form was refreshed, but I knew the values were incorrect. So I opened the queries to make sure they were correct and they were. Refreshed the form again, without any change. Checked that the form had as its record set the query I wanted, yes it was. Opened the form again without any changes. Then I shut the form down and re-opened and the display was correct I had wanted to code a Me.frm.refresh(?) but after all the steps I went through today, I don't know. Could anyone explain why the refresh didn't work on the form? In addition is .Refresh correct or would .Requery be correct?

[bknight]

The previous post took three tries.
the first resulted in the following error.
SQL ERROR [ mysqli ]

User eileenslounge already has more than 'max_user_connections' active connections [1203]

An sql error occurred while fetching this page. Please contact an administrator if this problem persists.

The second attempt indicated an Invalid entry
The third time was a charm.

[HansV]

Yes, there was a short server outage a while ago. Sorry about that.

[HansV]

Requery is generally better than Refresh. From the online documentation:

In an Access database, the Refresh method shows only changes made to records in the current set. Because the Refresh method doesn't actually requery the database, the current set won't include records that have been added or exclude records that have been deleted since the database was last requeried, nor will it exclude records that no longer satisfy the criteria of the query or filter. To requery the database, use the Requery method. When the record source for a form is requeried, the current set of records will accurately reflect all data in the record source.

[bknight]

From reading that, it sounds like the query should be re-queried as the form has as its record set a query.

[HansV]

Requerying the form will reload its record source, whether that is a table or query.

[bknight]

A small issue, the code to run the calculation is a OnClck of the command button. I attempted to do a re-query after the calculation, but Me. didn't have any of the form names. I guess the command would be on the form itself, but which would be the best to add that re-query after the command button is clicked and the calculation is complete?
Note the image is not of the form where the command button resides.

Code: Select all

Private Sub Command0_Click()
Call NumberRows
Call CalcProfit(Profit)
End Sub

[HansV]

I'd requery the form first, then perform the calculations.
If you want to requery another form than the form containing the button, use

Forms("NameOfTheForm").Requery

substituting the name of the form. If it is a subform, use

Forms("NameOfMainForm")!NameOfSubForm.Form.Requery