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 what happens is the user can select all the records by using a checkbox, then they click on a command button, when they click on that command button it only edits the first records. how do i get it tp update all of them.



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

 

[hermanlaksko]

The recordset will only add records until i = Me.Quantity, as it should.
In other words if Me.Quantity=2 then 2 records will be added.



Herman
Say no to macros

 

[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.

 

[hermanlaksko]

Why then "For i = 1 To Quantity" line I do not understand.

YrTable .... as I imagine it:
LineNumber CylinderBarcodeNumber WorksOrderNumber Qty
00003 200050232 12345 4
00003 20050233 12345 7
00003 20050233 12345 4

Recordset needed.... as I imagine it:
SELECT Table1.LineNumber, Table1.CylinderBarcodeNumber, Table1.WorksOrderNumber, Sum(Table1.Qty) AS SumOfQty
FROM Table1
GROUP BY Table1.LineNumber, Table1.CylinderBarcodeNumber, Table1.WorksOrderNumber;

Will give you 2 records. (qty 4 + 7 = 11)
Do while not Rs.EOF
Yr code
Rs.MoveNext
loop
Should do the trick

Note that I have removed spaces from your fieldnames and replaced with capitals, this is non of my busines but this makes working with fieldnames in code etc.etc. easyer, ([Yr fld name]/MyFldName) so just an extra info/hint.

Herman
Say no to macros