Record Selector Combo Box Question???

[SyBeerianTyGRRRR]

Hi folks,

I have a form in my db in which i store all my business contacts name, address, phone no etc. The components are as follows:

frmContacts (the Form !!)
tblContacts (Record Source)

I have put a record selector combo box at the top of frmContacts with the following code attached to the After Update event procedeure:

Private Sub cboContactNameSearchList_AfterUpdate()
On Error GoTo ErrorHandler

Dim strSearch As String

strSearch = "[LastName] = " & Chr$(39) & _
Me![cboContactNameSearchList] & Chr$(39)

'Find the record that matches the control
Me.Requery
Me.RecordsetClone.FindFirst strSearch
Me.Bookmark = Me.RecordsetClone.Bookmark

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End Sub

This works fine for most of the records stored but the problem is i only have first names for some of the people listed and the code above will only select records where a [LastName] value is present.
Can someone tell me how to change this code so that selecting one of these [FirstName] only contacts in the combo box will go to that record in the form???

Could it be something like:

strSearch = "[LastName]" & "[FirstName]" = " & Chr$(39) & _

???

Or alternatively do i just need to change some or one of the properties in Design View to enable this.

Any assistance would be greatly appreciated.

Stu
UK

 

[Eupher]

I would do 2 things:

1) Edit the row source of the combo and add the first name field to the grid. Arrange the grid so that the columns display as you wish, i.e. |FirstName|LastName| or vice versa.

2) Change your code to search on both fields, something like this:

Dim strSearch As String
   
strSearch = "[FirstName] = '" _
& Me![cboContactNameSearchList].Column(0) & "' AND " _
& "[LastName] = '" & Me![cboContactNameSearchList].Column(1) & "'"
      
'Find the record that matches the control
Me.Requery
Me.RecordsetClone.FindFirst strSearch
Me.Bookmark = Me.RecordsetClone.Bookmark

You'll need to adjust the column index in the search string to match the columns as you have them displayed in the combo. It's zero-based, so the first column displayed is .Column(0), the 2nd column is .Column(1), and so forth. Note that I've included the single quotes inside the double quotes instead of using the Chr() function to specify the character code. They should concatenate the same.

Ken S.

 

[SyBeerianTyGRRRR]

Thanks Eupher,

Will give it a try.

Ta very much for the help.

Stu

 

[SyBeerianTyGRRRR]

Nope, can't seem 2 get that to work at the moment.

Row source grid is as follows:

SELECT DISTINCTROW tblContacts.LastName, tblContacts.FirstName FROM tblContacts ORDER BY tblContacts.LastName, tblContacts.FirstName;

After Update code event is now:

Private Sub cboContactNameSearchList_AfterUpdate()
Dim strSearch As String

strSearch = "[FirstName] = '" _
& Me![cboContactNameSearchList].Column(2) & "' AND " _
& "[LastName] = '" & Me![cboContactNameSearchList].Column(1) & "'"

'Find the record that matches the control
Me.Requery
Me.RecordsetClone.FindFirst strSearch
Me.Bookmark = Me.RecordsetClone.Bookmark

End Sub

Can you advise what i may have missed or done wrong???

Any further help would be greatly appreciated.

Stu

 

[SyBeerianTyGRRRR]

To all still interested,

Have solved this problem by merely reversing the entry of LastName and FirstName in the Row Source property grid i.e.

SELECT DISTINCTROW tblContacts.FirstName, tblContacts.LastName FROM tblContacts ORDER BY tblContacts.FirstName, tblContacts.LastName;

and using the following After Update code:

Private Sub cboContactNameSearchList_AfterUpdate()
On Error GoTo ErrorHandler

Dim strSearch As String

strSearch = "[FirstName] = " & Chr$(39) & _
Me![cboContactNameSearchList] & Chr$(39)

'Find the record that matches the control
Me.Requery
Me.RecordsetClone.FindFirst strSearch
Me.Bookmark = Me.RecordsetClone.Bookmark

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End Sub

Everything now works just as i want. Many thanks to all who offered help.

Cheers

Stu
UK

 

[Eupher]

Stu, glad you got it working. For future reference, though, I'll point out that column references are zero-based. That is, the first visible column is column(0), not column(1) as you have in your code.

Ken S.

 

[Eupher]

Errata: In my previous post I said that the first visible column is the 0 column; that's not correct. The first column is the 0 column, whether it's visible or not.

Ken S.