Solved

MDI problems passing Record Numbers between Child Forms

Posted on 2001-09-14
13
188 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 4

Expert Comment

by:trkcorp
Comment Utility
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
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 4

Expert Comment

by:trkcorp
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Per recommendation, force-accepted.

Netminder
CS Moderator
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

763 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now