Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    khayward is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Location
    Oregon
    Posts
    89

    Subform control source/row source problem - Combo box shows ID(PK) rather than specified items

    Hi all,


    I ran into an issue with a subform that's twisting my brain a bit, I'm sure it's some obnoxiously simple thing but I'm lost:

    - I have a form with a subform. The form contains information about components drawn from tblMasterParts and the subform contains information about vendors and pricing drawn from a junction table, tblVNDPRTS, which is in turn connected to tblVendors

    - I have a field in the subform, VendorName, whose rowsource is tblVendors and control source is VendorName (The name of the control on the subform as well as a field in tblVendors)

    - The combobox for VendorName, when assigned no rowsource displays nothing. To be expected. When I change the rowsource to tblVendors I get the Vendor_ID numbers in the combobox instead of the VendorName entries.

    Attached are the relationship diagram and a snip of the form in design view with properties open. Let me know if I can provide more info. Any ideas will help, I'm stumped after trying (And searching) all morning.Click image for larger version. 

Name:	DB1 Rel diag.JPG 
Views:	30 
Size:	30.2 KB 
ID:	34471Click image for larger version. 

Name:	DB1 form design.JPG 
Views:	31 
Size:	202.5 KB 
ID:	34472

  2. #2
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    The Row Source for the combo box in the subform should return both VendorID and VendorName from tblVendors. You should have the Column Count set to 2, the Bound Column set to 1, and the Column Widths set so that the first Column is hidden: 0";1". This combo box should be bound to the VendorID field in VNDPRT.

  3. #3
    khayward is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Location
    Oregon
    Posts
    89
    Quote Originally Posted by Beetle View Post
    The Row Source for the combo box in the subform should return both VendorID and VendorName from tblVendors.
    Thanks for the super quick response! How do I go about setting two fields for the Row Source? Sorry if I'm being dense.

  4. #4
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    In the combo box properties, click the elipse (...) to the right of Row Source. This will open the query builder. Select tblVendors from the list of tables, then add the VendorID and VendorName fields to the query. Close the query builder (it will ask you if you want to save the row source and update the property - click Yes). Set the other combo box properties as described in #2. Post back if you have more questions.

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    If you don't went to mess with the query builder, type this in the rowsource:
    Code:
    Select VendorID, VendorName from tblVendors ORDER BY VendorName;
    Then as Bailey said, set the rest as in post#2.

    If you deal with Access very much, you will be doing this over and over and it will become very fast and second nature.

  6. #6
    khayward is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Location
    Oregon
    Posts
    89
    Thanks all for the responses, especially Sean Bailey - the information you provided was perfectly clear and concise, That fixed it! Thanks!

  7. #7
    khayward is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Location
    Oregon
    Posts
    89
    Aw, poop. I thought this had it licked, but I guess not. Now I can't enter any data, I'm guessing because of changing the combobox row source to a query. What do I do there? - I need the subform to write to table VNDPRT which suggests to me that I need a different approach than turning the combobox into a query, but I really don't know. I think perhaps I beat my head against the wall a bit too much and now I'm befuddled.

  8. #8
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    It should work.
    Did you change the control source property of the combobox to VendorID? That's what needs to go into the VNDPRT table (which is the subform's recordsource), not the VendorName. Look at your relationship diagram.

  9. #9
    khayward is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Location
    Oregon
    Posts
    89
    Just now I added another field for VendorID, which will be invisible later, changed the control source of VendorName to VendorID. I'm going to play around a tad to ensure that it's writing to the tables correctly - before I changed the control source it was adding a new vendor ID with the vendor number instead of the name, ie: it would add vendor #160 with vendor name as 35.

  10. #10
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    Now I'm confused (not that that's necessarily out of the ordinary)

    Just now I added another field for VendorID...
    You already had a field for VendorID.

    before I changed the control source it was adding a new vendor ID with the vendor number instead of the name, ie: it would add vendor #160 with vendor name as 35.
    ??? What Vendor Number? There is no Vendor Number field in your original post, just VendorID, VendorName and VendorWeb.


  11. #11
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Don't be discouraged. We CAN help.
    If you can post your db here, we can take a look. There are more things that could be causing problems like lookup fields in table design.
    Your problems are probably easy to fix, but hard to resolve via the forum because of communications problems with terminology ambiguity.

  12. #12
    khayward is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Location
    Oregon
    Posts
    89
    Quote Originally Posted by Beetle View Post
    You already had a field for VendorID.
    ??? What Vendor Number? There is no Vendor Number field in your original post, just VendorID, VendorName and VendorWeb.
    I think I may be guilty of poor communication here. I followed the steps you suggested and found that I could add no records and would get an error box when selecting a VendorName from the combo box. I added a zero width control to the subform for tblVendors.VendorID. That wasn't in the subform before, just VendorName.
    When I say vendor number I mean VendorID, sorry for the imprecise language. Prior to taking the steps you outlined the combo box contained and returned only VendorID, rather than VendorName, so I typed in some new records on the form with existing VendorNames and it would create a new record in tblVendors with a new VendorID and the VendorID of the typed VendorName as the VendorName. Adding the tblVendors.VendorID control to the subform appears to have corrected that - I now get a new record in VNDPRT with the correct VendorID and no new record in tblVendors unless the vendor is new. I'm going to modify the control at some point so that when it is a new vendor you have to fill out a form for that vendor as I do have a process in place to qualify vendors.

    I hope I've been able to clear up the confusion. I'm trying to think in the language of the business structure and the language of the database at the same time and little pebbles in the road like this one get me all confused. My brain was on the verge of shutting down yesterday, implementing the fix you suggested and then adding the other control was quite a blessing and had me going some more until my wife said to stop! Thank you!

  13. #13
    khayward is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Location
    Oregon
    Posts
    89
    Quote Originally Posted by davegri View Post
    Don't be discouraged. We CAN help.
    If you can post your db here, we can take a look. There are more things that could be causing problems like lookup fields in table design.
    Your problems are probably easy to fix, but hard to resolve via the forum because of communications problems with terminology ambiguity.
    I appreciate the offer. I know that it is recommended to remove personal or confidential data prior to posting, however my database currently doesn't contain anything like that, but it is over 20Mb - I have a lot of data. Will it be too large to post?

  14. #14
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Will it be too large to post?
    Do a compact/repair and then zip it. It might shrink enough.

    Still some confusion. With the form shown in post#1, I don't see how you could add any new records to tblVendors. As you say, a new form would be needed to do that.

  15. #15
    khayward is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Location
    Oregon
    Posts
    89
    db1.zipIt's much smaller now, so here it is:

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Combo Box control source?
    By Lou_Reed in forum Access
    Replies: 6
    Last Post: 04-28-2017, 01:14 PM
  2. Problem with control source
    By Bradex in forum Forms
    Replies: 1
    Last Post: 04-28-2016, 11:47 PM
  3. Replies: 3
    Last Post: 04-27-2016, 01:25 PM
  4. Replies: 5
    Last Post: 09-18-2013, 09:15 PM
  5. Replies: 1
    Last Post: 03-30-2013, 12:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums