Tab Control with a search box (1 Viewer)

[Sneale]

Hello, I need to create a tab control with a search box where the user will put in a portion employee name or ID number and will return with a list of possible employees. once the correct employee is selected, I want the subforms on the tab control to carry the employee information to add new records. I cannot figure this out. any help would be amazing

 

[vba_php]

your subform *control* is nothing more than a form object inside of another form object. you can insert a subform control through the ribbon or menu at the top on the appropriate tab. as far as your search is concerned, you would simply insert a button and put something like this behind the CLICK() event:

me.subform.form.filter = "[fieldName] LIKE " & """" & "*" & """" & me.searchBox & """" & "*" & """"
me.subform.form.filteron=true

but of course, you have to bind your subform control to a table or query where you FULL dataset is that you want to filter out values from. you can also do this as the user is typing characters, key by key. I believe the event of the search box for that is called KEYPRESS() or KEYDOWN().

 

[Sneale]

Thanks. I will see if I can get anywhere with that info.

 

[vba_php]

sorry. it was a little bit advanced. do you need a watered down version? =)

 

[AccessBlaster]

Not sure if this is what you are looking for. These are unbound search forms with tab controls.

 

[AccessBlaster]

I created theses a few years back, they are part of a SQL BE, but I should have the code if needed.

 

[AccessBlaster]

Behind the search button..

Private Sub cmdWHSearch_Click()
On Error GoTo Form_Err

    '*******************************************************************************
    'Code adapted from
    'http://www.techonthenet.com/access/forms/search2.php
    'The purpose of this code is to match the value in the control "txtSearchString"
    'and return all matching records, "like" txtSearchString.
    '*******************************************************************************

    If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
        MsgBox "You must select a field to search."

    ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
        MsgBox "You must enter a search string."

    Else

        GCriteria = " [" & cboSearchField.Value & "] LIKE '*" & txtSearchString & "*'"
        Form_frmWHSearch.RecordSource = "SELECT * FROM tblWarehouse INNER JOIN tblPurchData ON tblWarehouse.WarehouseID=tblPurchData.WarehouseID WHERE " & GCriteria
        DoCmd.OpenReport "rptWHSearch", acViewPreview, , GCriteria
        DoCmd.RunCommand acCmdFitToWindow

    End If

Form_Exit:
    Exit Sub

Form_Err:
    MsgBox Err.Description
    Resume Form_Exit

End Sub

And there is a module also.

    '******************************Code Start**************************************
    'This code is a modified version of the code that can be found at:
    'http://www.techonthenet.com/access/forms/search2.php
    'http://allenbrowne.com/casu-15.html
    'http://www.access-programmers.co.uk/forums/forumdisplay.php?f=63
    'http://www.accessforums.net/code-repository/
    'http://www.accessmonster.com/Default.aspx
    'http://support.microsoft.com
    'http://www.fontstuff.com/access/acctut20.htm
    'The original code has been modified by ********** from 07/2005 to 06/2012
    'It is not to be altered or distributed,
    'except as part of an application.
    '******************************************************************************

Option Compare Database
Option Explicit

'******************************************************************************
'Global variable used to store search criteria
'******************************************************************************

Global GCriteria As String

 

[vba_php]

all that code might be a bit difficult for this person to take in, AB. :/

 

[AccessBlaster]

all that code might be a bit difficult for this person to take in, AB. :/

I know and to be honest I adapted some of this not fully understanding it either back in 2005, but we all have to start our jounrney somewhere.

 

[vba_php]

yes, true. I even overloaded him I think.

 

[MajP]

This does what you ask
1)Allow to filter by two filters (customer id, or customer name)
2)Select from filter list
3)Filter child records
4)add new child records to the choosen customer

Using a class module you basically can do this in one line of code

Dim FAYT_Cust As New FindAsYouTypeCombo
Private Sub Form_Load()
  FAYT_Cust.InitalizeFilterCombo Me.CmboCust, "CompanyName"
End Sub

Private Sub Frame6_AfterUpdate()
  Select Case Frame6
    Case 1
      FAYT_Cust.FilterFieldName = "CompanyName"
    Case 2
      FAYT_Cust.FilterFieldName = "CustomerID"
  End Select
End Sub

 

[MajP]

I can see from that picture the Frame was not working. The after update event property needs to be updated to [Event Procedure]. I must have deleted it.

 

[AccessBlaster]

yes, true. I even overloaded him I think.

He only has only six posts so he may have moved on. There are tons of resources I showed a few.

 

[Sneale]

I havent moved on just yet. Been pulled away by other pressing issues. Thank you all for the clarifications. I will see what I can do and come back if there are any other questions or I get stuck. (I am relatively new to Access and am fumbling through with youtube and a couple books.)