how to do this

[icemonster]

ok, so i have this code

Private Sub cmdConfirm_Click()
For Each varItem In Me.lstSchedule.ItemsSelected
    'read the selected Item Id
    lngSchdulRecID = Me.lstSchedule.ItemData(varItem)
    'update the fields in the record in the "tblSchedule" table
    strSQL = "SELECT tblSchedule.Status, tblSchedule.StatusDate FROM tblSchedule " _
           & "WHERE (((tblSchedule.ScheduleRecID)=" & lngSchdulRecID & "));"
    Set rs = CurrentDb.OpenRecordset(strSQL)
    rs.Edit
    rs.Fields("Status").Value = 1
    rs.Fields("StatusDate").Value = Date
    rs.Update
    rs.Close
    Set rs = Nothing
Next varItem
'clear all selections
For Each varItem In Me.lstSchedule.ItemsSelected
    Me.lstSchedule.Selected(varItem) = False
Next varItem
Me.lstSchedule.Requery
End Sub

what it does it, when i click the command confirm, it will confirm the status of all the records selected in the listbox,

anyone know how instead of using a command, i use a combo box for the choices of what status i want it to be?

e.g

10 records status is cancelled, so i choose in the combo box the status cancelled and when done, click a button that will change all of the status in the listbox that was selected. thanks!

 

[DCrake]

Private Sub cmdConfirm_Click()
Dim strItems As String

For Each varItem In Me.lstSchedule.ItemsSelected
    '/read the selected Item Id
    lngSchdulRecID =  Me.lstSchedule.ItemData(varItem)
    strItems = strItems & "," & lngSchdulRecID 
Next varItem

'/Drop the last comma
strItems = Left(strItems,Len(strItems)-1)

'/ Wrap IN() around the items

strItems = "In(" & strItems & ")"

'/Build the sql statement

    'update the fields in the record in the "tblSchedule" table
    strSQL = "SELECT Status, StatusDate FROM tblSchedule " _
              & "WHERE ScheduleRecID & strItems;"

    Set rs = CurrentDb.OpenRecordset(strSQL)
    '/Write the changes back to the table
    Do Until Rs.EOF
        rs.Edit
        rs.("Status") = [B]Me.ComboBox[/B]        
        rs.("StatusDate") = Date
        rs.Update
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
'clear all selections
For Each varItem In Me.lstSchedule.ItemsSelected
    Me.lstSchedule.Selected(varItem) = False
Next varItem
Me.lstSchedule.Requery
End Sub

Have improved the syntax to cut down the read/write commands
Notice the Bold Me.ComboBox Point your combo at this field

 

[icemonster]

i tried the code you gave me but the:

rs.("status") = Me.cboStatus2
rs.("date") = Date

is red?

 

[icemonster]

here's what i did to what you gave me, i cant seem to get it to work, i get error 1 or too few parameters

Private Sub cmdSetStatus_Click()
Dim strItems As String

For Each varItem In Me.lstSchedule.ItemsSelected
    '/read the selected Item Id
    lngSchdulRecID = Me.lstSchedule.ItemData(varItem)
    strItems = strItems & "," & lngSchdulRecID
Next varItem

'/Drop the last comma
strItems = Left(strItems, Len(strItems) - 1)

'/ Wrap IN() around the items

strItems = "In(" & strItems & ")"

'/Build the sql statement

    'update the fields in the record in the "tblSchedule" table
    strSQL = "SELECT SCHEDSTATUS, SCHEDSTATUS FROM tblSchedule " _
              & "WHERE SCHEDULEID & strItems;"

    Set rs = CurrentDb.OpenRecordset(strSQL)
    '/Write the changes back to the table
    Do Until rs.EOF
        rs.Edit
        rs.("SCHEDSTATUS") = Me.cboStatus2
        rs.("StatusDate") = Date
        rs.Update
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
'clear all selections
For Each varItem In Me.lstSchedule.ItemsSelected
    Me.lstSchedule.Selected(varItem) = False
Next varItem
Me.lstSchedule.Requery
End Sub

 

[DCrake]

Sorry, Typo

rs("status") = Me.cboStatus2
rs("date") = Date

 

[icemonster]

it works now, but no i get run-time error '3061`

too few parameters, expected 1.

it's on the Set rs = CurrentDb.OpenRecordset(strSqL) line

 

[DCrake]

    'update the fields in the record in the "tblSchedule" table
    strSQL = "SELECT [B]SCHEDSTATUS[/B], [B]SCHEDSTATUS [/B]FROM tblSchedule " _
              & "WHERE SCHEDULEID & strItems;"

Should these not be different? Status and Date

 

[icemonster]

strSQL = "SELECT SCHEDSTATUS, SCHEDSTATUSDATE FROM tblSchedule " _
              & "WHERE SCHEDULEID & strItems;"

hehe. sorry for that, typo too, but as you can see i already changed it still getting the few parameters error.

 

[DCrake]

Post the full syntax

 

[icemonster]

Private Sub cmdSetStatus_Click()
Dim strItems As String

For Each varItem In Me.lstSchedule.ItemsSelected
    '/read the selected Item Id
    lngSchdulRecID = Me.lstSchedule.ItemData(varItem)
    strItems = strItems & "," & lngSchdulRecID
Next varItem

'/Drop the last comma
strItems = Left(strItems, Len(strItems) - 1)

'/ Wrap IN() around the items

strItems = "In(" & strItems & ")"

'/Build the sql statement

    'update the fields in the record in the "tblSchedule" table
    strSQL = "SELECT SCHEDSTATUS, SCHEDSTATUSDATE FROM tblSchedule " _
              & "WHERE SCHEDULEID & strItems;"

    Set rs = CurrentDb.OpenRecordset(strSQL)
    '/Write the changes back to the table
    Do Until rs.EOF
        rs.Edit
        rs("SCHEDSTATUS") = Me.cboStatus2
        rs("SCHEDSTATUSDATE") = Date
        rs.Update
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
'clear all selections
For Each varItem In Me.lstSchedule.ItemsSelected
    Me.lstSchedule.Selected(varItem) = False
Next varItem
Me.lstSchedule.Requery
End Sub

there thanks for helping me out man.

 

[DCrake]

    strSQL = "SELECT [B]SCHEDULEID[/B], SCHEDSTATUS, SCHEDSTATUSDATE FROM tblSchedule " _
              & "WHERE SCHEDULEID & strItems;"

Try this

Also do a Debug.Print strSQL

what does it return

 

[icemonster]

i just realized something, in the form there is already an strSQL so i changed it too strSQL2 and here's the debug:

debug.Print strSQL2
SELECT SCHEDULEID, SCHEDSTATUS, SCHEDSTATUSDATE FROM tblSchedule WHERE SCHEDULEID & strItems;

still getting too few parameters though.

 

[DCrake]

Spotted the mistake

strSQL = "SELECT SCHEDSTATUS, SCHEDSTATUSDATE FROM tblSchedule " _
              & "WHERE SCHEDULEID " & strItems & ";"

 

[icemonster]

well, good news is, the error earlier is gone, what i know have is a syntax error.

syntax error (missing operator) in query expression 'SCHEDULEID In(,1)'

 

[DCrake]

Replace

'/Drop the last comma
strItems = Left(strItems, Len(strItems) - 1)

With

'/Drop the leading/trailing comma(s)

If Right(strItems,1) = "," Then
    strItems = Left(strItems, Len(strItems) - 1)
End If

If Left(strItems,1) = "," Then
   strItems = Mid(strItems,2)
End If

 

[icemonster]

yay it works now. awesome! thanks so much man, i appreciate it.

 

[DCrake]

Got there in the end