VBA中的动态字段 [英] Dynamic field in VBA
问题描述
再次回来，
我昨天发布了一个与此类似的问题，但是所有供应商都有自己的表格，而且由于我计划这个数据库是可扩展的，所以设计必须更改。
所以现在我将所有供应商放在一个表中（如建议的那样），但我需要知道如何在VBA中创建动态字段，或者如果可能的话，可能需要SQL。 br />
当谈到VBA和SQL时，我是一个完全的业余爱好者，我刚刚开始学习数据库大约两周，所以我不知道还能从哪里开始。如果您看到某些内容或杂乱/无组织，请告诉我。
在此表中，名为AllVendors，我有几个名为的字段：[供应商1]，[供应商2]，[供应商3]，[供应商4]和[供应商5]。
我需要一个模块，它将查看名为[VENDOR 1]的字段并取出其中的任何内容并检查字段，在同一个表中，[（无论[VENDOR] 1]是）CAN SHIP SAME DAY]。
如果字段显示是然后它将再次更新空白字段[VendorOrder]，同样在同一个表格中，以表示（[供应商1]中的任何内容）。
如果字段显示否]那么它将继续[供应商2]等等...
要改写，我需要这个模块检查名为[AllVendors]的表并在此表中，查找名为[VENDOR 1]的字段，让'在[VENDOR 1]中说出ASDF。
然后，模块将在名为[ASDF CAN SHIP SAME DAY]的同一个表中搜索一个字段。
在此字段内，模块将检查单元格是否显示是。
如果确实说是，它会更新[VendorOrder]字段以说出ASDF。
如果在里面字段[ASDF CAN SHIP SAME DAY]它表示No，模块将查看[VENDOR 2]字段并将供应商带到那里，让我们说它是HJKL。
现在模块将查找名为[HJKL CAN SHIP SAME DAY]的字段，如果显示是，则添加值HJKL。到名为[VendorOrder]的字段。
但是，如果[HJKL CAN SHIP SAME DAY]显示No，则模块将查看[VENDOR 3]。
这个循环将重复，直到[（无论供应商是什么）CAN SHIP SAME DAY]说是。或当流程达到[供应商5]时。
感谢您的任何建议或意见
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
推荐答案
您是否读过在其他帖子中链接过的文章？
你不应该像这样设计你的桌子。这比将所有内容分成多个表时要好一些。
从您提供的小信息中，您的表应该只有两个字段，VendorName和CanShipSameDay。不需要为每个供应商提供单独的字段，也不需要单独的字段来确定该供应商是否可以在同一天发货。
请阅读链接的文章。然后做一个数据模型来设计你需要的所有表格和字段。当您遇到关于模型应该如何看待的问题时创建一个线程。
在设计正确的数据模型之前，不应该设计任何代码或查询或表单。您不必编写自定义代码来在正确设计的模型中查找动态列或表。
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.
Jrod2541，
放置好的工作您的供应商在一个表中。这是一个很好的第一步。
然而，我说这是非常有礼貌和尊重的，但你的问题根本没有意义（可能只是我不理解什么你问的问题。
表面上看，这里有一些问题：
供应商1-5都有 能力 当天发货？
如果只有一天在同一天发货，那么只需寻找那样做的那个？
如果供应商4和供应商5 BOTH 在同一天发货会怎样？您将始终选择供应商4，因为它是第一个？
在构造方面，您已经从五个供应商的五个表变为一个包含五个列的表，每个供应商一个，它本质上又有五个表格。
（我认为）你想要的是这样的：一个表，一个供应商名称的字段，一个表示它是否发货的字段同一天（应该是是/否复选框字段。您还可以在表格中添加其他相关信息，以获取地址，联系信息等。
这样，如果您正在寻找对于当天发货，您的查询非常简单：
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:
很抱歉这个混乱。
我之前没有提到这个，但我有一个ID行字段（我''我不确定这一点是否有用）
我在这个表中还有其他字段我也没提到，包括[SKU ITEM＃]和[（供应商）项目＃]字段链接到每个[SKU项目＃]。
所以，例如，我要去说有5家供应商：ASDF，HJKL，QWOP，ERIO和CVBN。
每个供应商都有自己的项目＃，链接到SKU ITEM＃。所以，在[ASDF项目＃]下，有4512,7894,5612和7847.
让我们说这些供应商项目与SKU项目有关：1234 ，1235年，1236年，1237年。
在[HJKL项目＃]下，它有：AB-45，SF-95，FR-78，TH-12和BP- 47连接到[SKU ITEM＃]：4875,4861,1231,7457和7456.
[SKU ITEM＃]有一个字段，每个字段都有一个字段供应商的供应商项目#s：[QWOP项目＃]，[ASDF项目＃]，[HJKL项目＃]，[CVBN项目＃]和[ERIO项目＃]。
一些SKU ITEM #s链接多个供应商项目#s
[（供应商）项目＃]字段中的某些值为空，因为有时供应商没有具体项目，虽然SKU ITEM＃字段已完全填满，因为这些是我们要订购的商品。
SKU ITEM #s是我们订购的商品以及我们如何组织商品
供应商商品#s是商品编号他们拥有的物品和我们想订购的物品
供应商1-5优先订购，数量越多，我们想要从他们那里订购。我们想要从他们那里订购的供应商数量越小。
如果供应商4和5可以在同一天发货，我们将从供应商4订购，因为''更受欢迎的供应商
每个领域都有多家供应商。例如，在[VENDOR 1]下，它可能会说：ID行1中的ASFD，ID行2中的HJKL，ID行3中的QWOP等等。
~Not所有供应商都有能力在当天发货。
因此模块需要检查ID行1，查看[供应商]中的任何内容1]，并检查字段[（供应商1）CAN SHIP同一天]
如果只有一个供应商可以在这一天发货，那么我们从那个订单供应商。
在模块确定我们最喜欢哪个供应商以及它是否可以在同一天发货之后，我们更新[VendorOrder]字段ID行＃
如果发送表格截图更容易，我也可以这样做。
希望这可以解决一些困惑，否则问我任何你需要的问题。
这组织得不好吗？我把它放在一起所以我可以将所有供应商放在一个列表中以及订购者。
[VendorOrder]字段不是我发送到的字段供应商，我只知道要为我要订购的每件商品订购哪家供应商。
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.
这篇关于VBA中的动态字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
