Linked Subforms 1

[chtullu]

Hello everyone,

Here is the situation. I've created two subforms that are based on the same underlying table - tblUnmatchedBrinks. I've also created two other subforms based on the same underlying table, in this case tblUnmatched Cadence. Now lets take the first two subform first - the ones based on tblUnmatchedBrinks. One subform displays all the underlying records in datasheet view. The second subform displays the data in form view, one record at at time. I want the user to be able to select a record in datasheet view and have that record displayed in form view in the formview subform. I need to do this for both sets of subforms on the same main form.

Here is what I've tried to do. In the datasheet subform on the form current event I've entered
hichRecord = Me.Database_ID
'MsgBox "The following record has been selected " & WhichRecord

MYRECORDSOURCE = "Select * From tblUnMatchedBrinks Where [Database Id]= " & WhichRecord & ";"
'Forms.frmMMRecon.subfrmUnmatchedBrinks1.Form.RecordSource = MYRECORDSOURCE

Where MYRECORDSOURCE is a variable having global scope. I've then set the subform (the one that displays records in formview) record source property to MYRECORDSOURCE. When I try to open the main form, access locks up. I've tried setting the formview subforms recordsource property from the other subform but it still doesn't work. I'll keep working at it. I know that the solution is pretty simple.

 

[TheAceMan1]

How are ya chtullu . . . . .
[ol][li]Set the [blue]RecordSource[/blue] of the forms in [purple]datasheet view[/purple] to [blue]Table or Query[/blue] as necessary to view all records. Be sure to include the PK.[/li]
[li]Set the [blue]RecordSource[/blue] of the forms in [purple]form view[/purple] to the same or equivalent query. In the query [blue]set criteria for the PK[/blue] as follows:

[blue]Forms![purple][b]MainFormName[/b][/purple]![purple][b]DataSheetsubFormName[/b][/purple].Form![purple][b]PKname[/b][/purple][/blue]

[/li]
[li]In the OnCurrent event of the forms in datasheet view, enter:

[blue]Forms![purple][b]MainFormName[/b][/purple]![purple][b]FormViewsubFormName[/b][/purple].Requery[/blue]

[/li][/ol]
[blue]Thats It . . . give it a whirl & let me know . . .[/blue]

See Ya! . . . . . .

 

[chtullu]

Hello,

Thanks for the info. It worked perfectly. In fact, I did try to use the same approach earlier but was unsuccesful. As I implemented your solution I noted that the subforms were not properly named and had taken their name from the underlying table or query. I should have caught that. At any rate thanks for the help. It works perfecttly. I knew it would be a simple solution. I realized that I was on the wrong track when I started to examine more complicated avenues.

Juan