Please help me read this code

**PC Datasheet** · Nov 12 '05, 04:17 PM

Re: Please help me read this code

If you would like my help, contact me at resource@pcdata sheet.com.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications

https://www.pcdatasheet.com

"Jayjay" <jjf_71@notmail .com> wrote in message
news:3fafefe4.2 4635875@news.ci s.dfn.de...[color=blue]
> When it comes to access, I'm pretty good using the built in features
> and can come up with some pretty complex functions to get what I need.
> But we have this database I'm doing for work that is trying to pull in
> too many things. The database is a construction job estimating
> program. First you setup a project, then you add items that will be
> needed for the job and you create a cost estimate. You then send this
> list out to construction companies who turn in their bids, which are
> added in to the program. The last step is to create a bid tabulation
> report, that is a cross tab of the original estimate along w/ the bids
> from the various construction companies.
>
> This was more complex than I could handle, so we hired someone to do
> it. 4 attempts later and a few thousand dollars and this report still
> does not function properly. Right now I'm attempting to learn VBA
> and fix this damned thing myself.
>
> But, I'm having trouble making sense fo this code he's put in, to
> figure out why 1 out of 5 times the "Total" variable puts in a "$1"
> instead of calculating the total by multiplying the unit * quantity.
>
> Any help would be greatly appreciated.
>
> Below is the code:
>
> Private Sub Detail_Format(C ancel As Integer, FormatCount As Integer)
> ' Place values in text boxes and hide unused text boxes.
> Dim Contractor As String
> Dim TempContractor
> 'Dim ItemCombo As Long
> Dim ItemCombo As String
> Dim i As Integer
> Dim intX As Integer
> Dim TempWMTotal As Single
> ' Verify that not at end of recordset.
> If Not rstReport.EOF Then
> ' If FormatCount is 1, place values from recordset into text
> boxes
> ' in detail section.
> If Me.FormatCount = 1 Then
>
> For intX = 9 To intColumnCount
> ' Convert Null values to 0.
> TempContractor = Me("Head" + Format$(intX))
> ' Replace underscores with periods (reversing what
> cross tab query does with periods)
> Contractor = ""
> For i = 1 To Len(TempContrac tor)
> If Mid(TempContrac tor, i, 1) = "_" Then
> Contractor = Contractor & "."
> Else
> Contractor = Contractor & Mid(TempContrac tor,
> i, 1)
> End If
> Next i
>
> 'ItemCombo = rstReport.Field s("itemcombo" )
> ItemCombo = rstReport.Field s("itemcombo" )
> Me("Unit" + Format$(intX)) = xtabCnulls(rstR eport(intX
> - 1))
> ' filter recordset to display current Contractor's
> record for current Combo Item
> Set rstTotals = dbsReport.OpenR ecordset("SELEC T * FROM
> [qryBidtabulatio nStep2] WHERE Contractor = '" & Contractor & "' AND
> [itemcombo] = '" & CStr(ItemCombo) & "'")
> Me("Total" + Format$(intX)) =
> rstTotals.Field s("TotalCharge" )
> ColumnTotals(in tX) = ColumnTotals(in tX) +
> rstTotals.Field s("TotalCharge" )
> Me.WMTotal = rstTotals.Field s("WMTotalItemC harge")
> TempWMTotal = Me.WMTotal
>
> If Me("Total" + Format$(intX)) <> Me("Unit" +
> Format$(intX)) * rstTotals.Field s("Quantity") Then
> Me("Diff" + Format$(intX)) = Me("Unit" +
> Format$(intX)) * rstTotals.Field s("Quantity")
> Else
> Me("Diff" + Format$(intX)) = ""
> End If
>
> rstTotals.Close
> Next intX
> WMTotalTotal = WMTotalTotal + TempWMTotal
> ' Hide unused text boxes in detail section.
> For intX = intColumnCount + 1 To conTotalColumns
> Me("Unit" + Format$(intX)). Visible = False
> 'Me("DLine" + Format$(intX)). Visible = False
> Next intX
>
> ' Move to next record in recordset.
> rstReport.MoveN ext
> End If
> End If
>
> End Sub
>[/color]

**MGFoster** · Nov 12 '05, 04:17 PM

Re: Please help me read this code

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The news group readers (we) would need to know what you want the
report to do/show; what the RecordSource of the report looks like;
what is this procedure supposed to be doing - it looks like it is
calculating totals. This may be more easily done using the control's
Running Sum property instead of using a function that calculates
totals.

The procedure holds a reference to a Recordset variable (rstReport)
that is not defined in the procedure. We'd need to know what records
this recordset is working with, so we can better understand why it is
being used. My guess - it is the same recordset that is produced by
the Report's RecordSource.

What records are returned by the Recordset "rstTotals" ? It looks like
it is supposed to return one record that is probably a summary (hence,
the name rstTotals). This record's field's data are put into controls
on the report - probably a summary line. If a summary it may be
easier to put a Footer section that could hold the totals (summary)
controls.

Summary control's ControlSource:

=Sum([Column1]) or =Avg([Column1])

Regards,

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP7Ax54echKq OuFEgEQLEFQCeN0 8bpFB2L7kmPQ8I7 jMp9a665FEAnjqY
OtYnSngUP5jvqgs 4B27JkFhW
=VKBh
-----END PGP SIGNATURE-----

Jayjay wrote:
[color=blue]
> When it comes to access, I'm pretty good using the built in features
> and can come up with some pretty complex functions to get what I need.
> But we have this database I'm doing for work that is trying to pull in
> too many things. The database is a construction job estimating
> program. First you setup a project, then you add items that will be
> needed for the job and you create a cost estimate. You then send this
> list out to construction companies who turn in their bids, which are
> added in to the program. The last step is to create a bid tabulation
> report, that is a cross tab of the original estimate along w/ the bids
> from the various construction companies.
>
> This was more complex than I could handle, so we hired someone to do
> it. 4 attempts later and a few thousand dollars and this report still
> does not function properly. Right now I'm attempting to learn VBA
> and fix this damned thing myself.
>
> But, I'm having trouble making sense fo this code he's put in, to
> figure out why 1 out of 5 times the "Total" variable puts in a "$1"
> instead of calculating the total by multiplying the unit * quantity.
>
> Any help would be greatly appreciated.
>
> Below is the code:
>
> Private Sub Detail_Format(C ancel As Integer, FormatCount As Integer)
> ' Place values in text boxes and hide unused text boxes.
> Dim Contractor As String
> Dim TempContractor
> 'Dim ItemCombo As Long
> Dim ItemCombo As String
> Dim i As Integer
> Dim intX As Integer
> Dim TempWMTotal As Single
> ' Verify that not at end of recordset.
> If Not rstReport.EOF Then
> ' If FormatCount is 1, place values from recordset into text
> boxes
> ' in detail section.
> If Me.FormatCount = 1 Then
>
> For intX = 9 To intColumnCount
> ' Convert Null values to 0.
> TempContractor = Me("Head" + Format$(intX))
> ' Replace underscores with periods (reversing what
> cross tab query does with periods)
> Contractor = ""
> For i = 1 To Len(TempContrac tor)
> If Mid(TempContrac tor, i, 1) = "_" Then
> Contractor = Contractor & "."
> Else
> Contractor = Contractor & Mid(TempContrac tor,
> i, 1)
> End If
> Next i
>
> 'ItemCombo = rstReport.Field s("itemcombo" )
> ItemCombo = rstReport.Field s("itemcombo" )
> Me("Unit" + Format$(intX)) = xtabCnulls(rstR eport(intX
> - 1))
> ' filter recordset to display current Contractor's
> record for current Combo Item
> Set rstTotals = dbsReport.OpenR ecordset("SELEC T * FROM
> [qryBidtabulatio nStep2] WHERE Contractor = '" & Contractor & "' AND
> [itemcombo] = '" & CStr(ItemCombo) & "'")
> Me("Total" + Format$(intX)) =
> rstTotals.Field s("TotalCharge" )
> ColumnTotals(in tX) = ColumnTotals(in tX) +
> rstTotals.Field s("TotalCharge" )
> Me.WMTotal = rstTotals.Field s("WMTotalItemC harge")
> TempWMTotal = Me.WMTotal
>
> If Me("Total" + Format$(intX)) <> Me("Unit" +
> Format$(intX)) * rstTotals.Field s("Quantity") Then
> Me("Diff" + Format$(intX)) = Me("Unit" +
> Format$(intX)) * rstTotals.Field s("Quantity")
> Else
> Me("Diff" + Format$(intX)) = ""
> End If
>
> rstTotals.Close
> Next intX
> WMTotalTotal = WMTotalTotal + TempWMTotal
> ' Hide unused text boxes in detail section.
> For intX = intColumnCount + 1 To conTotalColumns
> Me("Unit" + Format$(intX)). Visible = False
> 'Me("DLine" + Format$(intX)). Visible = False
> Next intX
>
> ' Move to next record in recordset.
> rstReport.MoveN ext
> End If
> End If
>
> End Sub
>[/color]

Please help me read this code

Please help me read this code

Comment

Comment