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

I am basically writting a program to assign inventory to a patient. The inventory is being added to the database by checkboxes. So when they click each of the check boxes, 1 is added to each of their equipment signed out. and inventory is decremented by 1 for each equipment.

When the Submit button is clicked everything is added to the DB.


Question:

As I am writting this, I realised if there was 100 pieces of equipment, that would take me forever to write. I am only using 20 equipments--i.e checkboxes. And if the company ever changes their equipment, I would have alot of rewritting to do.

The code below would have to be written for each checkbox selected. Is there a better way?

Here is what I came up with.

VB Code:

[B]//code for the actual checkbox[/B]
Private Sub chkbed_Click()
 
bedchk = False
 
'vbchecked is a reserved word
chkBed.Value = vbChecked
bedchk = True
 
End Sub
[B]
//adds to the patients equipment in the database[/B]
'customers only get one thing of each equip
add = 1
 
'Adding equipment to patient
With rspatientequip
.AddNew
 
id = getCustID
 
!CustId = id
 
If addequip = True Then
!bed = "bed"
 
End If
rspatient.Update
End With
 
 
 
[B]//code for updating inventory[/B]
 
Dim id As String
'customers only get one thing of each equip
reduce = 1
 
 
 
'taking away from inventory, Updates inventory field UNitIn Stock - reduce(1)
Dim newDBInstockValue As Integer
Dim dbInStock As Integer
 
With rsinventory
    .MoveFirst
    .Find "ProductName = 'Bed'"
    dbInStock = !UnitInStock
    
    newDBInstockValue = (dbInStock - reduce)
    !UnitInStock = newDBInstockValue
    
rsinventory.Update
End With

Use a listview with its checkboxes enabled

Quote:

---

VB Code:

Private Sub chkbed_Click()
 
bedchk = False
 
'vbchecked is a reserved word
chkBed.Value = vbChecked
bedchk = True
 
End Sub

---

Code from the above quote really makes no sense. Here is what you can do instead:

VB Code:

Private Sub chkbed_Click()
    bedchk = CBool(chkBed.Value)
End Sub

There's no need for CBool(), since both types (the one you're assigning with and the one you're assigning to) are booleans...

edit: Oops... this goes for Option Button. Didn't read well :blush:

Quote:

---

Originally Posted by RhinoBull

Code from the above quote really makes no sense. Here is what you can do instead:

VB Code:

Private Sub chkbed_Click()
    bedchk = CBool(chkBed.Value)
End Sub

---

what I did works though. I am not good at VB, so I am not sure what I can and shouldn't do.

whats the diff with your way and what I had?


everything above works, just need a more effiecint way.

I have like 20 checkboxes, I dont want to do the code above(my post) 20 times +

Quote:

---

Originally Posted by leinad31

Use a listview with its checkboxes enabled

---

hmmm, whats the listview? can you explain


thanks,
ched

Quote:

---

Originally Posted by ched35

... whats the diff with your way and what I had? ...

---

chkBed.Value get assigned when you click on the checkbox (same goes for option buttons) - you don't need to explicitely do that on click. You may (as I showed you) set some flags, get current value and proceed as you need to.
The way you have is a good way to run out stack/memory...

Quote:

---

Originally Posted by ched35

hmmm, whats the listview? can you explain

---

You can say ListView is an extended version of ListBox. Go to menu Project>>Components, and search for MS Windows Common Controls 5 or 6. Also, there's a complete guide to the ListView control in MS's MSDN.

Quote:

---

Originally Posted by gavio

You can say ListView is an extended version of ListBox. Go to menu Project>>Components, and search for MS Windows Common Controls 5 or 6. Also, there's a complete guide to the ListView control in MS's MSDN.

---

I looked at some examples but still don't know if that will help.

basically my form has in it.

info at the top to see the current patient

at the bottom of the screen there are checkboxes to select equipment for that patient to sign out.


not sure how a listview control helps me.

If you could explain further(based on my needs).

thanks for helping me understand,
ched

ok, I found a good example--finally. Most of the examples I found didn't work or load up.

so with a list view I can load all the equipment to the list. Now when a user selects or highlights them, how can I change inventory values for the DB.

changes occur after the submit buttons is hit.

thanks,

anyone?

Please, don't bump after ONLY 2 hours... give people some time... preferably, don't bump at all.

