Creating a new database with forms and subforms and I need some guidance (1 Viewer)

[estin]

Hi, I'm fairly new to Access and have only been learning vba the past couple of months. I understand the concepts of many uses of access and have so many ideas of what I’d like to do but, don’t always know where to start.

I've taken on a project where the linked table that are used I've pulled into a query on the fields that are needed and created a form with a subform.



I created the following buttons:

• One "Search for" text box that searches up to 5 fields in the query to show the results in a text box and the subform.
  • When I tab or hit enter the search begins.. ? Is there a way for this to happen with a Execute Search button instead? I tried to write the same code as the after update and implement it into a click but, there was an error.
  • ? I’d like to be able to show in this box if there are no results a message box unless anyone has any other suggestions.
• "Search Results" text box with 8 columns.
  • ? Can I select items from this box and move them into another form one or several at a time?
  • ? Is there a way for the after update code to run quicker? The timeframe is sometimes up to a minute for it to load. Average is 30 seconds.
• "Records Found" Text box
• "Records Selected" In progress pending the search results selections if possible.
• “Clear Form Button”
  • ? This button only clears the Search For box and not the Search Results Text box or the Subform. I’d like it to clear the whole form. How do I do that?
• "Linked Search for" button that displays the same information as the Search for. I did this because sometimes after the Search for runs it doesn't show the display information.
• Subform that shows 9 columns.
  • ? The only difference between the Text Box “Search Results” and the subform is the YES/NO column that I’d like the user to be able to select and move to a Form. Either from the subform or the Text box whatever is more Effient and user friendly.

Here is the code that I’ve used:  
 
Private Sub Clear_Form_Click()
Me.Searchfor = ""
Me.SrchText = ""
Me.SearchResults = ""
End Sub
 
 
Private Sub Reset_Searches_Click()
    Dim ctl As Control
 
    For Each ctl In Me.Controls
        Select Case ctl.ControlType
            Case acTextBox, acComboBox, acListBox, acCheckBox
                If ctl.ControlSource = "" Then
                    ctl.Value = Null
                End If
            Case Else
        End Select
    Next ctl
 
End Sub
 
Private Sub Searchfor_AfterUpdate()
'Create a string (text) variable
    Dim vSearchString As String
 
'Populate the string variable with the text entered in the Text Box Searchfor
vSearchString = Searchfor.Text
 
'Pass the value contained in the string variable to the hidden text box SrchText,
'that is used as the search criteria for the Query QRY_SearchAll
    SrchText.Value = vSearchString
 
'Requery the List Box to show the latest results for the text entered in Text Box Searchfor
    Me.SearchResults.Requery
 
'Tests for a trailing space and exits the sub routine at this point
'so as to preserve the trailing space, which would be lost if focus was shifted from Text Box Searchfor
    If Len(Me.SrchText) <> 0 And InStr(Len(SrchText), SrchText, " ", vbTextCompare) Then
        'Set the focus on the first item in the list box
            Me.SearchResults = Me.SearchResults.ItemData(1)
            Me.SearchResults.SetFocus
        'Requery the form to refresh the content of any unbound text box that might be feeding off the record,
        'source of  the List Box
            DoCmd.Requery
        'Returns the cursor to the the end of the text in Text Box Searchfor,
        'and restores trailing space lost when focus is shifted to the list box
            Me.Searchfor = vSearchString
            Me.Searchfor.SetFocus
            Me.Searchfor.SelStart = Me.Searchfor.SelLength
 
        Exit Sub
    End If
 
'Set the focus on the first item in the list box
    Me.SearchResults = Me.SearchResults.ItemData(1)
    Me.SearchResults.SetFocus
 
'Requery the form to refresh the content of any unbound text box that might be feeding off the record source of  the List Box
    DoCmd.Requery
 
'Returns the cursor to the the end of the text in Text Box Searchfor
    Me.Searchfor.SetFocus
 
    If Not IsNull(Len(Me.Searchfor)) Then
        Me.Searchfor.SelStart = Len(Me.Searchfor)
    End If
 
End Sub

Thank you in advance

 

[Mr Smin]

search for: You can have a button whose code is
Private Sub myButton_Click()
'do search code, or, to share with what you already wrote,
Call Searchfor
end sub

I'd set the value of Searchresults to "No results" rather than use a message box.

Sorry, don't have answers for your other questions.