Use List Box to Open Form (1 Viewer)

[cambonner]

Please help me with a problem I'm having constructing a database for a local food pantry. As an Access 2007 beginner, I can't figure out how to link names of clients in a list box to the chosen Client Data Entry Form. My list box is in its own form, with its contents derived from the first & last name fields of the main Client Info table. I want the db user (any of a number of db-phobic volunteers) to easily navigate to a client's record in order to update information. I've built a macro that opens the Client Data Entry Form when the name on the list box is double-clicked. However, this action always opens the form at the first record, not the record of the chosen name. The list box design view shows it as unbound. I assume it needs to be bound, but don't know how to do this. Any/All help appreciated.
Cameron

 

[John Big Booty]

Have a look at the Where condition of the DoCmd.OpenForm method.

The sample here uses that method in the List Box's Double Click event.

 

[cambonner]

John,
Thanks so much for leading me to the code that allows list box searches. The code works well, but I still can't get the found name in the SearchResults box to open the person's form on a double-click. In fact, the double-click produces no response. I've used the exact names provided in your example, except for the form I need to open, which is called fExisting Client Data Form. Any suggestions?
Thanks,
Cameron

PS: I've gotten the right form to open on double click, but not the right record. It takes me to a blank (new) record. I've written this code, which I assume is the source of my problem:
Private Sub SearchResults_DblClick(Cancel As Integer)
DoCmd.OpenForm "fExisting Client Data Form", acNormal, , "Last Name" = Me.SearchResults
End Sub

 

[John Big Booty]

Are you able to post a copy of your DB?

 

[cambonner]

John,
I can send you the db (about 1MB) via Dropbox, but I need to have email or facebook contact info. Or, as an email attachment.
Thanks for your help.
Cameron

 

[John Big Booty]

Just do a compact and repair on it, put it in a zip file and upload that.

 

[cambonner]

Hi John,
Here is the zipped file (I hope).
Also, would it be asking too much for you to help me figure out why when I fill out the New Client Data Entry form and save and close it, it does not add the new record to the first two tables?
Thanks so much for your help.
Cameron

 

[John Big Booty]

First up a couple of quick observations.

Avoid using spaces and other special characters in Object and Control Names. Limit yourself to alpha and numeric charcters and the underscore. Consider a naming protocol along the lines of FRM_FormName, TBL_TableName, QRY_QueryName etc.

You might also want to read up on the subject of Data Normalisation and perhaps even work through a tutorial on the subject.

Now the problem at hand; when you refer to a list box or combo, by default you are referring to the first (or Column(0) {often hidden}), so given that your last name is held in the second column you need to refer to it as Column(1). Now as this is a string value you also need to enclose it in quotation marks so your code needs to look like;

DoCmd.OpenForm "fExisting Client Data Form", acNormal, , "[last Name] [COLOR="Red"]= '" &[/COLOR] Me.SearchResults[COLOR="Red"].Column(1) & "'"[/COLOR]

Your other problem will likely be easier fixed once you have normalised your table structure.

 

[cambonner]

Thanks, Big John. Your code fix works great. As suggested, I'll get smarter on Normalization. My source of Access knowledge 'til now has been Access 2007 for Dummies, a group to which I clearly belong. It is curious that the Dummies book does not include normalizing in its contents or index.
You've been a great help!
Cameron