469,357 Members | 1,590 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,357 developers. It's quick & easy.

Check Box Help

Hi all,

I am currently working on a form that will allow a user to tick off certain medication errors.

A user can select up to five errors, there are around 15 listed.

I was wondering if Checkboxes woudl be the best solution for this or is their a better approach?

If checkboxes is the best approach coudl someone please tell me how to get the value that is stored in the label to appear in the table?

Example

"Medication ordered for wrong patient" is a label for a checkbox, how do i get that value stored in the table.
May 3 '06 #1
48 3860
wlc04
70
Create fields in your table for each of your options - format as yes/no. Put those on your form (they appear as checkboxes) and when the option is chosen it will update the data in your table.
May 3 '06 #2
Thanks,

I am new at access. But I am having difficulty understanding how I would link that Checkbox to a specific field in the table.
May 3 '06 #3
Is there a possible way where i can have all the medication errors located on a separate table and then have them populate a medication field on the main table. For example

Table 1 (MedErrors):

Ordered for wrong patient
Wrong drug administered
Incorrect Dose

Table 2 (Main Table)

Would have a field that woudl store all the MedErrors that are 'Checked off"
May 3 '06 #4
cweiss
36
The approach I would most likely take is to set up three tables:

1. Main table
2. Lookup table for medical errors
3. Main Table Errors table

The main table would be where all the info about a patient's visit is stored.

The lookup table would be structured like this:
Expand|Select|Wrap|Line Numbers
  1. Field 1
  2. Name: ErrorID
  3. Type: Number
  4. Example Data: 1 
  5.  
  6. Field 2
  7. Name: Error Description
  8. Type: Text
  9. Example Data: Medication ordered for wrong patient
The main table errors table would contain all the errors for a particular "transaction". The ErrorID would match the description in the lookup errors table. It'd be structured like this:
Expand|Select|Wrap|Line Numbers
  1. Field 1
  2. Name: MainTablePrimaryKey
  3. Type: (whatever type your primary key is in the transaction table)
  4. Example Data: PLM13685
  5.  
  6. Field 2
  7. Name: ErrorID
  8. Type: Number
  9. Example Data: 1
Basically, you may have multiple errors for a single "transaction", so you'll want to store the transaction identifier in another table along with the error code. That'll keep your main table smaller, and it'll be easier to query out info going forward.

As far as the form goes, set up your checkboxes and give them a value (1, 2, 3) that matches the ErrorID in the LookupErrors table, and make the caption of the label the same as the error description in your lookup errors table. Then in your TransactionError table just append the values of any checkboxes that are checked.

I use VBA when I'm updating like this though, specifically DAO. The basic code for this would be:

Expand|Select|Wrap|Line Numbers
  1. Sub AddRecord()
  2. Dim db as DAO.Database
  3. Dim rs as DAO.Recordset
  4.  
  5. Set db = CurrentDb()
  6. Set rs = db.OpenRecordset("TransactionErrors")
  7.  
  8. With rs
  9.   .Add
  10.   .Fields("MainTablePrimaryKey") = txtVisitID 
  11.   .Field("ErrorID") = chkError1
  12.   .Update
  13. End With
  14.  
  15. End Sub
This isn't exactly like the code you would use though, since you have multiple checkboxes you'll need to loop through them, get their values if they're checked, and add them all to the TransactionErrors table.

However, wlc04's approach is a LOT less complicated (I tend to overcomplicate things :)), to use his/her method, just add the extra fields to the main table, and then set the form's datasource to the main table. After that you can drop the error fields onto your form and you don't have to worry about code or whatnot, every time you add a new record they'll update. The table will just get a little messy depending on how many error codes you have and how many you think you'll add in the future.
May 3 '06 #5
CaptainD
135 100+
You will want to set up your tables as "relational" so each person can have their list of medications and then a table for listing errors in made in/with their medications.
tblCustomers, tblMedications with a foreign key to tblCustomers and a tblMedicationErrors with a foreign key to either medications and/or tblCustomers depending on how you want to create your form(s) or what works best for you.

