Alphabetic List box

[Castanz]

I have a database that has employees' first names and last names, but no SSN or employee number. My problem is the prevention of duplicates. I would like to prompt the data entry clerk if the clerk tries to enter any person who is already in the data base. For example: You type in Johnson for last name, then you type in the J for John. I would like a prompt to state in a list box: "There is a Jane Johnson; John Johnson, Jorge Johnson." Direct the user to the edit screen if the name is already in the database. Let the user continue if the employee is not in the database. The row source from the list box looks like this:

SELECT [Tbl_MEO Information].ID, [Tbl_MEO Information].[Last Name], [Tbl_MEO Information].[First Name], [Tbl_MEO Information].Inactive FROM [Tbl_MEO Information] WHERE ((([Tbl_MEO Information].[Last Name]) Like [Global_Letter] & "*") AND (([Tbl_MEO Information].Inactive)<>-1)) ORDER BY [Tbl_MEO Information].[Last Name];

Where "Tbl_MEO Information" is the table. Global Letter is the first letter of the Last name. I am testing with pulling up all last names that begin with the same letter. The problem is my form asks me for global_letter. If I type the letter I want the list box works correctly. The trick is to get the above code to accept the global_letter variable and not request it.

I hope I wasn't to long in my description. Any suggestions would be appreciated.

Al

 

[KUZZ]

the thre fields FIRST NAME, LAST NAME and SSN primary keys with no duplicates. In that case when you enter a dubplicate you will get a debug error, remember the number of the error and then

ON ERR GOTO ERR_TRANSLATOR

AND FOR ERR_TRANSLATOR WRITE

ERR_TRANSLATOR:

Select Case ERR.Number
Case Is = "XXX"
msgbox "THIS USER IS ALREADY IN THE DATABASE"
Case Is = "0094"
MSGBOX OTHER MESSAGE, ETC
Case Else
MsgBox ERR.Number & ": " & ERR.DESCRIPTION
End Select

Resume Exit_your_sub

Your clerc will have to hit esc to cancel the record entry or use SENDKEYS({esc},true) to have code send the tab equivalent to the system.


Good luck,
Kuzz

"Time spent debating the impossible subtracts from the time during which you
can try to accomplish it."

 

[Castanz]

I am going about this a different way. I saw a method of changing a list box through a User-Defined Function. In the properties of the list box I made the "Row Source Type" equal to ListEmps and the rowsource leaving it blank.

Function ListEmps(List4 As Control, id As Variant, _
    row As Variant, col As Variant, code As Variant) _
     As Variant
    Dim intOffset As Integer
    
    Select Case code
        Case acLBInitialize            ' Initialize.
            ListEmps = True
        Case acLBOpen                    ' Open.
            ListEmps = Timer        ' Unique ID.
        Case acLBGetRowCount            ' Get rows.
            ListEmps = 4
        Case acLBGetColumnCount    ' Get columns.
            ListEmps = 1
        Case acLBGetColumnWidth    ' Get column width.
            ListEmps = 30           ' Use default width.
        Case acLBGetValue                ' Get the data.
            ListEmps = 'extract row by row the record with the last name that begins with global_letter
    End Select
End Function

Can someone help me with the piece of code on the last ListEmps?
Thanks again,
Al

 

[TheAceMan1]

How are ya Castanz . . . .

My first stab at this (I havn't tested it yet) would be the following. Note: [blue]Blue[/blue] replaces the global letter, [purple]purple[/purple] are the actual field names provided by you!

TheQuery:

SELECT [Tbl_MEO Information].ID, [Tbl_MEO Information].[Last Name], [Tbl_MEO Information].[First Name], [Tbl_MEO Information].Inactive FROM [Tbl_MEO Information] WHERE ((([Tbl_MEO Information].[Last Name]) Like [blue]Nz(Forms![purple]YourFormName?[/purple]![purple]YourLastNameFieldName?[/purple],"")[/blue] & "*") AND (([Tbl_MEO Information].Inactive)<>-1)) ORDER BY [Tbl_MEO Information].[Last Name];

In the [blue]On Change Event[/blue] of the listbox, add the following line:
[blue]Me![purple]YourListBoxName[/purple].Requery[/blue]

[blue]The ListBox should adjust itself as you type each character![/blue]

Give it a whirl and let us know . . . . .

See Ya! . . . . . .

 

[Castanz]

Thanks. That got me going in the right direction. Here is the code I ended up with from your suggestion:

SELECT [Tbl_MEO Information].ID, [Tbl_MEO Information].[Last Name], [Tbl_MEO Information].[First Name], [Tbl_MEO Information].Inactive FROM [Tbl_MEO Information] WHERE ((([Tbl_MEO Information].[Last Name]) Like [Forms]![Edit Last Name or New]![Last Name] & "*") AND (([Tbl_MEO Information].Inactive)<>-1)) ORDER BY [Tbl_MEO Information].[Last Name];

Where "Edit Last Name or New" is the form name and "Last Name" is the field. For some reason I wasn't able to do it with a variable.

Thanks again,
Al

 

[PHV]

For some reason I wasn't able to do it with a variable
Provided Global_Letter is a variable (and not a control) you can try something like this:
sql = "SELECT I.ID,I.[Last Name],I.[First Name],I.Inactive" _
& " FROM [Tbl_MEO Information] AS I" _
& " WHERE I.[Last Name] Like '" & Global_Letter & "*'" _
& " AND I.Inactive<>-1 ORDER BY I.[Last Name]"

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244