Link to home
Start Free TrialLog in
Avatar of jomuniz
jomuniz

asked on

Linking Unbound Forms

Greeting Experts,

I have a form called frm_ActLog and a subform called frm_ActDetail.  frm_Actlog is a continues form that only displays a subset of the recordset.  frm_ActDetail is embedded in the form footer of frm_ActLog.  I want to be able to click on a record in frm_ActLog and have the detail record display in the subform frm_ActDetail.  

I am using SQL Server with DSN less connection (no linked tables).  I used to be able to perform this function just fine with bound forms.  Any suggestions?  Love to hear from you.....Jose
Avatar of Jeff Tennessen
Jeff Tennessen
Flag of United States of America image

This should be no problem. You would set the form/subform relationship up just as you would if you were using Jet tables (local or linked). The trick is to define the Record Source of the two forms correctly. I suggest creating queries for each of them that utilize the IN subclause of the FROM clause, for example:

SELECT *
FROM Table1 IN '' [ODBC;DRIVER=SQL Server;SERVER=ServerName;DATABASE=DBName]
WHERE <...etc...>

You can then bind the form to these queries and proceed just as you would have in the past.

This approach does not require a DSN or linked tables. We use it in a production app at our company that connects to a 10GB SQL Server backend and have had no discernable performance problems. Depending on the specifics of your data, you may or may not find this to be the case for you.

Jeff
I should note that it *is* possible to do this with unbound forms, but it is pretty code-intensive. Unless there is a strong requirements to use unbound forms, I would explore the IN subclause.

Jeff
Avatar of jomuniz
jomuniz

ASKER

Thanks Jeff.  I will try this and get back with you.
Avatar of jomuniz

ASKER

Jeff, before I proceed with you suggestion, please look at my code below.  I do bind the ADODB recordset to the forms recordset.  I do the same for frm_ActDetail, however I cannot create a link between the two.

frm_ActLog
gcnn1 and grst are defined as global

Private Sub Form_Open(Cancel As Integer)
    Set gcnn1 = New ADODB.Connection
    Set grst = New ADODB.Recordset
    With gcnn1
        .ConnectionString = gCnnStr
        .CursorLocation = adUseClient
        .Open
    End With
 
    grst.Open "SELECT ActivityLog.* FROM ActivityLog", gcnn1, adOpenDynamic, adLockOptimistic
    Set Me.Recordset = grst
   
 End Sub

frm_ActDetail
Private Sub Form_Open(Cancel As Integer)
   
    Dim rst2 As ADODB.Recordset
    Set rst2 = grst  
    Set Me.Recordset = rst2
 
    grst.Close
    gcnn1.Close
 
End Sub
ASKER CERTIFIED SOLUTION
Avatar of Jeff Tennessen
Jeff Tennessen
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jomuniz

ASKER

Would I substitute frm_ActDetail in <subformcontrol> ?
Avatar of jomuniz

ASKER

I am getting the following error "Data Provider could not be initialized" on the following line.  

Me.frm_ActDetail.LinkChildFields = "ID"

Private Sub Form_Open(Cancel As Integer)
' On Error Resume Next
    SysUser = PSysUser
    Dim rst2 As ADODB.Recordset

    Set gcnn1 = New ADODB.Connection
    Set grst = New ADODB.Recordset
    With gcnn1
        .ConnectionString = gCnnStr
        .CursorLocation = adUseClient
        .Open
    End With
 
    grst.Open "SELECT ActivityLog.* FROM ActivityLog", gcnn1, adOpenDynamic, adLockOptimistic
    Set Me.Recordset = grst
    Set rst2 = grst
    Set Me.frm_ActDetail.Form.Recordset = rst2

    Me.frm_ActDetail.LinkChildFields = "ID"
    Me.frm_ActDetail.LinkMasterFields = "ID"

    rst2.Close
    grst.Close
    gcnn1.Close
End Sub
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jomuniz

ASKER

I linked both forms using bookmark on the click event of the detail section of the main form.
After you reported the error, I went back and tried my second suggestion, and it does seem that there is some kind of problem with it. As I said, I'd never tried it. Sorry for the red herring! Here's another suggestion: Again remove Form_Open from frm_ActDetail, then put this code in frm_ActLog:


Private mcn As ADODB.Connection

Private Sub Form_Current()

  Dim rsDetail As New ADODB.Recordset
 
  With rsDetail
    .ActiveConnection = mcn
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic
    .Open "SELECT <fieldlist> FROM <detailtable> WHERE [ID] = " & [ID]
   
    Set Me.<subformcontrol>.Form.Recordset = rsDetail
  End With
 
  On Error Resume Next
 
  rsDetail.Close
  Set rsDetail = Nothing

End Sub

Private Sub Form_Load()

  Dim rsLog As New ADODB.Recordset
 
  Set mcn = New ADODB.Connection
  With mcn
    .CursorLocation = adUseClient
    .ConnectionString = "Provider=SQLOLEDB;Integrated Security=SSPI;Initial Catalog=<dbname>;Data Source=<servername>"
    .Open
  End With
 
  With rsLog
    .ActiveConnection = mcn
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic
    .Open "SELECT <fieldlist> FROM <logtable>"
   
    Set Me.Recordset = rsLog
  End With
 
  On Error Resume Next
 
  rsLog.Close
  Set rsLog = Nothing

End Sub

Private Sub Form_Unload(Cancel As Integer)

  On Error Resume Next
 
  mcn.Close
  Set mcn = Nothing

End Sub


I've tested this one, so I think it should work. You may get better performance using the IN subclause method I mentioned above, though. Test both and see.

Jeff