Set Variable Based Off User Selection (1 Viewer)

GC2010 · Jun 10, 2019

Is my best plan of attack here using

Code:

If .... then....else....end if

I have a combobox with 40 possible options in it. Based off the selection in the combo box I need to open a file browser to a specific folder. What seems most logical to me is to do

Code:

'Declared Public in diff module
Public FullAddress As String

Private Function OpenFolder(name As String)
Dim staticFolder, empolder As String

staticFolder = "C:\Personel\Employees\"

If name = "James" Then
    empolder = "J23815"
    FullAddress = staticFolder & empolder & "\" & Year(Now) & "\"
End If
if name = "Rachael" Then
    empolder = RC9291"
    FullAddress = staticFolder & empolder & "\" & Year(Now) & "\"
End If
End Function

Currently the empolder is not stored in a database anywhere it is a alpha-numeric combo that a supervisor created.

theDBguy · Jun 10, 2019

Hi. What is the Row Source of the combobox? It would be easier if you could include the folder name as a separate column in it. Or, is there a "rule" you can use to calculate it from the selection made?

jdraw · Jun 10, 2019

Do you have a list the relates empolder and Name? If so, post a copy.

This does NOT do what you think.

Dim staticFolder, empolder As String

Click to expand...

staticFolder will be dimmed as a variant
empolder will be a string datatype.

With Access vba, you must explicitly Dim variables,

1)Dim staticFolder as string , empolder As String OR

2)Dim staticFolder as string
Dim empolder As String

Name is a reserved word in Access.

isladogs · Jun 10, 2019

If the folder is fixed for each selection I would store that info in a table.
Your combo row source could then obtain both username and folder name as previously suggested by DBG.
Doing that means no need for If..Else..End If code. Plus its easier to edit in the future if names of folders are added/edited

GC2010 · Jun 10, 2019

theDBguy said:
Hi. What is the Row Source of the combobox? It would be easier if you could include the folder name as a separate column in it. Or, is there a "rule" you can use to calculate it from the selection made?

The row source is one field from a linked SharePoint table

I could create a new local access table that houses the full path and the employee name and maybe use Dlookup in the bbq to get the full folder path

theDBguy · Jun 10, 2019

GC2010 said:
The row source is one field from a linked SharePoint table

I could create a new local access table that houses the full path and the employee name and maybe use Dlookup in the bbq to get the full folder path

And the SharePoint List does not have the folder information?

GC2010 · Jun 10, 2019

theDBguy said:
And the SharePoint List does not have the folder information?

Correct, the SharePoint does not have the folder information

theDBguy · Jun 10, 2019

GC2010 said:
Correct, the SharePoint does not have the folder information

Can you add it to the SharePoint List?

GC2010 · Jun 10, 2019

theDBguy said:
Can you add it to the SharePoint List?

Unfortunately not, I do not have ability to add columns/fields to the SharePoint site.

theDBguy · Jun 10, 2019

GC2010 said:
Unfortunately not, I do not have ability to add columns/fields to the SharePoint site.

Then, I guess you'll have to create a local table for it then.

Micron · Jun 10, 2019

sounds like combo row source is a value list. While you can create a multi column combo this way, the FullAddress, staticFolder and empolder values would probably be better of coming from table(s) to the combo via query. The more you can get from it, the less code you'll need.

Alternatively, a Select Case block is often better than a whole bunch of IF's.

Forgot to mention, combo AfterUpdate may be a better event to use.

GC2010 · Jun 10, 2019

Micron said:
sounds like combo row source is a value list. While you can create a multi column combo this way, the FullAddress, staticFolder and empolder values would probably be better of coming from table(s) to the combo via query. The more you can get from it, the less code you'll need.

Alternatively, a Select Case block is often better than a whole bunch of IF's.

Forgot to mention, combo AfterUpdate may be a better event to use.

How would you advise using the AfterUpdate of combo?

I am so glad the crew here recommended a local table. I think that is going to be the bets bet since I am at roughly 39 now and the list can grow at any time.

Micron · Jun 10, 2019

by that I mean that since a combo is involved, once a value is chosen the after update event can get the chosen value from the bound column thus there's no need to test for what option was picked. The path at that point is to act accordingly. This is where the Select Case block can shine, or if the path is simpler (such as passing the chosen value to a query) it's just a matter of passing the value of the combo's bound column.

isladogs · Jun 10, 2019

If the combo has user name as the bound column and folder name as the 2nd column, the after update event can just pull that info using Me.Comboname.Column(1) as the numbering starts at zero.

As I said previously, you can then scrap If...Else..End If. You don't need Select Case either

GC2010 · Jun 11, 2019

isladogs said:
If the combo has user name as the bound column and folder name as the 2nd column, the after update event can just pull that info using Me.Comboname.Column(1) as the numbering starts at zero.

As I said previously, you can then scrap If...Else..End If. You don't need Select Case either

That is my issue the combobox is bound to a SharePoint table that does not have this information, and I am unable to edit the table to add it in.

I am thinking my best plan of attack is to create a separate local table in the access database with this information, and as you suggest on the after-update event use a VBA VLookup and pull in the location based off the combo-box (SharePoint Table) selected value

Something like this (not actual via syntax)

Code:

=DLookup("FullAddress", "localtable", "name = 'Comboboxvalue'")

theDBguy · Jun 11, 2019

GC2010 said:
That is my issue the combobox is bound to a SharePoint table that does not have this information, and I am unable to edit the table to add it in.

I am thinking my best plan of attack is to create a separate local table in the access database with this information, and as you suggest on the after-update event use a VBA VLookup and pull in the location based off the combo-box (SharePoint Table) selected value

Something like this (not actual via syntax)

Code:

=DLookup("FullAddress", "localtable", "name = 'Comboboxvalue'")

Hi. If you create a local table, then there's really no need to use DLookup(). You should be able to JOIN your table with the SP List and pull all the data you need into the Combobox.

GC2010 · Jun 11, 2019

theDBguy said:
Hi. If you create a local table, then there's really no need to use DLookup(). You should be able to JOIN your table with the SP List and pull all the data you need into the Combobox.

Hi - thank you for the continued support!!

Okay, so I have created the SQL for my join - which I believe should be set as the Row Source for the combo box. Now, I have only used a combobox for one field, which leads me to my question of how do I capture the EmployeeFolder based off the [Employee Name] selection from my combobox when this is the Row Source?

Code:

Select [_Data].[Emp Name], [_LocalFolders].[EmployeeFolder]
FROM [_Data] Inner Join [_LocalFolders]
On [_Data].[Emp Name] = [_LocalFolders].[Emp Name]
Order By [_Data].[Emp Name];

isladogs · Jun 11, 2019

Try doing what I suggested in post #14

Micron · Jun 11, 2019

or reverse the order of the 2 fields in the sql and just refer to the combo and forget the column reference. Likely 2 column combo would have column 0 hidden so that you only see name, but the folder value is what the combo holds when you refer to the combo.

Set Variable Based Off User Selection (1 Viewer)

Registered User.

I’m here to help

Super Moderator

MVP / VIP

Registered User.

I’m here to help

Registered User.

I’m here to help

Registered User.

I’m here to help

AWF VIP

Registered User.

AWF VIP

MVP / VIP

Registered User.

I’m here to help

Registered User.

MVP / VIP

AWF VIP

Similar threads

Users who are viewing this thread