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
Solved

MDI problems passing Record Numbers between Child Forms

Posted on 2001-09-14
13
191 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
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
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…

860 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