Combo box on from not showing table data after database was split.

[sheusz]

This is going to be a bit difficult to explain, so please bear with me.



To give a bit of background, I use an update query to import bulk data (originally sourced from an Excel spreadsheet) in to a table called SoftwareKeys

INSERT INTO SoftwareKeys ( KeyNo, Supplier, PurchaseDate, OrderNo, SoftwareName, OriginalLevel, Notes )
SELECT [_ImportFromExcel].KeyNo, [_ImportFromExcel].Supplier, [_ImportFromExcel].PurchaseDate, [_ImportFromExcel].OrderNo, [_ImportFromExcel].SoftwareName, [_ImportFromExcel].OriginalLevel, [_ImportFromExcel].KeyNoNotes
FROM _ImportFromExcel;

The DB has a form (Software Keys Entry Form) that users then use to assign the KeyNo to a CustomerID. They search for KeyNo and then fill in CustomerID, SoldDate and Notes. Everything else is populated by the update query.

This all worked very well before I split the DB.

After I split the DB I have found that the combo box on the form now does not show the table data of the data field SoftwareName. The update query still works fine as I can see the SoftwareName imported in to the SoftwareKeys table. It's just not showing up on the form.



The combo box SoftwareName is bound to the table SoftwarePackages.




As I said, this all worked perfectly prior to my splitting the DB. I've been tinkering with trying to find a solution, but don't know where the actual problem lies.

I have found that if I change the EXCEL spreadsheet column to reflect the SoftwareNameID, rather than the SoftwareName, then it works OK.

Am I missing something really obvious here? And suggestions would be greatly appreciated.

[ArviLaanemets]

