listboxes and temp tables

[owen]

Hello,

I have a list box containing all 1 months. I also have a
report that details monthly billings.
I would like to allow the user the opportunity to select
as many months' records as they want to be
Included in another report called BillingReportSelected.

My code is as follows:
Private Sub Command4_Click()


Dim lst As Access.ListBox
Dim strMonth As String
Dim strCostCode As Double
Dim strCostCodeTotal As Double
Dim strPercentCompleteThisMonth As Double
Dim strThisMonth As Double
Dim strSumPercent As Double
Dim strSumThisMonth As Double
Dim varItem As Variant
Dim intIndex As Integer
Dim intCount As Integer
Dim intRow As Integer
Dim intColumn As Integer
Dim i As String
Dim lngID As String

Set lst = Forms![Form2]![LstSelectMonth]

DoCmd.SetWarnings False
strSQL = "DELETE * from tblMonths"
DoCmd.RunSQL strSQL

If lst.ItemsSelected.Count = 0 Then
MsgBox "Please select at least one month"
lst.SetFocus
Exit Sub
End If

For Each varItem In lst.ItemsSelected

strMonth = lst.Column(0, varItem)
Debug.Print "Selected ID: " & lngID
strSQL = "INSERT INTO tblMonths (Month, CostCode,
CostCodeTotal, " _
& "PercentCompleteThisMonth, ThisMonth,
SumPercent, SumThisMonth ) " _
& "SELECT Month, CostCode, CostCodeTotal,
PercentCompleteThisMonth, " _
& "ThisMonth, PercentCompleteThisMonth,
PercentCompleteThisMonth FROM BillingReport " _
& "WHERE Month = " & strMonth & ";"
DoCmd.RunSQL strSQL

Next varItem

DoCmd.OpenReport reportname:="BillingReportSelected",
View:=acViewPreview

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "ErrorNo: " & Err.Number & ": Description: " &
_
Err.Description
Resume ErrorHandlerExit

End Sub


When I click on the command button, I get a parameters
message box with the months I've selected in the message
area and a prompt to fill in the month. Each month I've
selected gets its own parameter message box. If I enter
the months I want, the report comes up as desired.

What have I done????? How can I get the months to be
entered via the list box and do away with the message
boxes?

My temp table is called tblMonths and I'm getting the
records from a query called BillingReport.

Thanks,

Owen

 

[Guest]

Try changing your Where clause from
& "WHERE Month = " & strMonth & ";"
to
& "WHERE Month = '" & strMonth & "';"

note the two extra single quotes

 

[Guest]

Thank you, thank you, thank you; I was getting tired of
beating my head against the wall and the neighbors were
starting to complain.

-----Original Message-----
Try changing your Where clause from
& "WHERE Month = " & strMonth & ";"
to
& "WHERE Month = '" & strMonth & "';"

note the two extra single quotes

Hello,

I have a list box containing all 1 months. I also have a
report that details monthly billings.
I would like to allow the user the opportunity to select
as many months' records as they want to be
Included in another report called BillingReportSelected.

My code is as follows:
Private Sub Command4_Click()


Dim lst As Access.ListBox
Dim strMonth As String
Dim strCostCode As Double
Dim strCostCodeTotal As Double
Dim strPercentCompleteThisMonth As Double
Dim strThisMonth As Double
Dim strSumPercent As Double
Dim strSumThisMonth As Double
Dim varItem As Variant
Dim intIndex As Integer
Dim intCount As Integer
Dim intRow As Integer
Dim intColumn As Integer
Dim i As String
Dim lngID As String

Set lst = Forms![Form2]![LstSelectMonth]

DoCmd.SetWarnings False
strSQL = "DELETE * from tblMonths"
DoCmd.RunSQL strSQL

If lst.ItemsSelected.Count = 0 Then
MsgBox "Please select at least one month"
lst.SetFocus
Exit Sub
End If

For Each varItem In lst.ItemsSelected

strMonth = lst.Column(0, varItem)
Debug.Print "Selected ID: " & lngID
strSQL = "INSERT INTO tblMonths (Month, CostCode,
CostCodeTotal, " _
& "PercentCompleteThisMonth, ThisMonth,
SumPercent, SumThisMonth ) " _
& "SELECT Month, CostCode, CostCodeTotal,
PercentCompleteThisMonth, " _
& "ThisMonth, PercentCompleteThisMonth,
PercentCompleteThisMonth FROM BillingReport " _
& "WHERE Month = " & strMonth & ";"
DoCmd.RunSQL strSQL

Next varItem

DoCmd.OpenReport reportname:="BillingReportSelected",
View:=acViewPreview

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "ErrorNo: " & Err.Number & ": Description: " &
_
Err.Description
Resume ErrorHandlerExit

End Sub


When I click on the command button, I get a parameters
message box with the months I've selected in the message
area and a prompt to fill in the month. Each month I've
selected gets its own parameter message box. If I enter
the months I want, the report comes up as desired.

What have I done????? How can I get the months to be
entered via the list box and do away with the message
boxes?

My temp table is called tblMonths and I'm getting the
records from a query called BillingReport.

Thanks,

Owen

.