Please help me read this code

[Jayjay]

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(Cancel 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(TempContractor)
If Mid(TempContractor, i, 1) = "_" Then
Contractor = Contractor & "."
Else
Contractor = Contractor & Mid(TempContractor,
i, 1)
End If
Next i

'ItemCombo = rstReport.Fields("itemcombo")
ItemCombo = rstReport.Fields("itemcombo")
Me("Unit" + Format$(intX)) = xtabCnulls(rstReport(intX
- 1))
' filter recordset to display current Contractor's
record for current Combo Item
Set rstTotals = dbsReport.OpenRecordset("SELECT * FROM
[qryBidtabulationStep2] WHERE Contractor = '" & Contractor & "' AND
[itemcombo] = '" & CStr(ItemCombo) & "'")
Me("Total" + Format$(intX)) =
rstTotals.Fields("TotalCharge")
ColumnTotals(intX) = ColumnTotals(intX) +
rstTotals.Fields("TotalCharge")
Me.WMTotal = rstTotals.Fields("WMTotalItemCharge")
TempWMTotal = Me.WMTotal

If Me("Total" + Format$(intX)) <> Me("Unit" +
Format$(intX)) * rstTotals.Fields("Quantity") Then
Me("Diff" + Format$(intX)) = Me("Unit" +
Format$(intX)) * rstTotals.Fields("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.MoveNext
End If
End If

End Sub

[PC Datasheet]

If you would like my help, contact me at re******@pcdatasheet.com.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
www.pcdatasheet.com
"Jayjay" <jj****@notmail.com> wrote in message
news:3f***************@news.cis.dfn.de...

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(Cancel 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(TempContractor)
If Mid(TempContractor, i, 1) = "_" Then
Contractor = Contractor & "."
Else
Contractor = Contractor & Mid(TempContractor,
i, 1)
End If
Next i

'ItemCombo = rstReport.Fields("itemcombo")
ItemCombo = rstReport.Fields("itemcombo")
Me("Unit" + Format$(intX)) = xtabCnulls(rstReport(intX
- 1))
' filter recordset to display current Contractor's
record for current Combo Item
Set rstTotals = dbsReport.OpenRecordset("SELECT * FROM
[qryBidtabulationStep2] WHERE Contractor = '" & Contractor & "' AND
[itemcombo] = '" & CStr(ItemCombo) & "'")
Me("Total" + Format$(intX)) =
rstTotals.Fields("TotalCharge")
ColumnTotals(intX) = ColumnTotals(intX) +
rstTotals.Fields("TotalCharge")
Me.WMTotal = rstTotals.Fields("WMTotalItemCharge")
TempWMTotal = Me.WMTotal

If Me("Total" + Format$(intX)) <> Me("Unit" +
Format$(intX)) * rstTotals.Fields("Quantity") Then
Me("Diff" + Format$(intX)) = Me("Unit" +
Format$(intX)) * rstTotals.Fields("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.MoveNext
End If
End If

End Sub

[MGFoster]

-----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/AwUBP7Ax54echKqOuFEgEQLEFQCeN08bpFB2L7kmPQ8I7jMp9a 665FEAnjqY
OtYnSngUP5jvqgs4B27JkFhW
=VKBh
-----END PGP SIGNATURE-----

Jayjay wrote:

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(Cancel 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(TempContractor)
If Mid(TempContractor, i, 1) = "_" Then
Contractor = Contractor & "."
Else
Contractor = Contractor & Mid(TempContractor,
i, 1)
End If
Next i

'ItemCombo = rstReport.Fields("itemcombo")
ItemCombo = rstReport.Fields("itemcombo")
Me("Unit" + Format$(intX)) = xtabCnulls(rstReport(intX
- 1))
' filter recordset to display current Contractor's
record for current Combo Item
Set rstTotals = dbsReport.OpenRecordset("SELECT * FROM
[qryBidtabulationStep2] WHERE Contractor = '" & Contractor & "' AND
[itemcombo] = '" & CStr(ItemCombo) & "'")
Me("Total" + Format$(intX)) =
rstTotals.Fields("TotalCharge")
ColumnTotals(intX) = ColumnTotals(intX) +
rstTotals.Fields("TotalCharge")
Me.WMTotal = rstTotals.Fields("WMTotalItemCharge")
TempWMTotal = Me.WMTotal

If Me("Total" + Format$(intX)) <> Me("Unit" +
Format$(intX)) * rstTotals.Fields("Quantity") Then
Me("Diff" + Format$(intX)) = Me("Unit" +
Format$(intX)) * rstTotals.Fields("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.MoveNext
End If
End If

End Sub