none
Text box value not inputing correcty. I blanks out the form text box instead of putting a number into it. RRS feed

  • Question

  • I have a simple little 3 field table I have been trying to get field 3 to input into a form text box after the two combo boxes have inputs. I have the following information in a table in Access 2010. I have made two combo boxes that list the AssyNumber and the Side. When the two have been chosen I have an event for onupdate in the side combo box data property. The code is "Me.DiscComp.Value = Me.AssyNumber.Column(3)". I have tried on click and on change and nothing seems to work to put the value in automatically for the DiscComp text box. I would like the AssyNumber to be chosen along with the side and then the text box of DiscComp to put the number in column 3 automatically. Can any one make any suggestions? I put a number in the DiscComp text box on the form in form view and as soon as I choose a side on the side combo box the form text box for DiscComp goes blank. It is clearing the box and not putting the value in for the third column when the 2 conditions are met. I am fairly new to Access and have read some books, but need help.

    ID AssyNumber Side DiscComp
    1 04884-7 TOP
    2 00010-2 TOP 3
    3 00020-2 BOTTOM 1
    4 00020-2 TOP 1
    5 00028-1 BOTTOM 2
    6 00031-2 TOP 4
    7 00031-3 TOP 3
    8 00031-3 BOTTOM 19
    9 00033-12 BOTTOM 16
    10 00033-12 TOP 4
    11 00033-2 BOTTOM 16
    12 00033-2 TOP 4
    13 00041-1 BOTTOM 3
    14 00051-1 TOP 4
    15 00053-1 TOP 2
    16 00063-1 TOP 26
    17 00065-1 TOP 98
    18 02454-10 BOTTOM 1
    19 02454-8 BOTTOM 1
    20 02454-9 BOTTOM 1
    21 02578-1 TOP 1
    22 03148-4 TOP 3
    23 03149-10 BOTTOM 15
    24 03149-11 BOTTOM 15
    25 03149-12 BOTTOM 15
    26 03149-5 BOTTOM 15
    27 03371-1 TOP 1
    28 03373-3 TOP 3
    29 03373-4 TOP 3
    30 03377-1 TOP 2
    31 03568-12 TOP 35
    32 03568-12 BOTTOM 33
    33 03572-6 TOP 1
    34 03572-6 BOTTOM 18
    35 03706-5 TOP 2
    36 03706-5 BOTTOM 19
    37 03926-4 BOTTOM 14
    38 03926-5 BOTTOM 14
    39 03959-2 TOP 3
    40 03982-8 BOTTOM 2
    41 04141-7 BOTTOM 17
    42 04150-9 BOTTOM 31
    43 04229-5 BOTTOM 3
    44 04229-5 TOP 2
    45 04246-2 BOTTOM 1
    46 04431-2 TOP 2
    47 04442-2 BOTTOM 7
    48 04443-2 BOTTOM 7
    49 04445-2 BOTTOM 4
    50 04447-2 BOTTOM 4
    51 04449-7 TOP 6

    Thursday, March 17, 2016 11:04 PM

