Dynamic field in VBA

[Jrod2541]

Back again,

I posted a question similar to this yesterday, however all the vendors had there own tables, and since I plan on this database being expandable, the design had to change.

So now I have all my vendors in a single table (as suggested), but I need to know how to create a dynamic field in VBA or maybe SQL if possible.

I'm a complete amateur when it comes to VBA and SQL, I've just started learning about databases for about two weeks, so I'm not sure where else to start. If you see something off or messy/unorganized, please tell me.



In this table, called "AllVendors ", I have a several fields named: [VENDOR 1], [VENDOR 2], [VENDOR 3], [VENDOR 4] and [VENDOR 5].

I need a module that will look at the field called [VENDOR 1] and take whatever's in there and check the field, in the same table, [(whatever [VENDOR 1] is) CAN SHIP SAME DAY].

If the field says "Yes" then it will update the blank field [VendorOrder], again all in the same table, to say "(whatever is in [VENDOR 1]).

If the field says "No" then it will move on to [VENDOR 2] and so on...





To rephrase, I need this module to check a table named [AllVendors] and inside this table, look for a field named [VENDOR 1] and let's say inside [VENDOR 1] it says "ASDF".

The module will then search for a field in the same table named [ASDF CAN SHIP SAME DAY].

Inside this field, the module will check if the cell says "Yes".

If it does say "Yes", it will update the field [VendorOrder] to say "ASDF".

If inside the field [ASDF CAN SHIP SAME DAY] it says "No", the module will look at the [VENDOR 2] field and take the vendor inside there, let's say it is "HJKL".

Now the module will look for the field named [HJKL CAN SHIP SAME DAY] and if it says "Yes", add the value "HJKL" to the field named [VendorOrder].

If, however, [HJKL CAN SHIP SAME DAY] says "No", the module will look at [VENDOR 3].

This cycle will repeat until [(whatever the vendor is) CAN SHIP SAME DAY] says "Yes" or when the process reaches [VENDOR 5].



Thanks for any advice or comments

[Rabbit]

Did you read that article that was linked in your other thread?

You shouldn't design your table like this. This is only slightly better than when you had everything split out into multiple tables.

From the little information you have provided, your table should only have two fields, VendorName and CanShipSameDay. There's no need to have separate fields for each vendor and a separate field for whether or not that vendor can ship on the same day.

Please read that article that was linked. Then do a data model to design all the tables and fields you will need. Create a thread as you run into questions on how the model should look.

You should not be designing any code or queries or forms before you have a properly designed data model. You should not have to write custom code to find a dynamic column or table in a properly designed model.

[twinnyfo]

Jrod2541,

Good job for putting your vendors in one table. That's a good first step.

However, I say this trying to be very polite and respectful, but your question makes no sense at all (it could simply be my not understanding what you are asking).

Just on the surface, here are some questions:

Do vendors 1-5 all have the capability to ship the same day?

If only one ships the same day, then just look for the one that does so?

What happens if Vendor 4 and Vendor 5 BOTH ship the same day? You will always choose vendor 4 because it is first?

On the construction side, you have moved from having five tables for five vendors to having one table with five columns, one for each vendor, which is essentially having five tables again.

(I think) what you want is this: One table, with a Field for the Vendor Name, a Field to indicate whether it ships the same day (which should be a Yes/No checkbox field. You would also add other pertinent information in the table for address, contact info, etc.

This way, if you are looking for same day shipping, your query is as simple as:

SELECT * FROM tblVendors WHERE SameDayShipping;

This would give a list of all Vendors that do same day shipping. But, if there wre more than one, how do you choose?

I think we need a little more information on exactly what you are trying to do. However, if what I have described is what you are looking for, then we can move forward from that point and get you started in that direction.

[Jrod2541]

Sorry for the confusion.


I didn't mention this earlier, but I have an ID line field (I'm not sure if this is at all helpful)


I have other fields in this table I didn't mention as well, including the [SKU ITEM #] and the [(Vendor) ITEM#] fields that are linked to each [SKU ITEM #].



