Adding a control with code to a form

[bknight]

I have some code that updates daily information. I have created a form with
all the fields that change.

The problem is to add some control and then embed the code into it such that
when pushed executes the code.

 

[Jeanette Cunningham]

Hi bknight,
here is an example for a button called cmdRunQuery.

Private Sub cmdRunQuery_Click()
Dim strSQL as String

strSQL = "NameOfQuery"
CurrentDb.Execute strSQL, dbFailOnError

End Sub


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[bknight]

Thanks, I put a command button on the form and attached my code and all I got
was 10K error messages. One half did not have a description, the other half
indicated a resume without error. The coded works while in design mode and
pressing the run button (F5), without errors.

 

[bknight]

I guess I should have posted the code on the command button:

Private Sub Command45_Click()
On Error GoTo Err_Command45_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Exit_Command45_Click:
'Sub CalculateFields()
'This Function Will Calculate a Group of Indexes, Not Just the Last
Dim Db As Database
Dim Rs As Recordset
Dim Fld1 As Field, Fld2 As Field, Fld3 As Field
Dim Fld4 As Field, Fld5 As Field, Fld6 As Field
Dim Fld7 As Field, Fld8 As Field, Fld9 As Field
Dim Fld10 As Field, Fld11 As Field, Fld12 As Field
Dim I As Long, NumRec As Long, PrevIndex As Long
Dim Prev10 As Long, Prev5 As Long, PrevOSC As Long, PrevCum As Long
Dim strErrorMessage As String
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("Select Count(*) From tblMcClelland")
NumRec = Rs(0)
Set Rs = Db.OpenRecordset("Select * From tblMcClelland Order By Index")
Set Fld1 = Rs!CUMADVDEC
Set Fld2 = Rs!DAY
Set Fld3 = Rs!USTK
Set Fld4 = Rs!DSTK
Set Fld5 = Rs!UVOL
Set Fld6 = Rs!DVOL
Set Fld7 = Rs!TRIN
Set Fld8 = Rs!Diff
Set Fld9 = Rs!TEN_PCT
Set Fld10 = Rs!FIVE_PCT
Set Fld11 = Rs!OSC
Set Fld12 = Rs!SUM
For I = 1 To NumRec
If IsNull(Fld3) Or IsNull(Fld4) Then
strErrorMessage = MsgBox("There must be advancing and declining stocks on "
& Fld2, vbOKOnly)
Set Rs = Nothing
Set Db = Nothing
Exit Sub
End If
'Only calculate data greater than 5000
If Rs.AbsolutePosition > 5000 Then
'Calculates all data
'If Rs.AbsolutePosition <> 0 Then
If IsNull(Fld7) Then
Rs.Edit
'CLng Conversion Rounds to the Nearest Even Number
Fld7 = (CLng(10000 * (Fld3 / Fld4) / (Fld5 / Fld6))) / 10000
Fld8 = Fld3 - Fld4
Fld1 = Fld8 + PrevCum
Fld9 = CInt(Prev10 + (0.1 * (Fld3 - Fld4 - Prev10)))
Fld10 = CInt(Prev5 + (0.05 * (Fld3 - Fld4 - Prev5)))
Fld11 = Fld9 - Fld10
Fld12 = Fld11 + PrevOSC
Rs.Update
End If
End If
Prev10 = Fld9
Prev5 = Fld10
PrevOSC = Fld12
PrevCum = Fld1
Rs.MoveNext
Next I
Set Rs = Nothing
Set Db = Nothing
'Exit Sub

Err_Command45_Click:
MsgBox Err.Description
Resume Exit_Command45_Click

End Sub

 

[Jeanette Cunningham]

I have split the code into 2 subs.
I assumed that
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

is intended to save the current record and I replaced it with the modern
code equivalent for saving a record.

The other code as written had syntax errors.
I fixed the obvious ones.
Try it and see how you go with debugging it.


Private Sub Command45_Click()
On Error GoTo Err_Command45_Click

If Me.Dirty = True Then
Me.Dirty = False
End If

Call CalculateFields


Exit_Command45_Click:
Exit Sub

Err_Command45_Click:
MsgBox Err.Description
Resume Exit_Command45_Click

End Sub



Sub CalculateFields()
On Error GoTo Err_CalculateFields

'This Function Will Calculate a Group of Indexes, Not Just the Last
Dim Db As Database
Dim Rs As Recordset
Dim Fld1 As Field, Fld2 As Field, Fld3 As Field
Dim Fld4 As Field, Fld5 As Field, Fld6 As Field
Dim Fld7 As Field, Fld8 As Field, Fld9 As Field
Dim Fld10 As Field, Fld11 As Field, Fld12 As Field
Dim I As Long, NumRec As Long, PrevIndex As Long
Dim Prev10 As Long, Prev5 As Long, PrevOSC As Long, PrevCum As Long
Dim strErrorMessage As String