Answers

  • The table which you described in your original post is modelling a many-to-many relationship type between the entity type Assemblies and Sides.  In that table the combination of AssyNumber and Side constitute a candidate key, i.e. the values of these two columns, in combination, must be distinct in the table.  However, you have added a surrogate key, ID.  DisComp is a non-key attribute of the relationship type.

    The table into which you are inserting rows in your form is a referencing table in a relationship with the above table.  As you appear to have AssyNumber and Side columns in this table, rather than referencing the ID primary key of the referenced table, you are referencing the composite candidate key by maens of a corresponding composite foreign key.  This is perfectly valid in terms of the theoretical model, but if you choose to reference the first table in this way it would be better to dispense with the ID surrogate key column, and make the AssyNumber and Side columns the composite primary key of the first table.

    If you are correct in thinking that the DiscComp column in the referencing table is functionally dependant solely on the key of the referencing table, then to assign a value into it you would need code in the AfterUpdate event procedure's of the AssyNumber and Side combo boxes which looks up the DisComp value in the referenced table.  You cannot assume with complete confidence that the AssyNumber  will be selected before the Side, even though this might be the normal way it is done, which is why you need code in both control's event procedures.  The code would be like this:

        Dim strCriteria As String

        strCriteria = "AssyNumber  = """ & Nz(Me.AssyNumber,"")  & """ And " & _
            "Side = """ & Nz(Me.Side,"") & """"

        Me.DiscComp = DLookup("DiscComp", "YourReferencedTable",strCriteria)

    If either the AssyNumber or Side control is Null when a selection is made in the other control then the DLookup function call will return a Null, so this will be assigned to the DiscComp control.  Only when a value has been selected in both the AssyNumber   and Side controls will a row be found in the referenced table, and the value from the DiscComp column in that table assigned to the DiscComp control in your form.

    It must be emphasised that a value should be assigned to a DiscComp column in the referencing table only id the column is functionally dependant solely on the key of the referencing table, i.e. the value being assigned is a default one which can if necessary legitimately be edited so that it differs from the value looked up from the referencing table, or in the event of the DisComp value in the referenced row in the referenced table changing, the value in the referencing table must remain static as that when it was first inserted.  If this is not the case then there should be no DiscComp column in the referencing table as this would introduce redundancy and the table would be open to the risk of update anomalies.  In this case the value can be shown in an unbound text box in the form, with a ControlSource property of:

    =DLookup("DiscComp", "YourReferencedTable","AssyNumber  = """ & Nz([AssyNumber],"")  & """ And Side = """ & Nz([Side],"") & """")

    Ken Sheridan, Stafford, England

    Monday, March 21, 2016 11:38 PM

All replies

  • The Column property is zero-based, so if you want to insert the value from the third column of the Side combo box's RowSource the code in the Side combo box's AfterUpdate event procedure would be:

        Me.DiscComp = Me.AssyNumber.Column(2)

    However, this assumes that a value has been selected in the AssyNumber control before the Side control.  This cannot be guaranteed, so it would be better to put the code in the AssyNumber control's AfterUpdate event procedure.

    Your table design also assumes that the DiscComp column is dependent on the key, and not transitively dependent on the key via AssyNumber, i.e. it can be edited independently of the AssyNumber value.  If this is not the case then the DiscComp column is redundant and should be removed from the table, which is otherwise not normalized to Third Normal Form (3NF).  The value can be shown in an unbound control in the form, with a ControlSource property of:

        = [AssyNumber].Column(2)

    Ken Sheridan, Stafford, England

    Thursday, March 17, 2016 11:47 PM
  • I have tried both ways you suggested. In the end I need to know how many discrete components are on each assembly number and both ways I still just get the DiscComp form combo box blanked out when I try to autofill it with the discComp number. Not sure what I am doing wrong.
    Monday, March 21, 2016 5:21 PM
  • 1.  What is the RowSource property of the AssyNumber combo box?

    2.  You originally said the DiscComp control was a text box, but you are now saying it's a combo box.   I would assume it's the former, as the latter would make little sense.

    3.  Most importantly, you have not addressed the issue I posed:

    "Your table design also assumes that the DiscComp column is dependent on the key, and not transitively dependent on the key via AssyNumber,"

    You say you have tried both ways I suggested.  These are not alternatives, however.  One would be right and one would be wrong, depending on whether the above statement is correct or not.  If the column is not functionally dependant solely on the key of the table, then the Discomp value should be referenced in an unbound control, not assigned to a bound control as you are attempting.

    Ken Sheridan, Stafford, England

    Monday, March 21, 2016 6:03 PM
  • Sorry to have misled you with the text box versus combo box. You are right it is a text box. The Row Source for the AssyNumber combo box is a "SELECT DISTINCTROW PartNumber.AssyNumber FROM PartNumber;". This is a query I added in to make just unique numbers show up so that there are not multiple of the same assembly number showing.

    You are also right on the DiscComp is not dependant on the key. Only the AssyNumber.

    Sorry for the confusion. As I stated I am new and learning and trying to make this work.

    Monday, March 21, 2016 8:46 PM
  • The Row Source for the AssyNumber combo box is a "SELECT DISTINCTROW PartNumber.AssyNumber FROM PartNumber;".


    That returns only one column, so you cannot reference a third column, as no such column exists. If you were to include further columns in the RowSource query, you would not be able to use AssyNumber as the bound column as this does not apparently contain distinct values, which is a prerequisite of a combo box's bound column.

    I think you are going to have to explain to us in detail just what you are attempting to do in real life terms.  Things are not making much sense at present.


    Ken Sheridan, Stafford, England

    Monday, March 21, 2016 8:58 PM
  • What I am trying to accomplish is when an assembly number is chosen the discrete components automatically fill in on a form. But the other part of all this is the side matters also as there are different amounts of discrete components on top side versus bottom side. If I can use column 1 and 2 as a choice in a combo box to automatically fill in the AssyNumber text box from the part numbers table I am making the form for.
    Monday, March 21, 2016 9:24 PM
  • The table which you described in your original post is modelling a many-to-many relationship type between the entity type Assemblies and Sides.  In that table the combination of AssyNumber and Side constitute a candidate key, i.e. the values of these two columns, in combination, must be distinct in the table.  However, you have added a surrogate key, ID.  DisComp is a non-key attribute of the relationship type.

    The table into which you are inserting rows in your form is a referencing table in a relationship with the above table.  As you appear to have AssyNumber and Side columns in this table, rather than referencing the ID primary key of the referenced table, you are referencing the composite candidate key by maens of a corresponding composite foreign key.  This is perfectly valid in terms of the theoretical model, but if you choose to reference the first table in this way it would be better to dispense with the ID surrogate key column, and make the AssyNumber and Side columns the composite primary key of the first table.

    If you are correct in thinking that the DiscComp column in the referencing table is functionally dependant solely on the key of the referencing table, then to assign a value into it you would need code in the AfterUpdate event procedure's of the AssyNumber and Side combo boxes which looks up the DisComp value in the referenced table.  You cannot assume with complete confidence that the AssyNumber  will be selected before the Side, even though this might be the normal way it is done, which is why you need code in both control's event procedures.  The code would be like this:

        Dim strCriteria As String

        strCriteria = "AssyNumber  = """ & Nz(Me.AssyNumber,"")  & """ And " & _
            "Side = """ & Nz(Me.Side,"") & """"

        Me.DiscComp = DLookup("DiscComp", "YourReferencedTable",strCriteria)

    If either the AssyNumber or Side control is Null when a selection is made in the other control then the DLookup function call will return a Null, so this will be assigned to the DiscComp control.  Only when a value has been selected in both the AssyNumber   and Side controls will a row be found in the referenced table, and the value from the DiscComp column in that table assigned to the DiscComp control in your form.

    It must be emphasised that a value should be assigned to a DiscComp column in the referencing table only id the column is functionally dependant solely on the key of the referencing table, i.e. the value being assigned is a default one which can if necessary legitimately be edited so that it differs from the value looked up from the referencing table, or in the event of the DisComp value in the referenced row in the referenced table changing, the value in the referencing table must remain static as that when it was first inserted.  If this is not the case then there should be no DiscComp column in the referencing table as this would introduce redundancy and the table would be open to the risk of update anomalies.  In this case the value can be shown in an unbound text box in the form, with a ControlSource property of:

    =DLookup("DiscComp", "YourReferencedTable","AssyNumber  = """ & Nz([AssyNumber],"")  & """ And Side = """ & Nz([Side],"") & """")

    Ken Sheridan, Stafford, England

    Monday, March 21, 2016 11:38 PM
  • I have had no luck with this. I am just going to have to forget about making this work. Thanks.
    Thursday, March 24, 2016 8:40 PM
  • The DiscComp must be dependand solely on the key of the referencing table as you had explained. It turns out that the below code you had as an example:

    Dim strCriteria As String



    strCriteria = "AssyNumber = """ &
    Nz(Me.AssyNumber,"") & """ And " & _

    "Side = """ & Nz(Me.Side,"") &
    """"



    Me.DiscComp = DLookup("DiscComp", "YourReferencedTable",strCriteria)

    Worked for my needs. Can you explain just a little how you developed the formula? An thanks for all the help.

    Thursday, March 24, 2016 11:20 PM
  • Can you explain just a little how you developed the formula?

    Let's go back 30 years or so and look at Codd's rules for relational databases.  Rule #2 is The Guaranteed Access Rule, which states:

     'Every datum (atomic value) in a relational database is guaranteed to be logically accessible by
     resorting to a combination of table name, primary key value and column name.'
     
    In your table the key is a composite one made up of the two columns AssyNumber and Side, which in combination, have distinct values in the table.  Even if not defined as the primary key in the table design they are nevertheless a 'candidate key'.  The datum you want to find is the DiscComp value.  To find this, the second argument of the DLookup function is the table name (I've called it 'YourReferencedTable' as I don't know its real name), the first argument is the column name, DiscComp, and the third argument is the key, which in this case is the value of AssyNumber combined with the value of Side.  This is built as an expression which gets the values from the AssyNumber and Side controls in the form, and assigned to the strCriteria variable, which is then referenced as the DLookup function's third argument.  Consequently the DLookup function returns the DisComp value from the row in the referenced table where the values of the AssyNumber and Side columns' values are those of the AssyNumber and Side controls in the form.


    Ken Sheridan, Stafford, England

    Thursday, March 24, 2016 11:46 PM