Display Previously Selected Value in Combo Box (1 Viewer)

mmchaley · Nov 10, 2017

Hello all

I am trying to get records to display on a form in unbound combo boxes when I have previously selected a record.

Data Structure
table - QuoteLog - Primary Key - QuoteID - Subkeys ClientID, ContactCOID and BidPOCID (these last two are records of tbl_ClientContacts)
table - Clients - Primary Key - ClientID
table - ClientContacts - Primary Key - ContactID

Form - QuoteLog - this is based on tbl_QuoteLog

I have 3 unbound combo boxes on this form

1. Pulls the list of Clients - code saves the ClientID to tbl_QuoteLOg
2. Contracting Officer - Selects from tbl_ClientContacts the people who work for the Client and have the position Contacting Officer
3. Bid Point of Contact - Selects those who are checked as BidPOC from the company selected in the first combo box

The records save correctly in tbl_QuoteLog, however, after I close the form and re-open, the records in Client are typically correct, but the records in Contracting Officer and Bid POC are not showing.

I also have the Select the Client Combo Box again to get the correct list of Contacts to be listed in the 2nd and 3rd Combo Boxes.

My 2 questinos
1. How do I get the previously selected records to appear in those boxes?
2. How do I get the Combo Boxes for Contracting Officer and Bid POC to "see" the record showing in Client without re-selecting the Client?

Thanks,
Mark

Let me know if I am not clear on something.

pbaldy · Nov 10, 2017

I'm curious why they're unbound. If the values save, you must have some code that does it. In that event, you'd need to repopulate them with similar code.

Pat Hartman · Nov 12, 2017

You would use unbound combos for searching but bound combos for updating. You might have both on the same form. Typically the unbound search combos are in the form header and the bound combos are with the data in the detail section.

arnelgp · Nov 12, 2017

better to make the combos bound to their corresponding field in your table.
if you are unable to do that, there is still a fix.
using your form's Current Event set the values of the combos to corresponding values on your table. if you got to new record, set the combos value to null:

Code:

Private Sub Form_Current()
If Me.NewRecord Then
    ' blank out the combos for new record
    Me![ClientCombobox] = Null
    Me![ContractingOfficerCombobox] = Null
    Me![BidPointCombobox] = Null
Else
    ' set the value of combos to corresponding
    ' ids of current record
    Me![ClientCombobox] = Me.ClientID
    Me![ContractingOfficerCombobox] = Me.ContactCOID
    Me![BidPointCombobox] = Me.BidPOCID
End If
End Sub

********
replace the names of the combos to the name you have in the form

mmchaley · Nov 13, 2017

Clarification - I do have it bound to a control, I just started out with it unbound.

The solution above didn't work - I am not sure the issue. I will noodle this a bit and see if I can get close before coming back to this fount of knowledge.

Thanks much

Pat Hartman · Nov 14, 2017

I can't work out what you are trying to do.
Are you trying to get the combos to work as selection criteria?
Are you trying to use the combos to change values in a bound record?

arnelgp · Nov 14, 2017

yes the solution will not work because on post #1 you said it is Unbound.
for that to work, your combos Bound Column must be of same DataType as with the Bound Field.

mmchaley · Nov 15, 2017

Here is a recast of the problem

Data
tbl_QuoteLog - this houses all the data about a bid - including the primary key values for Client, Contracting Officer and Bid Point of Contact
tbl01Clients - This houses the data for the organizations - source for ClientID
tbl01ClientContacts - Data for contacts of organizations - Source for ContactCOID and BidPOCID

Form that I need help with

frm01_ QuoteJungle

Fields I need help with

Client - cboClientID
Contract Officer - cboContractCOID
Bid POC - cboBidPOCID

cboClientID

Control Source - ClientID
Row Source -

Code:

SELECT tbl01Clients.ClientID, tbl01Clients.Organization FROM tbl01Clients GROUP BY tbl01Clients.ClientID, tbl01Clients.Organization ORDER BY tbl01Clients.Organization;

Bound Column - 1

cboContractCOID

Control Source - ContactCOID
Row Source -

Code:

