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:
Thank you in advance
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.
Code:
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