Set Db = CurrentDb
Set Rs = Db.OpenRecordset("Select Count(*) From tblMcClelland")
NumRec = Rs(0)
Set Rs = Db.OpenRecordset("Select * From tblMcClelland Order By Index")
Set Fld1 = Rs!CUMADVDEC
Set Fld2 = Rs!DAY
Set Fld3 = Rs!USTK
Set Fld4 = Rs!DSTK
Set Fld5 = Rs!UVOL
Set Fld6 = Rs!DVOL
Set Fld7 = Rs!TRIN
Set Fld8 = Rs!Diff
Set Fld9 = Rs!TEN_PCT
Set Fld10 = Rs!FIVE_PCT
Set Fld11 = Rs!OSC
Set Fld12 = Rs!SUM

For I = 1 To NumRec
If IsNull(Fld3) Or IsNull(Fld4) Then
strErrorMessage = MsgBox("There must be advancing and declining stocks on "
& Fld2, vbOKOnly)
End If

'Only calculate data greater than 5000
If Rs.AbsolutePosition > 5000 Then
'Calculates all data
'If Rs.AbsolutePosition <> 0 Then
If IsNull(Fld7) Then
Rs.Edit
'CLng Conversion Rounds to the Nearest Even Number
Fld7 = (CLng(10000 * (Fld3 / Fld4) / (Fld5 / Fld6))) / 10000
Fld8 = Fld3 - Fld4
Fld1 = Fld8 + PrevCum
Fld9 = CInt(Prev10 + (0.1 * (Fld3 - Fld4 - Prev10)))
Fld10 = CInt(Prev5 + (0.05 * (Fld3 - Fld4 - Prev5)))
Fld11 = Fld9 - Fld10
Fld12 = Fld11 + PrevOSC
Rs.Update
End If
End If
Prev10 = Fld9
Prev5 = Fld10
PrevOSC = Fld12
PrevCum = Fld1
Rs.MoveNext
Next I
Set Rs = Nothing
Set Db = Nothing

Exit_CalculateFields:
Exit Sub

Err_CalculateFields:
MsgBox Err.Description
Resume Exit_CalculateFields

End Sub


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[bknight]

As you might believe, the original code was written to update the data in a
table. The database uses AC 97 (although I do have AC 2K) The form was
developed to simply the update procedure from:
1. Open table
2. Import/add new data
3. Open module in design state
4. Push F5 to run the code.

To:
1. Open form
2. Import/add data
3. Click on a command button to run code

The code DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70 was
added when the wizard created the command button.
Anyway I edited the code as per your post and compiled and recieved several
errors.
On Error GoTo Err_Command45_Click

If Me.Dirty = True Then "RED"
Me.Dirty = False "RED"
End If "RED"

Call CalculateFields "RED"

Exit_Command45_Click:
Exit Sub

Err_Command45_Click:
MsgBox Err.Description "RED"
Resume Exit_Command45_Click "RED"

Towards the end of the code

Err_CalculateFields:
MsgBox Err.Description "RED"
Resume Exit_CalculateFields "RED"

 

[Jeanette Cunningham]

I have to admit that I am not familiar with DoCmd.DoMenuItem acFormBar,
acRecordsMenu, 5, , acMenuVer70
My experience with access started with Access 95.
Maybe someone else can help you with that.

The code to update the data in the table seems very ancient as well.
Are you using access as the backend and some other program as the front end?
If you have an access front end, why not use something simpler like using a
bound form?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[bknight]

No, just using it as a data gathering and manipulating bit of software.

The code was written in the late 90's after I went to a programming course.

After posting I did some problem shooting:

I commented the If.Me.Dirty = True Then and removed the comment and got as
error message Expective End of Stament. The same error occurred when the
Call statement, MsgBox and Resume Exit statements were commented.

Any suggestions here?

 

[Jeanette Cunningham]

The code as you have it does not make a lot of sense to me.
Would you describe what you are trying to do.
Is this part of an access application?
You wrote: using it as a data gathering and manipulating bit of software.
Where is the data coming from and what are you trying to do with it?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[bknight]

Daily 5 fields are gathered/entered from external data sourcesI don't have
a available method of importing them from my data sources)
Day
USTK
DSTK
UVOL
DVOL

The rest of the fields are caculated from the last four entered. The whole
Db is my own built to obtain the calculated values and then store them. I
could send the Db if you like.

 

[Jeanette Cunningham]

From what you have described, it would be fairly easy to set up a table with
those 5 fields and a primary key.

Data entry:
Make a form based on that table, you could enter or copy and paste data into
the form.
The data will automatically save into the table.

Calculations:
The results of calculations don't need to be stored in the database.
The calculations can usually be done in a query and the results displayed in
a form or report.

This way you won't need code to save the data to the table. You can build a
query which will show the calculations.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


(e-mail address removed)...

 

[bknight]

Thanks for the input but the table exists, as does the form that is based on
the table. It would be more difficult to build a query, I think. The
calculated data may not need to be saved but that was my choice, probably
because I couldn't figure out a query years ago or now.
Any suggestions on how to help the current code problems with the form?

 

[bknight]

After posting and tinkering again, I moved ALL the lines is the private sub
including the ERR. lines to the left (no tab indentions). I compiled again
and obtained NO ERROR statements.

I then went back into view mode and pushed the command button, and lo and
behold no errors.

I did go back into the Db and found that I had created a query to display
some of the data (matching a criteria) but was unable to funtionally
complete. Obviously the problem is my lack of understanding on how to
manipulate the data.

What the query needs to accomplish is look up the previous day's fields
TEN_PCT, FIVE_PCT, OSC, SUM (of course that can't be done with the first day
since there are no previous day) and perform the calculations executed in the
procedure that I wrote. I do understand that the code is ancient and
probably could be written more efficiently but it served its purposes in the
table data.

If my thoughts in the previous post offended you, I appologize since I was
trying to make the thing work instead of changing the whole structure.

 

[Jeanette Cunningham]

<not offended>
I have gone through the code that does the calculation and removed the
anomalies.
Here is the original code cleaned up, it's all in one sub routine.

In the button's code, you call the sub like this:

Private Sub cmdRunQuery_Click()

Call CalculateFields

End Sub




Sub CalculateFields()
On Error GoTo Err_Handler
'This Function Will Calculate a Group of Indexes, Not Just the Last
Dim Db As Database
Dim Rs As Recordset
Dim Fld1 As Field, Fld2 As Field, Fld3 As Field
Dim Fld4 As Field, Fld5 As Field, Fld6 As Field
Dim Fld7 As Field, Fld8 As Field, Fld9 As Field
Dim Fld10 As Field, Fld11 As Field, Fld12 As Field
Dim I As Long, NumRec As Long, PrevIndex As Long
Dim Prev10 As Long, Prev5 As Long, PrevOSC As Long, PrevCum As Long
Dim strErrorMessage As String
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("Select Count(*) From tblMcClelland")
NumRec = Rs(0)
Set Rs = Db.OpenRecordset("Select * From tblMcClelland Order By Index")
Set Fld1 = Rs!CUMADVDEC
Set Fld2 = Rs!DAY
Set Fld3 = Rs!USTK
Set Fld4 = Rs!DSTK
Set Fld5 = Rs!UVOL
Set Fld6 = Rs!DVOL
Set Fld7 = Rs!TRIN
Set Fld8 = Rs!Diff
Set Fld9 = Rs!TEN_PCT
Set Fld10 = Rs!FIVE_PCT
Set Fld11 = Rs!OSC
Set Fld12 = Rs!SUM
For I = 1 To NumRec
If IsNull(Fld3) Or IsNull(Fld4) Then
strErrorMessage = MsgBox("There must be advancing and declining stocks on "
& Fld2, vbOKOnly)
End If

'Only calculate data greater than 5000
If Rs.AbsolutePosition > 5000 Then
'Calculates all data
If Rs.AbsolutePosition <> 0 Then
If IsNull(Fld7) Then
Rs.Edit
'CLng Conversion Rounds to the Nearest Even Number
Fld7 = (CLng(10000 * (Fld3 / Fld4) / (Fld5 / Fld6))) / 10000
Fld8 = Fld3 - Fld4
Fld1 = Fld8 + PrevCum
Fld9 = CInt(Prev10 + (0.1 * (Fld3 - Fld4 - Prev10)))
Fld10 = CInt(Prev5 + (0.05 * (Fld3 - Fld4 - Prev5)))
Fld11 = Fld9 - Fld10
Fld12 = Fld11 + PrevOSC
Rs.Update
End If
End If
Prev10 = Fld9
Prev5 = Fld10
PrevOSC = Fld12
PrevCum = Fld1
Rs.MoveNext
Next I
Set Rs = Nothing
Set Db = Nothing

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description
Resume Exit_Handler

End Sub

 

[bknight]

If you remember what changes you made, I would appreciate them individually
so that I may learn better.

Anyway added one step DoCmd.GoToRecord , , acLast to go to the last record
added without any calculations and all seemed well. I'll have to delete the
data and run again in tale form, but at least no error messages occurred when
I pushed the command button.

 

[bknight]

Well with Jeanette Cunningham's valuable time and effort the code was added
to the command button. I appreciate her help.