• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • Last Modified:

MDI problems passing Record Numbers between Child Forms

I have a small project which consists of making a read-only connection to a database, searching for and selecting one of many records, then displaying certain fields from the selected record.

Each record contains a large (but sparsely-populated) set of fields, and there are significant variations between record types.

I therefore wish to use a subset of two or three (from a possible total of nine) specially laid-out forms to display the data, depending on record type and user choices.

I chose to implement using a MDI parent (as a container) plus eleven child forms (two for pre-selection and a choice of three from nine for data display).

The 2 pre-selection forms are now partly operational, and I have two problems.

1.  Based on the user's initial search inputs I wish to filter the records to restrict them to a meaningful subset from which the user can select one or two; for this purpose they are presented in a DataRepeater Control. Instead the control is populated with _ALL_ rather than just the filtered records from the table.

2. When the user has made his selection I see no way to pass a pointer to the selected record -- the display form is always loaded displaying contents from record 1.

Questions:

How can I filter the records when I populate the Datarepeater?

I am currently using ADO Data Controls; how can I link or cascade them?.

Must I abandon the ADO controls and replace then with the equivalent code? (please say no!)

VB lacks a RECNO() intrinsic function with which to set a global variable -- does something equivalent exist?

Below is code from the second pre-selection form, where the (filtered?) records are being passed to the user for a final selection prior to display.

------------------------------------

Private Sub Form_Load()
   
    Dim strADO As String
    Dim strTab As String
    Dim strCon As String
    Dim adoConnect As ADODB.Connection
    Dim adoRecordset As ADODB.Recordset
    Dim adoCommand As ADODB.Command
    Dim cmdChange As ADODB.Command
   
    ' Open connection
    strCon = "Provider=MSDAORA.1;" & _
    "Password=xxxx;User ID=xxxx;Data Source=xxxx;Persist Security Info=True"
    Set adoConnect = New ADODB.Connection
    adoConnect.Open strCon
           
    ' Create a command object
    strTab = "SELECT * FROM TABLE_NAME WHERE FIELD_NAME LIKE " & varform & "*"
    Set cmdChange = New ADODB.Command
    Set cmdChange.ActiveConnection = adoConnect
    cmdChange.CommandText = strTab
    Set adoRecordset = New ADODB.Recordset
   
       
    Select Case varlist ' based on previous user choices for varlist and varform
        Case "FIELD_NAME1"
            ' Create a command object
         strTab = "SELECT * FROM TABLE_NAME WHERE FIELD_NAME1 LIKE " & varform & "*"
         Set cmdChange = New ADODB.Command
         Set cmdChange.ActiveConnection = adoConnect
         cmdChange.CommandText = strTab
         ' Open TABLE_NAME Table
             Set adoRecordset = New ADODB.Recordset
         adoRecordset.Open "TABLE_NAME", adoConnect, , , adCmdTable
       
     Case "FIELD_NAME2"
         :
         : etc                        
    End Select

    ' Now display the form + DataRepeater
    frmSelect.Show
   
    Set adoConnect = Nothing
    ' Waiting for user to inspect DataRepeater and click on a choice
   
    Exit Sub
End Sub

0
JimH
Asked:
JimH
1 Solution
 
trkcorpCommented:
Well, first things first... It looks like you are opening the table instead of executing your query against the table.  I can't see any reason for the command objects whatsoever in your code, set your connection, set your new recordset just as you have done then open the recordset something like this:

adoRecordset.Source = strTab
adoRecordset.Open , adoConnect, adOpenStatic, adLockReadOnly, adCmdText
 
or
adoRecordset.Open strTab, adoConnect, adOpenStatic, adLockReadOnly, adCmdText

See if this doesn't return the recordset you wish, then let people help you with the rest...
0
 
JimHAuthor Commented:
trkcorp,

Thanks for your reply -- it's been a while since I wrote in Visual Basic, and the ADO commands are new to me.

I will pursue your comments and respond later.

Thanks again,

JimH
0
 
JimHAuthor Commented:
trkcorp,

You wrote:
... set your connection, set your new recordset just as
you have done then open the recordset something like this:

adoRecordset.Source = strTab
adoRecordset.Open , adoConnect, adOpenStatic, adLockReadOnly, adCmdText

This comes very close, but VBA + Oracle show an error (which may indicate a possible problem with the final parameter: adCmdText. The Object Browser shows it to be a Global?).

When typing in that line, VBA prompts (and assists in generating the first 4 parameters, but not adCmdText) as follows:

Open([Source], [Active Connection], [CurserType as CursertypeEnum = adOpenUnspecified],

[LockTypeEnum = adLockUnspecified], [Options as Long = -1])

When I run the code this line is selected and produces the Oracle Error message:

+-------------------------------------------------+
| Run-time error'-2147217900 (8004Oe14)':         |
| ORA-00936: missing expression                   |
+-------------------------------------------------+

While the line is selected I can mouseover individual parameters and get a 'Tooltip' indication of values: 'strTab' and 'adoConnect' show expected string values; 'adOpenStatic' = 3; 'adLockReadOnly' = 1; 'adCmdText' = 1.

Below I include the current code, modified as per your comments:

Private Sub Form_Load()
   
    Dim strCon As String
    Dim adoConnect As ADODB.Connection
    Dim strTab As String
    Dim cmdChange As ADODB.Command
    Dim adoRecordset As ADODB.Recordset
   
    ' Open connection and Recordset
    strCon = "Provider=MSDAORA.1;" & _
    "Password=xxxx;User ID=xxxx;Data Source=xxxx;Persist Security Info=True"
    Set adoConnect = New ADODB.Connection
    adoConnect.Open strCon
               
    ' Open Table with Filtered Records
    Set adoRecordset = New ADODB.Recordset
   
    Select Case varlist ' based on previous user choices for varlist and varform
        Case "FIELD_NAME1"
         strTab = "SELECT FROM TABLE_NAME WHERE FIELD_NAME1 LIKE " & varform & "*"
         ' *** Following line produces error: ***
            adoRecordset.Open strTab, adoConnect, adOpenStatic, adLockReadOnly, adCmdText
         '
     Case "FIELD_NAME2"
         :
         : etc                        
    End Select
   
    Set adoConnect = Nothing
   
    Exit Sub
End Sub

I feel now that a solution to this part of the problem is pretty close, hopefully you can show me what is still missing.

JimH
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
trkcorpCommented:
Jyst drop the adcmdtext parameter altogether, it is optional & see what happens, also, I didn't realize we were working with Oracle which has its own set of idiosyncracies, play with the parms if you must...
0
 
trkcorpCommented:
Also, you may want to change the asterisk to a percent sign, Access is the only DB I know that "likes" the asterisk.
0
 
JimHAuthor Commented:
trkcorp,

I tried your suggestions with the following results:

1. Replacing the asterisk with a percent sign gives an 'Incorrect Character' error from Oracle.

2. Removing the adCmdText parameter makes no difference, I still get the Oracle 'missing expression' error.

Still close, but no cigar as yet ...

JimH
0
 
trkcorpCommented:
Is this really coded this way?
"SELECT FROM TABLE_NAME WHERE FIELD_NAME1 LIKE " & varform & "*"
Then SELECT WHAT, *, field names, etc...
bad syntax
0
 
JimHAuthor Commented:
trkcorp,

I was just now investigating the same line of code.
By changing it to:

strTab = "SELECT * FROM TABLE.NAME WHERE FIELD_NAME  LIKE '" & varform & "%'"

I was able to have it run without triggering an Oracle syntax error, but the data is not filtered by the "... LIKE '1%'" segment (which is what it reduces to, in my test case).

Do I need something like a command: 'adoRecordset.Refresh' in order to have the DataRepeater fields be updated with the filtered data?

I don't follow what your other lines mean:
'Then SELECT WHAT,*,field names, etc...'  ?
bad syntax

JimH
0
 
JimHAuthor Commented:
A follow-up:

I see what you mean, I think -- in editing the code for inclusion here I omitted the '*' from the 'SELECT FROM '

-- hope that clarifies things.

Jimh
0
 
trkcorpCommented:
J, I have done about all I can, I am not an Oracle person and I am not familiar with the DataRepeater control.  You should probably close this ? & resubmit it for your remaining issues... I expect no points but hope I have been of some assistance, good luck.
0
 
JimHAuthor Commented:
Having found my own solution (substitute a DataGrid control -- the DataRepeater causes problems -- see the link: http://support.microsoft.com/support/kb/articles/q197/4/28.asp ) I wish to close down this and the associated question, but the Tips section does not indicate how I should do this.

I would like to award say 100 points to trkcorp for his kind assistance, but again I do not know how this works.

Any help on this from the moderators would be appreciated.

JimH
0
 
DanRollinsCommented:
Hi JimH@devx,
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:

    Accept trkcorp's comment(s) as an answer.

JimH@devx, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0
 
NetminderCommented:
Per recommendation, force-accepted.

Netminder
CS Moderator
0

Featured Post

Industry Leaders: 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!

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