You can create a list of errors from a support table to populate the listbox and for each error place the information for that customer in the tblMedicationErrors.
(You need to get an understanding of "Normal Form" for relational databases. Most people (as I understand it) shoot for 3rd Normal form as a minimum)

This might help get you started.
http://databases.about.com/od/specif...malization.htm

Hope that helps
May 3 '06 #6
Thanks guys, this is very helpful.

However at my beginner level I'm having trouble with understanding some of your suggestions.

1. "As far as the form goes, set up your checkboxes and give them a value (1, 2, 3) that matches the ErrorID in the LookupErrors table, and make the caption of the label the same as the error description in your lookup errors table. Then in your TransactionError table just append the values of any checkboxes that are checked."

How do I do this, what field in teh properties do I set the value to (1,2,3...)

2. Main table errors table

You suggest two fields the Maintable ID and the Error ID, woudl there be a primary key?? If so would I create a new field and just set the type to Autonumber

3. VBA Code

Would this code go in the AfterUpdate field in the properties section?

Thanks again, you help is much appreciated
May 3 '06 #7
wlc04
70
What version of access are you running?
May 3 '06 #8
cweiss
36
How do I do this, what field in teh properties do I set the value to (1,2,3...)
Go to Properties->Data->Default Value, and set it to whatever value you want it to match back to in the lookup errors table. You can then retrieve this value through VBA by referencing the DefaultValue property of the checkbox (i.e. <CheckboxName>.DefaultValue).

2. Main table errors table

You suggest two fields the Maintable ID and the Error ID, woudl there be a primary key?? If so would I create a new field and just set the type to Autonumber
In your errors table, you'll need two fields. One field will be the primary key of the Main table, the other field will be the primary key of the Lookup Errors table. So for example, if a patient comes in, you would log their visit and assign it an ID of ABC123, they may have had three errors specific to that visit, and these errors have an error id in the lookup errors table of 10, 20, 30. So in your Main Table Errors table, you'll have three entries:
Expand|Select|Wrap|Line Numbers
  1. TransactionID   ErrorID
  2. ABC123    10
  3. ABC123    20
  4. ABC123    30
As far as the primary key goes, I would make the TransactionID and ErrorID primary keys. No need for an additional field, since I wouldn't think you would want duplicate the errors for the same transaction.

3. VBA Code

Would this code go in the AfterUpdate field in the properties section
I would actually set up a button for the user to click that executes any update commands, but bear in mind this isn't code you can copy/paste, it gives you an idea of how you would use DAO to add a record to your table, but you'll need to set up a way to iterate through each checkbox, see if they're checked, and add their defaultvalue (which should match the primary key of one of the errors in the lookup errors table).
May 3 '06 #9
What version of access are you running?
I got Access 97 right now.
May 3 '06 #10
wlc04
70
You could also create combo boxes on your form, which gives the user a list of options to choose from. Use the Errors table as the row source property for them. I've thrown together a very simple db using this method if you would like to see it. I run Access 2003, but can convert it down if you are not. If you would like me to send this to you, send me an email (wlc04@tampabay.rr.com) - I'm glad to help.
May 3 '06 #11
wlc04
70
I've attached the db in vs. 97. two tables, one form - let me know if this helps you.
May 3 '06 #12
I've attached the db in vs. 97. two tables, one form - let me know if this helps you.
Thanks for your help. Could you possibly post this zip file again. I tried to extract it and it said it contained no files...

Thanks
May 4 '06 #13
wlc04
70
I've tried to recreate the zip (winzip and winrar) and upload again, but the file's not showing up once it's posted. Trying to figure out why.
May 4 '06 #14
Go to Properties->Data->Default Value, and set it to whatever value you want it to match back to in the lookup errors table. You can then retrieve this value through VBA by referencing the DefaultValue property of the checkbox (i.e. <CheckboxName>.DefaultValue).
Could you please be more specific on how to this. I've never used VB before, and am sort of lost on how to this. I've created the tables but am just having difficulty linking the checkbox value to the tables.

Thanks
May 4 '06 #15
wlc04
70
You have created a form with the table as the source, correct?
May 4 '06 #16
cweiss
36
This is the code snippet from before, slightly modified and heavily commented (might be easier to read this if you paste it into a module):

