Creating a userform that links to a main table plus sub table

[neilsolaris]

Hi,

I created a spreadsheet previously, that uses VBA to create an invoice in PDF format from the data submitted in an Excel userform. One of the fields in the userform is a drop down list of suppliers (originating from an Access database table), and the address automatically appears in the invoice, based on the selected supplier. Just to clarify, I've linked the spreadsheet to Access, so that the supplier table is also on the spreadsheet itself.

That all works perfectly. However, it recently transpires that one of the suppliers is prone to have various addresses, even though their company name remains the same. The problem is, if I update the address on the table, the previous invoices stored on the Access database are going to be inaccurate.

So I'm thinking of creating a sub table in Access (called t_SupplierAddresses or something), whereby suppliers can have more than one address assigned to them.

So my question is, if I have two tables, the main supplier table and the sub table for the addresses, is it possible for the Excel userform to link to both tables? So that if I select, say, supplier A, all addresses for supplier A appear in another drop down menu, for me to select the correct one from?

If it is possible, can you explain broadly the approach I should take please? I'm happy to do the research afterwards, I just need a point in the right direction!

I hope I explained my question clearly, and many thanks in advance.

[JohnTopley]

There is no reason why you cannot link to multiple tables: I assume the logic is look in Main table and then check if there any addresses in the sub-table for your selected supplier. Combine these to produce your drop down.

Is there any merit (from a business perspective) in "tagging" addresses as Main or Subsiduary ?

So it appears to be a simple import of your new table from Access to Excel with appropriate "lookups".

[neilsolaris]

Many thanks John, that's good to know it's possible. I'm not home now, but I'll have a look into that when I'm back.

There is no reason why you cannot link to multiple tables: I assume the logic is look in Main table and then check if there any addresses in the sub-table for your selected supplier. Combine these to produce your drop down.

Is there any merit (from a business perspective) in "tagging" addresses as Main or Subsiduary ?

So it appears to be a simple import of your new table from Access to Excel with appropriate "lookups".

[beyond Excel]

Hello:
Think of the two sheets you see in the attached workbook as two tables in your Access.

For that reason, the macro that contains the Userform aims to treat the data as if it were taken from an Access file.

Since the userform mode is 'False', you can select one sheet or another while the userform is displayed:

Please Login or Register  to view this content.

[beyond Excel]

And here's a variant that includes having a primary address:

[neilsolaris]

Hello:
Think of the two sheets you see in the attached workbook as two tables in your Access.

For that reason, the macro that contains the Userform aims to treat the data as if it were taken from an Access file.

Since the userform mode is 'False', you can select one sheet or another while the userform is displayed:

Please Login or Register  to view this content.

Hi boyond Excel, I'm very sorry for my slow reply, I was out the house until very late, and only just noticed your post now. That's really great, many thanks for that. I'll have a look through the code now, and see how I can apply it to mine. I'll let you know how I get on, and if I get stuck along the way.

Thanks again.

[beyond Excel]

Go ahead, start and try - that's the only way to learn!

[neilsolaris]

Hi Beyond Excel,

I'm very slowly working through your code to try and understand how it works. I'm concentrating on the first one so far (the one without the primary address), as I think that's probably simpler.

On first glance, it looks like you use a different and much better method that what I do, to populate the fields in the userform. I would have entered the location of the data in the RowSource box in the userform properties box. But it looks like you've done it by way of "initialize", is that right?

I'm also a little bit new to Sql, but I understand it a little. Did you use a bit of Spl in the code?

The other thing I was confused about, is that you have a variable called Rst, which you listed in the declaration, but didn't declare a variable type as such, is that right? I was wondering what the reason for that is?

Many thanks for your help. I think once I can fully understand how this works I'll have a better chance of applying it to my spreadsheet!

[beyond Excel]

Make sure you read and understand the content of the following website:

Open Method (ADO Recordset)

[neilsolaris]

Make sure you read and understand the content of the following website:

Open Method (ADO Recordset)

Thanks, I'll read that now.