Hyperlink CBO Ascending order

[Stockwell43]

Hello,

I have a form that has two Combo boxes. One is to select a
department(Collateral, Loan Accounting, Inaging etc... and the second is a
CBO that has different items hyperlinked for that department like databases,
forms, spreadsheets etc..

So when a user selects Loan Accounting from the first dropdown then all the
forms, databases, spreadsheets etc that pertain to Loan Accounting are
listed.

The problem I am having is when I add a new item to the Loan Accounting
Table which is feeding the hyperlink CBO, even though I am sorting it in the
table it doesn't sort on the CBO? It shows up in the order that I load the
table before my sort. How do I get the Hyperlink CBO to sort so it will
always be in alpha order like the table?

Thanks!!

 

[Dirk Goldgar]

Hello,

I have a form that has two Combo boxes. One is to select a
department(Collateral, Loan Accounting, Inaging etc... and the second is a
CBO that has different items hyperlinked for that department like
databases,
forms, spreadsheets etc..

So when a user selects Loan Accounting from the first dropdown then all
the
forms, databases, spreadsheets etc that pertain to Loan Accounting are
listed.

The problem I am having is when I add a new item to the Loan Accounting
Table which is feeding the hyperlink CBO, even though I am sorting it in
the
table it doesn't sort on the CBO? It shows up in the order that I load the
table before my sort. How do I get the Hyperlink CBO to sort so it will
always be in alpha order like the table?

Set the rowsource of the combo box to a query (either a stored query or a
SQL statement) that selects the records from the table and explicitly sorts
them the way you want.

 

[Stockwell43]

Hi Dirk,

Thank you for replying. The thing is, I am not saving any records. The table
I am referring to just holds the items for that department(the hyperlinks to
their databases, forms, org charts etc..). So all this database is doing is
storing all the hyperlinks to the particular items so everyone doesn't have
to go into multiple folders and search through numerous documents. Nothing is
saved, no reports just a housing for hyperlinks. Does that make a difference?

Thanks!!

 

[Dirk Goldgar]

Hi Dirk,

Thank you for replying. The thing is, I am not saving any records. The
table
I am referring to just holds the items for that department(the hyperlinks
to
their databases, forms, org charts etc..). So all this database is doing
is
storing all the hyperlinks to the particular items so everyone doesn't
have
to go into multiple folders and search through numerous documents. Nothing
is
saved, no reports just a housing for hyperlinks. Does that make a
difference?

I don't think so. If I understand what you're saying, there is a table of
hyperlinks in the database, and a combo box that uses that table as its
rowsource. A sort order applied to the table won't automatically be carried
through into the combo box. To ensure that the items listed in the combo
box are sorted the way you want, you have to apply a sort sequence in the
specification of the rowsource itself.

So if you currently have the combo's RowSource property set to, for example,

tblHyperlinks

You would force a sort by setting it to something like:

SELECT * FROM tblHyperlinks ORDER BY LinkURL;

Those names are just examples, of course. If you'd care to post the actual
RowSource of the combo box and the names of the fields in the table, along
with a statement of the order you'd like to see them listed in, I could give
you a specific recommendation.

 

[Stockwell43]

You close and correct so far. The only thing is I have numerous table with
hyperlinks, one for each department. So when the user select the Department
CBO, in the Hyperlink CBO only the item pertaining to that department will
show. What saying makes sense but I think your referring to if I had one
table that stored all the hyperlinks correct? I suppose I could save each
record to a main table and that should take care of the problem. However, it
is a waste of space because it is not needed unless I can figure out a way to
delete the table records on exit of the form or when the database is closed.
Any ideas? If I not explaining this correctly please let me know as I
sometimes confuse myself which is why I have to talk things out a loud so it
makes sense to me )

The database works fine as it is really. I'm just trying to make it more
user friendly by having everything in alpha order because I think more people
would use it. Other wise, they will have to scroll the CBO list to find what
they want which could take a while depend on the items in that department.

Thank you for sticking with Dirk. You help is most appreciated!

 

[Dirk Goldgar]

You close and correct so far. The only thing is I have numerous table with
hyperlinks, one for each department. So when the user select the
Department
CBO, in the Hyperlink CBO only the item pertaining to that department will
show. What saying makes sense but I think your referring to if I had one
table that stored all the hyperlinks correct? I suppose I could save each
record to a main table and that should take care of the problem. However,
it
is a waste of space because it is not needed unless I can figure out a way
to
delete the table records on exit of the form or when the database is
closed.
Any ideas?

Well ... if I were doing it, I would have only one table with hyperlinks,
but I'd have a Department field in that table that I could use to filter a
query to show only the hyperlinks for a particular department. I'd use the
Department CBO to filter the records in the Hyperlink CBO, so the users
would only see the links for the selected department.

How many changes are you willing to undertake at once? Shall we just try to
get the combo box sorting properly with the table structure you've got, or
should we try to do it the "right" way, which will involve many more
changes?

Either way, I'll need you to post (a) the name and structure (field names
and types) of the table that contains the hyperlinks, (b) the name of the
form that contains the combo boxes, (c) the names of the combo boxes, (d),
the Row Source properties of both combo boxes, and (e) any code or macros in
the events of those combo boxes. I think there must be code in the
AfterUpdate, Click, or Change event of the Department combo box, or I don't
see how your current arrangement is working.