Quote:

---

Originally Posted by ched35

ok, I found a good example--finally. Most of the examples I found didn't work or load up.

so with a list view I can load all the equipment to the list. Now when a user selects or highlights them, how can I change inventory values for the DB.

changes occur after the submit buttons is hit.

thanks,

---

You iterate through the ListItems collection and test the Checked property of each ListItem... if its checked then you update the count in the DB for that item, so its best to store the primary key of the record in the ListItem.Tag property for sql reference purposes.

Here is what I have now for the listview. The list isn't populating for some reason. I am trying to read values from a database the inventory table. to the listview.

I am not getting any errors.

in project->components-> I added ADO data control
datagrid control
datalist control
windows common controls


Here is my module that I used for the connections. The whole project uses this.

VB Code:

Option Explicit
 
Public helpinghandCon As ADODB.Connection
 
Public Sub openConnection()
Set helpinghandCon = New ADODB.Connection
helpinghandCon.ConnectionString = "provider = Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source =" & App.Path & "\helpinghand.mdb"
helpinghandCon.Open
 
End Sub
 
Public Sub closeConnection()
helpinghandCon.Close
Set helpinghandCon = Nothing
 
End Sub
Public Sub openRecordset(tableName As String, recordsetName As _
                                    ADODB.Recordset)
Set recordsetName = New ADODB.Recordset
recordsetName.Open tableName, helpinghandCon, adOpenDynamic, _
                            adLockOptimistic, adCmdTable
End Sub
 
Public Sub closeRecord(recordsetName As ADODB.Recordset)
recordsetName.Close
Set recordsetName = Nothing
 
End Sub

Here is the Form I am using to populate listview I have.

VB Code:

Option Explicit
 
Dim rspatient As ADODB.Recordset
Dim rspatientequip As ADODB.Recordset
Dim rsinventory As ADODB.Recordset
 
Dim customerID As String
Dim bedchk      As Boolean
Dim reduce As Integer
 
Private Sub cmdGoBack_Click()
Call Load(frmAddPatient)
Call frmAddPatient.Show
 
'Call Unload(frmAddEquipPatient)
End Sub
 
Private Sub cmdSubmit_Click()
 
rspatient.MoveLast
'rspatientequip.MoveLast
'rsinventory.MoveLast
save
 
End Sub
 
[U]Private Sub Form_Load()[/U]
openRecordset "Patient", rspatient
openRecordset "Patientequip", rspatientequip
openRecordset "Inventory", rsinventory
 
'calling the name of the listview control and table I am using to fill it
Call AddToList(lvwMain, rsinventory)
 
'getting from module to place in the frame Patient here for testing...
 txtCustID = getCustID
 txtHospice = getHospice
 txtFirst = getFirstName
 txtLast = getLastName
 txtMiddle = getMiddleName
[U]End Sub[/U]
 
Private Sub save()
End Sub 'end function save
 
Private Function AddToList(List As Control, RS As ADODB.Recordset) As Boolean
On Error Resume Next
    'This is a  routine for quickly adding items from a recordset to a listitem.
    Dim mItem     As Variant
    Dim lx        As Long
    Dim strItem   As String
    Dim STRA      As String
    Dim STRB      As String
    Dim Refreshed As Boolean
        With List
            .ListItems.Clear
            .Sorted = False
            If RS.RecordCount > 0 Then RS.MoveFirst
            Do Until RS.EOF
                STRA = .ColumnHeaders(1)
                strItem = RS(STRA)
                'Add the item
                Set mItem = .ListItems.Add(, , strItem, 1, 7)
                'Do the subitems
                For lx = 1 To .ColumnHeaders.Count - 1
                    STRA = .ColumnHeaders(lx + 1)
                    STRB = .ColumnHeaders(lx)
                    If IsNull(RS(STRA)) Then mItem.SubItems(STRB) = "" Else mItem.SubItems(lx) = RS(STRA)
                Next
                'Refresh for appearance
                If List.ListItems.Count > 45 And Not Refreshed Then
                    Refreshed = True
                    DoEvents
                    List.Refresh
                End If
            RS.MoveNext '
            lx = lx + 1
            Loop
            RS.MoveFirst
            List.ListItems(1).EnsureVisible
            .Refresh
        End With
        AddToList = True
