Update fields on tab control after combo box event (1 Viewer)

YAM

New member
Local time
Today, 14:16
Joined
Jul 15, 2020
Messages
26
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.

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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:46
Joined
Oct 29, 2018
Messages
21,358
Hi. Just a thought... It might be easier if you could post a demo version of your db with test data.
 

YAM

New member
Local time
Today, 14:16
Joined
Jul 15, 2020
Messages
26
Please find attached.
 

Attachments

  • Test.accdb
    1.1 MB · Views: 526

theDBguy

I’m here to help
Staff member
Local time
Today, 01:46
Joined
Oct 29, 2018
Messages
21,358
Please find attached.
Hi. Thanks. Now, can you please give us a step-by-step instructions on how to duplicate the problem? Also, please elaborate on what the problem is. For example, what do you mean by "but not working?"
 

YAM

New member
Local time
Today, 14:16
Joined
Jul 15, 2020
Messages
26
Issue # 01 (frmContactsEntry)

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 on remaining tab controls "WorkDetails" and "PersonalDetails", i suspect it might be happening because of tab controls? because all fields of that PK is divided in three tabs.

it is same find.first procedure to filter records from unbound combo box which is not happening in this case, it should populate fields based on value i select in combo box txtNames to the remaining tab controls because these fields are from same table fields which is tblcontacts.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:46
Joined
Oct 29, 2018
Messages
21,358
Issue # 01 (frmContactsEntry)

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 on remaining tab controls "WorkDetails" and "PersonalDetails", i suspect it might be happening because of tab controls? because all fields of that PK is divided in three tabs.

it is same find.first procedure to filter records from unbound combo box which is not happening in this case, it should populate fields based on value i select in combo box txtNames to the remaining tab controls because these fields are from same table fields which is tblcontacts.
Okay, that's not a step-by-step instruction. Please remember, we are not familiar with your database as you are, so we won't be able to easily know what you mean. In any case, I tried to open frmContactEntry and got this error. Is this what you mean by "not working?"
error.png
 

YAM

New member
Local time
Today, 14:16
Joined
Jul 15, 2020
Messages
26
No sir, please open form frmContactsEntry, ignore this error may be this error because .focus property in event procedure.

After you open form you will see tab control AddNew on this tab control there is one combo box txtNames that is unbound its not bound to recordset.

After that you will see another 3 tab controls "WorkDetails" and "PersonalDetails" and "ContactsList" from these ignore "ContactList" and remaining 2 tab controls which is "WorkDetails" and "PersonalDetails" these are having the same table fields that AddNew tab having, what i am trying to do is from txtNames which is PK in tblContacts, i am trying to filter the records on form which is associate with that PK in find.first after update event of txtNames this is what not working, it is not showing all records related to that PK, may be due to tab controls i have to add more coding because txtNames is unbound field.

I hope i make everything clear in first issue.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:46
Joined
Oct 29, 2018
Messages
21,358
No sir, please open form frmContactsEntry, ignore this error may be this error because .focus property in event procedure.

After you open form you will see tab control AddNew on this tab control there is one combo box txtNames that is unbound its not bound to recordset.

After that you will see another 3 tab controls "WorkDetails" and "PersonalDetails" and "ContactsList" from these ignore "ContactList" and remaining 2 tab controls which is "WorkDetails" and "PersonalDetails" these are having the same table fields that AddNew tab having, what i am trying to do is from txtNames which is PK in tblContacts, i am trying to filter the records on form which is associate with that PK in find.first after update event of txtNames this is what not working, it is not showing all records related to that PK, may be due to tab controls i have to add more coding because txtNames is unbound field.

I hope i make everything clear in first issue.
Okay, thanks for the additional info. The first thing you'll have to try is set the Form's Data Entry property to No (instead of Yes, which is what it is now). Let us know what happens after that.
 

YAM

New member
Local time
Today, 14:16
Joined
Jul 15, 2020
Messages
26
Thanks the DBguy my mistake just overlooked DataEntry property aaahhh, anyhow its working fine.

Issue # 02
I have form frmContactSearch which is having three type of search option from which i have able to make event procedure for two and its working fine, however i have mentioned below for your reference to get help for third event procedure where i am getting wrong, after event of BtnSearch its not showing any record, this i have created for search as you type the records on form should appear or else if user click button on any time it show the same records that he type the value in txtsearch.

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
 

YAM

New member
Local time
Today, 14:16
Joined
Jul 15, 2020
Messages
26
i think no one interested to answer for this ?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:46
Joined
May 21, 2018
Messages
8,463
Code:
Private Sub txtSearch_Change()
Dim StrSearch As String
Dim strStext As String

strStext = Nz(Me.txtSearch.Text, "")
Me.txtSearch = strStext
'No field ContactID but should have one IMO
'(ContactID like  ' * " & strStext & " * ')"
StrSearch = "Select * from tblContacts where "
StrSearch = StrSearch & " (ContactMode Like '*" & strStext & "*')"
StrSearch = StrSearch & " Or (Names Like '*" & strStext & "*')"
StrSearch = StrSearch & " Or (CompanyName like '*" & 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
Me.RecordSource = StrSearch
Me.txtSearch.SetFocus
Me.txtSearch.SelStart = Len(Me.txtSearch.Text & "")
End Sub
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:46
Joined
May 21, 2018
Messages
8,463
FYI. There were several issues in the SQL statement. It was good to have the debug.print, but the trick is then to post that into the querybuilder. It will tell you if good and can point out the problem
StrSearch = " SELECT * from tblContacts where (ContactID like ' * " & strStext & " * ')"
StrSearch = StrSearch & " Or (CompanyName '*" & strStext & "*')"

1. No contactID field in the table provided
2. No "like" in the CompanyName part of the query. This resolves to "OR (CompanyName '*A*')

The change event happens on each keystroke. After/before update usually requires you to leave the control.
 
  • Like
Reactions: YAM

YAM

New member
Local time
Today, 14:16
Joined
Jul 15, 2020
Messages
26
Many thanks for your help.

Finally i have my last question, i have unbound text box field [txtSerial] on continues form which i want to be auto number with all entries listed, I want to put it as serial number on form because form control source have its own text PK in table which is [Names], i dont know is it possible or not, i can do it by adding autonumber field to table but that i dont want to add unnecessary to table hence asking if it possible on form itself with text box to increase the number without missing in serial on continues form ?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:46
Joined
May 21, 2018
Messages
8,463
If you want it to be permanent you would have to add to the table. If you simply want a visible counter
1 Record A
2 Record ....
3 Record .........

Then you can do this in a query such as a ranking query and make that the forms record source. Some examples
 

YAM

New member
Local time
Today, 14:16
Joined
Jul 15, 2020
Messages
26
Thanks Majp for you kind help, regarding serial no on form its way to complex and bunch of codes to be written which is not necessary i was looking for simple method.
 

Users who are viewing this thread

Top Bottom