By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,479 Members | 988 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,479 IT Pros & Developers. It's quick & easy.

generating records in subform-vba code

P: 27
hi guys,
could you please help me to write a VBA code that will allow to generate all records from parent form in one subform.
I created a form which generates the product code
ABCDE
Each A,B, C,Dand E has a drop down list with different options (1,2,3,4)but each 1, 2, 3,4 has a list of Vcodes(i.e option1 has V102,v103 andV123)
I would like to have full list of Vcodes in one subform when chosing one of the option) Iknow it's complicated but possible:)
form name:Part Number,
subform name: V Codes

any ideas would be greatly appreciated

thnx
magda
Aug 21 '08 #1
Share this Question
Share on Google+
12 Replies


FishVal
Expert 2.5K+
P: 2,653
Hello.

I could recall a similar thread. I'm going to attach to this thread a sample db implementing the solution.
The solution (based on table outer join) has an advantage that new records actually are being created only upon entering data - where is no need to clear unused entries after user input.

The code was preliminary tested only so I could not guarantee it 100% robust.

Regards,
Fish
Attached Files
File Type: zip LJ Form.zip (77.6 KB, 59 views)
Aug 21 '08 #2

P: 27
I'm afraid that my db is a bit more complicated .the main form has seperate fields A, B, C , D when each field has a dropdown list with choice of 1,2,3,4 options and each option 1,2,3,4, has allocated Vcodes .
The idea is to have all Vcodes in one subform when chosing the options from main form.ehh knew it would be hard to explain...
Aug 21 '08 #3

FishVal
Expert 2.5K+
P: 2,653
I'm afraid that my db is a bit more complicated .the main form has seperate fields A, B, C , D when each field has a dropdown list with choice of 1,2,3,4 options and each option 1,2,3,4, has allocated Vcodes .
The idea is to have all Vcodes in one subform when chosing the options from main form.ehh knew it would be hard to explain...
The example shows a general approach to create record slots using table outer join on 3 table many-to-many relationship. I'm sure your db is bit more complicated. ;) Regardless that I'm not sure it couldn't be used in your case.

Your explanation will make more sense if you post metadata for all relevant datasets.
Here is an example of how to post table MetaData :
Table Name=tblStudent
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; AutoNumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
Also RowSource of all comboboxes involved and RecordSource of the form and the subform will be usefull.

Regards,
Fish
Aug 21 '08 #4

P: 27
Tbl Part Number:
ID, Autonumber PK
Platform type, stream
Head orientation, stream
Head finish, stream
Stabilisation, stream
Power supply, stream

Tbl Platform type:
Item PK
Description

Tbl Head orientation:
Item PK
Description

Tbl Head finish:
Item PK
Description

Tbl Stabilisation:
Item PK
Description

Tbl Power supply
Item PK
Description

TblP/N Code
Code PK
Description

Tbl VCodes
Vcode PK
Description
P/N Code

Form :Part Number
Subform : P/N Code Query1 subform (VCode, VCode Description, Code)
Aug 21 '08 #5

FishVal
Expert 2.5K+
P: 2,653
Hello. The information you've provided is not complete.
  • What the "stream" is all about?
  • To make table relationships more clear you should mark what in your tables fields are FKs (I recommend you to post it as in the example below):

    TblP/N Code
    Code PK
    Description

    Tbl VCodes
    Vcode PK
    Description
    P/N Code FK([TblP/N Code])
  • Additionally to the previous point - you should post table metadata exactly as in the provided example:
    Expand|Select|Wrap|Line Numbers
    1. [TableName]:
    2. FieldName, FieldType, IndexInfo
    -where FieldType is datatype of the field including data subtypes
    -where IndexInfo is clear description whether the field is PK/(part of PK), FK/(part of FK) with table name where correspondent PK resides, whether the field is a part of an index including info on the index constraints.
    Example:
    Expand|Select|Wrap|Line Numbers
    1. [tblItems]
    2. keyItemID, Autonumber(Long), PK
    3. keyItemNameID, Long, FK(tblItemNames)
    4. keyShelfID, Long, FK(tblShelves)
    5. lngQuantity, Long, n/a
    6. dblPrice, Double, n/a
    7. dtePurchasedOn, Date, n/a
    8. txtComment, Text(255), n/a
    9.  
  • The most crucial point - relationship between products and codes is not clear. Is there any additional table involved.
  • Subform RecordSource is not clear either, as well as Master/Child relations between form and subform.

Hope you understand the importance of providing clear and inambigious information.

Regards,
Fish
Aug 21 '08 #6

