If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Adding a control with code to a form
|
#13
|
|||
|
|||
Adding a control with code to a form
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" wrote: 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 "bknight" wrote in message news:F3C4BFA6-DD7D-4159-BD3A-F10D0 ... 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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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 |
#14
|
|||
|
|||
Adding a control with code to a form
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 -- Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia |
#15
|
|||
|
|||
Adding a control with code to a form
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. "Jeanette Cunningham" wrote: 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 -- Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia |
#16
|
|||
|
|||
Adding a control with code to a form
Well with Jeanette Cunningham's valuable time and effort the code was added
to the command button. I appreciate her help. "bknight" wrote: 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. |
|
Thread Tools | |
Display Modes | |
|
|