Combo Box That will Auto Populate Form

[cnodadmo]

I have 3 combo boxes, 2 of which the users will see so they can input 2 fields where combo box 1 is coming from the result from a hidden form while combo box 2 is coming from the line item result from combo box 1 and the 3rd combo will be used to query the form because it's the primary key in the table but its simply an autonumber field so it doesn't make sense for the user to see it and enter anything in there. What I have been able to do is capture the primary key in combo box 3, it shows up but in order for the form to display the result for that value in combo box 3 I have to select that value from the drop down (even though it's already showing up in the screen). This won't work if my goal is to hide this combo box from the user. Does anyone know what I need to do so when that combo box gets populated with the one primary key from the first 2 combo boxes the form will automatically populated?

I can post my 2010 Access DB if that will help.

thanks in advance.

 

[John Big Booty]

You can use;

= Me.ComboName.Column([B][COLOR="Red"]X[/COLOR][/B])

to populate an unbound text box, where X is the column number in the combo that holds the data you wish to display.

Remember that the columns in a Combo or List box are numbered from Zero on up. The data that you wish to display in the text box need not be visible in the combo, but it does need to form part of the Row Source of the combo.

 

[cnodadmo]

You can use;

= Me.ComboName.Column([B][COLOR=Red]X[/COLOR][/B])

to populate an unbound text box, where X is the column number in the combo that holds the data you wish to display.

Remember that the columns in a Combo or List box are numbered from Zero on up. The data that you wish to display in the text box need not be visible in the combo, but it does need to form part of the Row Source of the combo.

I have to make the text box and other combo boxes bound to a table field because the user needs to make updates on these records but I did try that and the form just displays blank fields. Below is the VB I have after each combo box is updated with a value and just to touch on my original post, the value in ComboBox1 is determined from a hidden form and ComboBox2 (c_Facility_Number) is determined from a query based on ComboBox1 and ComboBox3 (c_ID) is determined by the result from ComboBox1 and ComboBox2.

Private Sub c_Facility_Number_AfterUpdate()

Me.c_ID.SetFocus
Me.c_ID.Requery
Me.c_ID.Value = Me.c_ID.ItemData(0)


End Sub

Private Sub c_ID_AfterUpdate()

Me.c_ID.Requery

i_Loan_Commitment = Me.c_ID.Column(3)
c_Property_Type = Me.c_ID.Column(4)
c_Facility_Secured = Me.c_ID.Column(5)
i_Fixed_Interest_Rate = Me.c_ID.Column(6)
i_Total_Spread = Me.c_ID.Column(7)
c_Statement_Basis = Me.c_ID.Column(8)
i_Net_Operating_Income = Me.c_ID.Column(9)
i_Total_Debt_Service = Me.c_ID.Column(10)
i_Total_Rentable_Sq_Feet = Me.c_ID.Column(11)
i_Total_Number_Of_Unit = Me.c_ID.Column(12)
i_Occupied_Sq_Feet = Me.c_ID.Column(13)
i_Lease_Expiration_Date = Me.c_ID.Column(14)

End Sub

This After Update VB replaced the Embedded Macro for "SearchForRecord" where condition = ="[ID] = " & Str(Nz([Screen].[ActiveControl],0))

I also tried using this condition ="[ID] = " & [c_ID]

but as mentioned in my original post it seems like I need ComboBox3 to get selected in order for the condition to execute, but it executes perfectly.

I've attached the database, the form "Edit Accounts" after the "Edit Financials" has been saved is where this I'm having this problem.

 

[John Big Booty]

Have a look at the attached sample.

 

[cnodadmo]

Have a look at the attached sample.

hi jbb, i tried that and still same result, nothing getting returned. Also, I can't bound the combo box that i want to auto populate that will be hidden because it's a PK and an Autonumber field. When i did that I got an error message that says I can't bound on this field.

It seems like the only thing that is able to retrieve the record based on ComboBox3 with the current setup that I have implemented is by using the embedded macro for "SearchForRecord" with using the ID in the Where condition.

 

[cnodadmo]

as a work around, i just created an extra column in the query to retrieve the ID in combobox3. this column had a value of "YES" and is the only column displayed when the user goes in that drop down. When the user selects YES the ID value is activated and then populates the form. Somewhat of a sly move to trick the user that they need to go through one extra step before the records get displayed but really I am just using the way they are going into combobox3 and activating that combobox so the form is displayed with whatever the ID is returned based on the result from combobox1 and combobox2.