A simple code that only work 1/2 the time

[haerion]

Hi all,

I am having some weird problem here. i got the following code, but for some reason, sometime it doesn't work and sometime it does. I was unable to find a pathern as to why it doesn't work sometime.

Here is the code:

Private Sub Itemno_Change()
On Error GoTo err_itemnoChange

Me.Desc1 = Me.Itemno.Column(1)
Me.Desc2 = Me.Itemno.Column(2)
Me.Eight = Me.Itemno.Column(5)
Me.Cat = Me.Itemno.Column(3)
Me.Avail = Me.Itemno.Column(4)

err_itemnoChange:
Exit Sub
End Sub

This code is on the onchange event of the listbox, which is in a subform.

Any of you got an idea to fix this?

 

[JoeAtWork]

Any of you got an idea to fix this?

Yes, actually display whatever error is occurring in your error handler.

err_itemnoChange:
[COLOR=blue]MsgBox "Error #" & Err.Number & ": " & Err.Description
[/color]
Exit Sub

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[TheAceMan1]

How are ya haerion . . .

Curious this!

[blue]Why are you using the change event?[/blue] ... the logic of your code doesn't appear to fit its use!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[missinglinq]

I agree with Aceman1, move your code to Itemno_AfterUpdate().

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[haerion]

Here is the error when it doesn't work:


Error # -2147352567:The object doesn't contain the automation object 'me'. (Possible it is not exaclty the same sentence since I had to translate it in english from french )

I am using this code so that when I select an item on the listbox, it automatically change the column 2-5 to the data corresponding.

Im good TheAceMan, hope you doing well too

And yes this is very weird

I change it to the afterupdate one, still getting the same error, but true, this one is better than change my mistake

Thanks all for your help

 

[haerion]

So you guys got any idea?

 

[TheAceMan1]

haerion . . .

Be Aware: column index in a listbox or combobox starts at zero! Perhaps what you really want is columns 0 thru 4.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[haerion]

No, column 0 is the item, seriously, the information is taken from the right place. It's just that something everything fill out perfectly and some other i got the error... that's just plain weird!!!

 

[NXMold]

Is it neccasary to use "me."? I would think the results are the same if using "Desc1 = Itemno.Column(1)".

Could the real error be a type mismatch?

 

[haerion]

I removed all the "me." and still got the same error

 

[wyoung71]

Have you tried using a combobox instead of a listbox?

 

[haerion]

I can't use a combobox, since it is a subform that is used in a datasheet view

 

[randy700]

Try substituting the complete name for Me.
Forms!frmName.cboName


Randy

 

[TheAceMan1]

haerion . . .

At this point I'd [blue]delete then reconstitute[/blue] the listbox ...

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[haerion]

I tried to delete/reconstitute the listbox, but i still got the same problem

The main form is "Entry"
The subform is "ItemCustWant1_subform"

The listbox lboItemno is on the subform
The Event is on the afterupdate of the lboItemno

The subform is in datasheet view.

If you need any more info to help you, just ask

Thanks

 

[wyoung71]

Try making it Form view in contiunous forms instead. I think your controls are limited in datasheet view. The listbox then becomes the value list you set up for the field in your table. You can mimic the datasheet view by adding a form header, and then running a continous form view in the detail. Then you retain all the benefits of your controls.

 

[haerion]

Ok, I tried in continous form view, and the result is the same, sometime work, sometime don't

 

[SeeThru]

REcord locking? - could the record be locked by another open talbe or form?

SeeThru
Synergy Connections Ltd - Telemarketing Services

 

[haerion]

My main form (Entry) is based on the query Main Table which is as follow:

SELECT [Table Header].IDTag, [Table Header].Date, [Table Header].Status, [Table Header].PO, [Table Header].ShipVia, [Table Header].ShipDate, [Table Header].FollowUp, [Table Header].SO, [Table Header].Acronym, [Table Header].CustName, [Table Header].Addr1, [Table Header].Addr2, [Table Header].Addr3, [Table Header].Postal, [Table Header].OnHold, [Table Header].SYTD, [Table Header].CieTel, [Table Header].CieFax, [Table Header].Level, [Table Header].TraneModel, [Table Header].TraneSN, [Table Header].Notes, [Table Header].Clerk, [Table Header].Contact, [Table Header].ContactEmail, [Table Header].ContactTel, [Table Header].ContactFax, Clerk.Name, [Table Header].TAF, [Table Header].Installation, [Table Header].Tech, [Table Header].TranePO, [Table Header].TrsfNo, Clerk.Store, [Table Header].TrsfNo2, Clerk.VersysUser, Clerk.VersysPass, Clerk.ELUser, Clerk.ELPass, Clerk.CitrixUser, Clerk.CitrixPass
FROM Clerk RIGHT JOIN [Table Header] ON Clerk.Clerk = [Table Header].Clerk
ORDER BY [Table Header].IDTag;

Then, there is the subform (ItemCustWant1_subform) based on qryItemCustWant which is as follow:

SELECT ItemCustWant.IDTag, ItemCustWant.Itemno, ItemCustWant.Desc1, ItemCustWant.Desc2, ItemCustWant.Cat, ItemCustWant.Avail, ItemCustWant.[$Eight], ItemCustWant.Price, ItemCustWant.Qty, [Qty]*[Price] AS Total, ItemCustWant.Check
FROM ItemCustWant
WHERE (((ItemCustWant.IDTag)=[forms]![entry]![txtIDtag]));

And finally there is a one-to-many relation between "Table Header" and "ItemCustWant" with the IDTag.

Got I can't find any reason as for why it doesn't work all the time...

 

[haerion]

I just realized that it is always working on the same item#. It is not that sometime it work and sometime it doesn't, but more like some item# work and other doesn't.

And moreover, I tried to add the following at the end of the code:
msgbox desc2

This one always work on all item#, but seem like the problem lies in the copy to txtdesc2. Gonna go crazy soon about all this stuff