Solved

MSHFlexGrid Problem

Posted on 2002-05-16
20
310 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 46

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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 46

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 46

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 46

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 46

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 46

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 46

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 46

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 46

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month5 days, 18 hours left to enroll

627 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