Please post the above information, and we can get this sorted out.

 

[Stockwell43]

Your a good man Dirk. I most definitely would like to do it the right way. I
was only trying to go about it the best I know as my experience is a bit
limited. If you are willing to help me do it correctly, the number of changes
are unimportant. Here is the information of what I believe you were asking
for.

A) tblcollateral, tblloanaccounting, tblimaging, each of these are separate
tables with data type as Hyperlink. Field names are Collateral,
LoanAccounting and Imaging

B) Name of form is: frmstorage

C) Name of CBO’s: Department and Hyperlink

D) Row Source for Departments: SELECT tbldepartments.Departments FROM
tbldepartments ORDER BY [Departments];

Row Source for Hyperlink: There is none

E) Code in OnChange event or Department CBO:

Private Sub Department_Change()
If Department.Text = "Imaging" Then
Hyperlink.Value = ""
Hyperlink.RowSource = "tblimaging"

ElseIf Department.Text = "Collateral" Then
Hyperlink.Value = ""
Hyperlink.RowSource = "tblcollateral"

ElseIf Department.Text = "Loan Accounting" Then
Hyperlink.Value = ""
Hyperlink.RowSource = "tblloanaccounting"

ElseIf Department.Text = "Spreadsheet" Then
Hyperlink.Value = ""
Hyperlink.RowSource = "tblspreadsheets"

ElseIf Department.Text = "Org Chart" Then
Hyperlink.Value = ""
Hyperlink.RowSource = "tblorgcharts"

ElseIf Department.Text = "Procedure" Then
Hyperlink.Value = ""
Hyperlink.RowSource = "tblProcedures"

Else

End If

End Sub

No change event or code for Hyperlink CBO

 

[Dirk Goldgar]

Your a good man Dirk. I most definitely would like to do it the right way.
I
was only trying to go about it the best I know as my experience is a bit
limited. If you are willing to help me do it correctly, the number of
changes
are unimportant. Here is the information of what I believe you were asking
for.

A) tblcollateral, tblloanaccounting, tblimaging, each of these are
separate
tables with data type as Hyperlink. Field names are Collateral,
LoanAccounting and Imaging

B) Name of form is: frmstorage

C) Name of CBO’s: Department and Hyperlink

D) Row Source for Departments: SELECT tbldepartments.Departments FROM
tbldepartments ORDER BY [Departments];

Row Source for Hyperlink: There is none

E) Code in OnChange event or Department CBO:

