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

[ArviLaanemets]

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.

Set BoundColum = 1, ColumnCount = 2, and ColumnWidths = "0;2.5" (or "0,2.5" - I'm not sure about delimiters in different regional settings) - i.e. the first column of combo's RowSource is linked to field SoftwareNameID, and user sees/selects second column (SoftwareName) of RowSource. The 1st column will be invisible, as it's width is 0.

[Gicu]

Hi,

As you said the db is in a bit (a lot..) of a mess, lots of orphan code, no Option Explicit at the top of module so variable declaration not enforced, I think it is going to be very unstable and prone to corruption. But returning to today's topic please have a look at the attached file, I have only changed the SoftwareKeys table (goes into back-end) to remove the lookup field and change the data type to number\long and the front-end append query (to join the SoftwarePackages by name and append the ID). In the table I have not renamed the field as I suggested earlier as I did not have time to check if doing that would break anything else in the front-end (since the db is split now I don't think you can rely on NameAutocorrect as the field change is in the back-end and the rest of the objects in the front-end). In any case I think it is working as expected.

Please let me know if you have trouble testing my changes.

Cheers,
Vlad

[sheusz]

Hi,

As you said the db is in a bit (a lot..) of a mess, lots of orphan code, no Option Explicit at the top of module so variable declaration not enforced, I think it is going to be very unstable and prone to corruption. But returning to today's topic please have a look at the attached file, I have only changed the SoftwareKeys table (goes into back-end) to remove the lookup field and change the data type to number\long and the front-end append query (to join the SoftwarePackages by name and append the ID). In the table I have not renamed the field as I suggested earlier as I did not have time to check if doing that would break anything else in the front-end (since the db is split now I don't think you can rely on NameAutocorrect as the field change is in the back-end and the rest of the objects in the front-end). In any case I think it is working as expected.

Hi Vlad

Thank you very much for your help. I'll have a look over the next day or so and advise.

In relation to your comments about the DB as a whole, I appreciate your observations. What do you mean by Orphan Code?

As I look to retirement, this app needs to become pretty bullet proof so that whomever takes over control (currently there is no-one) has some hope of maintaining it.

I have been toying with the idea of enlisting the services of a "real" developer to clean it up and make it more future proof.

When I started this project, many years ago, I was learning on the fly and had no idea how indefensible it would become to the organisation.

[Gicu]

Hi sheusz,

I know what you mean by "growing old" with a project, had quite a few of those...

Orphan code is what gets left in the form's class module after you delete a control that had VBA code attached to one or more of its events. That causes bloat and especially in a VBA project that hasn't been cleanly compiled in a while like yours is it can lead to VBA project corruption. There were also some other lines of code that were really needed (quite a few Docmd.Close acTable when the table was not actually open), the project would probably need a good review and scrubb.

Cheers,
Vlad

[sheusz]

Hi sheusz,

...the project would probably need a good review and scrubb.

Hi Vlad

That looks like a job for a rainy weekend. I don't suppose that there is a nifty piece of software out there that will do it for me?

As to the problem at hand, your solution did resolve the issue. Once I saw what you did I could actually understand what you were getting at.

Thank you again for your tutelage and insights.

Very much appreciated.

Stephen.

[Gicu]

Hey Stephen, you're very welcome. And unfortunatelly I am not aware of any software that would do that, it would probably be the developer's best friend....

I would start by removing all the old and unused objects (tables, queries, forms,...) then review the ones left and clean the code. I strongly suggest adding Option Explicit on top of all VBA modules as it will force you to properly declare and use the variables. You can set this in the VBA project options (Require Variable Declaration option) but it will kick in for new ones, for the existing ones you need to add it manually.

Then decompile the project and try to re-compile it. Work through all the compile errors until you're successful.

I would also review the linked tables as some I think should be local (the paste and import errors, even the main ExcelImport one as that would allow multiple users to import different Excel files without interfering with one another).

Cheers,
Vlad

[sheusz]

Hey Stephen, you're very welcome. And unfortunatelly I am not aware of any software that would do that, it would probably be the developer's best friend....

I would start by removing all the old and unused objects (tables, queries, forms,...) then review the ones left and clean the code. I strongly suggest adding Option Explicit on top of all VBA modules as it will force you to properly declare and use the variables. You can set this in the VBA project options (Require Variable Declaration option) but it will kick in for new ones, for the existing ones you need to add it manually.

Then decompile the project and try to re-compile it. Work through all the compile errors until you're successful.

I would also review the linked tables as some I think should be local (the paste and import errors, even the main ExcelImport one as that would allow multiple users to import different Excel files without interfering with one another).

Cheers,
Vlad

Hi Vlad

Thanks for the suggestions. I'll add them to my to-do list!