Expand|Select|Wrap|Line Numbers
  1. Sub AddRecord()
  2.  
  3. 'Dimension your variables, db is of type DAO.Database
  4. 'rs is of type DAO.Recordset
  5. Dim db as DAO.Database
  6. Dim rs as DAO.Recordset
  7.  
  8. 'Set the db variable to the database you are currently
  9. 'working in, and set the rs variable to the table
  10. 'you want to work with, in this case, the table's name
  11. 'is TransactionErrors
  12. Set db = CurrentDb()
  13. Set rs = db.OpenRecordset("TransactionErrors")
  14.  
  15. 'The With statement is basically saying
  16. 'I want to reference this variable, instead
  17. 'of typing out rs.Add, while inside the
  18. 'with block you can reference it by saying
  19. ' .Add
  20. With rs
  21.   'You must tell the recordset you want to add a record
  22.   'if you wanted to modify a record you would type
  23.   ' .Edit instead
  24.   .Add
  25.  
  26.   'The value of the field "MainTablePrimaryKey" should be
  27.   'set to the value contained in the Textbox txtVisitID
  28.   .Fields("MainTablePrimaryKey") = txtVisitID 
  29.  
  30.   'The value of the field "ErrorID" should be set to the
  31.   'value contained in the checkbox chkError's DefaultValue
  32.   'property
  33.   .Field("ErrorID") = chkError1.DefaultValue
  34.  
  35.   'Update the recordset with the data you just added
  36.   .Update
  37. End With
  38.  
  39. End Sub
You also need to set a reference (Tools->References) to the Microsoft DAO 3.6 Object Library

This is how you would go about adding the record to the table, if you have no experience with VBA though, this might not be the best solution to start out with...
May 4 '06 #17
wlc04
70
Rather than using the VBA - you could just set the source through the properties - MUCH EASIER.

Let me start with this - Have you ever designed a form?

Once I know this I will know how much detail to go into to help you.
May 4 '06 #18
You have created a form with the table as the source, correct?
Yes, i did that.
May 4 '06 #19
wlc04
70
Do you have the fields on the form yet? If so, right click and look at the properties - each textbox/checkbox... will show you which field it is referencing. Once they are on there and referenced to the correct field you should be set. This will work if you are using one table.

Did you decide to use more than one table? If so, you'll want a query to join the data in the two and then use that query as the source for your form.
May 4 '06 #20
Rather than using the VBA - you could just set the source through the properties - MUCH EASIER.

Let me start with this - Have you ever designed a form?

Once I know this I will know how much detail to go into to help you.
No, this is my first time. What I have read on the net is that Check boxes can only hold yes/no values. How can I change this so that it represents a value such as (1,2,3 ....) which will correspond to a medication error on a table.

Thanks for all your help, and your patience.
May 4 '06 #21
wlc04
70
You can't change the value of the checkbox. You'll have to adjust your thinking.

I wish I could get the .mdb file I created to you - I think seeing it would be better for you, but we'll work around that.
May 4 '06 #22
Do you have the fields on the form yet? If so, right click and look at the properties - each textbox/checkbox... will show you which field it is referencing. Once they are on there and referenced to the correct field you should be set. This will work if you are using one table.

Did you decide to use more than one table? If so, you'll want a query to join the data in the two and then use that query as the source for your form.

Yes, ok i'm getting on track. I am using multiple tables so I have the form referencing the query. However, what I"m confused about is that since the check box represents a number in a field. How can I match each check box to a specific number in that field??...If that makes any sense.
May 4 '06 #23
wlc04
70
So you created a checkbox for each available error, correct?

Basically, you'll just reference each checkbox with an error by using the name of the error - don't think of them with values (1,2,3). You have a checkbox on the form which has it's source as Error1 from your query - when checkbox is ticked off then that value will update your table. Make sense? To be able to recognize which error is which on your form - just change the caption on it's corresponding label.
May 4 '06 #24
So you created a checkbox for each available error, correct?

