continuous form record selection (1 Viewer)

[ClaraBarton]

I have the following form for editing various lists:

When form is first loaded or when using the option to switch lists in the first subform, the first item will not select until I've clicked on another record and then back to the first. It's not a deal breaker but not very professional acting. The code behind is this:

'Tie Recipes to txtLink which is set by recipe selected
'and filters RecipeDetail to selection
'Highlight selected row
Private Sub Form_Current()
10    On Error GoTo Form_Current_Error
20      ListFill
30      Me.Parent![txtLink] = Me!ID
'
40    If Not IsNull(Me.Parent![txtLink]) Then
50      Me.Repaint
60      Me.Refresh
70     End If

80    On Error GoTo 0
90    Exit Sub

Form_Current_Error:
100       MsgBox "Error " & Err.Number & " (" & Err.description & ") " & _
              " in procedure Form_Current, line " & Erl & "."
End Sub

Public Sub ListFill()

Dim intOpt As Integer
Dim sqlCookbook As String
Dim sqlChapter As String
Dim sqlType As String
Dim sqlUnit As String
Dim sqlGrocery As String
Dim frmList As Form

    Set frmList = Me.Parent.Form!lstsubRecipes.Form
    intOpt = Me.Parent!optList.Value
    Me.Parent!txtLink = Me.ID
    sqlCookbook = "Select RecipeID, Recipe, " & _
        "CookbookIDFK AS ID " & _
        "FROM qryRecipe"

    sqlChapter = "Select RecipeID, Recipe, " & _
        "CookbookChapterIDFK AS ID " & _
        "FROM qryRecipe"
       
    sqlType = "Select RecipeID, Recipe, " & _
        "TypeIDFK AS ID " & _
        "FROM qryRecipe"
       
    sqlUnit = "SELECT DISTINCT qryRecipe.RecipeID, qryRecipe.Recipe, " & _
        "t_ingredient.UnitIDFK AS ID " & _
        "FROM qryRecipe " & _
        "INNER JOIN t_ingredient " & _
        "ON qryRecipe.RecipeID = t_ingredient.RecipeIDFK " & _
        "WHERE (((t_ingredient.UnitIDFK) Is Not Null))"
       
    sqlGrocery = "SELECT DISTINCT qryRecipe.RecipeID, qryRecipe.Recipe, " & _
        "t_ingredient.GroceryIDFK AS ID " & _
        "FROM qryRecipe " & _
        "INNER JOIN t_ingredient " & _
        "ON qryRecipe.RecipeID = t_ingredient.RecipeIDFK " & _
        "WHERE (((t_ingredient.GroceryIDFK) Is Not Null))"

    Select Case intOpt
        Case 1
            frmList.RecordSource = sqlCookbook
        Case 2
            frmList.RecordSource = sqlChapter
        Case 3
            frmList.RecordSource = sqlType
        Case 4
            frmList.RecordSource = sqlUnit
        Case 5
            frmList.RecordSource = sqlGrocery
    End Select
End Sub

Basically just queries to fill the first subform.
In other words, the Parent.txtlink does not pick up the ID until I've clicked around.
Is there a way I can fix this?

 

[The_Doc_Man]

Just as a side note: Your lines 50 and 60... normally a .Refresh implies a .Repaint, just as a .Requery implies a .Refresh - so you can save yourself some extra work. But your ListFill implies a .Requery because you dinked with the .Recordsource - so you shouldn't have to do EITHER the .Repaint OR the .Refresh.

 

[ClaraBarton]

Huh! you are so right. Got rid of them.
The first click anywhere in the list except the first item immediately fills the Parent.txtlink.