Hi I am looking for the help on

[sabavno]

Hi

I am looking for the help on the following operation:
I have a list box. The procedure below checks what rows are selected, inserts the them into the temporary table and then sends the temporary table to the excel spreadsheet.
Everything works fine, but I gotta enhance this procedure now.
My BlackBerry record that I export to the excel has many fields and one of them is "MONITOR" (yes/no) field indicating whether the monitor is ordered. Then if MONITOR field is set to YES, I have to create a separate line in my spreadsheet for the MONITOR details. So that I would have a line for the record and then underneath the line for the MONITOR.
Would anyone suggest the way of doing that based on the code that I already have?

THanks

Public Sub xls()

Dim i As Long
Dim RecordID As Long
Dim rst As DAO.Recordset
Dim sql As String
Dim datExst As Long
DoCmd.SetWarnings False
For i = 0 To Me.List2.ListCount
If Me.List2.Selected(i) Then
RecordID = Me.List2.Column(0, i)
sql = "Select RecordID, AssetStatusId from BlackBerryRecord where RecordID= " & RecordID
Set rst = CurrentDb.OpenRecordset(sql, dbOpenDynaset)

rst.Edit
rst!AssetStatusID = 1
rst.Update

DoCmd.RunSQL "INSERT INTO tblTemp SELECT BlackBerryRecord.* FROM BlackBerryRecord WHERE BlackBerryRecord.RecordID = " & RecordID
Set rst = Nothing
End If
Next i

datExst = CStr(Format(Date, "mmddyy&quot)
DoCmd.OutputTo acOutputTable, "tblTemp", acFormatXLS, "V:\Polina\Planned - " & datExst & ".xls"
MsgBox "Successful Export"
DoCmd.RunSQL "Delete * from tblTemp"
DoCmd.SetWarnings True
Me.Refresh
End Sub

 

[Rolliee]

Funny Question:
I am looking for the help on the following operation:
I have a list box. The procedure below checks what rows are selected, inserts the them into the temporary table and then sends the temporary table to the excel spreadsheet.
Everything works fine, but I gotta enhance this procedure now.
My BlackBerry record that I export to the excel has many fields and one of them is "MONITOR" (yes/no) field indicating whether the monitor is ordered. Then if MONITOR field is set to YES, I have to create a separate line in my spreadsheet for the MONITOR details. So that I would have a line for the record and then underneath the line for the MONITOR.
Would anyone suggest the way of doing that based on the code that I already have?

THanks

Public Sub xls()

Dim i As Long
Dim RecordID As Long
Dim rst As DAO.Recordset
Dim sql As String
Dim datExst As Long
DoCmd.SetWarnings False
For i = 0 To Me.List2.ListCount
If Me.List2.Selected(i) Then
RecordID = Me.List2.Column(0, i)
sql = "Select RecordID, AssetStatusId from BlackBerryRecord where RecordID= " & RecordID
Set rst = CurrentDb.OpenRecordset(sql, dbOpenDynaset)



rst.Edit

'= = = = = = = = = = = = = = = = = = = = = = == === = ==
if BlackBerryRecord.MONITOR then rst![Something] = AValue
'= = = = = = = = = = = = = = = = = = = = = = == === = ==

rst!AssetStatusID = 1
rst.Update

DoCmd.RunSQL "INSERT INTO tblTemp SELECT BlackBerryRecord.* FROM BlackBerryRecord WHERE BlackBerryRecord.RecordID = " & RecordID
Set rst = Nothing
End If
Next i

datExst = CStr(Format(Date, "mmddyy&quot)
DoCmd.OutputTo acOutputTable, "tblTemp", acFormatXLS, "V:\Polina\Planned - " & datExst & ".xls"
MsgBox "Successful Export"
DoCmd.RunSQL "Delete * from tblTemp"
DoCmd.SetWarnings True
Me.Refresh
End Sub