Solved

MSHFlexGrid Problem

Posted on 2002-05-16
20
309 Views
Last Modified: 2008-02-01
I have a simple form which has a drop down combo and a MSHFlexGrid. The drop down combo contains a list of user id's, which will be use as a criteria.

The data in the MSHFlexGrid will be regenerated every time a new item is selected from the drop down combo.

The first time I select an item from the drop down combo, new data is populated in the MSHFlexGrid and I can select any row (selection mode is by row) from the MSHFlexGrid. However, the next time that I select an item from the combo, new data is also populated in the grid but I cannot select any row from the grid, it's default to the first row. The grid now is unselectable.

Please tell me what is wrong. Following is the code that I used to populate the MSHFlexGrid. Note: the recordset is small.


Sub cboUser_Click()
  PopulateData
End Sub

Sub PopulateData()

  DIM rec ADODB.recordset

  Set rec = CreateMyRecSet("SELECT * FROM User WHERE UserId = " & cboUser.text)
  Set myMSHFlex.datasource = rec
  myMSHFlex.refresh
  rec.close
  set rec = nothing
End Sub

Function CreateMyRecSet(strSQL as string) AS ADODB.Recordset
   .....This function returns a recordset without any problem
End Function


PLEASE HELP!!!!
0
Comment
Question by:manh2000
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
20 Comments
 
LVL 3

Expert Comment

by:kdotkannan
ID: 7013308
It must be some thing with the property.. Just search the properties. You will get it.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 7013510
If the Userid is a text column, your statement should be:
Set rec = CreateMyRecSet("SELECT * FROM User WHERE UserId = '" & cboUser.text & "'")
0
 

Author Comment

by:manh2000
ID: 7014269
I also think there is something wrong with the property setting when it reassign a new datasource but I couldn't figure out.

There is no problem creating the recordset, like I said. The userid is a number field.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 45

Expert Comment

by:aikimark
ID: 7014519
since the flexgrid datasource can't be changed and refers to a data control, what you must do is:

set datacontrol1.recordset = rec
0
 

Author Comment

by:manh2000
ID: 7014607
I tried that but it doesn't work.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 7014685
What if you set the datacontrol.recordsource = "SELECT * FROM User WHERE UserId = " & cboUser.text

and refreshed the datacontrol?
0
 

Author Comment

by:manh2000
ID: 7015170
I haven't tried it but the grid that I used doesn't connect to any source (like data environment), so I don't think it will work because it requires an object not a string
0
 
LVL 45

Expert Comment

by:aikimark
ID: 7015274
the Datasource must reference a datacontrol.
0
 

Author Comment

by:manh2000
ID: 7016276
It doesn't have to. It works when I assign a recordset to it.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 7016334
AM I DENSE OR WHAT?!?

Your procedure code:
~~~~~~~~~~~~~~~~~~~
 DIM rec ADODB.recordset

 rec.close
 set rec = nothing
~~~~~~~~~~~~~~~~~~~~
both defines/allocates/fills the recordset variable and destroys the recordset variable.

Why didn't I catch that earlier.
======================================
You need to allocate your recordset variable in the General Declarations section of your code and move the
   rec.close
   set rec = nothing

statements to the Form_Unload event.
0
 

Author Comment

by:manh2000
ID: 7017401
I don't know if you get it or not but the first time it works fine. The problem only occurs if I repopulate the flexgrid. Also, to let you know that, remove the rec.close and/or set rec = nothing doesn't solve the problem. I tried this so many times already.

0
 
LVL 45

Expert Comment

by:aikimark
ID: 7017416
You must move the "DIM rec" statement to the form's General Declarations section.
0
 

Author Comment

by:manh2000
ID: 7017429
Let me try that
0
 

Author Comment

by:manh2000
ID: 7018520
Still doesn't work
0
 
LVL 45

Expert Comment

by:aikimark
ID: 7019769
And your combobox contains numbers and not names?

=========================================
At this point I need you to copy/paste your code into a comment.  Please include everything from the Option Explicit statment through the end.
0
 

Author Comment

by:manh2000
ID: 7021884
I don't think my code will work on your machine because it connects to my local database. Why don't you create a simple project and try out? Let me know if it works. Name and number is not a big problem and I know what I'm doing.
Thanks
0
 
LVL 45

Expert Comment

by:aikimark
ID: 7021930
Please just show me the PopulateData() code.

==============================================
In order to recreate your problem I need to know:
1. Version and maintenance release of VB
2. Version and maintenance release of MSFlexGrid component
3. Version of ADO
4. Version and maintenance release of Windows.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7817720
Hi manh2000,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Save as PAQ -- No Refund.

manh2000, Please DO NOT accept this comment as an answer.
EXPERTS: Post a comment if you are certain that an expert deserves credit.  Explain why.
==========
DanRollins -- EE database cleanup volunteer
0
 
LVL 45

Expert Comment

by:aikimark
ID: 7818587
DanRollins,

I agree with your recommendation.
0
 

Accepted Solution

by:
SpideyMod earned 0 total points
ID: 7909640
per recommendation

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question