ComboBox ListIndex Error

[RascalBird]

HI

I've found a couple of similar posts but no matter what I try I can't figure out what's going wrong with my code and it's doing my head in! :banghead:

I have a form (frmInactiveBusiness) which lists old businesses. I want the user to be able to select the business and when clicking a command button, have the main Business Details form open and get populated with the Inactive Business information (if that makes sense hehe)

The code works perfectly except when I have the first record selected.

When the first record is selected the form doesn't populate at all which of course gives me a list index error (well there's nothing there).

I've put watches on and the variables seem to populate happily... any thoughts?

Function DisplaySelectedBusiness()
    Dim i As Long
    Dim F As Form
    Dim RS As Recordset
    Dim BusNum As String
    Dim x As String
    
    Dim LResponse As Integer
    
    i = 1
    
    ' get the form and its recordset
    Set F = Forms![frmInactiveBusiness]
    Set RS = F.RecordsetClone
    
    ' move to the first record
    RS.MoveFirst
    
    ' move to the first selected record
    RS.Move F.SelTop - 1
    
    i = F.SelTop
    
    BusNum = RS![BusinessKnownName]
    
    LResponse = MsgBox("Do you wish to view '" & RS![BusinessKnownName] & "'", vbOKCancel, "View Details")
            
    If LResponse = vbCancel Then
        Exit Function
    End If
    
    x = "SELECT [ABNName],[BusinessKnownName],[BusinessABN] FROM [business] WHERE [BusinessKnownName] =  '" & BusNum & "'"
    
    DoCmd.OpenForm "frmBusinessDetails"

    Forms!frmbusinessdetails!cmb_business.SetFocus
    
    Forms!frmbusinessdetails!cmb_business.RowSource = x
    Forms!frmbusinessdetails!cmb_business.Requery
    Forms!frmbusinessdetails!cmb_business.ListIndex = 0
    
    
End Function

 

[Isskint]

The problem may be with the line RS.Move F.SelTop - 1. If you are at first record and move 0 records, it sees the Absolute Position in the record set as 0 (which does not exist). try putting in a conditional test for first record and do not use Move if =1, EG

If F.SelTop > 1 then
       RS.Move F.SelTop - 1
End If

 

[RascalBird]

Erm... slightly embarrassed at having worked out what the solution was.

The first entry in [BusinessKnownName] had an apostrophe in it (e.g. HG's) which was of course was causing problems in the way the data was returned.

Delete the apostrophe out of the field and it worked.

How do I allow for apostrophe's in the code?

WHERE [BusinessKnownName] =   '" & BusNum & "'"

 

[Isskint]

I do not think you can. What you need is to include code to check an entry does not contain reserved characters (' and & are the biggest culprits) and remove them (or replace them) when they are first recorded (users will never understand).

I set up a query and a function to check for reserved characters. The query is an update query and the update value is the returned value from the function. The function uses replace to remove the reserved characters (or replace them). I get a 'SuperUser' (it makes them feel special) to run this once a week as a back stop to the checks i make at the point of input.