CODEWITHSUNDEEP
formsms-accesssubform
Z-A newbie
Z-A newbie

Reputation: 1

Sub-Forms in MS Acess

Background: I have two tables. One has a list of clients and some basic client attributes (tblClient). The second has a list of products. Each client can have multiple products so I have a one to many relationship here where the primary key (client name) is the foreign key in the product table.

What I'm looking to do: I'm looking to create two forms. The main form will have a snapshot of clients and have a small nested table to provide a summary of all the products. This I have completed successfully. I have also made a second form in which all the details associated with products can be put in (matching the products data table). What I want to do is to have a button on the main client form that when clicked opens up the product form ONLY to the products currently used by the client. If there are no product, it opens to a blank form with some pre-filled information (i.e. client name). For example, if I'm reviewing customer John Doe and he currently has product A, B, and C, I want to click a button to have the product details specific to John Doe pull up. If he has no products, I'd like Access to open up a new entry product form for me with John Doe's name filled in already to avoid confusion.

I'm very new to access, any help would be very much appreciated.

thanks, Z

Upvotes: 0

Views: 64

Answers (1)

Johnny Bones
Johnny Bones

Reputation: 8402

I'm assuming your 2nd form is linked to a table? If so, just filter the recordset when you open it.

For instance, the OnClick event of your button will look at the current ClientID and open the form based on that ClientID.

Dim stDocName As String
Dim stOpenArg As String

'Make stDocName the name of the form you're going to open
stDocName = "frmAssignProject"
'Make stOpenArg your variable to filter on
stOpenArg = Me.ClientID

DoCmd.OpenForm stDocName, , , , , , stOpenArg

Then, in the OnLoad event of your second form, filter based on your OpenArg:

private sub form_load()
   Dim rs as DAO.Recordset

   Me.RecordSource = "Select * From tblProducts Where ClientID = " & OpenArgs & ""

   Set rs = Me.RecordsetClone

   'This is where it determines if it should pull in the data from the previous form
   If rs.RecordCount > 0 Then       
   else
      Me.ClientID = Forms!frmClients.ClientID
      Me.ClientName = Forms!frmClients.ClientName
      etc... 
   end if

 end sub

The above is entirely "air code", it will probably need some tweaking but the concept is sound.

Upvotes: 1

Related Questions