Basically, you'll just reference each checkbox with an error by using the name of the error - don't think of them with values (1,2,3). You have a checkbox on the form which has it's source as Error1 from your query - when checkbox is ticked off then that value will update your table. Make sense? To be able to recognize which error is which on your form - just change the caption on it's corresponding label.
Ok things are looking a lot more clearer. However, where would I would I store the name of the error in the Checkbox properties, so that value can be referenced on the table.

Thanks,
May 4 '06 #25
wlc04
70
You might want to name the fields in the table so they can be recognizable.

On the form itself you can set the name property to anything you want. CkErr1 or CkWrgMed. Then if you create anything in VBA you will recognize it 1) it's a checkbox - because of the CK prefix and 2) which error it's referencing, either by number or desc.

If you do change the names, make sure you don't change the control source - this is how it updates your table.
May 4 '06 #26
You might want to name the fields in the table so they can be recognizable.

On the form itself you can set the name property to anything you want. CkErr1 or CkWrgMed. Then if you create anything in VBA you will recognize it 1) it's a checkbox - because of the CK prefix and 2) which error it's referencing, either by number or desc.

If you do change the names, make sure you don't change the control source - this is how it updates your table.
Ok this is where I am at right now.

I've created a mini DB that reflects what I am doing. So if you dont mind looking at it giving me some suggestions on whether I am on the right track or not it woudl be much appreciated.
Attached Files
File Type: zip CheckBox.zip (20.5 KB, 106 views)
May 4 '06 #27
wlc04
70
I'm having the same problem you had opening the zip file. Tells me there are no files in the archive. If you like, you can email it to me, but you'll have to change the extension because Outlook won't let an mdb file through. wlc04@tampabay.rr.com.
May 4 '06 #28
I'm having the same problem you had opening the zip file. Tells me there are no files in the archive. If you like, you can email it to me, but you'll have to change the extension because Outlook won't let an mdb file through. wlc04@tampabay.rr.com.
Ok thanks, Wendy. I sent you an email from my gmail account.
May 4 '06 #29
Ok thanks, Wendy. I sent you an email from my gmail account.
Hi Wendy,

I got a reply back saying that email address was invalid. Could you please confirm your email again please.

Thanks
May 4 '06 #30
wlc04
70
You can send it to either:

wlc04@tampabay.rr.com or

wcarp1@gmail.com
May 4 '06 #31
You can send it to either:

wlc04@tampabay.rr.com or

wcarp1@gmail.com
Ok I sent it to you gmail account.
May 4 '06 #32
wlc04
70
I made a couple of observations regarding the number of checkboxes. Also, I attached the db I told you I created for you - hope this helps.
May 4 '06 #33
I made a couple of observations regarding the number of checkboxes. Also, I attached the db I told you I created for you - hope this helps.
Thanks wendy. However I have about 39 errors (checkboxes) that will be on my form. So do you think I should have 39 fields each storing a different error?
May 4 '06 #34
Thanks wendy. However I have about 39 errors (checkboxes) that will be on my form. So do you think I should have 39 fields each storing a different error?
I also should note that I eventually want to run a report that will list the number of occurences of each error.
May 4 '06 #35
wlc04
70
With so many error possibilities you might want to go with the combo box option - then each one is a drop down box listing all possible errors.

As for the report - that should be fairly simple.

Create a query from the data table that will sum each error and then that query will be the source for your report.
May 4 '06 #36
With so many error possibilities you might want to go with the combo box option - then each one is a drop down box listing all possible errors.

As for the report - that should be fairly simple.

Create a query from the data table that will sum each error and then that query will be the source for your report.

Ok great, I think I will go with the combo box idea. Since each record could only have a Max 5 errors. I will just set up 5 combo boxes.

Thanks again for all your help, it is much appreciated.
May 4 '06 #37
wlc04
70
No problem. :)

If you run into any further problems, don't hesistate to ask.
May 4 '06 #38
No problem. :)

If you run into any further problems, don't hesistate to ask.
Sorry wendy your not off the hook yet :) I'm sure you're getting sick of me but I have one last question.

So my tables look like this now

Main Table
Patient ID
Patient Name
Addy so on.....

