Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    58

    Unanswered: Pass Through Query and Subform

    I have a subform which I would like to take data from a pass through query (currently configured via the record source). The problem is when I have linked up the subform to the main form and run it I get an error saying 'You can't use a pass-through query or a non-fixed-column cross tab query as a record source for a subform or subreport.' Is there any way around this other than using a make table query to copy the data in to the database.

    The pass through query is connecting via ODBC to an OMNIS database, the data collected is read only.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by HilcrRWise
    I have a subform which I would like to take data from a pass through query (currently configured via the record source). The problem is when I have linked up the subform to the main form and run it I get an error saying 'You can't use a pass-through query or a non-fixed-column cross tab query as a record source for a subform or subreport.' Is there any way around this other than using a make table query to copy the data in to the database.

    The pass through query is connecting via ODBC to an OMNIS database, the data collected is read only.
    Well - I thought I had a good alternative for you until I read the bit about OMNIS. No idea how to connect to this however if your data is read only you can populate an ADO recordset with your data and bind the form to this recordset. You'll have to figure out the connection string yourself - I have no idea. I typically use these resources though:

    http://www.carlprothman.net/Default.aspx?tabid=81
    http://www.connectionstrings.com/

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2004
    Posts
    58
    Thanks for the reply. I have limited knowledge of VB and none at all of using ADO. Can you run through what code to enter and how to bind the subform to the recordset.

    The connection string the pass through query uses is (Configured as a System DSN in the ODBC Data Source Administrator in Control Panel):
    ODBC;Driver=OMNIS;Dsn=INTACCESS;DataFilePath=P:\Ke ys\Integris\A2004_05end.df1;Username=;Password=~;

    The table to be accessed within the ODBC Database is:
    fKeyStage

    The fields from the table to be listed are:
    KSStuRecID
    KS2Year
    KS2_ENG_TT_SUB_NL
    KS2_MAT_TT_SUB_NL
    KS2_SCI_TT_SUB_NL
    KS3Year
    KS3_ENG_TT_SUB_NL
    KS3_MAT_TT_SUB_NL
    KS3_SCI_TT_SUB_NL


    Many thanks for your help.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    OK - the below is a knocked together guide and not necessarily the best way to code this e.g. you might want to create a separate function to create a connection object and create the recordset on the form but...

    Code:
     
    'Function to create and return recordset
    Public Function SubFRst() As ADODB.Recordset
     
    	Dim cnn As New ADODB.Connection
    	Dim strSQL As String
    	Dim i As Integer
    	
    	Set SubFRst = New ADODB.Recordset
       
    'Connection string to the database. I doubt the below will get you there but check out the links I posted - look at ODBC DSN connections.
    	cnn.ConnectionString = "ODBC;Driver=OMNIS;Dsn=INTACCESS;DataFilePath=P:\Ke ys\Integris\A2004_05end.df1;Username=;Password=~;"
    
    'Open connection
    	cnn.Open
    
    'Build up your sql
    	strSQL = "SELECT KSStuRecID, KS2Year "
    'And so on for all fields....
    	strSQL = strSQL & " FROM fKeyStage"
    
    'Get the client to do the work 
    	SubFRst.CursorLocation = adUseClient
    
    'Open your recordset
    	SubFRst.Open strSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
    
    'Disconnect the recordset
    	Set SubFRst.ActiveConnection = Nothing
    	
    	cnn.Close
    	Set cnn = Nothing
    	
    End Function 
     
    'On Your subforms OnOpen event:
    Private Sub Form_Open(Cancel As Integer)
     
    'The magic line....
    	Set Me.Recordset = SubFRst()
    
    'Populate the text boxes
    	Me.Text0.ControlSource = Me.Recordset.Fields(0).Name
    	Me.text1.ControlSource = Me.Recordset.Fields(1).Name
    '... and so on
    
    End Sub
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2004
    Posts
    58
    Thanks for the help the code worked great, however I have another issue now.

    The subform that gets its data from the ADO recordset needs to be linked to the mainform. I have set the link child and master fields of the subform and set it to requery whenever the mainform is changed. This worked fine when the subform was bound to a normal table, but now it uses the ADO recordset whenever the mainform is changed the subform does not update.

    Any Ideas?

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •