refresh

[lvmoore]

I have a subform (subfrmOrderLines) which has a control (txtItemdescription) whose keyup event creates a string (strsearch) which in turn feeds into a SQL that is used as the recordsource for a second subform (subfrmProductList) also on the main form.

My problem is that to show the most recent letter added to the txtItemdescription control I need to refresh the form. When I do that it erases all spaces which means I can't type in a string such as "Nutram chicken" because everytime I press the space bar the cursor returns to right after the 'm' on the word Nutram. Is there a way of coding around this?

My current thought is that there must be a way of making some sort of 'if/elseif' statement to skip the refresh if the character before the keypress was a space or a back arrow. Problem is I don't know how to say "the character entered before the keyup was a space/back arrow" in code -- but there must be a way.

All help welcome. For your perusal my code follows:

Private Sub txtItemDescription_KeyUp(KeyCode As Integer, Shift As Integer)

'--declare the variables
Dim strProduct As String
Dim strsearch As String
Dim strtest As String
Dim strSQL As String

'--refresh the form so field to include the most recently entered character
'**here is problem where I need an If statement to bypass refresh if last stroke was space or back arrow
    Me.Refresh

'--go to the end of the field
    Me!txtItemDescription.SelStart = Me.txtItemDescription.SelLength 'move cursor to end of the selection
'--set the wildcard search items
    strsearch = Chr(34) & "*" & Me!txtItemDescription & "*" & Chr(34)
    
'--set SQL that will be the recordsource for the form
    strSQL = "SELECT tblProducts.ItemDescription, tblProducts.Category, tblCategories.Category" _
        & " FROM tblCategories INNER JOIN tblProducts ON tblCategories.CatID = tblProducts.Category" _
        & " WHERE (tblProducts.Itemdescription like " & strsearch & ") Or (tblCategories.Category like " & strsearch & ")"
    
'--set the recorsource to the strSQL above and make subform visible
    Me.Parent!subfrmProductList.Form.RecordSource = strSQL
    Me.Parent!subfrmProductList.Form.Visible = True

End Sub

 

[pbaldy]

Try

If KeyCode = 32 Then
  Exit Sub
End If