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
-