auto expand on combo box with autofill VBA format issue stops auto expanding

[Dgreenwood]

I have created a form that has a combo box and that combo box has VBA code that gets other fields to autofill but the problem is my combo box no longer auto expands as I type in in my case a book title into the combo box. I have narrowed the issue down to the price field it has something to do with the currency format.

If I remove it from the code and the auto expand works again and all field bar price autofill. the number format field doesn't cause any issues just the price.

I have tinkered further and I think it is to do more with the fact the price field is used in a calculated field sub total field [price]*[quantity].

[jimatqsi]

Dgreenwood,
Welcome to Bytes.

You need to do a better job of explaining your problem and what you have done so far. You're typing a book title and you think the price field has something do with the list of book titles not auto-expanding? You have to help us make sense of that because we can't possibly guess what the connection is between those two.

"If I remove it from the code and the auto expand works again ..."
What is "it", currency formatting? Again, what has currency formatting to do with book titles?

"all field bar price autofill. " What are you saying here? You have multiple columns in your combobox and the column for price is not filling in? Or you have multiple combo boxes and the combo box for price does not work?

Please show us the any code you have. And what version of Access are you using? Be clear about what you have and what does not work.

Jim

[Dgreenwood]

Sorry,

Microsoft Access 2010

In my Form "Book Purchase" I have the following fields Autofilling from a comboBox field called "search" that has all the information linked via lookup wizard from the "Books" table.
ISBN Number (PK)
Title (of the Book)
Number in stock
Price

The code used to autofill the fields and is currently working fine is.

Private Sub cmoSearch_Change()
Me.txtTitle.Value = Me.cmoSearch.Column(1)
Me.txtISBN_Number.Value = Me.cmoSearch.Column(0)
Me.txtNumber_in_Stock.Value = Me.cmoSearch.Column(2)
Me.txtPrice.Value = Me.cmoSearch.Column(3)
End Sub

There are 2 other fields in the form
Quantity (user selects how many to purchase)
Sub Total (calculated field [price]*[quantity])

all the fields autofill fine the comboBox will only let me type in 1 letter and will auto fill the information into the relevant fields. So no expanding I have to use the drop down.

If I remove the line of code the autofills price the comboBox works perfectly and all the fields left in the code autofill perfectly.

I hope that is clearer


[imgnothumb]http://bytes.com/attachment.php? attachmentid=76 19[/imgnothumb]
[imgnothumb]http://bytes.com/attachment.php? attachmentid=76 20[/imgnothumb]

[jimatqsi]

That is clearer, but still not clear. All was good until the last bit. "If I remove the line of code the autofills price the comboBox works perfectly and all the fields left in the code autofill perfectly."

Are you saying that if you remove

Me.txtPrice.Value = Me.cmoSearch.Column(3)

then one of your combo boxes works better?

I don't really think that is the case. If that is what you think you see, I think you should look closer at what is happening. If txtPrice is really just a text box then I don't think filling it will change the behavior of a combo box.

I could be wrong, but I encourage to fiddle with it a while and see if something obvious doesn't bubble up to the surface.

Also, be sure to use code tags when you post code. Click the [CODE/] button and paste your code between the tags.

Jim

[Dgreenwood]

I have had a good tinker and the "Sub Total" field in the "Book Purchase" table is causing the problem (which is in the form "book Purchase" but is the source of the data.

It is a calculated field and when I have removed the calculation format the Combo Box for "select" works fine but the subtotal no longer works obviously.

I have had a look on the settings and can't see anything but I am guessing that as soon as I press a letter in the combo Box the fields auto fill and then the subtotal has data to make its calculation and prevents the auto expand from working. It simply enters the first result under the letter pressed.

the VBA code is built on the event "on Change" should this be moved to something else?

[Dgreenwood]

Moved the event to "after update" and it works go me!

[jimatqsi]

Yes, you made exactly the right move. OnChange is fired after every change within the value, OnUpdate is fired after the edit of the field is completed.

Good job!

Jim