Private Sub Department_Change()
If Department.Text = "Imaging" Then
Hyperlink.Value = ""
Hyperlink.RowSource = "tblimaging"

ElseIf Department.Text = "Collateral" Then
Hyperlink.Value = ""
Hyperlink.RowSource = "tblcollateral"

ElseIf Department.Text = "Loan Accounting" Then
Hyperlink.Value = ""
Hyperlink.RowSource = "tblloanaccounting"

ElseIf Department.Text = "Spreadsheet" Then
Hyperlink.Value = ""
Hyperlink.RowSource = "tblspreadsheets"

ElseIf Department.Text = "Org Chart" Then
Hyperlink.Value = ""
Hyperlink.RowSource = "tblorgcharts"

ElseIf Department.Text = "Procedure" Then
Hyperlink.Value = ""
Hyperlink.RowSource = "tblProcedures"

Else

End If

End Sub

So, from the look of it, you currently have these "Departments":

Imaging
Collateral
Loan Accounting
Spreadsheet
Org Chart
Procedure

Judging by the rowsource from your Departments combo box, you have a table
named "tbldepartments", which has a field named "Departments". (To me, it
would make more sense for that field to be named "Department", but I don't
want to break anything you've got going now by renaming it.) Is that field
the primary key of the table?

Create another table, named "tblDeptHyperlinks". Give this table two
fields:

Department (type Text)
HyperlinkURL (type Hyperlink)

Make the *combination* of the two fields the primary key of the table. You
do this by selecting both fields and clicking the little "key" icon on the
toolbar.

Now you need to load this table up with the hyperlink values from each
individual department's table. I don't know the names of the fields in
those tables, but suppose the field name in each table is "Hyperlink". Then
you could load the records from tblImaging by executing a query with SQL
like this:

INSERT INTO tblDeptHyperlinks (Department, HyperlinkURL)
SELECT "Imaging", [Hyperlink] FROM tblImaging;

Load the records for each of the other departments with similar queries:

INSERT INTO tblDeptHyperlinks (Department, HyperlinkURL)
SELECT "Collateral", [Hyperlink] FROM tblCollateral;

INSERT INTO tblDeptHyperlinks (Department, HyperlinkURL)
SELECT "Loan Accounting", [Hyperlink] FROM tblLoanAccounting;

INSERT INTO tblDeptHyperlinks (Department, HyperlinkURL)
SELECT "Spreadsheet", [Hyperlink] FROM tblSpreadsheets;

INSERT INTO tblDeptHyperlinks (Department, HyperlinkURL)
SELECT "Org Chart", [Hyperlink] FROM tblOrgCharts;

INSERT INTO tblDeptHyperlinks (Department, HyperlinkURL)
SELECT "Procedure", [Hyperlink] FROM tblProcedures;

Note: if the hyperlink field in these tables is not actually named
"Hyperlink", the above SQL statements will have to be corrected.

If all has gone well so far, tblDeptHyperlinks now contains all the records
you need.

On your form, set the RowSource of your Hyperlink combo box to:

SELECT HyperlinkURL FROM tblDeptHyperlinks
WHERE Department = [Forms]![frmStorage]![Department]
ORDER BY HyperlinkURL;

Note: In the combo box's Row Source property, that statement will all be one
one line, not on three lines as I've posted it for clarity.

Remove the event procedure you have for the Department combo box's Change
event. Delete that code. Instead, add the following event procedure for
the control's AfterUpdate event:

'----- start of code -----
Private Sub Department_AfterUpdate()

Me!Hyperlink.Requery

End Sub
'----- end of code -----

And that ought to do it. If you had forms and reports based on the
individual department tables (tblCollateral, tblLonAccounting, etc.) you
will need to replace them with forms and reports based on tblDeptHyperlinks.
When all is working well, you can get rid of the old tables.

 

[Stockwell43]

Hi Dirk,

Thank you for being so patient and explaining this in detail. I followed
what you wrote and it works great for collateral but the other tables
information doesn't show in the table you told me to create. I set up
separate queries exactly like Collateral but when I open the table, only
collaterals information shows. On the form, when I click on Colateral it show
everything in there but when I click on Loan Accounting or Imaging the other
CBO is blank. Collateral does come up in alpha order though.

Your a good man Dirk. I most definitely would like to do it the right way.
I
was only trying to go about it the best I know as my experience is a bit
limited. If you are willing to help me do it correctly, the number of
changes
are unimportant. Here is the information of what I believe you were asking
for.

A) tblcollateral, tblloanaccounting, tblimaging, each of these are
separate
tables with data type as Hyperlink. Field names are Collateral,
LoanAccounting and Imaging

