Hi everyone,
I have a form for contacts where i have two issues.
Issue # 01
I have tblContacts and it has large number of fields which i have divided on frmContactsEntry with tab controls and assign unbound combo box to filter the specific record on form the combo box is PK in tblContacts [Names] but as i said its large number of fields which include personal and official details of contacts i have divided in three tabs first is "AddNew" and "WorkDetails" and "PersonalDetails" my problem here is when i select unbound combo box which is txtNames on tab "AddNew" it is not filtering on records related to that PK, i suspect it might be happening because of tab controls? because all fields of that PK is divided in three tabs.
i have following code but not working.
Issue # 02
I have frmContactSearch which have 3 three type of search criteria from which i have able to make event procedure for two and its working fine, however i have mentioned below for you reference to get help for third event procedure where i am getting wrong, but need help on that one.
1# The code which working is below (Search Type) working fine.
2 # Search for birthdays between dates (From Date - To Date) working fine.
3# This i need to filter the record for wild string search as you type (Need help).
Also i have unbound text box field on continues form which i want to be auto number with each entry take place, i dont know is it possible or not, i can do it by table auto number field but that i dont want to add unnecessary to table hence asking if it possible on form itself.
Thanks for your time and cooperation.
I have a form for contacts where i have two issues.
Issue # 01
I have tblContacts and it has large number of fields which i have divided on frmContactsEntry with tab controls and assign unbound combo box to filter the specific record on form the combo box is PK in tblContacts [Names] but as i said its large number of fields which include personal and official details of contacts i have divided in three tabs first is "AddNew" and "WorkDetails" and "PersonalDetails" my problem here is when i select unbound combo box which is txtNames on tab "AddNew" it is not filtering on records related to that PK, i suspect it might be happening because of tab controls? because all fields of that PK is divided in three tabs.
i have following code but not working.
Code:
Private Sub Form_Load()
Me.txtNames.SetFocus
Me.WorkDetails.Enabled = False
Me.PersonalDetails.Enabled = False
Dim ctl As Control
Dim strGot As String
Dim strLost As String
For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup
With ctl
strGot = "=SetFocusCtl([Form].[Name],[" & ctl.Name & "].[Name], True)"
strLost = "=SetFocusCtl([Form].[Name],[" & ctl.Name & "].[Name], False)"
.OnGotFocus = strGot
.OnLostFocus = strLost
End With
End Select
Next
End Sub
Private Sub txtNames_AfterUpdate()
Me.Recordset.FindFirst "Names = '" & Me.txtNames & "'"
End Sub
Issue # 02
I have frmContactSearch which have 3 three type of search criteria from which i have able to make event procedure for two and its working fine, however i have mentioned below for you reference to get help for third event procedure where i am getting wrong, but need help on that one.
1# The code which working is below (Search Type) working fine.
Code:
Private Sub BtnApplyFilter_Click()
Me.txtFromDate = Null
Me.txtToDate = Null
If IsNull(Me.cboSearch) Then
MsgBox "Please Select the search Type"
Me.cboSearch.SetFocus
Else
If Me.cboSearch = "Active" Or Me.cboSearch = "Employee Contacts" Then
Else
If IsNull(Me.txtSearchCriteria) Then
MsgBox "Please provide Search Criteria"
End If
End If
End If
If Me.cboSearch = "Type" Then
Me.Filter = "ContactMode = '" & Me.txtSearchCriteria & "'"
Me.FilterOn = True
Else
Dim StrSearch As String
Dim strText As String
strText = Nz(Me.txtSearchCriteria, "")
If Me.cboSearch = "Contact Name" Then
StrSearch = "SELECT * from tblContacts where (Names like '*" & strText & "*')"
Me.RecordSource = StrSearch
Else
If Me.cboSearch = "Company Name" Then
StrSearch = "SELECT * from tblContacts where (CompanyName like '*" & strText & "*')"
Me.RecordSource = StrSearch
Else
If Me.cboSearch = "Contact Person" Then
StrSearch = "SELECT * from tblContacts where (ContactPerson like '*" & strText & "*')"
Me.RecordSource = StrSearch
Else
If Me.cboSearch = "Work City" Then
StrSearch = "SELECT * from tblContacts where (City like '*" & strText & "*')"
Me.RecordSource = StrSearch
Else
If Me.cboSearch = "Work Email" Then
StrSearch = "SELECT * from tblContacts where (WorkEmail like '*" & strText & "*')"
Me.RecordSource = StrSearch
Else
If Me.cboSearch = "Work Mobile" Then
StrSearch = "SELECT * from tblContacts where (WorkMobile like '*" & strText & "*')"
Me.RecordSource = StrSearch
Else
If Me.cboSearch = "Work Telephone" Then
StrSearch = "SELECT * from tblContacts where (TelephoneWork like '*" & strText & "*')"
Me.RecordSource = StrSearch
Else
If Me.cboSearch = "Work Address" Then
StrSearch = "SELECT * from tblContacts where (WorkAddress like '*" & strText & "*')"
Me.RecordSource = StrSearch
Else
If Me.cboSearch = "Home City" Then
StrSearch = "SELECT * from tblContacts where (PersonalCity like '*" & strText & "*')"
Me.RecordSource = StrSearch
Else
If Me.cboSearch = "Personal Mobile" Then
StrSearch = "SELECT * from tblContacts where (PersonalMobile like '*" & strText & "*')"
Me.RecordSource = StrSearch
Else
If Me.cboSearch = "Personal Email" Then
StrSearch = "SELECT * from tblContacts where (PersonalEmail like '*" & strText & "*')"
Me.RecordSource = StrSearch
Else
If Me.cboSearch = "Personal Address" Then
StrSearch = "SELECT * from tblContacts where (PersonalAddress like '*" & strText & "*')"
Me.RecordSource = StrSearch
Else
If Me.cboSearch = "Active" Then
StrSearch = "SELECT * from tblContacts where (Active = True)"
Me.RecordSource = StrSearch
Else
If Me.cboSearch = "Employee Contacts" Then
StrSearch = "SELECT * from tblContacts where (Employee = True)"
Me.RecordSource = StrSearch
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End Sub
2 # Search for birthdays between dates (From Date - To Date) working fine.
Code:
Private Sub BtnDateFilter_Click()
Me.txtSearchCriteria = Null
Me.txtSearch = Null
Me.cboSearch = Null
If IsNull(Me.txtFromDate) Or IsNull(Me.txtToDate) Then
MsgBox "Please provide both dates for search criteria (From Date, To Date)"
Else
Me.Form.RecordSource = "QryContactsSearchByDate"
End If
End Sub
3# This i need to filter the record for wild string search as you type (Need help).
Code:
Private Sub BtnSearch_Click()
Me.txtSearchCriteria = Null
Me.cboSearch = Null
Me.txtFromDate = Null
Me.txtToDate = Null
Dim StrSearch As String
Dim strStext As String
If IsNull(Me.txtSearch) Then
MsgBox "Please enter the search value"
Me.txtSearch.SetFocus
Exit Sub
End If
strStext = Me.txtSearch.Value
Me.RecordSource = StrSearch
StrSearch = " SELECT * from tblContacts where (ContactID like ' * " & strStext & " * ')"
StrSearch = StrSearch & " Or (ContactMode Like '*" & strStext & "*')"
StrSearch = StrSearch & " Or (Names Like '*" & strStext & "*')"
StrSearch = StrSearch & " Or (CompanyName '*" & strStext & "*')"
StrSearch = StrSearch & " Or (ContactPerson Like '*" & strStext & "*')"
StrSearch = StrSearch & " Or (City Like '*" & strStext & "*')"
StrSearch = StrSearch & " Or (WorkEmail Like '*" & strStext & "*')"
StrSearch = StrSearch & " Or (WorkMobile Like '*" & strStext & "*')"
StrSearch = StrSearch & " Or (TelephoneWork Like '*" & strStext & "*')"
StrSearch = StrSearch & " Or (WorkAddress Like '*" & strStext & "*')"
StrSearch = StrSearch & " Or (PersonalCity Like '*" & strStext & "*')"
StrSearch = StrSearch & " Or (PersonalMobile Like '*" & strStext & "*')"
StrSearch = StrSearch & " Or (PersonalFax Like '*" & strStext & "*')"
StrSearch = StrSearch & " Or (PersonalEmail Like '*" & strStext & "*')"
StrSearch = StrSearch & " Or (PersonalAddress Like '*" & strStext & "*')"
Debug.Print StrSearch
End Sub
Also i have unbound text box field on continues form which i want to be auto number with each entry take place, i dont know is it possible or not, i can do it by table auto number field but that i dont want to add unnecessary to table hence asking if it possible on form itself.
Thanks for your time and cooperation.