ComboBox pulls from table will not autofill

[gumbi17]

I new to VBA and I am having problems having my combo box auto fill the next text box. The table it pulls from is called Code-LeaseProvision and it has only 2 columns in it. So when built the combo box it pulls the both rows. One is labeled "Code" and other other "ProvisionDescription" I have it set when you use the combo box it fills Code but it will not fill the next text box labeled "ProvisionDescription". So when I put the event into the ProvisionDescription afterupdate as a VBA code. This is what I have:

Private Sub desc_AfterUpdate()
Me.[ProvisionDescription] = Me.

.Column(1)
End Sub

It fills the combo box but will not fill and update the LeaseProvision Text box.  Thanks for any help.

 

[bob fitz]

Try the following as the Control Source property of the text box:
Me.[NameOfYourComboBox].Column(1)

 

[gumbi17]

I renamed my combo box to cboCode and put this into VB but still it doesn't work.

Private Sub ProvisionDescription_AfterUpdate()
Me.[ProvisionDescription] = Me.[cboCode].Column(1)
End Sub

This is the sql of the combo box"
SELECT [Code-LeaseProvisions].Code, [Code-LeaseProvisions].[Provision Description], [Code-LeaseProvisions].Code
FROM [Code-LeaseProvisions]
ORDER BY [Code-LeaseProvisions].Code;

 

[play2mefish]

Private Sub desc_AfterUpdate()
Me.[ProvisionDescription] = Me.[cboCode].Column(1)
End Sub

I just did this very same thing in a db that I use every day and here is how I would do this:

Private Sub desc_AfterUpdate()
Me.ProvisionDescription.value = Me.cboCode.column(1)
End Sub

Hope this helps.

 

[bob fitz]

Have you tried the following as the Control Source property of the text box:
= Me.[NameOfYourComboBox].Column(1)

 

[gumbi17]

Yes, the control worked perfectly. The only way I have been able to get it to currently work is to have it go:

Private Sub desc_AfterUpdate()
ProvisionDescription.value = cboCode.column(1)
End Sub

The removal of the Me. makes it work, any reason why?

 

[bob fitz]

The removal of the Me. makes it work, any reason why?

I'm not certain, but I think that may be because you are using the name of a field in the record source rather that the name of a control on the form.

 

[MSAccessRookie]

I renamed my combo box to cboCode and put this into VB but still it doesn't work.

Private Sub ProvisionDescription_AfterUpdate()
Me.[ProvisionDescription] = Me.[cboCode].Column(1)
End Sub

This is the sql of the combo box"
SELECT [Code-LeaseProvisions].Code, [Code-LeaseProvisions].[Provision Description], [Code-LeaseProvisions].Code
FROM [Code-LeaseProvisions]
ORDER BY [Code-LeaseProvisions].Code;

I do not think that this is the right place for the code. I believe that it belongs in the OnChange Event for the Combo Box.

-- Rookie

 

[missinglinq]

The Rookie has identified the problem! You cannot assign a Value to a Control in that Control's AfterUpdate event!

Me.[ProvisionDescription] = Me.[cboCode].Column(1)

needs to be in the AfterUpdate event of cboCode. The OnChange event might work, but if the user were to enter characters into the Combobox empty text box, this event will fire with each character, and that could lead to problems.

Linq ;0)>

 

[MSAccessRookie]

The Rookie has identified the problem! You cannot assign a Value to a Control in that Control's AfterUpdate event!

Me.[ProvisionDescription] = Me.[cboCode].Column(1)

needs to be in the AfterUpdate event of cboCode. The OnChange event might work, but if the user were to enter characters into the Combobox empty text box, this event will fire with each character, and that could lead to problems.

Linq ;0)>

I use OnChange, because most of my Combo Boxes are defined with the Limit to List property set, and OnChange fires as soon as an item in the list has been selected.

-- Rookie