tbleMedError
PatientID
Error1ID (each of the following is populated by it's own combobox)
Error2ID
Error3ID

Med Error
ErrorID
MedError (description of the error)

So how can I get error1ID, Error2ID, Error3ID to link to ErrorID (in the MedError table). Is this possible. I want to reference the errors by the errorID not by the actual name of the error.

Thanks
May 4 '06 #39
wlc04
70
Am I seeing 3 tables?
May 5 '06 #40
wlc04
70
Mickey,

I've tweaked the db I sent you earlier. I've added a third table which will hold the patid and the 5 errors allowed. When selecting the format for each of these fields I chose to use the lookup wizard and link them to the other two tables - while doing this I chose to show both the ID and Desc of each (not everyone will remember Id no). Also, I selected to hold the Patid and ErrID no.'s in the fields rather than their descriptions, as you wanted. I created a form using this table as it's source.

Hope this helps.
May 5 '06 #41
cweiss
36
Just a suggestion, but however you decide to get your values in there, I would really reconsider the way you have tblMedError set up. Instead of having one field for each error, your tblMedErrors table should have only two fields, one field for the PatientID and one field for the ErrorID. These two fields should serve as your primary key--if there is no possibility of having the same error occur twice for a patient, otherwise something else will have to serve as the primary key.

There are a lot of reasons for this, but the main reasons are that it can become very difficult to maintain (what if at some point you have 50 different error codes?), and it's also a lot harder to write queries against a table structured like this

For example, if you did have 50 error codes, and you wanted to return the patients that had an error code between 20-40, here's the difference in your SQL statements:

Recommended Structure
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     PatientID
  3. FROM 
  4.     tblMedErrors
  5. WHERE 
  6.     ErrorID BETWEEN 20 and 40
  7.  
  8.  
Current Structure
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     PatientID
  3. FROM 
  4.     tblMedErrors
  5. WHERE 
  6.     Error20ID = 1
  7.     OR Error21ID = 1
  8.     OR Error22ID = 1
  9.     OR Error23ID = 1
  10.     OR Error24ID = 1
  11.     OR Error25ID = 1
  12.     OR Error26ID = 1
  13.     OR Error27ID = 1
  14.     OR Error28ID = 1
  15.     OR Error29ID = 1
  16.     OR Error30ID = 1
  17.     OR Error31ID = 1
  18.     OR Error32ID = 1
  19.     OR Error33ID = 1
  20.     OR Error34ID = 1
  21.     OR Error35ID = 1
  22.     OR Error36ID = 1
  23.     OR Error37ID = 1
  24.     OR Error38ID = 1
  25.     OR Error39ID = 1
  26.     OR Error40ID = 1
  27.  
  28.  
Or maybe you want to get a summary of the errors, and see how many of each type occurs:

Recommended Structure
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     ErrorID, Count(ErrorID) as ErrorCount
  3. FROM 
  4.     tblMedErrors
  5. GROUP BY 
  6.     ErrorID
  7.  
  8.  
Current Structure
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     SUM(Error1ID) as Error1IDCount
  3.     SUM(Error2ID) as Error2IDCount
  4.     SUM(Error3ID) as Error3IDCount
  5.     SUM(Error4ID) as Error4IDCount
  6.     SUM(Error5ID) as Error5IDCount
  7.     SUM(Error6ID) as Error6IDCount
  8.     ...
  9.     SUM(Error50ID) as Error50IDCount
  10. FROM 
  11.     tblMedErrors
  12.  
  13.  
These examples are a little exagerated, but you get the idea. Having multiple fields will make it easier to set up your form initially, but long term it can be very cumbersome.

Of course, it may not matter depending on your situation, but it's just a thought.
May 5 '06 #42
wlc04
70
Mickey,

cweiss has a point. Let me think about it for a bit and I'll get back to you.

Wendy
May 5 '06 #43
cweiss
36
I think the best approach, which avoids writing code, would be to just create a form bound to your main table, then create a subform bound to the tblMedErrors table and linked back to your main form (you can do all this through the form wizard actually).

As long as you've linked the error lookup table to the tblMedErrors table, you'll get the drop-down functionality you're looking for.

This may not make sense to you Mickey, but Wendy knows what I'm talking about.
May 5 '06 #44
wlc04
70
Mickey,


I made a change which shows the recommendation of cweiss. This should work for you. I sent it to your gmail account.

Wendy
May 5 '06 #45
Mickey,


I made a change which shows the recommendation of cweiss. This should work for you. I sent it to your gmail account.

Wendy
Thanks for you help Cweiss and wendy. However, Wendy the last email you sent me has an error in the query so i'm having difficulty understanding how to link the tables. Could you please take a look at it again.

thanks
May 5 '06 #46
Just a suggestion, but however you decide to get your values in there, I would really reconsider the way you have tblMedError set up. Instead of having one field for each error, your tblMedErrors table should have only two fields, one field for the PatientID and one field for the ErrorID. These two fields should serve as your primary key--if there is no possibility of having the same error occur twice for a patient, otherwise something else will have to serve as the primary key.

There are a lot of reasons for this, but the main reasons are that it can become very difficult to maintain (what if at some point you have 50 different error codes?), and it's also a lot harder to write queries against a table structured like this

For example, if you did have 50 error codes, and you wanted to return the patients that had an error code between 20-40, here's the difference in your SQL statements:

Recommended Structure
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     PatientID
  3. FROM 
  4.     tblMedErrors
  5. WHERE 
  6.     ErrorID BETWEEN 20 and 40
  7.  
  8.  
Current Structure
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     PatientID
  3. FROM 
  4.     tblMedErrors
  5. WHERE 
  6.     Error20ID = 1
  7.     OR Error21ID = 1
  8.     OR Error22ID = 1
  9.     OR Error23ID = 1
  10.     OR Error24ID = 1
  11.     OR Error25ID = 1
  12.     OR Error26ID = 1
  13.     OR Error27ID = 1
  14.     OR Error28ID = 1
  15.     OR Error29ID = 1
  16.     OR Error30ID = 1
  17.     OR Error31ID = 1
  18.     OR Error32ID = 1
  19.     OR Error33ID = 1
  20.     OR Error34ID = 1
  21.     OR Error35ID = 1
  22.     OR Error36ID = 1
  23.     OR Error37ID = 1
  24.     OR Error38ID = 1
  25.     OR Error39ID = 1
  26.     OR Error40ID = 1
  27.  
  28.  
Or maybe you want to get a summary of the errors, and see how many of each type occurs:

Recommended Structure
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     ErrorID, Count(ErrorID) as ErrorCount
  3. FROM 
  4.     tblMedErrors
  5. GROUP BY 
  6.     ErrorID
  7.  
  8.  
Current Structure
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     SUM(Error1ID) as Error1IDCount
  3.     SUM(Error2ID) as Error2IDCount
  4.     SUM(Error3ID) as Error3IDCount
  5.     SUM(Error4ID) as Error4IDCount
  6.     SUM(Error5ID) as Error5IDCount
  7.     SUM(Error6ID) as Error6IDCount
  8.     ...
  9.     SUM(Error50ID) as Error50IDCount
  10. FROM 
  11.     tblMedErrors
  12.  
  13.  
These examples are a little exagerated, but you get the idea. Having multiple fields will make it easier to set up your form initially, but long term it can be very cumbersome.

Of course, it may not matter depending on your situation, but it's just a thought.
Thanks Cweiss, you actually predicted the future. I will be creating reports that reflect the queries above. So I will stick with the one Error field.
May 5 '06 #47
wlc04
70
Actually - disregard the query, I should have deleted it. You should have 3 tables, 1 form. I believe I set it to show the form when you startup.
May 5 '06 #48
Actually - disregard the query, I should have deleted it. You should have 3 tables, 1 form. I believe I set it to show the form when you startup.
No worries, I got it to finally work. Thank you both for all your help. I will defintely have to recommend this forum to my friends. I wouldnt want you two to get bored.
May 5 '06 #49

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by scprosportsman | last post: by
5 posts views Thread by mabond | last post: by
13 posts views Thread by wireshark | last post: by
3 posts views Thread by John Wright | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.