As you haven't given enough info about the combo (record source, linked field), I can only guess - probably combo's linked recordsource field is SoftwareNameID, and you are you are trying to link it with field SoftwareName from table SoftwareKeys (combo's linked field). Of-course there will never be a match between them - one being some long integer number, and another some text.

[ranman256]

If SoftwareName is unique, you dont need the ID field.
Key the Name field.
Much like US States field. No need for an ID since all names are unique.

if the combo is bound to ID field, but user sees Name, then the combo can only be assigned the ID.

[Gicu]

I think you set up the SoftwareName in the SoftwareKeys table to be a lookup field based on the SoftwarePackages table. Most of us do not like lookup-fields as they cause problems as you describe (see http://access.mvps.org/access/lookupfields.htm). You should change the field name to match what it actually stores (the Id, not the name), remove the lookup field from the table (as you shouldn't allow users to edit the data in the table anyway) and edit the form to ensure the combo box displays the name based on the stored ID: two columns (ID and Name), first one Width=0 (hidden).

Cheers,
Vlad

[Missinglinq]

What is the Back End...Access or something else?

Linq ;0)>

[sheusz]

Hi Arvil

Thanks for the reply.

Apologies, I neglected to say that the linked recordsource field is SoftwareName. It is not SoftwareNameID. The recordsource is the SoftwarePackages table.

[sheusz]

Hi Ranman

Thanks for the reply.

If SoftwareName is unique, you dont need the ID field.
Key the Name field.
Much like US States field. No need for an ID since all names are unique.

if the combo is bound to ID field, but user sees Name, then the combo can only be assigned the ID.

I've always used ID fields out of habit. There is a lot of historical data stored, so changing now is not really an option. But I do see to what you are alluding.

[sheusz]

Hi Vlad

Thank you for the reply. This is probably the root cause of the issue. Even though it worked fine before the DB was split.

I think you set up the SoftwareName in the SoftwareKeys table to be a lookup field based on the SoftwarePackages table. Most of us do not like lookup-fields as they cause problems as you describe (see http://access.mvps.org/access/lookupfields.htm). You should change the field name to match what it actually stores (the Id, not the name), remove the lookup field from the table (as you shouldn't allow users to edit the data in the table anyway) and edit the form to ensure the combo box displays the name based on the stored ID: two columns (ID and Name), first one Width=0 (hidden).

This is the relationship setup for the tables



The form is already set up so the combo box displays the name based on the stored ID: two columns (ID and Name), first one Width=0

It appears that I cannot delete the SoftwareName - SoftwareNameID relationship since the DB has been split. Error "You can't delete a relationship inherited from a linked database"

Is this want I really need to do?

[Gicu]

I don't think the relationships are the problem. Open the back-end where your tables are now located, make a copy of the SoftwareKeys table and remove the lookup for the SoftwareName. You sould see that it actually stores the ID. From there you should be able to get your query and form to work, might need to change some data types for some fields, but should all be straight forward once you see the actual data stored in the fields.

If stuck maybe upload a small sample db (with no sensitive data in, just some dummy records to illustrate the issue).

Cheers,
Vlad

[sheusz]

I don't think the relationships are the problem. Open the back-end where your tables are now located, make a copy of the SoftwareKeys table and remove the lookup for the SoftwareName. You sould see that it actually stores the ID. From there you should be able to get your query and form to work, might need to change some data types for some fields, but should all be straight forward once you see the actual data stored in the fields.

If stuck maybe upload a small sample db (with no sensitive data in, just some dummy records to illustrate the issue).

Cheers,
Vlad

Hi Vlad

Thanks for the assistance. I'll play around with it over the next few days and will advise.

[sheusz]

I don't think the relationships are the problem. Open the back-end where your tables are now located, make a copy of the SoftwareKeys table and remove the lookup for the SoftwareName. You sould see that it actually stores the ID. From there you should be able to get your query and form to work, might need to change some data types for some fields, but should all be straight forward once you see the actual data stored in the fields.

If stuck maybe upload a small sample db (with no sensitive data in, just some dummy records to illustrate the issue).

Cheers,
Vlad

Hi Vlad

Turns out that you were 100% correct. In the SoftwareKeys table I changed the display control on the Lookup tab from ComboBox to TextBox and changed the BoundColumn of the ComboBox on the form from 1 to 2. All is working well now.

The only issue I had was that, since I deleted the lookup, all the legacy records (when viewed through the form) would show the SoftwareNameID and not the SoftwareName. So I created and ran a quick update query on the softwareKeys table to fix that.

Question. Now that the table SoftwareKeys is now storing text string instead of a lookup value, won't this cause the size of the DB (that table in particular) to increase?

[Gicu]

You shoudn't have run the update query, you do want to store the ID, not the name. I think you should have renamed the field SoftwareNameID (if you have Name Autocorrect turned on it should propagate the change to all (most) your dependent objects (it behaves much better lately then in first iterations). In your form the combo should still be bound to column 1, but make that 0" width (not visible) and the second one wide enough to accomodate the software name.

Cheers,
Vlad

[sheusz]

You shoudn't have run the update query, you do want to store the ID, not the name. I think you should have renamed the field SoftwareNameID (if you have Name Autocorrect turned on it should propagate the change to all (most) your dependent objects (it behaves much better lately then in first iterations). In your form the combo should still be bound to column 1, but make that 0" width (not visible) and the second one wide enough to accomodate the software name.

Hi Vlad

I don't understand this. The data is backed up so it's no drama, but I don't understand what renaming the SoftwareNameID will do. What do I rename it to?? How do I tell if Name Autocorrect turned on and what does it actually do?

Sorry about the questions, but I really want to try and understand what is happening, rather than just resolving the issue without knowing how or why.

[Gicu]

No worries about the questions of course. And sorry for not being more clear. You do not want to rename the SoftwareNameID (in SpftwarePackages), but I suggesting to rename the SoftwareName field in SoftwareKeys as SoftwareNameID and change its data type to long. As the field holds the actual ID and not the name it should be reflected somehow in the name. Because the field is a foreign key field associated with the primary key from SoftwarePackages table some developers would suggest naming it SoftwareNameID_FK, I usually don't but it is up to you.
Here are a few links for naming conventions for Access:
https://www.access-programmers.co.uk/forums/threads/ms-access-naming-conventions.225837/
https://www.consultdmw.com/ms-access-naming-conventions.html

Tha Name Autocorrect propagates the object name changes to all dedendant objects in the db. So if you change the name of the field your queries won't be broken but they will get atumatically updated by Access to use the new name. When it was first introduces it was a very buggy feature that was causing lots of grief so many developers were used to turn it off. I found that the latest versions are not too bad and I do use it when forced to make name changes (I still turn it off afterwards ). You find it under File\Options\Current Database.
https://support.microsoft.com/en-us/...4-a93c10a9d98b
Hope this helps you!

Cheers,
Vlad

[sheusz]

No worries about the questions of course. And sorry for not being more clear. You do not want to rename the SoftwareNameID (in SpftwarePackages), but I suggesting to rename the SoftwareName field in SoftwareKeys as SoftwareNameID and change its data type to long. As the field holds the actual ID and not the name it should be reflected somehow in the name. Because the field is a foreign key field associated with the primary key from SoftwarePackages table some developers would suggest naming it SoftwareNameID_FK, I usually don't but it is up to you.

Hi Vlad

Thanks again for the reply. Nope, I'm still not getting it . I think I tried what you suggested, but when I change the bound column from 2 to 1, I end up with the form just showing the SoftwareNameID number.

With your indulgence, I have attached a link to a copy of the original DB (it's over 5mb ZIP file, so to big to upload here) with a sample Excel file. If you open the front end _Development_fe.accdb the DB should open with the offending form (Software Keys Entry Form) in question. If you use the button "Import Codes Wizard" at the top right of the form body, it will step you through importing the included xlsx file. Then use the form to search for one of the imported serial numbers (e.g 846885) or go to the last record of the parent form using the record selector at the bottom of the form, and you will see that the Software Package combo on the form is blank, but the SoftwareKeys table is correct (as I understand it).

As I mentioned previously, If I change the SoftwareName in the xlsx file to the SoftwareNameID (9), then the form displays correctly. Only problem with this is that the author of the xlsx file needs to know the ID for each software package.

I really appreciate your assistance if you would like to dabble. You will probably notice a lot on no-no's in the DB. It is a project that has been developing since 2005 (prior to that it was a Dbase 3 application).

https://drive.google.com/file/d/1Jdd...ew?usp=sharing

https://drive.google.com/file/d/1Jdd...ew?usp=sharing