Monday, July 25, 2005

Using a Combo Box in Excel

From the top menu, navigate thru: View / Toolbars / Control Toolbox



When the Control Toolbox appears, Select the Combo Box control. With your mouse, make a rectangular shape on the excel sheet.



Define the choices that will be available in the Combo Box. In this example, the names of fruits are written on Column F from row 1 thru 4.




Tell the combobox where to get it's data from. Right click on the combobox to get it's properties.




Then in the ListFillRange, enter the range of cells that hold the combox values. In this case F1:F4.



That's all there is to it, close design view and your combo box should be working fine.

Tuesday, July 19, 2005

Securing an Access Database

A reader from Kuwait wrote the other day...


Orlando,

I was browsing for some help on the net and just saw
your name, I had a question, could you please help me.

My question is, i have developed a database, and i
want to give it to my friend, the question is, i want
to secure the Queries, Form & Reports from being
edited or re-designed, i dont want these to be visible
whenever he uses it. he should be able to print &
update database, but he should not be able to see what
queries i have set as well he should not be able to go
to the design view to modify my forms & Reports.

Could you please help.

Regards,

Oswald
Kuwait


Oswald...

There are a couple of different ways that the above can be accomplished.

1) User Level Security. But I don't recommend it because it is difficult to maintain.

2) A combination of setting the Start Up options, tweaking the properties of the Forms, and making an mde file.

You will have to play around with option number 2 to get the desired results. But here are a few pointers.

From the menu bar, go to Tools / Start Up and un-check "Display Database Window". Next time the database starts, the database window will not be available. If you want to see it, then you would go to Window / Unhide and select the database window. If you choose to not see the database window, you would have to have some form that would serve as the main menu for your database.




Another thing that you could do is hide your database objects, but this is the least effective way, because in the Database Options there is the option to View Hidden Objects. But anyway, here is how you could hide them: Right Click on the object to arrive at the Properties window and check Hidden.




Another alternative is to allow Design Changes on Forms Only in Design View. And to not allow viewing the form any other way then you intended. Forms can be viewed as FORMS, DATASHEETS, PIVOT TABLES, PIVOT CHART. So make sure that in the Forms Format Properties you unselect the view types you do not want the user to use.







Finally, after you have implemented a combination of the tips above, make the database an MDE file. Go to Tools / Database Utilities / Make MDE file.

Sunday, July 10, 2005

How to Filter Access Subforms based on a control from a Main Form

If you are reading this then you have probably noticed that filtering a Subform based on a control from the Main form is a pain in the neck.

Here's one way to filter the subform by changing the underlying form.

Sub frameTaskStatus_Click()
Dim intStat As Integer
intStat = frameTaskStatus.Value
' 1 = all
' -1= closed
' 0 = open
' 2 = new open frmCreateNewRequest


Select Case intStat
Case -1
Me!sbfrmMainRequests.SourceObject = "subfrmRequestsFiltered"
Case 0
Me!sbfrmMainRequests.SourceObject = "subfrmRequestsFiltered"
Case 1
Me!sbfrmMainRequests.SourceObject = "subfrmRequestsAll"
Case 2
Me!sbfrmMainRequests.SourceObject = "subfrmRequestsNew"
End Select

End Sub



Cases -1 and 0 (closed and open, respectively) make subfrmRequestsFiltered the underlying subform. This subform's datasource is a query that applies a filter also based on the value selected in frameTaskStatus.

Case 1 makes subfrmRequestAll the underlying subform in the Main form. subfrmRequestAll is also based on a query but it is not filtered.

Case 2 makes subfrmRequestsNew the underlying subform in the Main form. subfrmRequestsNew is a data entry only form.

Wednesday, July 06, 2005

How to Determine the Version or Edition of SQL Server

Open Query Analyzer, connect to any database on your server and type one of the following:

SELECT SERVERPROPERTY('ProductLevel')
SELECT @@VERSION
SELECT SERVERPROPERTY('ProductVersion')


If you are not sure whether you are running an edition of the SQL Server 2000 database engine or MSDE 2000, execute the following query:
SELECT SERVERPROPERTY('Edition')