SELECT LastName + ', ' + FirstName AS Name, tbl01ClientContacts.ContactID, tbl01ClientContacts.ClientID FROM tbl01ClientContacts WHERE (((tbl01ClientContacts.ClientID) = 4) AND  ((tbl01ClientContacts.Position) = 'Contracting Officer')) ORDER BY tbl01ClientContacts.LastName;

Bound Column - 2

cboBidPOCID

Control Source - BidPOCID
Row Source -

Code:

SELECT LastName + ', ' + FirstName AS Name, tbl01ClientContacts.ContactID, tbl01ClientContacts.ClientID FROM tbl01ClientContacts WHERE (((tbl01ClientContacts.ClientID) = 4) AND  ((tbl01ClientContacts.BidPOC) = -1)) ORDER BY tbl01ClientContacts.LastName;

Bound Column - 2

cboClient ID Event Procedure

Code:

Private Sub cboClientID_AfterUpdate()
On Error Resume Next

    Me.cboContractCOID.RowSource = "SELECT LastName + ', ' + FirstName AS Name, tbl01ClientContacts.ContactID, tbl01ClientContacts.ClientID " & _
            "FROM tbl01ClientContacts " & _
            "WHERE (((tbl01ClientContacts.ClientID) = " & Me.ClientID.Value & ") " & _
            "AND  ((tbl01ClientContacts.Position) = 'Contracting Officer')) " & _
            "ORDER BY tbl01ClientContacts.LastName;"
    Me.cboContractCOID.Value = Null
    Me.cboContractCOID.Requery

    Me.cboBidPOCID.RowSource = "SELECT LastName + ', ' + FirstName AS Name, tbl01ClientContacts.ContactID, tbl01ClientContacts.ClientID " & _
            "FROM tbl01ClientContacts " & _
            "WHERE (((tbl01ClientContacts.ClientID) = " & Me.cboClientID.Value & ") " & _
            "AND  ((tbl01ClientContacts.BidPOC) = -1)) " & _
            "ORDER BY tbl01ClientContacts.LastName;"
    Me.cboBidPOCID.Value = Null
    Me.cboBidPOCID.Requery
End Sub

The purpose of the Combo Boxes

To be able to select the organization and contacts in that organization and assign them to the quote

Current Behavior - New Record

Assign Client from drop down
Contract Officer drop down displays the people with title Contracting Officer who work for the company assigned in Client
Bid POC Drop down displays people who are checked as Bid POC who work for the company assigned in Client

Current Behavior - Existing Record after close and Re-open

Client - Displays the Name of the Assigned Client
Contracting Officer - Blank - Using the Drop Down List displays all records with Position Contracting Officer regardless of Organization
Bid POC - Blank - Using the Drop Down List displays all records with Bid POC checked regardless of Organization

Desired Behavior - New Record - Working as Intended

Desired Behavior - Review Existing Record

Client - Display Assigned Organization - Working as Intended

Contract Officer - Display the Assigned Contract Officer, ability to change Contract Officer if needed without reassigning the Organization
Bid POC - Display the Assigned Bid POC, ability to change Bid POC if needed without reassigning the Organization

Thanks for the help

arnelgp · Nov 15, 2017

put the ids of the two combos on first column and sort on id not lastname. bound the two to column 1. combo will always follow the sort order on column 1.

mmchaley · Nov 15, 2017

That did not solve the issue

The data for the drop down pulls from tbl01ClientContacts

On review of the record I need the data to pull the "contact"ID number from tbl_QuoteLog and display the corresponding name from tbl01ClientContacts

I am not sure how to make that happen code wise

arnelgp · Nov 16, 2017

what datatype is being save on tbl_quotelog, are they the same with the two combos. do you have any code on the form's current event?

mmchaley · Nov 16, 2017

No code in On Current
Related data type is Long Integer

I have a 50% fix - I have created a text box and used Dlookup to populate the value and placed this text box directly over the related combo box.

This does not solve all the issues, but does cover the most critical one.

Display Previously Selected Value in Combo Box (1 Viewer)

mmchaley

Registered User.

pbaldy

Wino Moderator

Pat Hartman

Super Moderator

arnelgp

..forever waiting... waiting for jellybean!

mmchaley

Registered User.

Pat Hartman

Super Moderator

arnelgp

..forever waiting... waiting for jellybean!

mmchaley

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

mmchaley

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

mmchaley

Registered User.

Similar threads

Users who are viewing this thread