[RESOLVED] Is There a batter way to implement Checkboxes?

I keep getting a compiler error when I tried you sSQL. keeps saying expected an expression. and end of something.

So i will put this in the for loop?

Did you get a chance to look at the updated DB, Did I do it correctly?

I guessed the names you used for your controls, you will have to update the second part of the sql and give the corresponding sources of CustID, ProductID, AmountSignedOut. The values in the values clause will also be comma separated.

am i suposed to do more with the sSQL string or is they way you worte it good.

I checked the controls and you are a good guesser.

I put it in my for loop, but no matter what it doesnt like the sSQL = part.

VB Code:

Private Sub save()
 
'increments the table inventory UnitInStock using SQL String
'Const SQLInc = "UPDATE inventory SET UnitInStock = UnitInStock + 1 "
 
'this is the decrement need to subtract from Inventory once signed out
Const SQLDec = "UPDATE inventory SET UnitInStock = UnitInStock - 1 "
 
 
 
Dim lstYourItem As ListItem
Dim strTemp As String
strTemp = ""
 
 
 
For Each lstYourItem In ListView1.ListItems
   If lstYourItem.Checked Then strTemp = strTemp & "," & lstYourItem.Tag
   
sSQL = "INSERT INTO patientequip (CustID, ProductID, AmountSignedOut) VALUES ("
sSQL = sSQL & txtCustID.Text & "," & lstYourItem.Tag & ", " & ") "
   
Next
 
'if string temp does not equal empty string do the code below
If strTemp <> "" Then
 
 
strTemp = SQLDec & "WHERE ProductID IN (" & Mid(strTemp, 2) & ") "  'Mid removes leading comma
 
 
helpinghandCon.Execute strTemp
 
 
'all checks have been passed and equipmnt has been added.
MsgBox "Equipment Has Been added to " + txtCustID, vbOKOnly, "Success"
 
Unload Me
 
 
End If

VB Code:

Private Sub save()
'increments the table inventory UnitInStock using SQL String
'Const SQLInc = "UPDATE inventory SET UnitInStock = UnitInStock + 1 "
 
'this is the decrement need to subtract from Inventory once signed out
Const SQLDec = "UPDATE DaTable SET DaColumn = DaColumn - 1 "
 
 
Dim lstYourItem As ListItem
Dim strTemp As String
strTemp = ""
 
'iterate through the listview's listitems collection, (For Each lstItem In ListView1.ListItems)
'Everytime I encountered a listitem that's checked, I concatenate the value stored
'in the Tag property (which holds ProductID) to strTemp.
'tag was set when the form loads--i set it.
 
For Each lstYourItem In ListView1.ListItems
   If lstYourItem.Checked Then 
      strTemp = strTemp & "," & lstYourItem.Tag
 
      'since item is checked then append patientequip table with this item
      sSQL = "INSERT INTO patientequip (CustID, ProductID, AmountSignedOut) VALUES ("
      sSQL = sSQL & txtCustID.Text & "," & lstYourItem.Tag & ", 0) "  'you forgot to place a value for AmountSignedOut column. I set it to zero
      helpinghandCon.Execute sSQL   'since your still a beginner, we'll forego starting and commiting transactions.
   End If
Next
 
'if string temp does not equal empty string do the code below
If strTemp <> "" Then
   'The ProductIDs in the string are comma separated with a leading comma.
   'So I used Mid(strTemp,2) to discard the leading comma.
   'I then created the query, using the comma separated product IDs in the WHERE clause.
   'Since the CSV is a list of comma separated Product IDs then the WHERE
   'clause becomes WHERE ProductID IN (productID_CSV)
 
   strTemp = SQLDec & "WHERE ProductID IN (" & Mid(strTemp, 2) & ") "  'Mid removes leading comma
   helpinghandCon.Execute strTemp
End If
 
End Sub

now I get an error saying no values given for one or more required parameters.


It points to the last SQL Statement. strTemp.

just to make sure...The sSQL will grab the customer info, does it update the equipment?

the strTemp, that subtracts from inventory since being signed out. Would I add something here for the +1 to equipment signed out.

Hmmmm... ok do this first please. Given an equipment in the listview, what are the table updates that must be done? And are you using stored procedures?

I'm asking so we can give you a cleaner implementation of the code above.

basically this.

I create a patient with ID 4. I then click add equipment button, which opens another form. CustID is saved(within a module). In the ListView say I select bed(which has UnitsINStock of 400). I will subtract 1 from inventory of the bed. and add 1 to the equipment signed out by the patient.

I stored custId in a module. Maybe I normalized the DB incorrectly. Its the one on the previous page. I kept it the same.

The textboxes in frmAddEuiqpPatient are not getting values... so the insert query errors

hmmm, is the query using those textboxes.

I am assuming this is coming from the Access DB tables right?

VB Code:

sSQL = "INSERT INTO patientequip (CustID, ProductID, AmountSignedOut)

This is reading the CustId thats passed in, which I guess I can change it so its coming from the module instead of mod to txt box.

VB Code:

sSQL = sSQL & txtCustID.Text & "," & lstYourItem.Tag & ", 0) "

I made sure this time the values showed up, and it still points to the first line (sSQL =)

if you run the app add patient, change one of the txt values say first name, then they will show on the next form. Wierd how it does that.

thanks for still helping me.

am I allowed to repost, with a diff title since the topic is different?