Hooking up the custom database to the Junxure Client Form
In Junxure, you have the ability to hook up a custom Access database to the
client form. Once it is hooked up, you will have a new button on the top
left of the client form.
Clicking this button will open the custom database to the form that you have
specified in the options settings. The beauty of this, is that you can
then build the form in your custom database to open for the client that you are
on in Junxure, allowing you to extend Junxure in a way that is seamless to the
users.
I will give you step by step instructions for completing this whole process.
So let’s get started.
Setup the Options.
You will need to turn on the option to display this button, and then you will
need to specify a form to open in the custom access database. In this
example, I am going to use the frmClients as the form that I want to open, but
that form actually does not exist in the access database. We are going to
create it later. For now, just turn on the option by going to Maintain
System, System Options, Global Options , custom and checking the box and adding
“frmClients” to the form name.
No that you have turned on this option, Junxure will open the custom database
and then open the frmClients. Since you probably don’t have a frmClients,
you will probably get an error if you were to try this right now. You will
first need to configure your custom database.
Custom Database Location
newer version of Junxure, you may not have either that folder or that file.
If it is there, you can open it now. If you don’t have it I have provided
the file that I am building in this example, so you can download it and it will
already have the form that I am showing you how to build. You can download
a copy of one here. This is a self extracting zip file that will extract
to the proper folder.
CustomJunxure.exe
Once you have the c:\junxure\code\customjunxure.mdb file on your system, double-click on it to
open it and you will see this screen.
This is the custom database screen that I have created, but you are not forced
to use this database. You can actually use any access.mdb file in place of
this file as long as it is named customjunxure.mdb.
In this blog entry, I am going to assume that you are using this file.
Before you will be able to “read” any of the Junxure data, you will need to be
sure you have configured and ODBC Connection to your Junxure database.
Here is a link that has directions for setting up and ODBC connection.
http://www.junxure.com/kb/ViewArticle.aspx?ArticleID=151
Now lets get started setting up the frmClients in the custom database.
The first thing you will notice in this custom database, is that you are
basically “Locked” out of the design of the menu and you probably cannot figure
out how to get to the “database Container” in the database. We hide it
when this opens, and to see the database container, you will click “F11″.
That will open the database container and you will see something like this, if
you are using Access 2003, and something a bit different if you are using
another version of access. It all works the same but the interface is a
bit different. Go ahead and hit “F11″ with the open database.
Here you will see a list on the left that allows you to select the different
object types in the database. In this demo, we will be working with
Tables, queries, forms and Modules. Don’t worry about the modules, there
is only one thing that we need to do and I will walk you thru it.
Refresh Table Links
The next thing that we will want to do is to refresh the table links.
This requires that you have the link table manager installed in your access
version. If you did not install the link table manager, you can drop and
reattach all the tables, but I would recommend that you install the link table
manager when prompted if you do not have it installed.
Click on Tools | Database Utilities | Linked Table Manager and that
will open the linked table manager.
If you are using Access 2007 it is on the Ribbon on the Database Tools Tab
It will look like this.
Click Select all, then click OK. If your ODBC connection is properly
setup, it will refresh all of the links and this database will now be able to
view the data in Junxure.
| Caution: When you are looking at “DATA” in the custom database, you are actually looking at data in Junxure’s SQL database and any changes or deletions made here, will also be made in Junxure. |
In order to assist you in really getting the most benefit out of the custom
database, we have build a “hook” into Junxure to allow you to see the what
client your are on in Junxure and what employee you are logged in as. In
order to take advantage of this, you will need to create a module that will call
some functions that we have created. Don’t worry if you don’t understand
this, just follow these steps.
1. Click on the Modules entry in the database container.
2 Click the New button on the top
This will open a module window and it will be called Module 1. It is in
this module that we will want to “Paste” in some code.
3. Copy this code below and paste it into the window, below the “Option
Compare Database” text that appears in the module windows. The code is
between the horizontal lines, not including the lines.
Function getCurrentID()
Dim o As Object
Set o = CreateObject("JxPublicObject.clsPublicObject")
Dim msg As String
msg = o.GetCurrentInfo
Dim ClientID
Dim emp
If msg & "" = "" Then
'added for testing, if clientform is not open, I return 1187, you can change this for your favorite client
getCurrentID = 1187
Exit Function
End If
If InStr(msg, ",") >= 0 Then
ClientID = Right(msg, Len(msg) - InStr(msg, ","))
EmpID = Left(msg, InStr(msg, ",") - 1)
Else
ClientID = 0
End If
getCurrentID = ClientID
End Function
Function getCurrentEMPID()
Dim o As Object
Set o = CreateObject("JxPublicObject.clsPublicObject")
'I am not sure why this is not regestring
Dim msg As String
msg = o.GetCurrentInfo
Dim ClientID
Dim emp
If InStr(msg, ",") >= 0 Then
ClientID = Right(msg, Len(msg) - InStr(msg, ","))
EmpID = Left(msg, InStr(msg, ",") - 1)
Else
ClientID = 0
End If
getCurrentEMPID = EmpID
End Function
|
When you paste in that code it should look something like this.
If you only see the first function, don’t worry, you may have the editor
setup to only show one function at a time.
We are going to test it in a second so just continue along.
Testing the Functions
Open Junxure and Go to a client in Junxure. With the client form open,
we want to get the ID of the client that you are on.
Leave Junxure open and go back to the code windows. Look for the window
that is labeled Immediate Window.
If you do not see it in the code window you can click on “View”
Immediate
Window
In the immediate window, type the following. A question mark followed
by getCurrentID() and hit enter.
You should see the current ID from Junxure below what you typed. If
that works, you now have the ability to get the current client ID. Now
lets test it for the current logged on employee.
Type ?GetCurrentEMPID() and hit return
You should see the employee ID. If you get any errors, you need to
check the following.
1. Junxure is started and Running.
2. The client form is open and on a client.
3. You have installed the Junxure Public Object. This is found in
C:\Junxure\codeDN\JxPublicObject\SetupJxPublicObject.msi. If you have
done number 1 and 2, then run this MSI and try again. If it still does not
work, then you have a problem and you should call support, they can help you
with getting the public object installed.
Save The Module.
Click the save Icon and then follow the prompts to save the module, then
close the module window by clicking the X in the top right of the window.
Build a query to return the current Client’s Information
1. Return to the database container, and click on Queries.
2. Click the new button like you did for module. This will open a query
designer dialog.
Select Design view and click OK, this will open a table selection dialog.
Select tblClients and click OK and Close. This will take you to the
query designer with tblClients at the top of the designer.
Double click on the any fields that you think you may want to use, but for
this demo, we are going to use these fields.
ID
Lastname
Firstname
This will give you something that looks like this.
Now what we want to do, is filter this query to only
return the client that we have open in Junxure. To do that we are going to
add criteria to the ID field.
In the cell under ID and to the right of Criteria, you
are going to type =getcurrentID() and that will call the function that we made
in previous steps.
Once you are done, it will look like this.
To test this, you can click on the menu bar “View” and
“Datasheet View’ and you should see the client that you have open in Junuxre.
Now click the save Icon and save this query as “qryClients”
close the query by clicking the X in the top right
corner.
Building the Form
From the database container, click on forms and click
new
Click Autoform:Columnar and put qryClient in the box at
the bottom, then click OK. This will create a form that shows the fields
that you selected in your query.
The newly designed form should look like this.
You now have a form that will open to your open client
in Junxure. We will do a few things to this form and you will be well on
your way to being able to use the custom database.
Click on View | Design View from the menu. This will put
the form in Design View.
I am going to do the following.
1. Make the form a bit bigger.
Drag the bottom right corner of the form to make it a
bit bigger. I would make it about an inch taller and an inch wider for
now. You can always design it to your hearts content later.
2. Resize the white text boxes to be the same size
Select the three white boxes and use the menu “Format”
size To Shortest
This will make them all the same size.
3. put a button on the form to close the application.
Find the toolbox and select a button from the toolbox.
If you don’t see the toolbox, you can click View Toolbox from the menu and it
will toggle it on and off. Find the button that will make a button on the
toolbox, click it and then draw a button on your form. It does not matter
where you draw it, just put it on the form somewhere.
that will pop up a dialog box, that will help you tell
Access what you want that button to do when it is clicked.
Select Application, Quit Application, and then click
Finish, you will then have a button on your form, that when clicked will close
the application, returning you back to Junxure.
4. Save the form as frmClients.
Click the same save button you used above to save the
form. It will want to save it as qryClients, since that is what you based
the form off of, but you want to change that to frmClients to match what you
have in Junxure on your options page.
5. Put some code on the form to maximize it when it
opens.
Find the box in the top right of the form designer and
double click it. Select the event tab, then click in the On Open cell to reveal
the ellipsis button then click the ellipsis button to open the code builder.
Double click the code builder item in the list box.
This will open the code editor with a new OnOpen event
for the form. Type the following in that event so it looks like this
That will cause the form to maximize when you open it.
Now close the code editor by clicking the X at the top.
Close the toolbox and the properties windows and then
close the form by clicking the X at the top of it.
Be sure to SAVE everything when you are prompted.
Be sure to close the Access Custom Database and exit
Access or it will not be able to open the form when you click the button to test
it.
Testing the database from Within Junxure
Return to Junxure and open the client form to a client
of your choosing. I choose to open the form for Ken Golding
Now click the button for that the arrow is pointing to
and it will open the custom database to the client that you have open in Junxure
Now you click the stop sign button and you will return
to Junxure.
Summary
This gives the user a seamless hook into your custom
database. While this demo does not give you much in terms of
functionality, it does demonstrate that you can easily hook into Junxure to open
your Access database to the form that you decide, and if you write reports in
that custom database, and those reports are for the displayed client, you can
really extend Junxure allowing you to design your own reports and you need.
For more information on creating reports in Access, you
can see my previous blog posts, or just google microsoft access report designer
tutorial for a list of resources.
Click
here for Link