End Function
 
 
Private Sub lvwMain_ItemCheck(ByVal item As MSComctlLib.ListItem)
   ' Set the variable to the SelectedItem.
    Set lvwMain.SelectedItem = item
    If item.Checked Then
        ' user checked an item
        lvwMain.ListItems.item(lvwMain.SelectedItem.Index).SmallIcon = 6
        lvwMain.ListItems.item(lvwMain.SelectedItem.Index).Bold = True
        lvwMain.ListItems.item(lvwMain.SelectedItem.Index).ForeColor = vbBlack
        lvwMain.ListItems.item(lvwMain.SelectedItem.Index).Checked = True
        lvwMain.ListItems.item(lvwMain.SelectedItem.Index).ListSubItems(1).Bold = True
        lvwMain.ListItems.item(lvwMain.SelectedItem.Index).ListSubItems(1).ReportIcon = 58
        
        lvwMain.ListItems.item(lvwMain.SelectedItem.Index).ListSubItems(2).Bold = True
        lvwMain.ListItems.item(lvwMain.SelectedItem.Index).ListSubItems(2).ReportIcon = 58
        
        lvwMain.ListItems.item(lvwMain.SelectedItem.Index).ListSubItems(3).Bold = True
        lvwMain.ListItems.item(lvwMain.SelectedItem.Index).ListSubItems(3).ReportIcon = 58
        
        ' Code to handle checked items.
    Else
        ' user unchecked an item
        lvwMain.ListItems.item(lvwMain.SelectedItem.Index).SmallIcon = 7
        lvwMain.ListItems.item(lvwMain.SelectedItem.Index).Bold = False
        lvwMain.ListItems.item(lvwMain.SelectedItem.Index).ForeColor = vbBlack
        lvwMain.ListItems.item(lvwMain.SelectedItem.Index).Checked = False
        lvwMain.ListItems.item(lvwMain.SelectedItem.Index).ListSubItems(1).Bold = False
        lvwMain.ListItems.item(lvwMain.SelectedItem.Index).ListSubItems(1).ReportIcon = 0
        
        lvwMain.ListItems.item(lvwMain.SelectedItem.Index).ListSubItems(2).Bold = False
        lvwMain.ListItems.item(lvwMain.SelectedItem.Index).ListSubItems(2).ReportIcon = 0
        
        lvwMain.ListItems.item(lvwMain.SelectedItem.Index).ListSubItems(3).Bold = False
        lvwMain.ListItems.item(lvwMain.SelectedItem.Index).ListSubItems(3).ReportIcon = 0
        
        ' Code to handle unchecked items.
 
    End If
End Sub

It's better to declare mItem as ListItem, instead of Variant. Never declare as variant. Also (declare List as ListView, instead of Control):

VB Code:

Private Function AddToList(List As [B]ListView[/B], RS As ADODB.Recordset) As Boolean

What's the .View in your ListView? Have you add any Column Headers?

what do you mean .View?

add to list function is supposed to add the columns and then fill the data from the DB.

I have the sample program, if you wanna see it. They used a module class and I used a module.

I didn't add command stuff they had cause when I did it didn't work, got errors

this is what they had, its from the module
Set cmdEmployees = New ADODB.Command

cmdEmployees.ActiveConnection = cnnConnection
cmdEmployees.CommandType = adCmdTable
cmdEmployees.CommandText = "Employees"
rstEmployees.CursorType = adOpenKeyset
rstEmployees.LockType = adLockOptimistic
Set rstEmployees = cmdEmployees.Execute

EmployeesRecordCount = rstEmployees.RecordCount

for the example program I mentioned, I used my DB instead of the Nwind one. The values do not get read in. Wierd, and the column names are the same somehow.

Does any one have a good example program for reading in from a database to a listview control?

I attached the sample program with my DB, I just called Nwind...not the real name.

I figured out why its not working. IN the column heads there is:

EmployeeID LastName FirstName etc.....

These are the fields name in the DB. I just my DB to reflect and it worked

Looking through the code I haven't the slightest Idea where those columns are coming from. What's even stranger, I changed to a different DB(included).


Can someone tell me where these column names are coming from?

thanks

Check under...

VB Code:

'Do the subitems

... routine in AddToList() function.

but where is it getting the names for the columns?

there is no name EmployeeID FirstName etc.....

I had to change my DB to those name inorder for it to work

