Access 2003 database layout questions

[Procman]

Hi,
I work for a city Senior Citizens Program and I’m creating a new database to replace and old database and several separate and outdated processes (pen and paper) work processes. Many of the users are not very computer savvy as they are seniors who volunteer.

My idea is to have the tables and the forms, etc in separate files on a network share and use Data Access Pages with disconnected recordsets for the forms. Due to budget restrictions, there are only 3 copies of Access, but 7 users.

There are separate tables for tracking: Basic info (name, address, etc), membership, activities participation, and transportation. All of the tables will be one-to-many relationships based on the ClientID (primary key in the Info table). Most of the reporting will be exported to Excel.

Question: Is it possible to set it up this way? It’s basically a Star configuration and I haven’t found any examples like this in my research.

All of the forms (single view) will use a combobox on the client last name. When a form opens, the field is blank. When the user starts typing the last name it auto completes and populates some of the other fields, then moves to the next field to enter data.

How do I accomplish the autocomplete and have the combobox drop down when there is more than one match on the last name so the user can use the arrow keys and spacebar to select the client? I had figured out part of the SQL statement, but it was in a file that was overwritten.

I’m actually a driver and scheduler, so my time for creating this is very limited. If I can get assistance with these, I can take care of the rest. Any help is greatly appreciated.

Jim

[Jim Doherty]

Hi,
I work for a city Senior Citizens Program and I’m creating a new database to replace and old database and several separate and outdated processes (pen and paper) work processes. Many of the users are not very computer savvy as they are seniors who volunteer.

My idea is to have the tables and the forms, etc in separate files on a network share and use Data Access Pages with disconnected recordsets for the forms. Due to budget restrictions, there are only 3 copies of Access, but 7 users.

There are separate tables for tracking: Basic info (name, address, etc), membership, activities participation, and transportation. All of the tables will be one-to-many relationships based on the ClientID (primary key in the Info table). Most of the reporting will be exported to Excel.

Question: Is it possible to set it up this way? It’s basically a Star configuration and I haven’t found any examples like this in my research.

All of the forms (single view) will use a combobox on the client last name. When a form opens, the field is blank. When the user starts typing the last name it auto completes and populates some of the other fields, then moves to the next field to enter data.

How do I accomplish the autocomplete and have the combobox drop down when there is more than one match on the last name so the user can use the arrow keys and spacebar to select the client? I had figured out part of the SQL statement, but it was in a file that was overwritten.

I’m actually a driver and scheduler, so my time for creating this is very limited. If I can get assistance with these, I can take care of the rest. Any help is greatly appreciated.

Jim

Please be aware that Data Access pages are not MS supported beyond version 2003 and are deprecated in favour of ASP for website interfacing.

The runtime executable of Access 2007 however is a 'free' download and provided you are happy and able to develop in the latest retail version then the runtime version will give you the flexibility of distributing a royalty free application as opposed to buying additional licence to support 7 desktops.

The SQL Server Express edition also is free and will give you a robust backend platform to support your 'star' configuration which incidentally is the usual arrangement when working on a network using a 'split' mdb Access configuration.

I am not aware of your skill level but given you are speaking about disconnected recordsets, I assume it to be good. I am not aware of the specifics by which your implementation of these becomes necessary given you could easily backend everything into the centralised database platform 'live'? so to speak.

As to the 'autolookup' you mention this is a general term used by Access for something that SQL language does in any event. It is the standard mechanism by which information is brought into a standard query definition from one or more tables party to the query joins and is documented in the Access help files

[Procman]

Hi Jim, thanks for your help.

The reasons I’ve chosen to do it this way are:
- the city I work for is technologically WAY behind. Basically, there is no separate IT dept. The people doing those jobs have learned how to do what they need to do, but are not trained “Administrators ”.
- I’m doing this on a $0 budget just so we can run our center. Personnel cutbacks have us below our minmum and we are forced to rely on senior volunteers to do a lot of the work.
- I’m the only one at my center who has any idea that this is even possible, let alone HOW to do it.
- I am a Driver and they will not give me any administrator rights to do anything on the workstations. We are having to press them to even give us a shared network folder and allow all of the 7 computers to access the city network instead of only accessing the internet. (Sad, I know.)
- We have mostly MSO 2003 and a couple with 2007. Of these, only 3 even have Access. Based on the city’s $3 million deficit, I don’t see any upgrades in the near future.
- My skill level is novice. I’ve done some VBA and my intent is to learn the Visual Studio apps as well as the open source ones. I’m trying to learn the “standard” ways to do things given what I have to work with.

