Information not editing the correct record

[shaz123]

Hi peeps,

I have a combobox where i select a works order from, the results are displayed on a list box. The number of records in the list box is displayed in the text field. Once an individual has clicked on the combobox, depeding on the number of records on the listbox, when clicking on the command button a inout box should appear asking for the cylinder number they wish to deliver, chaging its status to delivered.
However when trying to do this the information is not editing the correct record. Below is my coding any help will be greatly appreciated.

For i = 1 To Me.Quantity 'depending on the textfirld showing the number of records

Stringy = InputBox("Please Enter/Scan The cylinder barcodes numbers you wish to Deliver")
    
  StrSQl = "UPDATE tbl_Delupdate SET [D Status] = 'Delivered',[Date of D Status] = '" & Stringy1 & "' "
   StrSQl = StrSQl & "WHERE [Works Order Number] = '" & [Works Order Number] & "' AND [Cylinder Barcode Label] = '" & Stringy & "' AND [Line Number] = '" & [Line Number] & "' AND [D Status] IS NULL OR [D Status] <> 'Delivered' "
   db.Execute (StrSQl)

Stringy1 = InputBox("Transaction Date, (DD / MM / YY)", "Spec Gas System", Now())
    
    MsgBox "Selected items have been marked as delivered for date " & Stringy1, vbInformation, "Items Changed Successfully"
   Me.Refresh
       
        End If
    Next i
        
          rs2.Close
          db.Close
End Sub

 

[Remou]

You seem to be using Stringy1 in strSQL, so you must put it before strSQL. Are Line Number and Works Order Number number or text fields? If they are number fields, you should not have single quotes.

 

[shaz123]

thank for your response, stringy1 is before StrSQl, i must of pasted it wrong on the forum sorry, and line number and works order nubmer are text fields.

Is it necessary to select the each order within the list box, to edit that particular field, for example if there are two records, can i edit both of them when only one fo them is highlighted.

 

[Remou]

Do you wish to update all the records shown in the listbox? This is possible. It is also possible to select a number of records and update just the selected records. Does your list box include the unique ID for the records to be updated?

 

[shaz123]

Well once, a works order is selected from the combobox, i ould then like to update all the recrods showing the listbox. I wanted to be able to click on a coomand button and update all the recordw within the listbox.

FOr instacnce
Works order number 35689 is selected from the combo box

It then brings up

line number Product Number
000001 35688
00002 58964

When clicking on the command button both records should be updated.

Ur asking about a unique field, the thing is the Driver will be selectiung the a data from selecting a work order number which does have a unique Transaction ID, but this does not mean anyting to them in this case.

Within my table u can see the 35689 gets repeated so its not unique, as Transaction ID does not mean anyting to them , i therefore referecned everyting to wrok order number

Workorder number Line number Product nubmer
35689 000001 35688
35689 000002 58964

Thanx in advance just a bit stressed at the mo,

 

[Remou]

What you have and what you show your users are two completely different things. This would be a lot easier with a single unique number. What numbers combine to give a unique record? All three:
Workorder number
Line number
Product number

Or a selection?

Why did you decide on a listbox? I think a subform would be easier to update, however a listbox is also possible. Here is a rough sketch of the idea I am trying to get to work with your set-up:

[tt]'Assuming header row, so i=1
For i = 1 To Me.List0.ListCount - 1
strList = strList & "," & Me.List0.Column(0, i)
Next

strSQL = "Update tblTable Set [dteDate]=#" & Date & "# Where ID In (" & Mid(strList, 2) & ")"

CurrentDb.Execute strSQL[/tt]

 

[shaz123]

soiw for late reply, i had to go to a meeting, workorder number, line number and yes product number will give me a unique record.

Hows is a subform created?

in ur code above were is Date refering to

strSQL = "Update tblTable Set [dteDate]=#" & Date & "# Where ID In (" & Mid(strList, 2) & ")"

 

[shaz123]

Do you think it would be better to request the drivers to input a worksorder number for instance within the query the form is based on

 

[Remou]

The code I showed is merely a sketch of an idea that I am aiming at, it will not work with your set up in its present form. Date in code means today's date, just as Time means this moment.

A subform can be created using the wizard. The RecordSource of a subform can be changed using code:

[tt]Me.[Subform Control Name].Form.Recordsource = "Select * From tblTable WHERE [Works Order Number] = '" & [Works Order Number] & "'"[/tt]

It is also possible to set Link Child and Link Master Fields for a subform. If, say, you set the Link Master to the combo and the Link Child to a suitable field, the records shown would change automatically, without code, when you selected a different number in the combo. It is quite easy to loop through the recordset of a subform and update fields.

I wonder about your original Where statement as you are showing that a number of records will be updated and then, perhaps, excluding some of these without a warning.

You could look at eliminating the Input boxes by including extra textboxes on the form for cylinder number and transaction date. This would allow you to check the data rather than running SQL off unchecked input.

You seem to be getting [Works Order Number] and [Line Number] from the list box, but you are not referencing the column in which these fields appear.

 

[shaz123]

do u mean in this code were u say "You seem to be getting [Works Order Number] and [Line Number] from the list box, but you are not referencing the column in which these fields appear."

is this not refering to the data in the list box

'" & [Line Number] & "'
'" & [Works Order Number]

 StrSQl = StrSQl & "WHERE [Works Order Number] = '" & [Works Order Number] & "' AND [Cylinder Barcode Label] = '" & Stringy & "' AND [Line Number] = '" & [Line Number] & "' AND [D Status] IS NULL OR [D Status] <> 'Delivered' "
   db.Execute (StrSQl)

 

[Remou]

No. I'm afraid not. You can see this with a message box or debug.print.

For example:
[tt]MsgBox Me.List0.Column(1, Me.List0.ItemsSelected(0))[/tt]

Will show the second column of the first item selected in a listbox called List0.

 

[shaz123]

Thankx alot for your help, but i think its is guna be diffiuclt using a listbox and were as the worksorder number is not unique, what ive dun is created another form, where when opened it asks for the work order number, it then brings up all the orders for that number.

again how would i count the number of records on the form thrugh a textbox, what i have dun is given the driver the option to slect all the order and edit it by selecting a select all button chekcing each text box, but it only edits information for the first line selected and not the others.

Im sorry im biewng a reall pain

 

[Remou]

What is the code behind the button?