P: 27
Dear Fish,
Please find below requested info:
1. [tbl Part Number]
2. ID, Autonumber, PK
3. Platform Type, Text(255) FK(tbl Platform Type), Combo Box(Select from [Platform Type]
4. Head Orientation, Text(255) FK(tbl Head Orientation), Combo Box(Select from [Head Orientation]
5. Head Finish, Text(255) FK(tbl Head Finish), Combo Box(Select from [Head Finish]
6. Stabilisation, Text(255) FK(tbl Stabilisation), Combo Box(Select from [Stabilisation]
7. Power Supply, Text(255) FK(tbl Power Supply), Combo Box(Select from [Power Supply]

8. [tbl Platform Type]
9. Item, Text(255) PK
10. Description, Text(255), n/a

11. [tbl Head Orientation]
12. Item, Text(255) PK
13. Description, Text(255), n/a

14. [tbl Head finish]
15. Item, Text(255) PK
16. Description, Text(255), n/a

17. [tbl Stabilisation]
18. Item, Text(255) PK
19. Description, Text(255), n/a

20. [tbl Power Supply]
21. Item, Text(255) PK
22. Description, Text(255), n/a

23. [tbl P/N Code]
24. Code, Text(255) PK
25. Description , Text(255) n/a

26. [tbl VCodes]
27. Description, Text(50) n/a
28. Issue, Text(50) n/a
29. P/N Code , Text(50) FK(tbl P/N Code)



Form :Part Number
Subform : P/N Code Query1 subform (VCode, VCode Description, Code)
I didnít specify what Master/Child relation has been set up as donít know what would be the best option.
Hope that makes sense now :) Many thanks for patience.
Aug 22 '08 #7

FishVal
Expert 2.5K+
P: 2,653
Ok.

I guess options ABCDE from the post #1 are those five FKs from [tbl Part Number]. So you want to create a code based on combination of these fields. Am I right? If so, then please tell more about it:
  • It is quite clear that possible combinations of P/N Code and VCode are defined by the correspondent tables. Is there any rule/constraint as for combination of ABCDE values with P/N / VCode pairs?
  • Where the resulting code is supposed to be stored?
  • Why do you want to use subform control? And what is that mysterious subform description is all about? :) If it is a query, then please post SQL of this query.

Regards,
Fish
Aug 22 '08 #8

P: 27
I guess options ABCDE from the post #1 are those five FKs from [tbl Part Number]. So you want to create a code based on combination of these fields. Am I right?

Yes you are absolutely right.


[*] It is quite clear that possible combinations of P/N Code and VCode are defined by the correspondent tables. Is there any rule/constraint as for combination of ABCDE values with P/N / VCode pairs?

There is no rule. Our engineers specify what Vcodes are allocated to each option (each Vcode belong to different A or B or C or D or E[*] Where the resulting code is supposed to be stored?

I thought the best would be form and a table, but may be you will have better sugestion...[*] Why do you want to use subform control?

As when choosing each option from dropdown list subform would show all Vcodes applicable???Is that right???

And what is that mysterious subform description is all about? :)
Don't know ...trying my best..
Is there any chance you can help me please ??

Many thanks,
M
Aug 26 '08 #9

FishVal
Expert 2.5K+
P: 2,653
And what is that mysterious subform description is all about? :)
Don't know ...trying my best..
Is there any chance you can help me please ??
Hello.

I will try my best to help you. :)
Though, so far I'm afraid I don't follow completely the buiseness logic of your database. And the main reason for this is that you are not sure your tables relationships are well or even properly suited.

It seems to me that I was under wrong impression as for relations of [tbl P/N Code] and [tbl VCodes] tables to [tbl Part Number].

Could you explain it more clear?

Regards,
Fish
Aug 26 '08 #10

P: 27
Yes. I will explain to you the purpose of this database.
The potential customer comes to us with an enquiry for particular item.

The idea is to generate the Part Number which will specify exactly the parts it needs to be built from.
So, if I choose a particular Platform Type from dropdown list the subform will show all Vcodes applicable for this option,.Then next if I choose a particular head orientation it will add up more VCodes applicable for this option...Do you know what I mean now...?So the relations between tables are correct, I do not know how to generate all applicable Vcodes in one list .(thought subform would be the best option)...
Aug 26 '08 #11

FishVal
Expert 2.5K+
P: 2,653
Yes. I will explain to you the purpose of this database.
The potential customer comes to us with an enquiry for particular item.

The idea is to generate the Part Number which will specify exactly the parts it needs to be built from.
Could you please clarify what do you mean saying "generate the Part Number".

So, if I choose a particular Platform Type from dropdown list the subform will show all Vcodes applicable for this option,. Then next if I choose a particular head orientation it will add up more VCodes applicable for this option...
Does it mean some Vcodes are not applicable for particular options? If so, then how applicable codes could be distinguished from inapplicable in context of particular option?

Do you know what I mean now...?So the relations between tables are correct, I do not know how to generate all applicable Vcodes in one list .(thought subform would be the best option)...
So many stupid questions from me give an evidence that table structure you've posted is at least insuficient to store all necessary information.

One more stupid question: :)
What is [tbl P/N code] is all about?

Kind regards,
Fish

P.S. I will ask our experts community for help with this thread.
Aug 26 '08 #12

P: 27
You know what.... I think I've obtained enough information ...For the last couple days you have been asking me all those questions and I have got the feeling that we are going round the circles..I give up... will try to srt it out other way...
Aug 26 '08 #13

Post your reply

Sign in to post your reply or Sign up for a free account.