none
Combobox based on a lookupfield RRS feed

  • Question

  • Hi all,

    I have a table tbl_A with a field "Name" , type "Number" and has a lookup to the table tbl_Names.

    When i open the table i see the names.

    Now, i have a form based on tbl_A and i see the names

    In the form, i create a combobox cmbName to find a name in the recordset with rowsource "select name from tbl_A"

    The combobox shows the ID in stead of the names.

    Is there a command to show the names or have i to make a link to the tbl_Names?

    To find a record in the recordsource, i have to search for the name and not for the ID. I can solve this problem, but is there not an easyer way?

    Solution:

    Combobox source: "select tbl_Names.Name FROM tbl_A INNNER JOIN tbl_Names ON tbl_A.Name=tbl_Names.ID"

    In AfterUpdate event of combobox:

    Dim rst as DAO.Recordset

    Set rst=me.recordsetclone

    rst.findfirst "Name='" & me.cmbName & "'"

    Me.bookmark=rst.bookmark

    Wednesday, August 19, 2015 1:36 PM

Answers

  • What me wonder is that the field "Name" in tbl_A is a number (with a lookup to tbl_names), but to find a record in tbl_A, i need the name and not the number.

    I can only assume that, as I said in my last post "the form's RecordSource unnecessarily joins the referenced table to the referencing table.................... and that the non-key Name column from the referenced Names table is returned in the RecordSource query rather than the foreign key column from the referencing tbl_A table."  Otherwise the line in your code:

         rst.findfirst "Name='" & me.cmbName & "'"

    would raise a data type mismatch error as you are concatenating a literal text value into the string expression.  You can see this in my demo to which I referred you if the line:

        .FindFirst "ContactID = " & ctrl

    in the AfterUpdate event procedure of the cboGoToContact control in the frmFindContact form were changed to:

        .FindFirst "ContactID = '" & ctrl & "'"

    viz:


    Ken Sheridan, Stafford, England


    Thursday, August 20, 2015 1:34 PM