So, for example, I'm going to say there are 5 vendors: ASDF, HJKL, QWOP, ERIO and CVBN.

Each vendor has their own item # that link to the SKU ITEM #. So let's say under [ASDF ITEM #] there is 4512, 7894, 5612 and 7847.

And let's say those vendor items connect with the SKU ITEMS: 1234, 1235, 1236, 1237.


Under [HJKL ITEM #] it has: AB-45, SF-95, FR-78, TH-12 and BP-47 which connect to [SKU ITEM #]: 4875, 4861, 1231, 7457 and 7456.

There is a single field for [SKU ITEM #] while there is a field for each vendor for their vendor item #s: [QWOP ITEM #], [ASDF ITEM #], [HJKL ITEM #], [CVBN ITEM #] and [ERIO ITEM #].

Some SKU ITEM #s link with multiple vendor item #s

Some values in the [(Vendor) ITEM #] fields are empty because sometimes the vendor doesn't have the specific item, while the SKU ITEM # field is completely filled because those are the items we want to order.

The SKU ITEM #s are the items we order and how we organize the items

The vendor item #s are the item numbers of the items that they have that and we want to order




vendors 1-5 are ordered in priority, the higher the number, the LESS we want to order from them. the smaller the vendor number the MORE we want to order from them.

If VENDOR 4 and 5 can ship on the same day, we'll order from VENDOR 4 because that's the more preferred vendor



There are multiple vendors in each of those fields. For example under [VENDOR 1] it might say: ASFD in ID line 1, HJKL in ID Line 2, QWOP in ID Line 3 and so on.


~Not all vendors have the capacity to ship on that day for that item.


So the module would need to check ID line 1, look at whatever's in [VENDOR 1], and check the field [(VENDOR 1) CAN SHIP SAME DAY]


If only one vendor can ship that item on this day, then we order from that vendor.



After the module figures out which vendor we prefer most and if it can ship on that same day, we update the [VendorOrder] field for the ID line #



If it's easier to send a screenshot of the table I can do that as well.


Hopefully this cleared up some confusion, otherwise ask me any questions you need.

Is this poorly organized? I just put this together so I could have all the vendors on a single list along with who to order from.


The [VendorOrder] field is not what I send to the vendor, it is just so I know which vendor to order from for each item I want to order.

[Jrod2541]

The problem I need help with, after I figure out the design, is what would I need to put into my module so that it updates the [VendorOrder]

[twinnyfo]

Hopefully this cleared up some confusion, otherwise ask me any questions you need.

This post made my head hurt! Have you looked at this topic: Database Normalization?

Yes, both Rabbit and I are pushing this one hard for you. You need to come up with an organized plan for how this is all going to work with your tables, otherwise we cannot help you out on this.

I wouldn't start to advise on your modules when your tables are in such disarray.

[Jrod2541]

alright, I'm just going to keep working on it, I'll probably be back with more questions.

Thanks for the advice

[twinnyfo]

We can still help, but I think you need to work piece by piece. Right now, we are trying to eat an elephant. Give us just one bite (byte) at a time!

:-)

[Rabbit]

From what I can glean, you'll want 3 tables instead of the one. A vendor table with one row per vendor. An item table with one row per item. And a VendorItem table with one row per item per vendor.

No vendor specific fields. No vendor specific tables.

[Jrod2541]

Ok, I know you didn't ask for this but here's a snapshot of a sample table with 4 made-up vendors, I don't think I explained the table well enough earlier but perhaps this will help you further understand:

http://prntscr.com/3wfz6m

If not, I'll look back at the article and try again.

Thanks for your support

[Jrod2541]

Thanks Rabbit, I'll come back later with three tables


EDIT: I'm not exactly sure how to make a table for Vendor item #s without making Vendor specific fields because some SKU ITEM #s link with multiple vendor Item #s.
Should I just make a list of all the vendor item #s in a single field?
How would I link that field with the sku item #s?

[NeoPa]

Check that article again. If you still don't know then we'll get into some more detail for you (Database Normalisation and Table Structures).