none
Form Control Updates RRS feed

  • Question

  • Experts -

    How do I create a form control that will show the value in the underlying table/field as well as a static list of choices in the event the user wants to change this value?

    Thank you!


    Bonediggler

    Thursday, May 12, 2016 4:14 PM

Answers

  • You first need a table of all user initials, Users say, with just the one column (field), UserInitials say, designated as the primary key.  Then in the claims form add a combo box with the user initials column as its ControlSource property and a RowSource property like this:

    SELECT [UserInitials] FROM [Users] ORDER BY [UserInitials];

    The user can then select the relevant initials from the drop down list and this will be stored in the column in the table for the current record.  You can of course add additional columns such as the users FirstName, LastName etc to record other information about the users.  This was done in my organisation, but the network administrators did find they had to cater for two people with the same initials occasionally, e.g. we had two people with the initials CME, so one was recorded in the table as just CE; much to her annoyance!

    Ken Sheridan, Stafford, England

    • Marked as answer by Bonediggler Friday, May 13, 2016 1:30 PM
    Thursday, May 12, 2016 8:05 PM
  • The form's RecordSource should be the table or a query on the table, and the control should simply be bound to the relevant column.  You should be able to select the column from the drop down list in the ControlSource property of the combo box in its properties sheet.  If the column is named User ID then that should be selected as the ControlSource property; there is no need to qualify it with the table name.  Note that the foreign key column to which the control is bound must be of the same data type  as the column being returned from the Users table by the combo box's RowSource.  If the user's initials are being stored in the column then the data type will be Short Text in current versions of Access, or Text in older versions.

    Ken Sheridan, Stafford, England

    Thursday, May 12, 2016 10:16 PM

All replies

  • At first sight it sounds like you are referring to a simple bound combo box whose RowSource property returns a list of values from a referenced table.  Can you explain in more detail exactly what you have in mind?

    Ken Sheridan, Stafford, England

    Thursday, May 12, 2016 5:08 PM
  • Hi Ken - that's what I thought but wanted to check  first.

    Basically a user will be interacting with medical claims data via a form.  The record source for the form is a table that contains (among other things) claim # and user initials.  As they work on a claim they will need to input their user initials in the control mentioned previously so we can track who is doing what. 

    In this control I need to 1) restrict which values can be entered (the list box part) 2) update the table when a value is selected and 3) display any value that has already been input.

    Hope that makes sense.  Let me know if you need more details.


    Bonediggler

    Thursday, May 12, 2016 5:20 PM
  • You first need a table of all user initials, Users say, with just the one column (field), UserInitials say, designated as the primary key.  Then in the claims form add a combo box with the user initials column as its ControlSource property and a RowSource property like this:

    SELECT [UserInitials] FROM [Users] ORDER BY [UserInitials];

    The user can then select the relevant initials from the drop down list and this will be stored in the column in the table for the current record.  You can of course add additional columns such as the users FirstName, LastName etc to record other information about the users.  This was done in my organisation, but the network administrators did find they had to cater for two people with the same initials occasionally, e.g. we had two people with the initials CME, so one was recorded in the table as just CE; much to her annoyance!

    Ken Sheridan, Stafford, England

    • Marked as answer by Bonediggler Friday, May 13, 2016 1:30 PM
    Thursday, May 12, 2016 8:05 PM
  • Thanks again!

    I'm wondering though, how this is going to update the value in the claims table?

    The drop down part is working, but when I try to choose a value this error is returned: "Control can't be edited; it's bound to unknown field [t_Users]![User_ID]."


    Bonediggler

    Thursday, May 12, 2016 8:22 PM
  • The form's RecordSource should be the table or a query on the table, and the control should simply be bound to the relevant column.  You should be able to select the column from the drop down list in the ControlSource property of the combo box in its properties sheet.  If the column is named User ID then that should be selected as the ControlSource property; there is no need to qualify it with the table name.  Note that the foreign key column to which the control is bound must be of the same data type  as the column being returned from the Users table by the combo box's RowSource.  If the user's initials are being stored in the column then the data type will be Short Text in current versions of Access, or Text in older versions.

    Ken Sheridan, Stafford, England

    Thursday, May 12, 2016 10:16 PM
  • Using a user's initials to identify a person is an example of a very poorly thought out design. It's obvious and even extremely likely that more than one person will share the same initials and then the entire design falls down. Reading this type of thing makes me wonder what other shortcomings this system may have. I work with a database that contains millions of people records and we even have many people sharing their first name, last name and date of birth. Of course, we use a unique id to identify people.
    Thursday, May 12, 2016 10:24 PM
  • It sounds like you need an INSERT INTO solution.


    Option 1

    As Remou said, you have no quotes around your text. I've added some single quotes around each of your fields and added some line breaks for easier reading. Does that work?

    Private Sub cmd_go_Click()
        Dim insertstring As String
        insertstring = "INSERT INTO KWTable (KW, Source, Code) VALUES ('" & _
                        text_key.Value & "','" & _
                        combo_source.Value & "','" & _
                        text_code & "');"
        DoCmd.RunSQL insertstring
    
    End Sub



    Option 2

    I think this will handle storing single and double quotes in your table:

    Private Sub cmd_go_Click()
    Dim rst As recordset
    Set rst = CurrentDb.OpenRecordset("KWTable ")
    With rst
         .addnew
         .fields("KW")=text_key.Value
         .fields("Source")=combo_source.Value
         .fields("Code")=text_code
         .update
     End with
     End sub
    If you have the form bound to the table, there is no reason to run SQL. The data will just be inserted into the bound fields



    MY BOOK

    Friday, May 13, 2016 11:07 AM
  • Sorry - should have been more clear on this point.  There are only a handful of users, and each user's "initials" are not their actual initials but a value based on their initials that is assigned by a different, enterprise system.

    So the values being used in this case are unique.


    Bonediggler

    Friday, May 13, 2016 1:15 PM