B) Name of form is: frmstorage

C) Name of CBO’s: Department and Hyperlink

D) Row Source for Departments: SELECT tbldepartments.Departments FROM
tbldepartments ORDER BY [Departments];

Row Source for Hyperlink: There is none

E) Code in OnChange event or Department CBO:

Private Sub Department_Change()
If Department.Text = "Imaging" Then
Hyperlink.Value = ""
Hyperlink.RowSource = "tblimaging"

ElseIf Department.Text = "Collateral" Then
Hyperlink.Value = ""
Hyperlink.RowSource = "tblcollateral"

ElseIf Department.Text = "Loan Accounting" Then
Hyperlink.Value = ""
Hyperlink.RowSource = "tblloanaccounting"

ElseIf Department.Text = "Spreadsheet" Then
Hyperlink.Value = ""
Hyperlink.RowSource = "tblspreadsheets"

ElseIf Department.Text = "Org Chart" Then
Hyperlink.Value = ""
Hyperlink.RowSource = "tblorgcharts"

ElseIf Department.Text = "Procedure" Then
Hyperlink.Value = ""
Hyperlink.RowSource = "tblProcedures"

Else

End If

End Sub

So, from the look of it, you currently have these "Departments":

Imaging
Collateral
Loan Accounting
Spreadsheet
Org Chart
Procedure

