Combo Box Issue (1 Viewer)

StuartG · Nov 17, 2018

Hi all,
Sorry another question from me

In my DB i have combo boxes that returns a value from a particular field, however i wanted to limit the number of results that the Combo box shows, this is because my DB has alot of records and it ends up showing a long drop down box.

It would be handy if it could show the last 5 entries?

Thanks for support.
S.

June7 · Nov 17, 2018

Maybe just set the ListRows property to 5?

StuartG · Nov 17, 2018

June7 said:
Maybe just set the ListRows property to 5?

do you know if this shows the last 5 records or starts from the first record in the DB?

isladogs · Nov 17, 2018

Sort them in reverse order and it will show the last five.

StuartG · Nov 17, 2018

OK, so the limit to list didn't appear to work for some reason..

i attach photos of what i mean.
(Hopefully I've done nothing wrong)

StuartG · Nov 17, 2018

StuartG said:
OK, so the limit to list didn't appear to work for some reason..

i attach photos of what i mean.

(Hopefully I've done nothing wrong)

Excuse the number I put but it was trial and error. I tried 5 and didn’t work..

Sent from my iPhone using Tapatalk

isladogs · Nov 17, 2018

You need to change list rows to 5. Its nothing to do with limit to list
The screenshot isn't very clear but you shouldn't have lots of empty rows.
Check the row course used for this

StuartG · Nov 17, 2018

isladogs said:
You need to change list rows to 5. Its nothing to do with limit to list

Did that Isla and it didn’t work [emoji20]

Sent from my iPhone using Tapatalk

StuartG · Nov 17, 2018

isladogs said:
You need to change list rows to 5. Its nothing to do with limit to list
The screenshot isn't very clear but you shouldn't have lots of empty rows.
Check the row course used for this

Hi Isla.
Thanks I didn’t see the other part. There are some empty fields in the actual data it’s being retrieved from as we don’t always have it available.

In regard to row course I don’t know what this is or how to fix.

Sent from my iPhone using Tapatalk

isladogs · Nov 17, 2018

OK I have that form open in front of me now.

1. Which combo did you want to limit to the last 5 records?
The only one with a lot of items is sales representative

2. Which combo is open in the screenshot with lots of blank records because I can't find it

3. I can see why arnel has used a tblDummy. Its to create an extra dummy record for NEW in the preorder reference combo. Its not a method I would use but it does work.
BUT if you are using that form wouldn't you always want a NEW value?
If so, that could be simplified but it would mean altering arnel's hard work. Up to you really

4. The Create Excel File button will by default create a file in the folder C:\Users\yourusername\documents\PreOrder.
The code used is:

Code:

 saveToFolder = Environ("userprofile") & "\documents\PreOrder"

If you don't want that location, its easy to change.
What would you prefer instead?

Also as you are using Office 2016, would it be better to create .xlsx files rather than .xls?

5. Despite having 27 buttons on your main form, there is no Close button. Any reason?

StuartG · Nov 17, 2018

isladogs said:
OK I have that form open in front of me now.

1. Which combo did you want to limit to the last 5 records?
The only one with a lot of items is sales representative

2. Which combo is open in the screenshot with lots of blank records because I can't find it

3. I can see why arnel has used a tblDummy. Its to create an extra dummy record for NEW in the preorder reference combo. Its not a method I would use but it does work.
BUT if you are using that form wouldn't you always want a NEW value?
If so, that could be simplified but it would mean altering arnel's hard work. Up to you really

4. The Create Excel File button will by default create a file in the folder C:\Users\yourusername\documents\PreOrder.
The code used is:

Code:

saveToFolder = Environ("userprofile") & "\documents\PreOrder"

If you don't want that location, its easy to change.
What would you prefer instead?

Also as you are using Office 2016, would it be better to create .xlsx files rather than .xls?

5. Despite having 27 buttons on your main form, there is no Close button. Any reason?

Great, thanks Isla.

It is the SFDC Opportunity.
Attached screenshot for reference.

I would prefer a new value always, that means when the team open the form its already selected to "new" but only if it is easy to achieve.

4. not sure if i have confused you?
it was more the name of the output file, i was hoping to have it as.

Pre-Order Document - [CPQ Quotation Number] - [Customer Name] - [Main Instrument]

Instead of Arnel current output like the below:

POF-PN-0790225-2018-321-001
Arnel wrote the code per the below:

' preOrder Reference will be in the format:
' POF-[Project Number]-[year]-[julian]-[001]

the download file we receive is outputted as an XLS, so would be better to keep as XLS

When the DB opens it automatically loads this "main Menu form" and the users keep this open in background.
the close buttons are built in each form that the user opens.

isladogs · Nov 17, 2018

OK just to clarify...

StuartG said:
It is the SFDC Opportunity. Attached screenshot for reference.

I can change the row source from what it is now (on the left) to the reduced list on the right

This is the last 5 records excluding any records that are empty or NONE
Do all your values start with PN?

I would prefer a new value always, that means when the team open the form its already selected to "new" but only if it is easy to achieve.

Yes its easy enough to have New appear at Form_Load
Do you still need a drop down for that box.
If not it can just be a textbox with default New but updating when you select the project number (as now)

Code:
4. not sure if i have confused you?
it was more the name of the output file, i was hoping to have it as.

Pre-Order Document - [CPQ Quotation Number] - [Customer Name] - [Main Instrument]

So if the values were as shown below

...then you would want:

Code:

POF-PN-1064055-2018-321-003-CPQ-00156531-Coca-Cola-Ultimate 3000.xls

or did you mean

Code:

POF-PN-1064055-CPQ-00156531-Coca-Cola-Ultimate 3000.xls

in the same specified folder as you have now?

When the DB opens it automatically loads this "main Menu form" and the users keep this open in background.
the close buttons are built in each form that the user opens.

Click to expand...

OK but what about when they want to close the database?

StuartG · Nov 17, 2018

Sure.

Yep this is what i would rather, the combo box always showing the last 5 records in the DB.

in regard to the "new" field, long as the user can select the PN reference and the Quotation and then DB fills the rest out then all good.

In the SFDC all records will start with PN

Not quite in regard to the output file.
We as a team have a standard filename that everyone in the business knows which is

Pre-order - CPQ-00156321 - [Company Name] - {Main Instrument]

I need to drop POF-PN-106455 and replace with Pre-order-CPQ........ - [customer Name] - [main instrument]

Same specified output file is fine.

I had never thought about a button to close the DB, usually the team just click the "X" on the application Window.

isladogs · Nov 17, 2018

StuartG said:
Yep this is what i would rather, the combo box always showing the last 5 records in the DB.

Done

in regard to the "new" field, long as the user can select the PN reference and the Quotation and then DB fills the rest out then all good.
OK so i'll change it to a textbox with New as the default

Not quite in regard to the output file.
We as a team have a standard filename that everyone in the business knows which is
Pre-order - CPQ-00156321 - [Company Name] - {Main Instrument]

I need to drop POF-PN-106455 and replace with Pre-order-CPQ........ - [customer Name] - [main instrument]

So to double check, using the values I showed before, would it be:

Code:

Pre-order-CPQ-00156531-Coca-Cola-Ultimate 3000.xls

I had never thought about a button to close the DB, usually the team just click the "X" on the application Window.

I'll add it for you to consider

Hopefully return this to you tomorrow

StuartG · Nov 17, 2018

isladogs said:
Done

in regard to the "new" field, long as the user can select the PN reference and the Quotation and then DB fills the rest out then all good.

OK so i'll change it to a textbox with New as the default

So to double check, using the values I showed before, would it be:

Code:

Pre-order-CPQ-00156531-Coca-Cola-Ultimate 3000.xls

I'll add it for you to consider

Hopefully return this to you tomorrow

This is great. Appreciate the support.

Sorry could you do the excel name of file to..

Pre-order document - [CPQ Quotation] - [Customer Name] - [Main instrument]

Thanks for the closing idea. Never something I thought of so might be good.. [emoji848][emoji2]

Thanks again Isladog.. [emoji3]

Sent from my iPhone using Tapatalk

StuartG · Nov 18, 2018

StuartG said:
This is great. Appreciate the support.

Sorry could you do the excel name of file to..

Pre-order document - [CPQ Quotation] - [Customer Name] - [Main instrument]

Thanks for the closing idea. Never something I thought of so might be good.. [emoji848][emoji2]

Thanks again Isladog.. [emoji3]

Sent from my iPhone using Tapatalk

Morning Isladog.. [emoji2]

Is there anything I can help with on the above or is it something you would need to complete for me?

Stuart

Sent from my iPhone using Tapatalk

isladogs · Nov 18, 2018

Just finished it.
Changes made to frmPreOrderForm
a) Changed PreOrderReference to textbox with default 'New'
The old combo is still there (hidden) in case of problems
b) Changed row source of ProjectID combo to only show the last 5 records as requested
c) Added message "Creating Excel file. Please wait a few seconds . . ." on the form which is only visible whilst that happens
d) Made several changes to cmdCreateExcel to:
i) Modify the destination file name as requested
I've left Arnel's code (disabled) in case of issues with my new code
ii) Remove the security warning that appeared when opening the Excel file using application.FollowHyperlink

