Multiple records in a subform 2

[cdw0308]

I have a form called invoice and a subform called inv_subfrm.
The subform contains several records. The fields are:
name, duration, rate, total.
Duration and rate are number fields. Total supplied from formula when a command button (cmd_calc) is clicked. The button is on the footer of the subform. When i click it it only changes the current record in the subform. I want it to run the command for all the records. Is there some type of loop or another way of doing this.

cdw

 

[SBendBuckeye]

One way would be to open up a recordset matching the form (eg with the same selection criteria) and then spin through the recordset doing your calculations. This would require some VBA to do.

If you need some additional help, please post again with the record source for the main form and also for the subform.

Good Luck!

Have a great day!

j2consulting@yahoo.com

 

[cdw0308]

Yeah if you could give me some kind of an example of how do this i would appreciate it.
My record source for the sub form [Inv_subfrm] is:
inv_duration (number), inv_name (text), inv_itm_num (text), inv_rate (number), inv_total (number) and sub_inv_num.

Me record source for the sub form [Invoice] is:
inv_num (text), inv_date (date), inv_proj_num (text.

They are linked by inv_num (master) and sub_inv_num (child).

Inv_total is filled in when the command button is clicked.
The code i have for the button so far is:

If (inv_duration < 4) Then
inv_total = (inv_rate/3)
End If
If (inv_duration > 4 And inv_duration < 18.666) Then
inv_total = ((inv_rate/3) * (inv_duration /7))
End If
If (inv_duration >18.666) Then
inv_total = inv_rate
End If

I need this to perform my calculation on all the records in the sub form.

I would appreciate any help

cdw

 

[SBendBuckeye]

I am really swamped right now so let me point you generally to get you started. From your response it sounds like you are able to work your way around in code OK. This example uses a DAO recordset.

Depending on what version you are working with you may need to set a reference to it. To do that, while you are in your VB editor go to Tools, Click References and then scroll down until you come to the Microsoft list and select the highest DAO file listed (eg DAO 3.61) by checking the box next to it. If you close and reopen Tools, References it will now show up in the list.

Here is how you would use it in code. This is off the top of my head so there may be some syntax errors. If you hit F2 it will pop up the object browser. Select DAO from the libraries list and then you will see all of the properties and methods for DAO. Methods (subs or functions) look like green erasers. If you click on one of them and look down at the bottom it will show you the information needed to use it. If you right click it will also take you to help for that item.

Here is an example:

1. In your database create a table tblFamily with 4 fields:
FamilyID - Autonumber - Primary Key
FName - Text
LName - Text
Income - Currency

2. Create a query named qryFamily and select all fields
from tblFamily and sort it by LName and FName ascending

3. Enter data into the table and make sure you put some in
with different last names and out of order so you can
see the query work

4. Create this module and single step through it to get a
feel for how recordsets work

5. Note that fields are zero based
With rst / End With allows you to remove a level of
qualification (eg .fields instead of rst.fields)
Fields collection can be referenced by number or name

Sub RecordsetExample()
Dim rst As DAO.Recordset
Dim lngCount As Long
Dim lngIncome As Long
Set rst = CurrentDb.OpenRecordset(&quot;qryfamily&quot
rst.MoveFirst
Do While Not rst.EOF
With rst
lngIncome = lngIncome + .Fields(&quot;Income&quot.Value
MsgBox .Fields(0).Value & vbCrLf & _
.Fields(1).Value & vbCrLf & _
.Fields(2).Value & vbCrLf & _
.Fields(3).Value & vbCrLf & _
lngIncome
rst.MoveNext
End With
lngCount = lngCount + 1
Loop
MsgBox &quot;Total records: &quot; & lngCount & vbCrLf & _
&quot;Total Income: &quot; & lngIncome
rst.Close
Set rst = Nothing
End Sub

Here are a couple alternatives for your code also:

1. Using If/ElseIf/Else

If inv_duration < 4 Then
inv_total = inv_rate / 3
ElseIf inv_duration > 18.666 Then
inv_total = inv_rate
Else
inv_total = ((inv_rate / 3) * (inv_duration / 7))
End If

2. Using Select Case

Select Case inv_duration
Case < 4
inv_total = inv_rate / 3
Case > 18.666
inv_total = inv_rate
Case Else
inv_total = ((inv_rate / 3) * (inv_duration / 7))
End Select

3. Presumably you want a running total in which case it
becomes inv_total = inv_total + (inv_rate / 3), etc.

Good Luck and Enjoy!

Have a great day!

j2consulting@yahoo.com

 

[cdw0308]

Here is what I have so far. It seems to update the totals on each row. But the only problem is that it updates the inv_total field on all of records to the value of the last record (inv_total).
I think i might just have something out of place. I would appreciate it if you could take a look at this.

Private Sub CMD_Calc()
Dim rs As DAO.recordset
Set rs = CurrentDb.OpenRecordset(&quot;Invoice_Subform&quot
rs.MoveFirst
Do While Not rs.EOF

IF (rs!inv_duration < 4) Then
Total = (rs!ren_inv_rate /3)
strSQL = (UPDATE Invoice_Subform Set inv_total = &quot; & Total & &quot;;
DoCmd.RunSQL strSQL

ElseIf (rs!inv_duration >18.666) then
Total = (rs!ren_inv_rate /1)
strSQL = &quot;UPDATE Invoice_Subform Set inv_total = &quot; & Total & &quot;;
DoCmd.RunSQL strSQL

Else
Total = ((rs!ren_inv_rate /3) * (rs!inv_duration /7))
strSQL = &quot;UPDATE Invoice_Subform Set inv_total = &quot; & Total & &quot;;
DoCmd.RunSQL strSQL

End IF
rs.MoveNext
Loop
Set rs = Nothing
Me.Refresh
End Sub


thanks,
cdw

 

[SBendBuckeye]

Hello cdw,

To answer your specific question, you need a Where clause to limit it to just that specific row. Something like the following if Name is a unique key:

strSQL = &quot;UPDATE Invoice_Subform Set inv_total = &quot; & Total & &quot;WHERE Name = '&quot; & Name & &quot;';&quot;

Note the single quotes ' around the Name field if it is a string.

BUT, you are designing yourself into a trap here!! Database design theory says you should never (or at best, extremely rarely) save a calculated field in a database. If you do, it is very easy to get things out of sync.

If you always recalculate the totals, your data will always be accurate. If something beyond your control requires that you save it in the database, then the total should be saved at the main form invoice level, not at the subform level.

Hope this doesn't totally confuse you and Good Luck!

PS. You have done a nice job thus far.




Have a great day!

j2consulting@yahoo.com

 

[cdw0308]

Thanks, SBendBuckeye
you have been alot of help.

I put a where clause in there that now seperates the updates by the invoice #.

But i dont think i was clear with my original question. The inv_total field that is on every record in the subform does not work correctly. I works on the first field now and the following fields for that invoice # have all the same inv_total. I kinda has me thinking that there is something wrong with my recordset part of the code.
But i have looked and looked and cannot see the problem.

cdw

 

[SBendBuckeye]

Hello cdw,

Why don't you post some sample data and show what you are getting and what you need. Don't forget to post the main form data also since the subform is most likely automatically filtering your subform based on the link fields (eg if the main form and subform are linked via CustomerID then the subform is automatically filtered by CustomerID because that is what the main form is displaying.

Good Luck!

Have a great day!

j2consulting@yahoo.com

 

[cdw0308]

Main form sample data:

Inv_Num: 100
Inv_Date: 10/20/03
Inv_Job#: P100
Inv_Grand_total: $500.00


SubForm sample Data:
inv_num: 100
inv_duration(days): 29
inv_name: hammer
inv_item_num: HS-001
unit_ID: B-02
inv_rate: $11.00
inv_total: $11.00 <--(correct according to above if statements)

next record on subform:
inv_num: 100
inv_duration(days): 29
inv_name: drill
inv-item_num: HS-002
unit_ID: B-03
inv_rate: $33.00
inv_total: $11.00 <--(this shouldnt be 11 it should actually be 33 but it seems to not be changing recordsets, but im not sure.)

** All other records end up with 11 as inv_total as well.

The subform and main form is linked via the inv_num.

Example with new invoice#:

inv_num: 101
inv_duration(days): 29
inv_name: saw
inv_item_num: HS-004
unit_ID: A-05
inv_rate: $14.00
inv_total: $14.00 <-- correct according to if statements.

** Next record under that invoice number will return a 14 for the value of the inv_total, no matter what other values are entered for the rate and duration.

I hope this will explain better what I am having problems with. Seems to only be calculating my statements on the first record and just adding it to all records on the subform by invoice #.


cdw

 

[SBendBuckeye]

Hello cdw,

Instead of your update SQL, try rst.Edit and rst.Update as below:

rst.Edit
IF (rs!inv_duration < 4) Then
rst.inv_total = (rs!ren_inv_rate /3)
ElseIf (rs!inv_duration >18.666) then
rst.inv_total = (rs!ren_inv_rate /1)
Else
rst.inv_total = _
((rs!ren_inv_rate /3) * (rs!inv_duration /7))
End IF
rst.Update

Good Luck!

Have a great day!

j2consulting@yahoo.com

 

[cdw0308]

Thanks SBendBuckeye

I finally got it to work. That was very helpful. Thanks for helping me over these few days.

cdw

 

[SBendBuckeye]

You are welcome. I have been helped in a similar manner many times on this board. As an aside, when you have code like the above, if you place your cursor on a give word, say update and hit F1 it will take you right to the help for the word in question.

Also, if you hit F2 that will pop up the object browser. You can then search for recordset and see all of the methods, properties and events associated with one. If you right click on any of those you can select help and immediately go to the help in question.

Good Luck!

Have a great day!

j2consulting@yahoo.com