Judging by the rowsource from your Departments combo box, you have a table
named "tbldepartments", which has a field named "Departments". (To me, it
would make more sense for that field to be named "Department", but I don't
want to break anything you've got going now by renaming it.) Is that field
the primary key of the table?

Create another table, named "tblDeptHyperlinks". Give this table two
fields:

Department (type Text)
HyperlinkURL (type Hyperlink)

Make the *combination* of the two fields the primary key of the table. You
do this by selecting both fields and clicking the little "key" icon on the
toolbar.

Now you need to load this table up with the hyperlink values from each
individual department's table. I don't know the names of the fields in
those tables, but suppose the field name in each table is "Hyperlink". Then
you could load the records from tblImaging by executing a query with SQL
like this:

INSERT INTO tblDeptHyperlinks (Department, HyperlinkURL)
SELECT "Imaging", [Hyperlink] FROM tblImaging;

Load the records for each of the other departments with similar queries:

INSERT INTO tblDeptHyperlinks (Department, HyperlinkURL)
SELECT "Collateral", [Hyperlink] FROM tblCollateral;

INSERT INTO tblDeptHyperlinks (Department, HyperlinkURL)
SELECT "Loan Accounting", [Hyperlink] FROM tblLoanAccounting;

INSERT INTO tblDeptHyperlinks (Department, HyperlinkURL)
SELECT "Spreadsheet", [Hyperlink] FROM tblSpreadsheets;

INSERT INTO tblDeptHyperlinks (Department, HyperlinkURL)
SELECT "Org Chart", [Hyperlink] FROM tblOrgCharts;

INSERT INTO tblDeptHyperlinks (Department, HyperlinkURL)
SELECT "Procedure", [Hyperlink] FROM tblProcedures;

Note: if the hyperlink field in these tables is not actually named
"Hyperlink", the above SQL statements will have to be corrected.

If all has gone well so far, tblDeptHyperlinks now contains all the records
you need.

On your form, set the RowSource of your Hyperlink combo box to:

SELECT HyperlinkURL FROM tblDeptHyperlinks
WHERE Department = [Forms]![frmStorage]![Department]
ORDER BY HyperlinkURL;

Note: In the combo box's Row Source property, that statement will all be one
one line, not on three lines as I've posted it for clarity.

Remove the event procedure you have for the Department combo box's Change
event. Delete that code. Instead, add the following event procedure for
the control's AfterUpdate event:

'----- start of code -----
Private Sub Department_AfterUpdate()

Me!Hyperlink.Requery

End Sub
'----- end of code -----

And that ought to do it. If you had forms and reports based on the
individual department tables (tblCollateral, tblLonAccounting, etc.) you
will need to replace them with forms and reports based on tblDeptHyperlinks.
When all is working well, you can get rid of the old tables.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Hi Dirk,

Thank you for being so patient and explaining this in detail. I followed
what you wrote and it works great for collateral but the other tables
information doesn't show in the table you told me to create. I set up
separate queries exactly like Collateral but when I open the table, only
collaterals information shows. On the form, when I click on Colateral it
show
everything in there but when I click on Loan Accounting or Imaging the
other
CBO is blank. Collateral does come up in alpha order though.

When you built and ran the append queries for the other tables, did you
remember to change the literal "Collateral" to "Imaging", "Loan Accounting",
etc., as appropriate for each table? Did you remember to actually *execute*
the append queries for the other tables? If so, did you get any message
other than the normal "About to add n records" confirmation dialog?

Do I understand correctly that when you open tblDeptsHyperlinks, you only
see records with "Collateral" in the Department field? It sure sounds like
the records didn't get loaded into the table, but I can't see why if you
carried out the steps I suggested and got no errors.

 

[Stockwell43]

Ok, I clicked on each query to tell me I am about to append X-amount of rows
and I didn't do that before because now they are showing. I do apologize but
I never used an Append Query before but it neat to see how it works and I
wrote everything down for future use. Now, if I want to add to the Loan
Accounting, Collateral or Imaging tables can I just add the hyperlinks as
before and it automatically picks it up or do I need to click on the query
again to refresh it?

Dirk, your good man and I truely appreciate all your help in walking me
through this process. I saved everything and it's more knowledge gain and I
am happy to have learned something new I can incorporate down the road on
other databases.

Thank you again and have a great day!!!!!!

 

[Dirk Goldgar]

Ok, I clicked on each query to tell me I am about to append X-amount of
rows
and I didn't do that before because now they are showing. I do apologize
but
I never used an Append Query before but it neat to see how it works and I
wrote everything down for future use. Now, if I want to add to the Loan
Accounting, Collateral or Imaging tables can I just add the hyperlinks as
before and it automatically picks it up or do I need to click on the query
again to refresh it?

*If* you did it that way, you would need to run the appropriate append query
again every time you added a record. But I don't recommend doing it that
way. Instead, you should make all future additions or edits in the
tblDeptHyperlinks table. The idea is that you now have one and only one
table that stores the hyperlinks for all the departments, so you don't need
or want the old, department-specific tables any more. Unless they contain
other data that you also need to preserve, just delete them (as soon as you
are comfortable doing that).

If I were you, I'd make continuous form bound to tblDeptHyperlink for
editing, and on that form I'd use a combo box to choose the Department for
each record, and a text box to enter the hyperlink. The combo box would
have the tblDepartments table as its row source, and would be bound to the
Department field in tblDeptHyperlinks.

I saved everything and it's more knowledge gain and I
am happy to have learned something new I can incorporate down the road on
other databases.

Great! That's the way I learned this stuff.

Thank you again and have a great day!!!!!!

You're welcome.

 

[Stockwell43]

Excellent idea Dirk, works like a charm!! Very nice piece of advice and your
a great teacher to boot!

Thank you again for all your help and it will definitely be put to very good
use.