-
[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
-
Re: Is There a batter way to implement Checkboxes?
Use a listview with its checkboxes enabled
-
Re: Is There a batter way to implement Checkboxes?
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
-
Re: Is There a batter way to implement Checkboxes?
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:
-
Re: Is There a batter way to implement Checkboxes?
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 +
-
Re: Is There a batter way to implement Checkboxes?
Quote:
Originally Posted by leinad31
Use a listview with its checkboxes enabled
hmmm, whats the listview? can you explain
thanks,
ched
-
Re: Is There a batter way to implement Checkboxes?
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...
-
Re: Is There a batter way to implement Checkboxes?
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.
-
Re: Is There a batter way to implement Checkboxes?
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
-
Re: Is There a batter way to implement Checkboxes?
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,
-
Re: Is There a batter way to implement Checkboxes?
-
Re: Is There a batter way to implement Checkboxes?
Please, don't bump after ONLY 2 hours... give people some time... preferably, don't bump at all.
-
Re: Is There a batter way to implement Checkboxes?
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.
-
Re: Is There a batter way to implement Checkboxes?
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
-
Re: Is There a batter way to implement Checkboxes?
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?
-
Re: Is There a batter way to implement Checkboxes?
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
-
Re: Is There a batter way to implement Checkboxes?
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?
-
1 Attachment(s)
Re: Is There a batter way to implement Checkboxes?
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
-
Re: Is There a batter way to implement Checkboxes?
Check under...
... routine in AddToList() function.
-
Re: Is There a batter way to implement Checkboxes?
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.
-
Re: Is There a batter way to implement Checkboxes?
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.
-
Re: Is There a batter way to implement Checkboxes?
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.
-
Re: Is There a batter way to implement Checkboxes?
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
-
Re: Is There a batter way to implement Checkboxes?
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.
-
Re: Is There a batter way to implement Checkboxes?
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
-
Re: Is There a batter way to implement Checkboxes?
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)
-
Re: Is There a batter way to implement Checkboxes?
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
-
Re: Is There a batter way to implement Checkboxes?
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.
-
Re: Is There a batter way to implement Checkboxes?
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
-
Re: Is There a batter way to implement Checkboxes?
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???
-
Re: Is There a batter way to implement Checkboxes?
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.
-
Re: Is There a batter way to implement Checkboxes?
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?
-
Re: Is There a batter way to implement Checkboxes?
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.
-
1 Attachment(s)
Re: Is There a batter way to implement Checkboxes?
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
-
Re: Is There a batter way to implement Checkboxes?
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.
-
1 Attachment(s)
Re: Is There a batter way to implement Checkboxes?
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.
-
Re: Is There a batter way to implement Checkboxes?
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.
-
Re: Is There a batter way to implement Checkboxes?
ok, thanks!! No hurry, I have a month or so to finish up the basics.
-
1 Attachment(s)
Re: Is There a batter way to implement Checkboxes?
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
-
Re: Is There a batter way to implement Checkboxes?
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) & ") "
-
Re: 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?
-
Re: Is There a batter way to implement Checkboxes?
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.
-
Re: Is There a batter way to implement Checkboxes?
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
-
Re: Is There a batter way to implement Checkboxes?
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
-
Re: Is There a batter way to implement Checkboxes?
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.
-
Re: Is There a batter way to implement Checkboxes?
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.
-
Re: Is There a batter way to implement Checkboxes?
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.
-
Re: Is There a batter way to implement Checkboxes?
The textboxes in frmAddEuiqpPatient are not getting values... so the insert query errors
-
Re: Is There a batter way to implement Checkboxes?
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.
-
Re: Is There a batter way to implement Checkboxes?
am I allowed to repost, with a diff title since the topic is different?