Solved

MDI problems passing Record Numbers between Child Forms

Posted on 2001-09-14
13
192 Views
Last Modified: 2006-11-17
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
Comment
Question by:JimH
[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
13 Comments
 
LVL 4

Accepted Solution

by:
trkcorp earned 200 total points
ID: 6483913
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
 

Author Comment

by:JimH
ID: 6487402
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
 

Author Comment

by:JimH
ID: 6487843
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
Independent Software Vendors: 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!

 
LVL 4

Expert Comment

by:trkcorp
ID: 6487882
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
 
LVL 4

Expert Comment

by:trkcorp
ID: 6487917
Also, you may want to change the asterisk to a percent sign, Access is the only DB I know that "likes" the asterisk.
0
 

Author Comment

by:JimH
ID: 6488058
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
 
LVL 4

Expert Comment

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

Author Comment

by:JimH
ID: 6488400
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
 

Author Comment

by:JimH
ID: 6488412
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
 
LVL 4

Expert Comment

by:trkcorp
ID: 6488429
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
 

Author Comment

by:JimH
ID: 6494551
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
 
LVL 49

Expert Comment

by:DanRollins
ID: 7208428
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
 
LVL 5

Expert Comment

by:Netminder
ID: 7241243
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!

Question has a verified solution.

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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

726 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