After update problem with a list box 1

[under8d]

I am trying to create a form where the user selects the distinct first letter of the last names from a list box.

SELECT DISTINCT [qryMem].[LETTER] FROM qryMem;

Then from a list box containing the distinct last names;

SELECT DISTINCT [qryMem].[LAST_NAME], [qryMem].[LETTER] FROM qryMem WHERE ((([qryMem].[LETTER]) Like [Forms]![SearchFormByLetter]![lstLetter]));

The final (third) list box presents the first names and the member ID’s to the user. The member ID’s are required since there are several duplications of first and last names in the database and a method is required to distinguish them.

SELECT [qryMem].[FIRST_NAME] & ' - ' & [qryMem].[MEM_ID] FROM qryMem WHERE ((([qryMem].[LAST_NAME])=[Forms]![SearchFormByLetter]![lstLastName])) ORDER BY [qryMem].[FIRST_NAME];

So far, kit seems to work just fine.

Now I am trying to update the record which lives on the same form as the list boxes. Here is the code that I have that lives in the “After Update” of the lstFirstName list box, however I haven’t been able to figure out the syntax. Because I have both a “MemId” field and a “FIRST_NAME”. in the final list box

Private Sub lstFirstName_AfterUpdate()
'Want it to move to MemID text box and
'find the record of whatever “MemID” is selected in the combo box
DoCmd.ShowAllRecords
Me!txtMemID.SetFocus
DoCmd.FindRecord Me!lstFirstName
End Sub

This is a project that I worked on a few years ago for a church membership and it was abandoned. I haven’t touched it or any other Access projects since. I suspect that the solution is evident, however it seems that about 99% of what I knew about MS Access has slipped away from me and I would like to get it back! Thank heaven for folks like you.

I have browsed through the forums and haven’t found anything that is obviously like I am trying to accomplish, and I am feeling like I am in way over my head (as usual).

I hope that I have made this clear enough. Any suggestions would be appreciated.

Ray

 

[TheAceMan1]

How are ya under8d . . .

First two things:
[ol][li]Your final search should be looking for [blue]Mem_ID[/blue], not [blue]First_Name[/blue] as its possible to have the same last & first names![/li]
[li]I think [blue]you've over extended yourself[/blue] with the third listbox.[/li][/ol]
[purple]If you want to try the follow copy/backup the form under another name so you can come back to square one . . .[/purple]

Consider the following SQL for the 2nd listbox [blue]lstLastName[/blue]:

[blue]SELECT DISTINCT MEM_ID, LAST_NAME, FIRST_NAME 
FROM qryMem 
WHERE (LETTER] Like [Forms]![SearchFormByLetter]![lstLetter]) 
ORDER BY LAST_NAME,FIRST_NAME;[/blue]

Here the listbox shows all three fields (left to right as in the query). Realize wether you use lastname and/or firstname your depending on MEM_ID for uniqueness no matter what! Besides I think its easier to know what your looking at and put together at 1st glance.

If the above SQL is installed [blue]lstLastName[/blue] will require the following property changes:
[ol][li]Column Count [blue]3[/blue][/li]
[li]Column Widths [blue]1";1";1"[/blue][/li]
[li]List Width [blue]3"[/blue][/li][/ol]
You can adjust as necessary once you see the list.

With the above you'll now be triggering your search with the AfterUpdate event of [blue]lstLastName[/blue] with:

[blue]Me.Recordset.FindFirst "[Mem_ID] = [red][b]'[/b][/red]" & Me!lstLastName & "[red][b]'[/b][/red]"[/blue]

If Mem_ID is numeric remove the two single quotations in [red]red[/red].

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[under8d]

Thanks AceMan1;

I appologize for not responding to your helpful post. I am trying to mop up a number of issues before I leave on a holiday for 10 days. I may not get around to implementing your ideas before I leave, but will certainly explore your suggestions. You will probably hear form me again as I clearly have very little idea as to what I am doing. Much appreciated!

Ray