Check Box Help

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Mickster
    New Member
    • May 2006
    • 27

    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.
  • wlc04
    New Member
    • May 2006
    • 70

    #2
    Checkbox

    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.
    Last edited by wlc04; May 3 '06, 04:39 PM. Reason: further explanation

    Comment

    • Mickster
      New Member
      • May 2006
      • 27

      #3
      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.

      Comment

      • Mickster
        New Member
        • May 2006
        • 27

        #4
        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"

        Comment

        • cweiss
          New Member
          • Apr 2006
          • 36

          #5
          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:
          Code:
          [U][B]Field 1[/B][/U]
          [B]Name:[/B] ErrorID
          [B]Type:[/B] Number
          [B]Example Data:[/B] 1 
          
          [U][B]Field 2[/B][/U]
          [B]Name:[/B] Error Description
          [B]Type:[/B] Text
          [B]Example Data:[/B] Medication ordered for wrong patient
          The main table errors table would contain all the errors for a particular "transactio n". The ErrorID would match the description in the lookup errors table. It'd be structured like this:
          Code:
          [U][B]Field 1[/B][/U]
          [B]Name:[/B] MainTablePrimaryKey
          [B]Type:[/B] (whatever type your primary key is in the transaction table)
          [B]Example Data:[/B] PLM13685
          
          [U][B]Field 2[/B][/U]
          [B]Name:[/B] ErrorID
          [B]Type:[/B] Number
          [B]Example Data:[/B] 1
          Basically, you may have multiple errors for a single "transactio n", 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 TransactionErro r 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:

          Code:
          Sub AddRecord()
          Dim db as DAO.Database
          Dim rs as DAO.Recordset
          
          Set db = CurrentDb()
          Set rs = db.OpenRecordset("TransactionErrors")
          
          With rs
            .Add
            .Fields("MainTablePrimaryKey") = txtVisitID 
            .Field("ErrorID") = chkError1
            .Update
          End With
          
          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 TransactionErro rs 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.
          Last edited by cweiss; May 3 '06, 05:41 PM.

          Comment

          • CaptainD
            New Member
            • Mar 2006
            • 135

            #6
            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 tblMedicationEr rors 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 tblMedicationEr rors.
            (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.


            Hope that helps

            Comment

            • Mickster
              New Member
              • May 2006
              • 27

              #7
              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 TransactionErro r 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

              Comment

              • wlc04
                New Member
                • May 2006
                • 70

                #8
                version

                What version of access are you running?

                Comment

                • cweiss
                  New Member
                  • Apr 2006
                  • 36

                  #9
                  Originally posted by Mickster
                  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:
                  Code:
                  TransactionID   ErrorID
                  ABC123    10
                  ABC123    20
                  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).

                  Comment

                  • Mickster
                    New Member
                    • May 2006
                    • 27

                    #10
                    Originally posted by wlc04
                    What version of access are you running?
                    I got Access 97 right now.

                    Comment

                    • wlc04
                      New Member
                      • May 2006
                      • 70

                      #11
                      Another Option

                      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.

                      Comment

                      • wlc04
                        New Member
                        • May 2006
                        • 70

                        #12
                        I've attached the db in vs. 97. two tables, one form - let me know if this helps you.

                        Comment

                        • Mickster
                          New Member
                          • May 2006
                          • 27

                          #13
                          Originally posted by wlc04
                          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

                          Comment

                          • wlc04
                            New Member
                            • May 2006
                            • 70

                            #14
                            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.

                            Comment

                            • Mickster
                              New Member
                              • May 2006
                              • 27

                              #15
                              Originally posted by cweiss
                              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

                              Comment

                              Working...