my original column names where ProductId ProductName etc.... and it wouldn't read in the stuff.

ok, I figured out where the column name are coming from.

I went to list view properties --> column headers tag
and changed the appropriate names to my needs.

quick question, how do I get the values that are checked in a list view, so that when an item is checked the item in the DB is decremented when the SUBMIT button is clicked on.

the post from earlier said something about the ProductID's Key....how do I do that.

is there another way?

thnks.

Does this help:

VB Code:

Option Explicit
 
Dim i As Integer, j As Integer
Dim tmp As String
Dim lvwItem As ListItem
 
Private Sub Command1_Click()
 
For i = 1 To ListView1.ListItems.Count
    Set lvwItem = ListView1.ListItems.Item(i)
        If lvwItem.Checked = True Then
            If ListView1.ColumnHeaders.Count > 0 Then
                tmp = vbNullString: tmp = lvwItem.Text
                    If ListView1.ColumnHeaders.Count > 1 Then
                        For j = 2 To ListView1.ColumnHeaders.Count
                            tmp = tmp & ";" & lvwItem.SubItems(j - 1) 'divide columns with ";" sign
                        Next j
                    End If
            End If
                'now use tmp however you want
                Debug.Print tmp
        End If
Next i
 
End Sub

basically I want to have the selected items from the list:

what ever is selected, from the inventory table, I want the UnitInStock Decremented
and from the patientequipment table, I want the equipment thats selected to be +1 in the equipment columns.

I am trying to modify your way, might take me awhile.

is there a diff way? or is what you wrote good.

ok, I came up with this and it only works for the first record in UnitInStock--can't figure out how to use the i from the for loop. Is there a way to use the i value to jump to the next record.

is there a way to refresh the listView once values have been changed. i.e submit button is clicked.

VB Code:

'go through each item
For i = 1 To ListView1.ListItems.Count
 
    'set the first value to lvwitem, then do again with 2,3,4...
    Set lvwItem = ListView1.ListItems.item(i)
    
        'if the item is checked
        If lvwItem.Checked = True Then
        
        'gets the values from under the Column UnitInStock
            tmp = ListView1.ListItems.item(i).ListSubItems(2)
            
            With rsinventory
            .MoveFirst            'i tried to use .index(i) that didnt work...whats the proper way
        
                unit = !UnitInStock
                
                !UnitInStock = unit - 100
 
           End With
            
                       End If
                'now use tmp however you want
                Debug.Print tmp
        'End If
Next i

If you stored the primary keys in the listitem.tag property then you could iterate through listitems and collect these IDs (comma separated). You will use these IDs in your WHERE clause for your update query... below is a decrement

UPDATE table SET inventory = inventory - 1 WHERE ID IN (100,101,200)

while this is an increment

UPDATE table SET inventory = inventory + 1 WHERE ID IN (100,101,200)

VB Code:

Const SQLInc = "UPDATE DaTable SET DaColumn = DaColumn + 1 "
Const SQLDec = "UPDATE DaTable SET DaColumn = DaColumn - 1 "
 
Dim lstYourItem As ListItem
Dim strTemp As String
 
strTemp = ""
For Each lstYourItem In ListView1.ListItems
   If lstYourItem.Checked Then strTemp = strTemp & "," & lstYourItem.Tag
Next
strTemp = SQLInc & "WHERE DaPrimaryKey IN (" & Mid(strTemp, 2) & ") "  'Mid removes leading comma
 
connADO.Execute strTemp

Keep it simple

i tried your code but I am getting an error. object doesnt support this property or method

it doesnt like lstYourItem.

I don't understand what you wrote, could you explain.

For the tag property, Do you mean when I click on properties for the listview and assign a value in the TAG under coulumn headers.

I assigned column 3 UnitInStock the tag of 100.


what is lstYourItem? is that the same as my lvwItem //tried this too and got the same error.

What is DaColumn?

whats the DaPrimaryKey?

thanks for helping.

here is what I have and it works when I put a number by the ????? below. So If I put 1, it will work when the ID columns have a 1.

How do I get the tags ID that I saved so I can update stuff thats clicked.

strTemp = SQLInc & "WHERE ????? IN (" & Mid(strTemp, 2) & ") " 'Mid removes leading comma

all code below.

VB Code:

Private Sub getColumnHeaders()
 
