Avatar of Marilync1266
Marilync1266
 asked on

Question about Combo Boxes in Microsoft Access

I have a form where the user selects a product type from a combo box and what they select is used to narrow down the selection in the opportunity combo box.  Then when they click on create a quote, the data from the opportunity combo box is appended into a table and that record is displayed on a different form.

I would like to give user another option, instead of selecting a value in the product type combo box box they can enter a key number in the opportunity ref no text field.  Once they do that, I would like the opportunity name combo box to automatically populate with that record and the value to be displayed.  

Marilyn
create-quote.jpg
Microsoft AccessMicrosoft Excel

Avatar of undefined
Last Comment
Marilync1266

8/22/2022 - Mon
Rey Obrero (Capricorn1)

basically, in the afterupdate event of the opp refNo textbox, iterate thru the combo box and look for the value entered in the textbox, then select when found.

better if you can upload a db, so it will be easier to discuss this.
Marilync1266

ASKER
Can I upload without the tables?  They are linked to outside databases.  

I've attached a screen prints, the 1st is where I type in a value in the Opp Ref No field and I want the value to automatically display in the opportunity combo box, without needing to select it by clicking on the arrow.

Create-Quote-with-Opp-No.jpg
Rey Obrero (Capricorn1)

create a dummy table with the same data type as the original table.

provide the following;
a. rowsource of the combo
b. bound column of the combo
c. column count

the value you typed in the textbox, in which column of the combo box you will find that value?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Rey Obrero (Capricorn1)

test this codes, change the names of the controls accordingly

private sub textbox_afterupdate()

dim j
with me.comboname
for j=0 to .listcount-1
     if .column(0,j)= me.textbox then       'assuming the bound column is 1
       .selected(j)=true
       exit for
    end if
next
end with

end sub
Marilync1266

ASKER
The rowsource of the combo is qsel_Quote_Opportunity
The bound column is # 3 - the Opportunity Ref No which is the number you can type in the text box
The column count is 10
cboOpportunity-Properties.jpg
Marilync1266

ASKER
Hi,
I'm getting an error - member not found - see attached
combo-box-error.jpg
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Rey Obrero (Capricorn1)

change .selected to .value

private sub textbox_afterupdate()

dim j
with me.comboname
for j=0 to .listcount-1
     if .column(2,j)= me.textbox then       'use 2 if bound column is 3
       .value(j)=true
       exit for
    end if
next
end with

end sub
ASKER CERTIFIED SOLUTION
Rey Obrero (Capricorn1)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Marilync1266

ASKER
Thank you so much it worked!!!!