Combobox (on tab control) event not firing.

[Ricter]

Hello all,

The after_update code for my combobox, located on a tab control, is not firing after update. The combobox is displaying its row source correctly. What is wrong please?

cboShipper > Row Source:
SELECT [tblCompanies].[CompanyID], [tblCompanies].[CompanyName], [tblCompanies].[CompanyAdd1], [tblCompanies].[CompanyAdd2], [tblCompanies].[CompanyCity], [tblCompanies].[CompanyProvST], [tblCompanies].[CompanyPostal], [tblCompanies].[CompanyCountry], [tblCompanies].[CompanyTaxID], [tblCompanies].[CompanyType] FROM tblCompanies ORDER BY [CompanyName];

cboShipper > After Update: Event Procedure

Private Sub cboShipper_AfterUpdate()
Shipper = cboShipper.Column(1)
ShipperAdd1 = cboShipper.Column(2)
ShipperAdd2 = cboShipper.Column(3)
ShipperCity = cboShipper.Column(4)
ShipperProvST = cboShipper.Column(5)
ShipperPostal = cboShipper.Column(6)
ShipperCountry = cboShipper.Column(7)
End Sub

None of these fields, also on the tab control (same tab) are being set after leaving the combobox.

Thank you.

 

[John Big Booty]

Welcome to the forum.

Try moving your code to the Combo's On Change event.

Are all the fields referred to in your code bound or unbound?

If they are bound (to a table) then this is a violation of the rules of data normalisation, perhaps this tutorial will help you get your head around this concept.

 

[John Big Booty]

...also when posting code, use the code tag (that's the button with the cross hatch on it # at the top of the posting window) as this makes it a little easier to read (the code).

 

[Ricter]

Welcome to the forum.

Try moving your code to the Combo's On Change event.

Are all the fields referred to in your code bound or unbound?

If they are bound (to a table) then this is a violation of the rules of data [edit] perhaps this [edit] will help you get your head around this concept.

Thank you!

By "bound" do you mean that the text fields have a control source which is a field in a table? If so, yes, they are bound. I do not know a lot about normalization, but I think of it in terms of "atomization" (among other things), which I think I've done here...

 

[John Big Booty]

Thank you!

By "bound" do you mean that the text fields have a control source which is a field in a table? If so, yes, they are bound. I do not know a lot about normalization, but I think of it in terms of "atomization" (among other things), which I think I've done here...

It sounds as if the text boxes are bound. Given that the information you are trying to save to those text boxes is part of the Row Source of your combo box, there is absolutely no need to save that information again in this table. instead use unbound text boxes and as their Control Source use;

= cboShipper.Column(1)

changing the column number as appropriate.

I would highly recommend that you become familiar with the term and the concept of Data Normalisation as this is a corner stone of good DB design, failure to do so will lead you sooner or latter into a world of great hurt.

 

[boblarson]

I do not know a lot about normalization, but I think of it in terms of "atomization" (among other things), which I think I've done here...

There's definitely more to it than that. As JBB has pointed out, you do not store data multiple times in a database. That violates the rules of normalization. You store data once, in one place, with the exception of an ID number for foreign keys (which are the primary key in another table). The rest of the record is obtainable by a query to pull the information together at the time you need to display it.

Oh, and JBB - the AfterUpdate event would be the appropriate event and the key is to find out why it might not be firing before trying to work around it as the problem may be more severe than it would seem.

 

[boblarson]

None of these fields, also on the tab control (same tab) are being set after leaving the combobox.

It is likely that you don't have your Column Count property properly set for the combo box. It would need to have the number of columns that are in the combo's row source. And that is true even if you are going to do it the right way and use the

=[ComboNameHere].[Column](1)

as a control source for your text boxes to display the information. And remember that the columns are ZERO based so the second column is (1) and the third would be (2), etc.

 

[Ricter]

Thanks, all, I appreciate the help.

I guess I am saving the company data more than once, since it is saved in the lkpCompanies table, the source of my combobox, and then I'm saving it into tblShipments. But, based on previous experience, I think that's what I want. tblShipments is history, and if a company changes its address, etc., I retain that history even though I update it in lkpCompanies. I suppose I could create an entirely new company record in lkpCompanies for these instances, but do I really need to do that for the sake of elegance? Just asking. : )

I have been using the zero-based column numbering scheme, but I am querying for 10 columns and only setting 7 fields with the code. That's ok, isn't it?

Anyway, this procedure is not working in on_change either...

Rick

 

[boblarson]

Thanks, all, I appreciate the help.

I guess I am saving the company data more than once, since it is saved in the lkpCompanies table, the source of my combobox, and then I'm saving it into tblShipments. But, based on previous experience, I think that's what I want. tblShipments is history, and if a company changes its address, etc., I retain that history even though I update it in lkpCompanies.

Yes, you can do that if that is important to retain. It will add quite a bit to your data because it would be saved with every order, whereas a table to store company addresses with an effective date would allow you to store changes once.

I have been using the zero-based column numbering scheme, but I am querying for 10 columns and only setting 7 fields with the code. That's ok, isn't it?

Yes, you don't have to use them all, but you do need to set the column count to the entire number of them unless the ones you are not concerned with are at the very end.

Anyway, this procedure is not working in on_change either...

Have you put a breakpoint in to see if it is hitting the after update event of the combo? And, if it isn't firing like it should, perhaps the form, or control, is having corruption issues and will either need to be replaced or you can try a DECOMPILE and COMPILE to see if that gets it back in order (but do save a copy BEFORE attempting any of this so you don't get hosed if something goes terribly wrong in the process).

 

[Ricter]

Yes, you can do that if that is important to retain. It will add quite a bit to your data because it would be saved with every order, whereas a table to store company addresses with an effective date would allow you to store changes once.

Yes, you don't have to use them all, but you do need to set the column count to the entire number of them unless the ones you are not concerned with are at the very end.


Have you put a breakpoint in to see if it is hitting the after update event of the combo? And, if it isn't firing like it should, perhaps the form, or control, is having corruption issues and will either need to be replaced or you can try a DECOMPILE and COMPILE to see if that gets it back in order (but do save a copy BEFORE attempting any of this so you don't get hosed if something goes terribly wrong in the process).

Ok, this problem has been driving me crazy. I created new tables, in a new db, and a new form, and cut the whole thing to bare bones, to try and get my combobox to take its data and store it in a different table. Could not make it work.

Last night it hit me, I have indeed made this work, in the past, and fortunately I have an older version (2000) of Access still installed on my laptop. So, ran my tests on a new db this morning and, bingo! It works.

Now I have to ask different questions. My work office installation of Access is 2007. Is there something about Access 2007 that's different from 2000, some kind of security setting, that would not allow me to "move" the data like I've been trying to do? Is there possibly something about my network/hardware platform at work that makes Access run differently?

Rick

 

[Ricter]

Got it. Trusted locations issue, the VBA code would not fire until I had the path to my db on the list.

Thanks, everyone, for your help.

Rick

 

[bboss]

try enabling macros.
Look for 'security warning' - certain contents in the database has been disabled. ; on the screen
click 'options' button.
select 'enable this content' radio button, OK

 

[boblarson]

try enabling macros.
Look for 'security warning' - certain contents in the database has been disabled. ; on the screen
click 'options' button.
select 'enable this content' radio button, OK

Pointless post - the answer was already given, and accepted, at least 9 months ago.