'build the listviews column headers
Dim Header As ColumnHeader
 
'build header Name
Set Header = ListView1.ColumnHeaders.Add()
    Header.Text = "ProductID"
    Header.Width = ListView1.Width * 0.28
    ' width of the header is 29% of the total width of listview1.
    ' I choose 29%, not 30%, because with 29% the horizontal scrollbar will disappear.
    ' So in total I use 99% and not 100%
 
'build header Address
Set Header = ListView1.ColumnHeaders.Add()
    Header.Text = "ProductName"
    Header.Width = ListView1.Width * 0.4
    ' width of the header is 40% of the total width of listview1
    
'build header Telephone
Set Header = ListView1.ColumnHeaders.Add()
    Header.Text = "UnitInStock"
    Header.Width = ListView1.Width * 0.3
    ' width of the header is 30% of the total width of listview1
    
'we have to reset ItemIndex, so there is nothing selected in listview1
ItemIndex = 0
 
 
End Sub
 
'gets the records from the database
'set column header names in the listview properties to Match columns in DB
'To have numbers sorted normally, go to listviw property sorting, check sort, sortkey = 1
 
Private Sub addToListView()
'End Sub 'end function save
 
' We use the integer 'a', so we know in which row we are writing (listview)
    Dim a As Integer
    a = 1
 
 
Set rsinventory = helpinghandCon.Execute("SELECT * FROM inventory ORDER BY ProductID", , adCmdText)
    
    ' Load the data.
    Do While Not rsinventory.EOF
     '   Set list_item = ListView1.ListItems.Add(, , rsinventory!ProductID) 'column 0
      '  list_item.SubItems(a) = rsinventory!ProductName 'column 1
       ' list_item.SubItems(a) = rsinventory!UnitInStock 'column 2
 
 
 ListView1.ListItems.Add , , rsinventory!ProductID
        ListView1.ListItems(a).ListSubItems.Add , , rsinventory!ProductName
        ListView1.ListItems(a).ListSubItems.Add , , rsinventory!UnitInStock
        
 
 
 
 
' We must know which row contains the data from which row from the table People.
        ' Therefore we copy the ID from the table to the Tag of the row which contains
        ' the data from that row in the table. Sorry if this sounds complicated,
        ' but I'm not that good in teaching LOL :-)
        ListView1.ListItems(a).Tag = rsinventory!ProductID
 
 
 ' Increase 'a'
        a = a + 1
 
 
        ' Move to the next row in the recordset
        rsinventory.MoveNext
        
    ' Do the same stuff again, until we reached the End Of File
    Loop
 
    ' Close the recordset and connection.
    'rsinventory.Close
    'helpinghandCon.Close
End Sub
 
'when the submit button is clicked, all checked items are saved
Private Sub save()
 
Dim a As Integer
Dim id As Integer
 
a = 1
 
If ItemIndex <> 0 Then
 
 rsinventory.Index = "ProductID"
 
rsinventory.MoveFirst
 
rsinventory.Seek "=", ListView1.ListItems.item(ItemIndex).Tag
'we reset itemindex to 0, this means that nothing is selected in listview1
            ItemIndex = 0
            
            
Else
 
Const SQLInc = "UPDATE inventory SET UnitInStock = UnitInStock + 100 "
Const SQLDec = "UPDATE DaTable SET DaColumn = DaColumn - 1 "
 
Dim lstYourItem As ListItem
Dim strTemp As String
 
strTemp = ""
For Each lstYourItem In ListView1.ListItems
   If lstYourItem.Checked Then strTemp = strTemp & "," & lstYourItem.Tag
id = ListView1.ListItems(a).Tag
Next
strTemp = SQLInc & "WHERE [B]?????[/B] IN (" & Mid(strTemp, 2) & ") "  'Mid removes leading comma
 
helpinghandCon.Execute strTemp
End If
 
 
End Sub

Quote:

---

Originally Posted by ched35

i tried your code but I am getting an error. object doesnt support this property or method

it doesnt like lstYourItem.

I don't understand what you wrote, could you explain.

For the tag property, Do you mean when I click on properties for the listview and assign a value in the TAG under coulumn headers.

I assigned column 3 UnitInStock the tag of 100.


what is lstYourItem? is that the same as my lvwItem //tried this too and got the same error.

What is DaColumn?

