Checkboxes

[shaz123]

I have form, with a number of orders on there, i wanted to be able to give the user the option to select all the orders and update each record, however when i give the user the option to seleect all the orders on the first line is updated. how can i get all the records to be updated the code i am using is shown below, any help will be greatly appreciated.

   Dim db As DAO.Database
    Dim String2 As Integer
    Dim i As Integer
    Dim rs As DAO.Recordset
    Dim StrSQL As String
    
   Set db = CurrentDb
    Set rs = db.OpenRecordset("kell")
    
    For i = 1 To Me.Quantity
        Stringy2 = InputBox("The Cylinder/Cylinders Will Be with the driver 
        from The Specified Date Below:-", "Spec Gas 2006, (DD / MM / YY)",    Me!Text68 & "")
    
    If StrPtr(Stringy2) <> 0 Then
    rs.AddNew
        rs![Line Number] = Me![Line Number]
               rs![Time of Transaction] = Me![Transaction Date]
                rs![Cylinder Barcode Label] = Me![Cylinder Barcode Label]
                rs![Cylinder Number] = Me![Cylinder Number]
                rs![ProdNo] = Me![ProdNo]
                rs![Status] = Me![Status]
                rs![AberdeenWONumber] = Me![AberdeenWONumber]
                rs![Works Order Number] = Me![Works Order Number]
                rs![CustNo] = Me![CustNo]
                rs![Customer Order Number] = Me![Customer Order Number]
                rs![Date of Transaction] = Stringy2
                rs![User name] = Me![User name]
                rs![Employee ID] = Me![Empoyee ID]
                rs![A Number] = Me![A Number]
                rs![New Status] = "With Driver"
               rs![Date of Transaction] = Me!Text68
                Me.Text71 = "With Driver"
                Me.Text71.ForeColor = vbRed
                Me.Text74 = Me.Text68
         rs.Update
         MsgBox "The cylinder is now with the driver", vbInformation, "Returned    Successfully"
       
        End if
    Next i
    
       rs.Close
        db.Close

End Sub

 

[Jonathan Kok]

The code you posted is adding a new record. Is that what you wanted to do?

Did you want to update all of the records in the source table or query, or only a subset?

Is Quantity the record count we talked about in an earlier thread?

Did you want to update all of fields in the table, or only the 'Date of Transaction' field?

Assuming 1)You're updating all of the records in the table or query (not a subset), 2)the 'Quantity' is the record count, and 3)You only want to update the Date of Transaction:

Inputbox("blah") As stringy2
If StrPtr(stringy2) <> 0 Then
    With rs     'this will set any . or ! function to be preceded by 'rs'
        .MoveFirst 'this will move to the first record in the recordset you've declared
        For i = 1 To Quantity
            .edit  'allows edits to occur
            ![Date of Transaction] = stringy2  'set the date to the inputed string
            .Update 'complete the edit
            MsgBox "..."
            .MoveNext  'go to the next record
        Next i  'do it again
    End With
End If

EDIT: Couple things to keep in mind.
1. If you simply reference a field by its name (eg. "[Field Name]"), Access will automatically assume you're talking about the field name on the current form. You do not need to preceed it with "Me."
2. In the example above, I used the 'With' statement to cut down on the typing. As commented, it forces access to preced every '.' or '!' with 'rs'. So, if I say, '.movenext' or '.update', it interprets it as 'rs.movenext' and 'rs.update'.

 

[shaz123]

Thanxs for you help, but I dont think i made myself clear enough,

My form is based upon a table called tbl_TransactionMaster, which shows orders (line numbers) based on works order number. I wanted to give the individual the option to carry out partial and full deliveries. An individual has the option to either click on an individual command button next to a line number, which will then copy the record from tbl_transactionMaster to tbl_kel depending on the command button clicked. This part works fine, however when giving the user the option for a full delivery,i have provided a checkbox on the form, when they have selected the checkboxes and on the confirm delivery icon, only the first record for the fisrt checked line number is copied to the tbl_kel table,

Even by adding, the code below, the first line number shown is only inputted, no matter how many checkboxs are ticked.

For i = 1 To Quantity

Next i

For instance
if the form shows
Line number Cylinder barcode number Works order number
00003 200050232 12345
00003 20050233 12345

When i select all the checkboxes on the form, the fisrt line number is added into tbl_kel twice. I wanted it to add the fisrt and second one.

Any help will be appreciated.