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

[davegri]

db1-davegri-v02.zip

Changed text box to combobox for Manufacturer on main form (Another example of how combobox dropdowns work)
Changed Recordsources of both main and subform to a single table instead of joined tables (resolved problem of attempt to successfully update multiple tables via one form).


Fixed combobox functionality on subform for Vendor.
Removed table lookup fields in table design.
Added simple form for updating vendor table. You call the form by clicking the icon at the bottom of the vendor combobox dropdown.
The blank form at startup is because the form is looking at parts in tblMstrParts that have null PartNumber. Those records need to have a partnumber added.
Also there are a large number of records in VNDPRT with blank VendorID. Those also need to be fixed.
The blanks were probably introduced during your testing changes.

Looking good. Any problems post back.

Edit: Deleted and replaced attachment v01 with v02. For some reason the search combobox macro quit working with an error. I converted the macro to VBA and it worked OK.

[khayward]

db1-davegri-v02.zip

Changed text box to combobox for Manufacturer on main form (Another example of how combobox dropdowns work)
Changed Recordsources of both main and subform to a single table instead of joined tables (resolved problem of attempt to successfully update multiple tables via one form).
Fixed combobox functionality on subform for Vendor.
Removed table lookup fields in table design.
Added simple form for updating vendor table. You call the form by clicking the icon at the bottom of the vendor combobox dropdown.
The blank form at startup is because the form is looking at parts in tblMstrParts that have null PartNumber. Those records need to have a partnumber added.
Also there are a large number of records in VNDPRT with blank VendorID. Those also need to be fixed.
The blanks were probably introduced during your testing changes.

Looking good. Any problems post back.

Edit: Deleted and replaced attachment v01 with v02. For some reason the search combobox macro quit working with an error. I converted the macro to VBA and it worked OK.

Thanks for doing this. I've been busy cleaning up data all day so I haven't had a chance to look at it, but I do want to ask a couple of questions so that I can understand the goals. Obviously I'm no whizz at this stuff, I only know enough to be dangerous and am a bit clueless when it comes to macros and SQL, so here goes:

(Let me preface by again repeating my thanks!)

- Changing text box to combo box - I can see how this will be a bit of a no brainer for new records - is it possible for me to protect existing records with a pop-up or something? The folks who may end up using this are overworked and liable to make a goof. If I can save them a headache and myself a bit of work...

- Changing recordsource to a single table; I'm not sure I follow what the intention is here, or how it works. I thought that what I had was working. Can you explain what the improvement is? (Sorry if I'm being dense, spending all day trawling 150,000 cells for issues makes my head a bit numb)

[davegri]

- Changing text box to combo box - I can see how this will be a bit of a no brainer for new records - is it possible for me to protect existing records with a pop-up or something? The folks who may end up using this are overworked and liable to make a goof. If I can save them a headache and myself a bit of work...

Is this a change from what you had before? Any data value that you change on the form will change the table before and now. A combobox is no different from a textbox in this regard.
Things can be done to protect data. For example, the textboxes or comboboxes could be disabled except for new records. In that case, a button could be added to Allow Edits.

Changing recordsource to a single table

This is a general rule of thumb for Access forms, One Table, One Form. If a query contains more than one table it can become unupdateable and it's easy enough to avoid defining it that way.

[khayward]

Thanks for the feedback. I'm going to try and take a look at the modifications you made today and try to wrap my head around the changes - I want to understand and sometimes it seems that the changes and improvements are too subtle for me to grasp properly. - I think I'm OK when it comes to the underlying structure and relationships but there are so many options and variations for form and query design and functionality that I tend to get a bit lost in it - I only discovered tabbed controls a couple of days ago even though it's a button in the control group on the ribbon. Basically, I have no idea of the possibilities. Thank you for taking time out to help me with this, I do appreciate it.

[khayward]

Is this a change from what you had before? Any data value that you change on the form will change the table before and now. A combobox is no different from a textbox in this regard.
Things can be done to protect data. For example, the textboxes or comboboxes could be disabled except for new records. In that case, a button could be added to Allow Edits.

This is a general rule of thumb for Access forms, One Table, One Form. If a query contains more than one table it can become unupdateable and it's easy enough to avoid defining it that way.

I went through and looked at the changes, I appreciate your help. Thank you again! I do have another question relating to the same database but it's a different topic so I'm going to see if I can find anything already written and then maybe post it later.