All the above except the last item were very quick & easy to do.
Removing the security warning was a total pain & I wasted a couple of hours looking into that
I did wonder why Arnel had used FollowHyperliink to open a local file.
BUT when I tried running two alternatives, both of which have worked perfectly for me for many years, I got a blank Excel file. :banghead::banghead:
I stubbornly persevered & found an alternative by Allen Browne (GoHyperlink) that worked perfectly & removed the security warning

That took around 2 hours to solve! Grrrr!

The rest of the Excel file stuff is still what arnel provided

I've left all the code in place - anything disabled can be removed after you've checked what you have is working as intended

Hope that helps

StuartG · Nov 18, 2018

isladogs said:
Just finished it.
Changes made to frmPreOrderForm
a) Changed PreOrderReference to textbox with default 'New'
The old combo is still there (hidden) in case of problems
b) Changed row source of ProjectID combo to only show the last 5 records as requested
c) Added message "Creating Excel file. Please wait a few seconds . . ." on the form which is only visible whilst that happens
d) Made several changes to cmdCreateExcel to:
i) Modify the destination file name as requested

Brilliant, thank you for that, greatly appreciated.

Am i able to look at the end result, as there appears to be no attachment

isladogs · Nov 18, 2018

Clicked Send Reply by mistake.
Its there for you now!

StuartG · Nov 18, 2018

isladogs said:
Clicked Send Reply by mistake.
Its there for you now!

The output and file name is great and exactly as I wanted, however i had noticed an issue with the PRE-ORDER form.

When closing the PRE ORDER form and reopening the form, it opens with the previous record information and i am then unable to change anything or create a new record.
Or
Was it left like this because I mentioned that the user would be deleting the record after each creation?

Apologies that you spent quite a few hours resolving the security warning, but i am grateful for what you have completed for me.

Thanks for the exit button, one click and it closes the whole program

Combo Box Issue (1 Viewer)

Registered User.

AWF VIP

Registered User.

MVP / VIP

Registered User.

Attachments

Registered User.

MVP / VIP

Registered User.

Registered User.

MVP / VIP

Registered User.

Attachments

MVP / VIP

Attachments

Registered User.

MVP / VIP

Registered User.

Registered User.

MVP / VIP

Attachments

Registered User.

MVP / VIP

Registered User.

Similar threads

Users who are viewing this thread