Solved Query in form not populating field (1 Viewer)

[Sh8dyDan]

< 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:

Record Source = Employee_qry​

GoToContact_cmb Row Source = EmployeeDropdown_qry​

When you change tabs the code executes in this order:

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

Each Tab has its own Private 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


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 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

SELECT ContactType_tbl.ContactTypeID, ContactType_tbl.ContactType
FROM ContactType_tbl
WHERE (((ContactType_tbl.ContactType)="Office Phone"));

Event After Update

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

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

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]

 

[theDBguy]

Hi. Welcome to AWF!

Can't look at your file right now but just wondering if you're using any lookup fields at the table level.

Sent from phone...

 

[June7]

Not a design I would use. Instead of changing RecordSource, I would just have form bound to Contact_tbl then apply filter criteria to the form. Seldom a need to include lookup tables in RecordSource for a form. Cascading comboboxes on continuous form might justify.

I didn't find any lookup fields in tables.

 

[Sh8dyDan]

Hi. Welcome to AWF!

Can't look at your file right now but just wondering if you're using any lookup fields at the table level.

Sent from phone...

No, I'm not using lookups.

 

[June7]

I set a breakpoint in tab_Change procedure and don't see company name.

Yes, issue must be with company combobox RowSource because there are no items in list and therefore nothing can display. The Contact Type is Office Phone but there are no companies with that type.

 

[Sh8dyDan]

I set a breakpoint in tab_Change procedure and don't see company name.

Yes, issue must be with company combobox RowSource because there are no items in list and therefore nothing can display. The Contact Type is Office Phone but there are no companies with that type.

I also placed a breakpoint and F8 though and observed at T2_EnableControls, T2_ContactType_cmb = 2, T2_Company_cmb = 1
which would make T2_Company_cmb = ACME Widgets.

 

[June7]

Actually, don't think I would even use a Tab control. 3 of the tabs are virtually identical. Have 4 command buttons arranged to look like tabs. Have one set of controls and code sets filter property and requeries comboboxes and for Office, hide controls that aren't pertinent.

 

[arnelgp]

try removing the Criteria from the Row Source of the combo?

 

[Sh8dyDan]

Actually, don't think I would even use a Tab control. 3 of the tabs are virtually identical. Have 4 command buttons arranged to look like tabs. Have one set of controls and code sets filter property and requeries comboboxes and for Office, hide controls that aren't pertinent.

That is not a bad idea. As I'm still honing my skill and knowledge, I may have unfortunately thought using tabs would be easier. It looked like less code and a few extra controls. Who knew, not me? I may have chosen a path based on ignorance, so I must continue with obstinance. Next time will be different.

 

[June7]

I have been modifying your db. So far I have removed about 3/4 of controls and cut out 3/4 of the code. I am about to delete most of the query objects

 

[Sh8dyDan]

try removing the Criteria from the Row Source of the combo?

I tried that as well, but what I was wanting to do with the cascading combo boxes was narrow the choices as you drilled down. Based on the table design, if I were to choose Employee my only choice would be ACME Widgets. Removing the Criteria shows all options regardless of the choice.

 

[Sh8dyDan]

I have been modifying your db. So far I have removed about 3/4 of controls and cut out 3/4 of the code. I am about to delete most of the query object.s

Interesting, very curious, and still keeping the core functionality.

 

[arnelgp]

I tried that as well, but what I was wanting to do with the cascading combo boxes was narrow the choices

ContactTypeID has nothing to do with Any Company? both are Indepent to each other.
what you need is a Junction table between ContactTypeID and CompanyID.
Listing all ContactID and CompanyID on this table and use it as record source of the combo.
ContactTypeID shouldn't be in your Customer table.
because a Customer can be of Many Contact type. but on your current customer table design,
you can only assign 1 Type to each company.

 

[Sh8dyDan]

ContactID has nothing to do with Any Company? both are Indepent to each other.
what you need is a Junction table between ContactID and CompanyID.
Listing all ContactID and CompanyID on this table and use it as record source of the combo.
ContactTypeID shouldn't be in your Customer table.
because a Customer can be of Many Contact type. but on your current customer table design,
you can only assign 1 Type to each company.

Oh, I think see what you're saying. It seems, Contact_tbl would be the junction table between Company and ContactType? One Company with many Contacts and one ContactType with many Contacts. I may be wrong, cause I have never worked with a many-to-many relationship.

 

[June7]

Check out this revision.

I think you meant: Company with many contact types and each contact type associated with multiple companies. That is many-to-many.
As currently structured, each company is associated with only one contact type. Clarify - are the companies the contact or are the people? Seems like people are and they are classified by type. Not really seeing a need to assign type to companies but I did not change that which means the non-people (Office) contacts are not assigned a company (removed from field) because there is no company associated with Office type. Cannot populate company because company combobox does not have any listed for Office. If you do want to classify companies by type then need another table CompanyConType. So, no, Contact is not junction table between Company and ContactType, CompanyConType would be and CompanyConType would be RowSource for Company combobox.

Similar case could be made for Country and Company. If a company can be in multiple countries and a country can have multiple companies, need Companies, Countries, and CountryCompany tables. However, as is, each company is assigned a single country and that means CountryID value could just be saved into Company and CompanyID would not be in Country.

 

[Pat Hartman]

Here's a different kind of address book

 

[Pat Hartman]

You can't have this code inside the form's BeforeUpdate event.

If Me.Dirty Then
    Beep
    Response = MsgBox(Msg, Style, Title, Help, Ctxt)
    If Response = vbYes Then
        DoCmd.Save
    End If
    If Response = vbNo Then
        Me.Undo
        T1_RequeryControls
    End If
Else
    DoCmd.Close
End If

DoCmd.Save doesn't save a record. It saves the form which should never have been changed by the user so it irrelevant. If you were to insert an actual save record command such as DoCmd.RunCommand acCmdSaveRecord, you would get an error since you are already in the save process once this event runs so you cannot restart the save from within the BeforeUpdate event.

The code should be more like this. There is no need to check for dirty. This event only runs if the form is dirty so the If is redundant.

    Response = MsgBox(Msg, Style, Title, Help, Ctxt)
    If Response = vbYes Then
    Else
        Me.Undo
        Cancel = True
    End If

 

[Pat Hartman]

I made a few changes to the example I posted earlier.

Address Book

Address Book with alphabet buttons, a sort option, and a different filter option as well as two reports.

www.access-programmers.co.uk

 

[Sh8dyDan]

Check out this revision.

I think you meant: Company with many contact types and each contact type associated with multiple companies. That is many-to-many.
As currently structured, each company is associated with only one contact type. Clarify - are the companies the contact or are the people? Seems like people are and they are classified by type. Not really seeing a need to assign type to companies but I did not change that which means the non-people (Office) contacts are not assigned a company (removed from field) because there is no company associated with Office type. Cannot populate company because company combobox does not have any listed for Office. If you do want to classify companies by type then need another table CompanyConType. So, no, Contact is not junction table between Company and ContactType, CompanyConType would be and CompanyConType would be RowSource for Company combobox.

Similar case could be made for Country and Company. If a company can be in multiple countries and a country can have multiple companies, need Companies, Countries, and CountryCompany tables. However, as is, each company is assigned a single country and that means CountryID value could just be saved into Company and CompanyID would not be in Country.

Thanks for the code modifications. A great example of a similar effect of tabs with just buttons. And it ran faster with better transitions between screens.

 

[Sh8dyDan]

Here's a different kind of address book

This is a really nice and functional address book. Definitely going to be peaking under the hood on this one.