Search a SUBFORM & return corresponding values in MAINFO

[page]

Hi! New here to the forum - please help! I've worked with Access off & on for years, but this one is REALLY stumping me (I have a couple other issues, but let's cover the big one first). I am working in a form that holds the primary data for shipping (entry ID, date expected, date delivered, vendor, etc). This MAINFORM has 3 subforms. I have done this because there are multiple one to many relationships. The users want to perform searches based on the SUBFORM. I do currently have the SUBFORM linked to the MAINFORM via the parent / child values.

For example, I have an Invoice SUBFORM that shows multiple invoice numbers & amounts that correspond to the MAINFRM entry ID. I need to implement the capability to search for an Invoice number in the Invoice SUBFORM & return the corresponding MAINFORM values. Does this make sense? Can anyone help?



Thx! Page

[page]

Thought I'd post the answer in case anyone else has same issue. Placed a command button on my main form with Event Procedure as follows...

----------------------------------------------------------------------
Private Sub cmdSearchContainers_Click()
On Error GoTo Err_cmdSearchContainers_Click

DoCmd.OpenForm "f-Master Data Table Entry New", acNormal
Me.RecordSource = "q-Search Containers"

Exit_cmdSearchContainers_Click:
Exit Sub

Err_cmdSearchContainers_Click:
MsgBox Err.Description
Resume Exit_cmdSearchContainers_Click

End Sub
------------------------------------------------------------------------------

In addition, I created a query called "q-Search Containers" with the following SQL....

-----------------------------------------------------------------------------

SELECT [t-Master Data Table New].*
FROM [t-Master Data Table New] INNER JOIN [t-Containers] ON [t-Master Data Table New].[System Number] = [t-Containers].[Master Record Number]
WHERE ((([t-Containers].[Container Number])=[Entered the Container Number for which you are searching]));

------------------------------------------------------------------------------

In short, this opens the master form with the specific container number specified by the user. I also created a similar button to "View All Records" which opens the master form with all records. This way, users can easily find a specific record & then return to all records.

------------------------------------------------------------------------------

Private Sub cmdViewAllRecords_Enter()
On Error GoTo Err_cmdViewAllRecords_Enter

DoCmd.OpenForm "f-Master Data Table Entry New", acNormal
Me.RecordSource = "q-form - Master Data Table Entry New"

Exit_cmdViewAllRecords_Enter:
Exit Sub

Err_cmdViewAllRecords_Enter:
MsgBox Err.Description
Resume Exit_cmdViewAllRecords_Enter

End Sub

-----------------------------------------------------------------------------------

This is the only way that I could find to open a form based on a search of a record in a subform.

Thanks