selecting an entry in listbox, show record

[Guest]

I am trying to come out with a function that when you have selected an entry
in the listbox, it will open up another form with the relevant details
pertaining to the entry in the listbox. As in my listbox field will be
Company. And when i click on like ABC company, it will show the relevant
details of the company like date and time for visit to customer site? It can
also be on the same form.

I tried doing that but got multiple entries for the company in my record if
i put another record in the subform. Any suggestions?

 

[Guest]

Your post is somewhat confusing, but a couple of things should be considered.
First, is there any specific reason you are using a List Box for this? If
you are selecting only one company, the Combo Box is a much better control to
use for locating a specific record. List boxes are great when you need to
allow the user to make multiple selections or when you want to display
multiple columns all the time.

The most common technique is to use the After Update event of the combo box
to locate and present the selected record.

This sentence really confused me:
"> I tried doing that but got multiple entries for the company in my record
if

i put another record in the subform. Any suggestions?"

You don't get multiple entries in one record, and what do you mean by "put
another record in my subform"?

 

[Guest]

Oh sorry, my bad. It's for Single Selection. Thanks.

I meant that for the combo box, we choose one company. For that company, we
have multiple records of the date and times of visit to that company.

So you mentioned about the AfterUpdate event, may I know what code should I
put there? kinda like a noob in this.

I mean for multiple records as in when I added another entry under the
subform for the same company. I would get another record with the same
company the next time around.

About put another record in my subform, as i mentioned about the logs of
dates and times of visit, there would be multiple entries for that company
for the visits.

Best regards,
Chris

 

[Guest]

Well, I am not quite sure what you are trying to accomplish. So you have
multiple records for a client by Date and Time. The first question is can
there be more than one visit on the same day ?
This would make a difference in how you design your form.
Also, there are more than one approach that could be used for this. One
would be to use 3 combo boxes with a technique called "Cascading Combos".
That is where the second combo would filter on the selection of the first and
the third would filter on the selection of the second. Then you would have
what you need to select a unique record.

Another approach would be to have a subform displaying the records and use a
combo box on the main form to filter the record source of the sub form. Then
use the subform to select a specific record.

In either case, the first combo to look up the client would look something
like this:

Private Sub cboFindClient_AfterUpdate()
Dim rst As Recordset

Set rst = Me.RecordsetClone

With rst
.FindFirst("[CLIENT_NO] = " & Me.cboFindClient)
If Not .NoMatch Then
Me. Bookmark = .Bookmark
End If
End With

Set rst = Nothing

End Sub