whats the DaPrimaryKey?

thanks for helping.

---

I declared a listitem object and a string.

I then iterated 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.

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 your WHERE clause becomes WHERE ProductID IN (productID_CSV)

If the query didn't work then maybe ProductID is a string data type in the database? If so, each id should be placed within single quotes.

Since I had no idea of your table structure I used DaColumn and DaTable which is YourColumn and YourTable respectively.

And your implementation of cmdSave is confusing... you execute my code when there's nothing selected in the listview. Eh???

yeah, I was confused about my cmdsave too--LOL. I was trying to use code from a sample program.

You method now works perfectly. I had the ProductID set to text. changing to number fixed the problem.

Your explanation really helped me understand.

Thanks everyone for taking the time to help me.

would I be able to update another table with your way as well? pretty much, decrement the inventory(did this one already) and add 1 to each of the checked items to the customer equipment table

I wanted to update patientEquip table, which contains a CustID, Bed, Wheelchair, etc...the column names. under each bed, wheelchair, there will be a 1 or 0. 1 being assigned out.


I have form that the employee adds the customer, and then clicks add equipment to patient. I have the custID saved for the next form(frmAddEquiptoPAtient), where we were adding update inventory stuff.

so basically I want to update this table too. I forgot about that part sorry.

any ideas?

Hard to create the relevant query without knowing the structure of the tables involved (as well as the data types of their fields)... in addition to that, also include info regarding which control (eg. textbox, combobox) provides the data to which fields.

Also, will this be an UPDATE query, which updates an existing record, or an INSERT query which adds a record to the table Which do you need, pls explain more clearly.

Quote:

---

Originally Posted by leinad31

Hard to create the relevant query without knowing the structure of the tables involved (as well as the data types of their fields)... in addition to that, also include info regarding which control (eg. textbox, combobox) provides the data to which fields.

Also, will this be an UPDATE query, which updates an existing record, or an INSERT query which adds a record to the table Which do you need, pls explain more clearly.

---

I will attach the database so you can see it. Do you wanna see the program itself.

It should add the new custID, and then add the new equipment. Getting the info from a textbox from the previous form. All I really needed is the custID, so I saved it.


attached the DB. Its really basic for now. doesn't follow 2nf, 3nf

Are you sure you want to implement patientequip that way, each equipment as a column? I thought you wanted a method that scales even with additional equipment, hence the use of the listview.

what would be a better way?

I will attach my form so you can see what I mean. The App is ugly for now because I am remaking an old project that wasn't finished.

but basically the employee will click on add patient, and will be taken to the addpatient form. They will hit submit, and if they want to add equipment to the patient at that time. they will click on Add Equipment.

the patient info shows the patient being modified--it wont work cause I hard coded the values in. other wise it will work when you manually enter in stuff.

I am just learning DB design so any insights will be helpful.

I need to go, hopefully someone else can look at your source code... as to the table structure of patientequip, for now I suggest as minimum; 1) autonum pri-key, 2) custID foreign key, 3) ProductID foreign key 3) TransDate for historical inventory movement reports, 4) Units consumed field also for inventory purposes

So if a patient uses 5 equipments, then there will be five records with one record per equipment. Pairing custId-productID allows for many-to-many relationship; eg. relative to a custID you have many productID, and by shifting view relative to productID columns you get a list of patients that used the equipment. This structure also scales to changes in number of equipment (num of records in inventory).

Only downside is that this table will grow in size very fast.

Note I didn't normalize custID and TransDate to make explanation simpler... up to you if you will normalize this table.

ok, thanks!! No hurry, I have a month or so to finish up the basics.

ok, I think I did what you said. I wanted to keep that DB simple so non-technical people could look at it and do what ever. But your way still looks simple.

in msAccess, I didnt know what join type to choose.
I also wanted to keep an amount signed out column, just incase they wanted to signout more than 1.

well, when anyone gets a chance take a gander and let me know if I enterpreted correctly. And still need help with the code part.


thanks

In the listitems iteration, you will execute an INSERT query that will add a record into patientequip table for each checked listitem.

sSQL = "INSERT INTO patientequip (CustID, ProductID, AmountSignedOut) VALUES ("
sSQL = sSQL & txtCustID.Text & "," & lstYourItem.Tag & ", " lstYourItem.SubItem(YourIndex) & ") "

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?