VBA help needed

[ramez75]

Hi,

So I have this database and I am trying to create a way to update a subform using criteria from the combox in the form. I have been thinking about how to go around accomplishing this and couldn't make it work using the queries (I might be wrong). I believe a VBA of some sort might be required to accomplish what I need to be done.

So let me start by explaining what I have. I have 2 tables linked to my database using ODBC. So I have 2 append queries to download the data into my 2 tables below.

Table 1 has:
- CPN
- ITEM_NBR
- FIRST_COMPONENT

Table 2 has:
- ID
- CP_PN
- CP_ITEM_NBR
- NEXT_COMP_NBR
- ITEM_NBR

What I am trying to create is a 3rd table which will house the parent item number along with the corresponding child item numbers. Below is an example of how Table 3 should look like

Table 3
PARENT_NBR CHILD_NBR
12345 34567
12345 g6739
12345 0896756
12345 9677383
98765 674937
98765 673938
98765 34567
etc...etc....

So this is were it gets tricky and complex on how to make table 3 by using table 1 & 2

Step1. So take an "ITEM_NBR" (PARENT_NBR) from Table 1 and combine the numbers found in the columns "CPN" and "FIRST_COMPONENT" that corresponds to the chosen "ITEM_NBR", this number will be used to search the "ID" column in table 2.

Step2. Once we have a match then we will copy the value in "CP_ITEM_NBR" in table 2 to be the first "CHILD_NBR" in table 3

Step3. At the same time you take the number in "CP_CPN" column in table 2 that corresponds to the "CP_ITEM_NBR" and use it to search the "CPN" column in Table 1 to see if there is a value/number in the "FIRST_COMPONENT" field corresponding to the "CPN" number you used.

Step4. You again take the "CPN" used in Step3 and combine it with the corresponding "FIRST_COMPONENT" (if it is not NULL) number to create a number that will be used to search the "ID" column in table 2.

Step5. Once we have a match then we will copy the value in "CP_ITEM_NBR" in table 2 to be the next "CHILD_NBR" in table 3

Step6. Repeat Step3, Step4 and Step5 till you reach a point where the "FIRST_COMPONENT" field in Table 1 is NULL and you cant combine the numbers per Step4.

While doing all the above steps the VBA should be also doing the below steps

Step7. VBA to check "NEXT_COMP_NBR" field in Table 2 if NULL. If not Null then do the following"

Step8. Take first 5 digits of "NEXT_COMP_NBR" and search the "CP_CPN" column in Table 2. IF match found then copy corresponding "CP_ITEM_NBR" number to be the next "CHILD_NBR" in table 3

Step9. Repeat Step8. till "NEXT_COMP_NBR" is NULL. Stop

Once all the above steps are completed that means the "PARENT_NBR" has all the corresponding "CHILD_NBR" in table 3

Hope my explanation make sense.

Any light or help on this is greatly appreciated

Thanks

RB

 

[gemma-the-husky]

why do you need a third table?

you can always just use a query with the details you were going to save in table 3.

 

[ramez75]

How can I add all the criteria I mentioned into the query. I couldn't wrap my head around it

why do you need a third table?

you can always just use a query with the details you were going to save in table 3.

 

[gemma-the-husky]

I don't understand the concept of "next item number". This is probably the mistake within access. In a database there is NO next item.

given this

PARENT_NBR CHILD_NBR
12345 34567
12345 g6739
12345 0896756
12345 9677383
98765 674937
98765 673938
98765 34567


the parent table holds this
PARENT_NBR
12345 other details
98765 other details

the ITEM TABLE holds this
CHILD_NBR MAIN ITEM NUMBER
34567 12345 other details
g6739 12345
0896756 12345
9677383 12345
674937 98765
673938 98765
34567 98765

there is no "next" item

the main item 12345 has 5 sub items
the main item 98765 has 3 sub items

there are techniques for reviewing individually the sub items relevant to a main item, but there is no "next" item as such. It is important to understand this.

ie. the "next" item to 34567 in the sub item table can be any other item in the table, depending on how you choose to identify the next item.

 

[gemma-the-husky]

do you mean that you want a single field identifying the item numbers relevant to the main item?

ie main item 1: subitems 123, 234, 456, 789

this can be dome, although it isn't relational - but you wouldn't want to store this in a third table. you would evaluate it every time you needed it.

 

[ramez75]

The "NEXT_COMP_NBR" is the name of the column in the ERP system that I am linked to using OBDC. I am using an Append query to download all the required columns into my database

So the output I am hoping to accomplish whether using a QUERY or VBA is to have a column that list the main item and the second column listing all the corresponding subitems to that main item. The Query or VBA will be linked to the FORM with a Button that each time I chose a main item from the combox I click on the button and the subform is populated with the appropriate subitems

Thanks

RB

do you mean that you want a single field identifying the item numbers relevant to the main item?

ie main item 1: subitems 123, 234, 456, 789

this can be dome, although it isn't relational - but you wouldn't want to store this in a third table. you would evaluate it every time you needed it.

 

[The_Doc_Man]

At first blush, because it is coming from a data source that (a) you can't control and (b) does not appear to be "traditionally" normalized, I think I agree that you will need VBA to do this. However, this is something for which we on this forum might have trouble offering advice because it involves a concept that is not normally part of Access.

You are acting like the tables have a sequential order that is predictable. In Access, table order is predictable only if you open that table with a query that includes an ORDER BY clause based on a field in that table. If the key that provides the table's order isn't visible, then in Access there is no guaranteed order of appearance of any record. To then talk about the next record is the same as like talking about the quotient of a division by zero. The result of either situation is an undefined/undefinable quantity.

What that means in practical terms is that without our ability to see the structure of the REAL data source (not what you wanted to see, but rather what you have to pick apart when examining your source), we will be very much handicapped in telling you how to do what you requested.

IF you can build queries for each of the tables you need to examine THEN you could use VBA to do .MoveNext or .FindFirst or whatever other actions are required to find the records of interest. However, those queries will have to somehow capture the essence of the order of appearance intended for the records in question.

 

[ramez75]

"The_Doc_Man" I agree with you I cant control the database that have the data and hence that's why I appended the columns that have the data that pertains to what I need accomplished. I also figured out the sequence/logic for how to get the output but I needed help in writing the vba.

I will attach a sample database maybe that will help show what I am trying to accomplish

Thanks

RB

 

[ramez75]

I have attached a sample of the database. As you can see my goal is to choose the parent item number from the combox and once I click on the button in the subform I will populate it with the subitem numbers using the logic I explained in post #1

Table 1 and Table 2 are created by append queries.

Hope that clarifies what I am trying to do

Thanks

RB