Combo Box VBA coding help required

[myem1983]

Dear All,

Enclosed please find my database.
I need your help on followings:

1) If in form "frrm_BizFile" I select anything in "Product" then in "Mode" following results should appear.
If selected AE then Mode only reflects Air


If selected AI then Mode only reflects Air
If selected OE then Mode only reflects FCL, LCL, Project
If selected OI then Mode only reflects FCL, LCL, Project
If selected Trucking then Mode only reflect Moves
If selected CHB then Mode only reflects File

2) If in form "frrm_BizFile" I select in Product then POL and POD should also reflect as follows:
If selected AE then POL should not be allowed to select any other country other than Pakistan.
If selected AI then POL should not be allowed to select any city of Pakistan.
If selected OE then POL should not be allowed to select any other country city except Pakistan.
If selected OI then POL should not be allowed to select any city from Pakistan country.

If selected CHB then it allow to select POL of any country.
If selected Trucking then it allow to select POL of any country.

Similarly,
If selected AI then POD should not be allowed to select any other country other than Pakistan.
If selected AE then POD should not be allowed to select any city of Pakistan.
If selected OI then POD should not be allowed to select any other city except Pakistan.
If selected OE then POD should not be allowed to select any city from Pakistan country.

3) If product is selected AI or AE and Mode is selected as AIR then TEU becomes inactive. While KGS and CBM can accept input.
If product is selected OE or OI and Mode is selected as LCL then TEU becomes inactive, while KGS and CBM can accept input value.
If product is selected OE or OI and Mode is selected as FCL then TEU becomes active, while CBM become inactive and KGS is optional.
If product is selected OE or OI and Mode is selected as Project then TEU, CBM and KGS remain active to accept input value.
If product is selected Trucking and Mode is selected as Moves then TEU, CBM and KGS remain active to accept input value.
If product is selected CHB and Mode is selected File then TEU CBM and KGS remian active to accept input value.

Rates Tracker_27-12-2018.zip

[ssanfu]

Note: the top two lines in EVERY code module should be

Option Compare Database
Option Explicit

1) The combo box "cboMode" Row source would be

SELECT tbl_Mode.[Mode ID], tbl_Mode.[Mode Name], tbl_Mode.[Product Code]
FROM tbl_Mode
WHERE (((tbl_Mode.[Product Code])=[forms]![frrm_BizFile].[cboProduct]))
ORDER BY tbl_Mode.[Mode Name];

Need VBA in the after update event of combo box "cboProduct"

Private Sub cboProduct_AfterUpdate()
    Me.cboMode.Requery
End Sub

Still thinking/working on 2 & 3

[ssanfu]

In table "tbl_BizFile", I had to set the property "Allow Zero Length" to YES for fields POL & POD.

If selected CHB then it allow to select POL of any country.
If selected Trucking then it allow to select POL of any country.

Are there conditions like the above for POD?





See if this is close.... Do LOTS of testing...

[myem1983]

Dear Steve,
Your provided code for cboProduct (after update) and cboMode source are working perfectly fine!!
Its a magic.

Regards,
Yousuf

[myem1983]

Dear Steve,

Your updated file is breathtaking!!!! :-D
You have done 1, 2 and 3 WOW!!!!
I'm seriously thankful to you for this support!!!

After posting here I made few forms in my original database but now I've deleted my original file and I've created same forms in your file which is now acting as original file for me.
Because your file contains a lot of coding in VBA.... phew!!

Honestly I had no idea about MS Access or VBA - I'm found with MS Excel and have some good command on Excel, but after using MS Access I see there is a huge potential.
What we were doing on excel was child-play lolzzz... Access is so powerful. wow!!

I'm still learning and reading about access & vba - I'm novice even anything before novice.

But the support and welcome from the community is excellent!!
Seriously beyond my expectation - this has encouraged me to work more on MS ACCESS.

Thank you for the support!!!

[myem1983]

Hi,

Sorry I messed up the earlier file which you changed and updated in this thread.
MAHI.zip
got messed up - my database and file was not responding properly so i planned to make a new one and tried to copy the codes which you did for me but looks like its not working
specially following parts:

frm_BizFile
combo box Mode is not dependent on Product
When AI or OI etc are selected then relevant POL and POD are not appearing (hope you remember I m mentioned when OI, AI (imports) are selected then POL should not reflect Paksitan ports).
in the same form if any customer is added or product or anything is added the same is not refelecting in updatd combox list? can you please assist.

Appreciate your help Steve!

[ssanfu]

combo box Mode is not dependent on Product

Remove the criteria from the Row Source query for cboMode.

When AI or OI etc are selected then relevant POL and POD are not appearing (hope you remember I m mentioned when OI, AI (imports) are selected then POL should not reflect Paksitan ports).

This seems to be working......

in the same form if any customer is added or product or anything is added the same is not refelecting in updatd combox list

Customers seem to be updating properly.
For the product combo box on frm_BizFile, open the form frm_Product and add this code

Option Compare Database    '<<-- make sure these two lines exist in every module!!
Option Explicit            '<<-- make sure these two lines exist in every module!!

'add this sub
Private Sub Form_Close()
    Forms!frm_BizFile.cboProduct.Requery
End Sub

In the IDE for "frm_BizFile", remove spaces in every instance of "PortName" and "PortCountry" in the code.


I have to say I think you are in for a rough time because your table designs and relationships need a lot of work. Look at the relationship window for MAHI.accdb (just posted) and the relationship window for the dB I posted in Post #3.

I don't think you understand what PK and FK fields are used for. You have
"tbl_BizFile" linked to "tbl_RateInput" on "RefNo". Then "tbl_RateInput" is linked to "tbl_BizFile" on fields "Product" and "Mode". Not a good design....




Good luck with your project.......

[myem1983]

Remove the criteria from the Row Source query for cboMode.
I did but then if I select product as AI then in mode only AIR should appear or likewise if any other product is selected then its relevant Mode should appear only.

This seems to be working......
Yes correct,

For the product combo box on frm_BizFile, open the form frm_Product and add this code
Done

In the IDE for "frm_BizFile", remove spaces in every instance of "PortName" and "PortCountry" in the code.
I'm sorry I didnt get your point what do you mean by IDE?
Yes I removed the spaces from Port Name and Port Country....

I don't think you understand what PK and FK fields are used for. You have
"tbl_BizFile" linked to "tbl_RateInput" on "RefNo". Then "tbl_RateInput" is linked to "tbl_BizFile" on fields "Product" and "Mode". Not a good design....

Yes sir, you observation is correct - I'm just trying to make this happen and running.... as admitted in the beginning Access is a whole new world - I tried reading some books to have basic understanding about form and table design but despite of attempt I'm not able to design things simple and clear and resulting a mess up situation.... really having hard time to cope with the things when so much is happening in your domestic life.... Anyway, I'll still try to explain how cycle starts.

a) we receive inquiry which needs to go into Biz File > from that inquiry in biz file we have to procure rates which needs to be recorded in Rates Input file.
b) If inquiry in biz file is secured / win then status will be changed.
c) after this I've to make a follow-up sheet or table where shipments which are secured will be recorded and milestones will be updated in the form of check boxes or radio button with dates.

But I'm still struggling with A & B.

It is not necessary that we will procure or record rates when an inquiry will come, for basic regions we will be obtaining rates which will be recorded in rates input so at any given point of time if anyone need to see what are the rates and if they are available then they can quote to customer.

Likewise if anyone of us receives any inquiry from customer or sales and rates are already procured for some other customer we dont need to reach out to our source twice for the same rate.

Idea of biz file is to have a track for which area, product and mode we are getting more inquiry and out of what ratio we are winning and lost etc.

All these fields are interlinked with each other so please suggest how should I design my tables or forms so relationship is LEFT to RIGHT in smooth way?

I really want to do all this with my hands so that I can learn something better and innovative.
You mentioned in one of your post that Vlookup wizard are disaster but I'm not able to understand how else then a field will query from another table?

I'm sorry for taking so much of your time.

Sincere regards,
Yousuf