Form in Datasheet view with subform in single view

[mleiser]

I need to create a form that has two parts, one part that shows a list of records as a datasheet, and a second part/subform that shows details about the record that is selected in the first part of the form. Any ideas?

 

[boblarson]

Which version of Access are you using? Access 2007 and 2010 have what are called Split Forms which is exactly what you are describing.

 

[mleiser]

Hi! Thank you very much replying! I'm sorry I forgot to include that information. I'm using 2003. Do I have to upgrade to do what I want?

 

[boblarson]

No, but you will likely need to use two different forms on a main form and set one to be single form and one to be datasheet view and then in the form's On Current event you can set the filter for the single form to be based on the selection of the datasheet form.

 

[mleiser]

Hi again! I created a master form called “Promotions” which contains two secondary forms, “Promotions Summary” which is a datasheet Form, and “Promotions Details” which is a single form. In which form to I set up the filter, in “Promotions Details”? I’m not sure I know where and how to do that. I went into Properties, clicked on the “event” tab, scrolled down to the “On filter” row, and entered [forms]![promotions Summary]![id], but it didn’t work for me.

Am I in the wrong place?
Thank you very much for your help!
Mutty Leiser[FONT=&quot] [/FONT]

 

[boblarson]

If you can, post the database.

Make sure to change any sensitive data to fake data and then also run COMPACT AND REPAIR and then ZIP the file so it is under the 2Mb limit here.

 

[mleiser]

Hi again! Here's a mini-version of my database. I hope I attached it correctly.

 

[boblarson]

Okay, here you go. See if this works like you think it should.

 

[mleiser]

Thanks a million!!

Mutty Leiser

 

[mleiser]

Bob: Perhaps you can help me again. I'm applying this same technique to another Form (main form with datasheet subform and single view subform, and I'm trying to link the two by copying the VB code you sent me previously. Here's what I have, but it's getting stuck on me.ID:

Private Sub Form_Current()
On Error GoTo Current_ErrorHandler

If CurrentProject.AllForms("Catalog and Web Work Master Form").IsLoaded Then
If Not IsNull(Me.ID) Then
Me.Parent.Controls("Catalog and Web Work Details").Form.Filter = "ID=" & Me.ID
Me.Parent.Controls("Catalog and Web Work Details").Form.FilterOn = True

Else
Me.Parent.Controls("Catalog and Web Work Details").Form.Filter = "ID = -9999999"
Me.Parent.Controls("Catalog and Web Work Details").Form.FilterOn = True
End If
End If
Current_Exit:
Exit Sub

Current_ErrorHandler:
If Err.Number <> 2455 Then
MsgBox Err.Description, vbExclamation, "Error #: " & Err.Number
Resume Current_Exit
Resume
End If
End Sub


Thank you very much in advance!
Mutty Leiser

 

[mleiser]

Here's some more information in case it will bring someone closer to a solution to my problem. My event procedure looks like this (a little different from my last post):

Private Sub Form_Current()
On Error GoTo Current_ErrorHandler

If CurrentProject.AllForms("Catalog and Web Work Master Form").IsLoaded Then
If Not IsNull(Me.ID) Then
Me.Parent.Controls("Catalog and Web Work Details Form").Form.Filter = "ID=" & Me.ID
Me.Parent.Controls("Catalog and Web Work Details Form").Form.FilterOn = True

Else
Me.Parent.Controls("Catalog and Web Work Details Form").Form.Filter = "ID = -9999999"
Me.Parent.Controls("Catalog and Web Work Details Form").Form.FilterOn = True
End If
End If
Current_Exit:
Exit Sub

Current_ErrorHandler:
If Err.Number <> 2455 Then
MsgBox Err.Description, vbExclamation, "Error #: " & Err.Number
Resume Current_Exit
Resume
End If
End Sub

I'm getting an error message that says: "MS Access can't find the field 'Catalog and web Work Details Form' referred to in your expression."

"Catalog and web Work Details Form" is a Form, not a field, but perhaps there's something wrong in my syntax.

Thanks.

 

[mleiser]

I managed to fix the problem. Something must of corrupted with the second sub form. I removed it from the main form and added it back, and now it works fine!