Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

MSHFlexGrid Problem

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
manh2000
Asked:
manh2000
1 Solution
 
kdotkannanCommented:
It must be some thing with the property.. Just search the properties. You will get it.
0
 
aikimarkCommented:
If the Userid is a text column, your statement should be:
Set rec = CreateMyRecSet("SELECT * FROM User WHERE UserId = '" & cboUser.text & "'")
0
 
manh2000Author Commented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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

set datacontrol1.recordset = rec
0
 
manh2000Author Commented:
I tried that but it doesn't work.
0
 
aikimarkCommented:
What if you set the datacontrol.recordsource = "SELECT * FROM User WHERE UserId = " & cboUser.text

and refreshed the datacontrol?
0
 
manh2000Author Commented:
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
 
aikimarkCommented:
the Datasource must reference a datacontrol.
0
 
manh2000Author Commented:
It doesn't have to. It works when I assign a recordset to it.
0
 
aikimarkCommented:
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
 
manh2000Author Commented:
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
 
aikimarkCommented:
You must move the "DIM rec" statement to the form's General Declarations section.
0
 
manh2000Author Commented:
Let me try that
0
 
manh2000Author Commented:
Still doesn't work
0
 
aikimarkCommented:
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
 
manh2000Author Commented:
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
 
aikimarkCommented:
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
 
DanRollinsCommented:
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
 
aikimarkCommented:
DanRollins,

I agree with your recommendation.
0
 
SpideyModCommented:
per recommendation

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now