One question I have about ‘standards ’ is what is the best way to store the text fields for names, addresses, etc? These will be used for printed transportation schedules in standard or title case and potentially for mail merges. I’ve also read a lot of discussion regarding table and field names. Is there a basic standard for most databases / programming languages?

The reason for disconnected recordsets is that it is possible that several users will be working with same groups of records at the same time. So the records will only be locked during the update.

For the user level I am dealing with, there will be a text box in the header of each form with instructions that will change as they move to each control - Simple step-by-step instructions.
As for the combobox control (cboLASTNAME or whatever is the proper case), it will be the primary control on most of the forms and will be the basis for populating other controls on the form.

I’ve gotten to the point where it is connected to the recordset as the form loads and has the first record visible. As the user starts typing in the control, the form fields are cleared and as each key is pressed, the value is stored in a public variable (strVar1).

The problem I’m having at this point is how to have it search the recordset to find all last names starting with the first character entered and open the drop down displaying the possible choices. As each character is entered, the list of names is reduced to only the possible matches. This will give the users the option to use the arrow keys / spacebar to select the name without needing to scroll the entire list.

I’m guessing I’m missing something simple at this point, but after hours of trying to figure it out I’m confused. Is there a simpler way to accomplish this than what I've put together through pasting examples and modifying them to my controls?

Below is part of the code for the form events:

Option Compare Database
Option Explicit
Dim rsCLIENTINFO As ADODB.Recordset
Dim cnSPT As ADODB.Connection
Dim strCONNECTION As String
Public strVar1 As String

Private Sub cboLastName_KeyUp(KeyCode As Integer, Shift As Integer)

 Dim strCharacter As String
 
    ' Convert ANSI value to character string.
    strCharacter = Chr(KeyCode)
    ' Convert character to ANSI value.
    KeyCode = Asc(strCharacter)
    'Debug.Print strCharacter
    
 strVar1 = strVar1 & strCharacter
 Debug.Print strVar1
 Debug.Print "KU = " & Me.cboLastName
    
 'I've tried many different ways to do this.
 'This is where I get the various errors.   
 rsCLIENTINFO.Find "txtLastName Like" & (strVar1)

End Sub

[Jim Doherty]

Your quest

Hi Procman :)

Well let us hope a whole bunch of people come piling in with useful hints and contributions.

I for one have a great deal of sympathy for the embattled, passionate, entrepreneurial individual trying to get the job done set against a portfolio of professionals offering nothing positive yet resisting/criticising till blue in the face.

It terms of database structure take a look at this

http://bytes.com/topic/access/insights/585228-database-normalization-table-structures

Within the access specification constraints of this

http://bytes.com/topic/access/insights/603897-microsoft-access-specifications

and this if your strategy is disconnected recordsets and web related

http://bytes.com/topic/access/insights/653114-persisting-recordset

http://bytes.com/topic/access/insights/783985-publishing-access-data-web

Some friendly references to ADO structure

http://bytes.com/topic/access/insights/661222-meet-ado-cursors

http://bytes.com/topic/access/insights/691618-ado-events-advanced-content

The list goes on but I dont want give information overload.

In relation to a standard naming of fields/tables or other objects I personally use the Lechinsky/Roddick naming convention. It is generally accepted as a good standard for use in VBA/VB. Beyond that ie: other languages the floor is open so to speak but lets face it once you become entrenched in one naming convention human nature tends to keep you entrenched

In terms of searching/filtering techniques these are many and varied of course and there are many 'good' examples on here of how to do that. I I am hoping other experts and mods will provide links to others in house as the thread steadily grows. Obviously I hope it does become too big so that it becomes boring to get involved. Feel free to repost specific questions to specific problems that you may have and hopefully that keeps the thread enjoyable and readable

As a starter... I will give you an example of an mdb file I created as demo for a user who wanted to mimic a google type search box in Access. This relies on the ON CHANGE event of a textbox control to provide the data value for incremental searching character by character. Take a look maybe it might give you a few ideas. Admittedly it is not done in Access Data Pages but the principle is there.

Personally I do not use Access Data Pages but I rather hope someone can provide meaningful examples to you in a similiar way

http://bytes.com/topic/software-development/answers/888229-producing-google-type-search-box-access-2007-a

Another search and retrieval mechanism via example download is this

http://bytes.com/topic/access/answers/771434-need-help-creating-customized-search-form

The above example method allows for multi field searching in combination and was put together merely to assist in technique, hone skills etc etc

Feel free to disect and rip apart thats the whole point of this.

Your design obviously is a matter for you given your circumstances but I do suspect given your passion and if are anything like what happened with me your IT Dept will 'have' to sit up and listen given your empire will arise like the proverbial Pheonix from the ashes :)

Good luck