All replies

  • You need to set the column count to 2 and the column widths to 0;1
    • Proposed as answer by André Santo Wednesday, August 19, 2015 2:05 PM
    Wednesday, August 19, 2015 1:59 PM
  • Hi. On the topic of lookup fields at the table level, take a look at this: The Evils of Lookup Fields in Tables
    Wednesday, August 19, 2015 2:37 PM
  • Hi, thank you for your quick reaction.

    I repeat:

    In the form, i create a combobox cmbName to find a name in the recordset with rowsource "select name from tbl_A"

    The combobox shows the ID in stead of the names.

    So i have only one field selected and therefore i can't choose two collums

    Wednesday, August 19, 2015 2:48 PM
  • Your combo box Row Source should be "SELECT [ID], [Name] FROM tbl_Names ORDER BY [Name];."

    The Bound Column should be 1.

    And, as stated above, you need to set the Column Count to 2 and the Column Widths to 0;1.

    This will display the tbl_Names.Name field, but will assign the selected tbl_Names.ID to the tbl_A.Name field.

    Wednesday, August 19, 2015 3:19 PM
  • 1) a field "Name"

    2) create a combobox cmbName to find a name in the recordset with rowsource "select name from tbl_A"

    3) The combobox shows the ID in stead of the names.

    Is there a command to show the names

    Good Morning Lteu,

    If I understand correctly,

    1) Your Table has an error due to the use of a reserved word "Name". I suggest changing that to something more meaningful like FName or LName or Company Name whatever best fits your db needs.

    2) Your Row Source is correct. I see no reason to add any extra columns.

    3) Above you mention the combobox cmbName with rowsource "select name from tbl_A" now here you mention the rowsource as Combobox source: "select tbl_Names.Name FROM tbl_A INNNER JOIN tbl_Names ON tbl_A.Name=tbl_Names.ID". I am guessing you are referring to the Control Source for the cbo. If this is the case, try not having a control (an unbound combo box).

    The After Update event would look like this

    Private Sub cbmbName_AfterUpdate()
        DoCmd.SearchForRecord , "", acFirst, "[Your Field Name here] = " & "'" & Screen.ActiveControl & "'"
    End Sub

    This will allow your combo box to show the name field without adding more columns and bring that record to the Form when you select it in the combo box list.

    Please let me know if this is not what you are looking for or if you need additional assistance in this matter.

    Thank you!!!


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Wednesday, August 19, 2015 4:23 PM
  • As personal names can legitimately be duplicated, they are unsuitable as a key.  You can select by a name and navigate to a record whose distinct numeric key matches the hidden key of the selected name, in which case you'll go to the first match, or you can filter the form to one or more records with the selected name.  You'll find examples of both in FindRecord.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    BTW, where a combo box's RowSource returns only two columns, of which the first is the hidden key, you merely have to set the ColumnCount property to 0.  You do not have to specify a second dimension, which is only necessary if multiple columns are to be shown in the drop down list.

    Ken Sheridan, Stafford, England

    Wednesday, August 19, 2015 4:34 PM
  • 1) a field "Name"

    2) create a combobox cmbName to find a name in the recordset with rowsource "select name from tbl_A"

    3) The combobox shows the ID in stead of the names.

    Is there a command to show the names

    Good Morning Lteu,

    If I understand correctly,

    1) Your Table has an error due to the use of a reserved word "Name". I suggest changing that to something more meaningful like FName or LName or Company Name whatever best fits your db needs.

    2) Your Row Source is correct. I see no reason to add any extra columns.

    3) Above you mention the combobox cmbName with rowsource "select name from tbl_A" now here you mention the rowsource as Combobox source: "select tbl_Names.Name FROM tbl_A INNNER JOIN tbl_Names ON tbl_A.Name=tbl_Names.ID". I am guessing you are referring to the Control Source for the cbo. If this is the case, try not having a control (an unbound combo box).

    The After Update event would look like this

    Private Sub cbmbName_AfterUpdate()
        DoCmd.SearchForRecord , "", acFirst, "[Your Field Name here] = " & "'" & Screen.ActiveControl & "'"
    End Sub

    This will allow your combo box to show the name field without adding more columns and bring that record to the Form when you select it in the combo box list.

    Please let me know if this is not what you are looking for or if you need additional assistance in this matter.

    Thank you!!!


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Chris,

    Though you are correct that the OP should change the name of the "Name" field, that is not the OP's stated problem.  The OP's stated problem is trying to reference one table for names to insert into their other table.

    As far as renaming their field, it should be more appropriately titled "NameID."

    As the OP describes, the field is called "Name" and it is defined as a numeric data type.  Although, the OP does not describe what specific numeric type it is.  So we have to assume that the user is inserting a numeric record id from the reference table.

    As the OP further states, the combo box is showing only the id value.  The current SQL references only the "Name" field (numeric data type) of the target table to receive the value.

    All that said, your statement indicating that the OP's Row Source is correct is incorrect, as again, the data comes from a separate table.

    Lastly, your additional advice to create a function to locate the record is inappropriate in the current circumstance.






    Wednesday, August 19, 2015 4:38 PM
  • BTW, where a combo box's RowSource returns only two columns, of which the first is the hidden key, you merely have to set the ColumnCount property to 0.  You do not have to specify a second dimension, which is only necessary if multiple columns are to be shown in the drop down list.

    Ken Sheridan, Stafford, England

    Ken, I think that you mean that one merely needs to set the Column Widths to 0, not the Column Count.  Setting the Column Count to 0 will result in Access presenting you with a message indicating an invalid entry, 1-255 required.

    It really is 6 in one and half dozen in the other though.  It's hardly an effort to define the second column's width.  For someone who is novice at this sort of thing, I hardly think it's worth mentioning shortcuts like that.  It can be confusing and can potentially create issues under different circumstances.


    Wednesday, August 19, 2015 6:01 PM
  • I think that you mean that one merely needs to set the Column Widths to 0, not the Column Count.


    Mea culpa!  Correct.


    Ken Sheridan, Stafford, England

    Wednesday, August 19, 2015 6:15 PM
  • @Running Man

    1) The OP's stated problem is trying to reference one table for names to insert into their other table.

    2) As far as renaming their field, it should be more appropriately titled "NameID."

    3) As the OP describes, the field is called "Name" and it is defined as a numeric data type.  Although, the OP does not describe what specific numeric type it is.  So we have to assume that the user is inserting a numeric record id from the reference table.

    4) As the OP further states, the combo box is showing only the id value.  The current SQL references only the "Name" field (numeric data type) of the target table to receive the value.

    5) All that said, your statement indicating that the OP's Row Source is correct is incorrect, as again, the data comes from a separate table.

    Lastly, your additional advice to create a function to locate the record is inappropriate in the current circumstance.






    1) You may have misunderstood the question. I think you are incorrect. The OP never mentions copying or moving data from one table to another nor does the OP ever even mention a second table.

    2) NameID is not the best naming convention as it does not properly describe anything about Name.

    3) You know what they say about assuming. MOre to the point the OP specifically states the ID Field is displayed instead of the Name Field which is a good indication that the Name Field should be ruled out as the ID Field.

    4) When using a combo box as the OP has demonstrated in question, it is perfectly normal to NOT use the ID field at all as I demonstrated in the example it is not needed. If you would like I can send you a copy of a working demonstration of that which the OP described.

    5) The OP is using only 1 Table. From the way I interpreted the users question, the Row Source is correct for looking up the record to the Form. It is standard practice to use an Unbound Control for this.

    6) I think it is more appropriate for the OP to say I have totally missed the mark and extremely inappropriate for you to speak to anyone in this forum in the manner you have.

    @ Lteu,

    I apologize for interrupting your thread in defense of my post to try helping you. If I am in error with my suggestion please let me know and I will remove it to lend better understanding to those who may be looking for a similar solution to the issue you are experiencing.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Wednesday, August 19, 2015 7:55 PM
  • @Running Man

    1) The OP's stated problem is trying to reference one table for names to insert into their other table.

    2) As far as renaming their field, it should be more appropriately titled "NameID."

    3) As the OP describes, the field is called "Name" and it is defined as a numeric data type.  Although, the OP does not describe what specific numeric type it is.  So we have to assume that the user is inserting a numeric record id from the reference table.

    4) As the OP further states, the combo box is showing only the id value.  The current SQL references only the "Name" field (numeric data type) of the target table to receive the value.

    5) All that said, your statement indicating that the OP's Row Source is correct is incorrect, as again, the data comes from a separate table.

    Lastly, your additional advice to create a function to locate the record is inappropriate in the current circumstance.






    1) You may have misunderstood the question. I think you are incorrect. The OP never mentions copying or moving data from one table to another nor does the OP ever even mention a second table.

    I'm sorry to say, but the OP did mention two tables:

    "I have a table tbl_A with a field "Name" , type "Number" and has a lookup to the table tbl_Names."

    • tbl_A
    • tbl_Names

    2) NameID is not the best naming convention as it does not properly describe anything about Name.

    I guess you're just looking to argue here.  "NameID" was an example of anything better than the reserved word "Name."

    3) You know what they say about assuming. MOre to the point the OP specifically states the ID Field is displayed instead of the Name Field which is a good indication that the Name Field should be ruled out as the ID Field.

    Since you're wrong on count 1, I don't see the need to say a lot more here.  Although, I didn't assume anything.  In fact, I believe I read very closely, which might have saved you a little embarrassment here.  You've clearly failed to read completely or comprehend both the OPs situation or the explanation I gave you.

    4) When using a combo box as the OP has demonstrated in question, it is perfectly normal to NOT use the ID field at all as I demonstrated in the example it is not needed. If you would like I can send you a copy of a working demonstration of that which the OP described.

    Once again, wrong!  Regardless of the OP's situation, which you did miss the mark, it would be a poor design and violation of proper normalization to add the name of a person, place or thing redundantly in a field. 

    5) The OP is using only 1 Table. From the way I interpreted the users question, the Row Source is correct for looking up the record to the Form. It is standard practice to use an Unbound Control for this.

    As you are wrong in your initial assumptions / interpretations, all else that follows is wrong.

    6) I think it is more appropriate for the OP to say I have totally missed the mark and extremely inappropriate for you to speak to anyone in this forum in the manner you have.

    I'm sorry if you feel I was wrong to point out your misunderstanding and bad advice, but it's the OP that suffers when given ill-informed, misleading information.

    @ Lteu,

    I apologize for interrupting your thread in defense of my post to try helping you. If I am in error with my suggestion please let me know and I will remove it to lend better understanding to those who may be looking for a similar solution to the issue you are experiencing.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012


    Wednesday, August 19, 2015 8:31 PM
  • Alright I apologize to all forum members and visitors and to you RunningManHD I did miss that there are two tables.

    I stand by the statement that you are too harsh in the way you speak. I have watched your belittling attitude here in the forums since you have been here. You are more gruff than helpful. I suggest that you work on your people skills.

    I would also say that there is a difference between using a combobox to find a record versus copy from 1 table to another. Can you point out where that is? I quote you here.

    "Chris,

    Though you are correct that the OP should change the name of the "Name" field, that is not the OP's stated problem.  The OP's stated problem is trying to reference one table for names to insert into their other table."

    This was the first point I made to you. The OP does not seem to want to copy name from 1 table to another. Do you agree with this point?


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Wednesday, August 19, 2015 8:42 PM
  • Alright I apologize to all forum members and visitors and to you RunningManHD I did miss that there are two tables.

    I stand by the statement that you are too harsh in the way you speak. I have watched your belittling attitude here in the forums since you have been here. You are more gruff than helpful. I suggest that you work on your people skills.

    I would also say that there is a difference between using a combobox to find a record versus copy from 1 table to another. Can you point out where that is? I quote you here.

    "Chris,

    Though you are correct that the OP should change the name of the "Name" field, that is not the OP's stated problem.  The OP's stated problem is trying to reference one table for names to insert into their other table."

    This was the first point I made to you. The OP does not seem to want to copy name from 1 table to another. Do you agree with this point?


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012


    Alright I apologize to all forum members and visitors and to you RunningManHD I did miss that there are two tables.

    I stand by the statement that you are too harsh in the way you speak. I have watched your belittling attitude here in the forums since you have been here. You are more gruff than helpful. I suggest that you work on your people skills.

    I would also say that there is a difference between using a combobox to find a record versus copy from 1 table to another. Can you point out where that is? I quote you here.

    "Chris,

    Though you are correct that the OP should change the name of the "Name" field, that is not the OP's stated problem.  The OP's stated problem is trying to reference one table for names to insert into their other table."

    This was the first point I made to you. The OP does not seem to want to copy name from 1 table to another. Do you agree with this point?


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    I don't see the need to drag this out in the OP's thread.  Obviously you've got a problem with the advice I give.  I don't belittle, I provide direct concise answers to problems.  Perhaps you're just a little too sensitive.  I don't have time to give advice and sugar coat it too. 

    If I see something blatantly wrong, I am going to point it out, be it from the OP or a commenter.   I don't think I was rude to you in any way.

    You also still seem to misunderstand both the OP's and my statements.  I never said the OP was trying to copy or move anything.  To clarify once more for you... By the OP's description, it appears that the OP is trying to reference the primary key of a table that is described as a reference table of unique names.  The OP is attempting to use a combo box to select the foreign key value for the table and field "tbl_A.Name," again, which is defined as a numeric field.

    The following should generally be the OP's table structure according to description:

    tbl_A

    Field Name

    Data Type

    Required

    Unique

    Key

    Description

    ID

    AutoNumber

    Yes

    Yes

    PK

    Record ID

    Name

    Long Integer

    Yes

    No

    FK

    Name id

    tbl_Names

    Field Name

    Data Type

    Required

    Unique

    Key

    Description

    ID

    AutoNumber

    Yes

    Yes

    PK

    Record ID

    Name

    Text (50)

    Yes

    Yes

    Name of something

    Instead of referring to the reference table "tbl_Names," the OP's combo box, Row Source, SQL, was referring to the target table "tbl_A."  You're advice attempted to appose what I had already stated, that this was incorrect.  In fact, the SQL should be referring to "tbl_Names."

    Again, I'm sorry if you don't like my advice or the way I give it.  I've been programming, designing databases, and providing instruction, both classroom and free in the forums for decades.  Most appreciate what I have to offer.  I can only empathize with anyone that doesn't.  As they say, you can lead a horse to water, but you can't make him drink.

    Finally, to the Lteu, my sincerest apologies.

    Wednesday, August 19, 2015 9:49 PM
  • You didn't answer the question of what you meant when you said

    Quote

    to insert into their other table

    Unquote

    This suggests copy and paste right? 


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Wednesday, August 19, 2015 9:58 PM
  • You didn't answer the question of what you meant when you said

    Quote

    to insert into their other table

    Unquote

    This suggests copy and paste right? 


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012


    No, it doesn't and I've explained that now several times in detail.  It means that they are using the combo box to select a value for their field, which by virtue inserts the value of the selection into the field.
    • Edited by RunningManHD Wednesday, August 19, 2015 10:18 PM
    Wednesday, August 19, 2015 10:11 PM
  • It means that they are using the combo box to select a value for their field, which by virtue inserts the value of the selection into the field.
    However, that is not what the OP is attempting.  Their original question is quite clear, and specifically says 'find a name in the recordset'.  They then go on to cite the code which they are employing to do this by synchronizing bookmarks.   The combo box is clearly an unbound control used for navigational purposes, not a bound control for assigning a value to the foreign key column in the referencing table.

    Perhaps due to the use of the appalling lookup field wizard, while they give the foreign key column's data type as 'number', they apparently  believe that the value of the column is the text value of the non-key column in the referenced table, not that of its numeric primary key, and have consequently wrapped the value in single quotes characters when building the string expression in the code.

    What is puzzling is that they say that the code cited is a 'solution' when, if their statement that the form is bound to tbl_A is correct, a data mismatch error would have been expected.  If this code is working I can only assume that the form's RecordSource unnecessarily joins the referenced table to the referencing table, in the same way that their combo box's RowSource as given in their original post does (though they later give a different SQL statement for this), and that the non-key Name column from the referenced Names table is returned in the RecordSource query rather than the foreign key column from the referencing tbl_A table.


    Ken Sheridan, Stafford, England

    Wednesday, August 19, 2015 11:27 PM
  • Thanks Ken,

    I think you covered it very well.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Thursday, August 20, 2015 2:49 AM
  • Hi all,

    KCDW,

    The field "name" is not the problem. its a fictive name

    You said: The rowsource of the combobox is correct. This is so when i select tbl_A innerjoin tbl_names (than i became the names), but not when i select the name from tbl_a (than i became the ID-number in the combobox, but the name in datasheetview).

    What me wonder is that the field "Name" in tbl_A is a number (with a lookup to tbl_names), but to find a record in tbl_A, i need the name and not the number.

    The after update works of cource also with your statement. So you searchs also for the name and not for the number.

    So it works, but you need to be carefully with fieldtypes based on lookuptables. Thanks to all

    Thursday, August 20, 2015 11:56 AM
  • What me wonder is that the field "Name" in tbl_A is a number (with a lookup to tbl_names), but to find a record in tbl_A, i need the name and not the number.

    I can only assume that, as I said in my last post "the form's RecordSource unnecessarily joins the referenced table to the referencing table.................... and that the non-key Name column from the referenced Names table is returned in the RecordSource query rather than the foreign key column from the referencing tbl_A table."  Otherwise the line in your code:

         rst.findfirst "Name='" & me.cmbName & "'"

    would raise a data type mismatch error as you are concatenating a literal text value into the string expression.  You can see this in my demo to which I referred you if the line:

        .FindFirst "ContactID = " & ctrl

    in the AfterUpdate event procedure of the cboGoToContact control in the frmFindContact form were changed to:

        .FindFirst "ContactID = '" & ctrl & "'"

    viz:


    Ken Sheridan, Stafford, England


    Thursday, August 20, 2015 1:34 PM