Combo box has bipolar disorder!

[gusbrunston]

Hi:

I always try to be as brief as possible, but this time...please bear with me!

Combo box on a form to find a particular record with the PK has worked for months. Then I installed OfficeXP. This particular form only, the combo box went awry. Will find the records for about half of the 171 records (from an SQL statement that returns 171 records, no problem) and not for the other half of the records. For the ones it won't find I get a "Can't go to that record" error, and debugs as follows:[tt]
**************************************************
Private Sub ComboID_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.RecordsetClone
rs.FindFirst "[TenantID] = " & Str(Me![ComboID])
Me.Bookmark = rs.Bookmark
ComboID = ""
End Sub
**************************************************[/tt]
Ok, I removed Office XP, installed Office 2000, and all the update service packs.

Still have the problem. I thought it might be a difference in VBA between XP and 2000, because there seems to be two ways to say "Me.RecordsetClone"...that way and "Me.Recordset.Clone", with the dot...don't know which version uses which.

Any clues will be very much appreciated.

Glad to post any additional information you think might be helpful [tt] Gus Brunston - Access2002(DAO)[/tt] Intermediate skills.

 

[ajdesalvo]

I see that Me![ComboID] must be a number as you are converting it to a string.

If that case, try this:

rs.FindFirst "[TenantID] = '" & Str(Me![ComboID]) & "'"

Good luck! Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[gusbrunston]

[tt]
Hi, Anthony:

Thanks for the tip, but...no joy.

now it can't get past:

rs.FindFirst "[TenantID] = '" & Str(Me![ComboID]) & "'"

******************************************************* [tt] Gus Brunston - Access2002(DAO)[/tt] Intermediate skills.

 

[ajdesalvo]

I checked some code I had, I missed the RecordSetClone issue:

This runs ok for me:

Set rs = Me.Recordset.Clone

I used a ComboBox Wizard to look up the result in a form, and it built it this way.

Good luck. Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[gusbrunston]

[tt]
Hi:

Thanks for your help with this. I, too, when using the wizard, get the same (...Me.Recordset.Clone...). Also, with the wizard, I get the same behavior of the combo box. I'm beggining to suspect that for some reason, even though the SQL statement seems to work o.k., that it has something to do with the records in the table itself, or perhaps the way they are stored...

I'll have access tomorrow to the back up copies of the front end and back end of the db to see how far back this problem goes.

Thanks again, [tt] Gus Brunston - Access2002(DAO)[/tt] Intermediate skills.

 

[gusbrunston]

[tt]
Hi, Anthony (and whoever it might concern):

Well, I created a new database and imported all the necessary objects to test the form in a different database. The combo box works in the new database.

But now I have the prodigious job of either importing all objects into the new database or finding the last uncorrupted backup (we backup every day).

Arrrrrgh!

Thank goodness it's just the front end and the data (in another database) seems intact.

Anthony, thanks for following this through with me.
[tt] Gus Brunston - Access2002(DAO)[/tt] Intermediate skills.

 

[ajdesalvo]

You are welcome!
Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[gusbrunston]

[tt]
Just to close this thread...

I'm sure that OfficeXP corrupted my pre-existent database. I have removed OXP and reinstalled Office2000. Restored the most recent Office2000 backup of the front end. Works fine. I have OfficeXP installed on the box that runs the back end: that doesn't seem to be a problem.

I'm going to explore this with Microsoft. [tt] Gus Brunston - Access2000(DAO)[/tt] Intermediate skills.

 

[JeremyNYC]

Gus,

Just so you know, you don't need access installed on the box that holds the back end. It never gets opened on that machine, unless you walk over there and fire up the database.

Also, have you checked this thread? I got hammered by this at one of my client's offices. Stopped me dead in my tracks. The implication I get from this is that it's not about using an a2k mdb in axp, it's about using a2k on a machine that has, or has had axp on it, and then importing stuff and closing the database without compiling. I didn't read through this whole thread, as I just flipped over here from the thread in which you refer to this one, but it looks as if you may have experienced the same thing. If so, I feel your pain.

Jeremy =============
Jeremy Wallace
AlphaBet City Dataworks

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[JeremyNYC]

Oh, good. First I call it a thread instead of an article, and then I forget to post the link! Sorry. Here's the link to an article on ms.com:

http://support.microsoft.com/default.aspx?scid=kb;EN-CA;q304548

=============
Jeremy Wallace
AlphaBet City Dataworks

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[c0i0nes]

If you don't get the full expected result from a control like a combo box on a form, always check that there is no active filter on the form or subform remaining from some previous use of the form.