< SOLVED - Tables are in need of further normalization, specifically at a minimum 2 Many-To-Many relationships. >
Long time lurker, first time posting.
Hello everyone,
My name is Dan, I am an access hobbyist. I've dabbled before with Access on much smaller, simpler projects. Recently I was given the task of updating the company phone list. Instead of doing a simple Excel sheet, I thought this would make a great excuse to create a contact database done up like an application. I have treaded through the forums of many sites for solutions and VBA code examples and have managed to get almost all the features I'd like. However, I've run into a problem and I need help.
I have an Access 365 application I am working on. I used the Access Contact Template as a base. I have removed about 95% of the macros and converted them to VBA. Go to Contact Combo in the nav bar still needs to be converted to VBA. Looking to do a search as you type auto dropdown with that.
Its purpose is to hold employee, office, contractor, and vendor contact information in a single table.
I have created a form where each group gets its own tab.
The form opens with:
When you change tabs the code executes in this order:
Each Tab has its own Private Sub
The problem is with the office tab. When first clicking on the Phone Tab for a fraction of a second you can see the company name in the field but then Form_Current and TabGroup_tab_Change run and blank out the Company field. This does not happen on any other tabs. I believe it might be a problem with the query on T2_Company_cmb Row Source and the Contact_tbl.
T2_ContactType_cmb
Row Source
Event After Update
T2_Company_cmb
Row Source
Event After Update
Table Relationships
Also, I have noticed when on tabs 2-4 if I press the form refresh button multiple times or use nav controls the field labels gradually get darker like each refresh overlays the old screen instead of redrawing.
I have attached my Access file for inspection
I also created an Example Form with just the offending fields.
I have removed extraneous fields and features and populated tables with fictitious contacts.
I hope I have provided a clear picture of the problem, please ask if you have further questions.
Thank you in advance for your help.
Long time lurker, first time posting.
Hello everyone,
My name is Dan, I am an access hobbyist. I've dabbled before with Access on much smaller, simpler projects. Recently I was given the task of updating the company phone list. Instead of doing a simple Excel sheet, I thought this would make a great excuse to create a contact database done up like an application. I have treaded through the forums of many sites for solutions and VBA code examples and have managed to get almost all the features I'd like. However, I've run into a problem and I need help.
I have an Access 365 application I am working on. I used the Access Contact Template as a base. I have removed about 95% of the macros and converted them to VBA. Go to Contact Combo in the nav bar still needs to be converted to VBA. Looking to do a search as you type auto dropdown with that.
Its purpose is to hold employee, office, contractor, and vendor contact information in a single table.
I have created a form where each group gets its own tab.
The form opens with:
Record Source = Employee_qry
GoToContact_cmb Row Source = EmployeeDropdown_qry
When you change tabs the code executes in this order:
Code:
Private Sub Form_Current()
Select Case TabGroup_tab.Value
Case 0
T1_EnableControls
T1_RequeryControls
Case 1
T2_EnableControls
T2_RequeryControls
Case 2
T3_EnableControls
T3_RequeryControls
Case 3
T4_EnableControls
T4_RequeryControls
End Select
End Sub
Code:
Private Sub T1_EnableControls()
T1_Company_cmb.Enabled = (Not IsNull(T1_ContactType_cmb))
T1_Country_cmb.Enabled = (Not IsNull(T1_Company_cmb))
End Sub
Private Sub T1_EnableControls()
T1_Company_cmb.Enabled = (Not IsNull(T1_ContactType_cmb))
T1_Country_cmb.Enabled = (Not IsNull(T1_Company_cmb))
End Sub
Code:
Private Sub TabGroup_tab_Change()
On Error GoTo TabGroup_tab_Change_Err
Select Case TabGroup_tab.Value
Case 0
Me.RecordSource = "Employee_qry"
Me.GoToContact_cmb.RowSource = "EmployeeDropdown_qry"
Case 1
Me.RecordSource = "Office_qry"
Me.GoToContact_cmb.RowSource = "OfficeDropdown_qry"
Case 2
Me.RecordSource = "Contractor_qry"
Me.GoToContact_cmb.RowSource = "ContractorDropdown_qry"
Case 3
Me.RecordSource = "Vendor_qry"
Me.GoToContact_cmb.RowSource = "VendorDropdown_qry"
End Select
The problem is with the office tab. When first clicking on the Phone Tab for a fraction of a second you can see the company name in the field but then Form_Current and TabGroup_tab_Change run and blank out the Company field. This does not happen on any other tabs. I believe it might be a problem with the query on T2_Company_cmb Row Source and the Contact_tbl.
T2_ContactType_cmb
Row Source
Code:
SELECT ContactType_tbl.ContactTypeID, ContactType_tbl.ContactType
FROM ContactType_tbl
WHERE (((ContactType_tbl.ContactType)="Office Phone"));
Event After Update
Code:
Private Sub T2_ContactType_cmb_AfterUpdate()
On Error GoTo T2_ContactType_cmb_AfterUpdate_Err
T2_Company_cmb = Null
T2_Country_cmb = Null
T2_Company_cmb.Enabled = (Not IsNull(T2_ContactType_cmb))
T2_Country_cmb.Enabled = (Not IsNull(T2_Company_cmb))
T2_Company_cmb.Requery
T2_ContactType_cmb_AfterUpdate_Exit:
Exit Sub
T2_ContactType_cmb_AfterUpdate_Err:
MsgBox Error$
Resume T2_ContactType_cmb_AfterUpdate_Exit
End Sub
T2_Company_cmb
Row Source
Code:
SELECT Company_tbl.CompanyID, Company_tbl.Company, Company_tbl.ContactTypeID
FROM Company_tbl
WHERE (((Company_tbl.ContactTypeID)=[Forms]![ContactDetail_frm]![T2_ContactType_cmb]));
Event After Update
Code:
Private Sub T2_Company_cmb_AfterUpdate()
On Error GoTo T2_Company_cmb_AfterUpdate_Err
T2_Country_cmb = Null
T2_Country_cmb.Enabled = (Not IsNull(T2_Company_cmb))
T2_Country_cmb.Requery
T2_Company_cmb_AfterUpdate_Exit:
Exit Sub
T2_Company_cmb_AfterUpdate_Err:
MsgBox Error$
Resume T2_Company_cmb_AfterUpdate_Exit
End Sub
Table Relationships
Also, I have noticed when on tabs 2-4 if I press the form refresh button multiple times or use nav controls the field labels gradually get darker like each refresh overlays the old screen instead of redrawing.
I have attached my Access file for inspection
I also created an Example Form with just the offending fields.
I have removed extraneous fields and features and populated tables with fictitious contacts.
I hope I have provided a clear picture of the problem, please ask if you have further questions.
Thank you in advance for your help.
[ICODE][ICODE]
[/ICODE][/ICODE]Attachments
Last edited: