I have a form that list details of customer projects. I have 5 other forms I want to access for this customer eg funding, project costs each of which has its own table. I want to be able to move between forms for a particular project and add or amend records in the other tables. (ie related records) I don't want to use sub forms because I don't like the look of them. I want to do this using command buttons on the main customer projects. How do I do this ?????
I need multiple forms not sub forms
- Thread starter lodgey47
- Start date
I had the same issue. Tabcontols are a good solution. If you don't like Tabcontrols(I had too many other forms to use tabs. You don't) here is another solution. Assuming that each of your 5 forms are bound to tables which are linked to your main table this code will open whichever one of the 5 forms you select in the listbox and populate the FK field with the same value as the PK in your main form thus correctly linking the records.
Create a listbox on your main form listing each of your 5 forms. Attach the following code to the after update event:
----------------------
Private Sub Mycbo_AfterUpdate()
Select Case Me.Mycbo
Case "New Form 1"
Dim strFrm As String
strFrm = "frm_mynewform1"
'save pending edits
Me.Refresh
'open the "new" form
DoCmd.OpenForm strFrm
'create the new record in the newly opened form
DoCmd.GoToRecord acDataForm, strFrm, acNewRec
'reference the newly opened form inside the With block
With Forms(strFrm)
'and set the values of any of its controls
.ForeignKeyField = me.PrimaryKey
End With
Case "New Form 2"
Dim strFrm As String
strFrm = "frm_mynewform2"
'save pending edits
Me.Refresh
'open the "new" form
DoCmd.OpenForm strFrm
'create the new record in the newly opened form
DoCmd.GoToRecord acDataForm, strFrm, acNewRec
'reference the newly opened form inside the With block
With Forms(strFrm)
'and set the values of any of its controls
.ForeignKeyField = me.PrimaryKey
End With
Case "New Form 3"
Dim strFrm As String
strFrm = "frm_mynewform3"
'save pending edits
Me.Refresh
'open the "new" form
DoCmd.OpenForm strFrm
'create the new record in the newly opened form
DoCmd.GoToRecord acDataForm, strFrm, acNewRec
'reference the newly opened form inside the With block
With Forms(strFrm)
'and set the values of any of its controls
.ForeignKeyField = me.PrimaryKey
End With
Case "New Form 4"
Dim strFrm As String
strFrm = "frm_mynewform4"
'save pending edits
Me.Refresh
'open the "new" form
DoCmd.OpenForm strFrm
'create the new record in the newly opened form
DoCmd.GoToRecord acDataForm, strFrm, acNewRec
'reference the newly opened form inside the With block
With Forms(strFrm)
'and set the values of any of its controls
.ForeignKeyField = me.PrimaryKey
End With
Case "New Form 5"
Dim strFrm As String
strFrm = "frm_mynewform5"
'save pending edits
Me.Refresh
'open the "new" form
DoCmd.OpenForm strFrm
'create the new record in the newly opened form
DoCmd.GoToRecord acDataForm, strFrm, acNewRec
'reference the newly opened form inside the With block
With Forms(strFrm)
'and set the values of any of its controls
.ForeignKeyField = me.PrimaryKey
End With
End Select
End Sub
---------------
Create a listbox on your main form listing each of your 5 forms. Attach the following code to the after update event:
----------------------
Private Sub Mycbo_AfterUpdate()
Select Case Me.Mycbo
Case "New Form 1"
Dim strFrm As String
strFrm = "frm_mynewform1"
'save pending edits
Me.Refresh
'open the "new" form
DoCmd.OpenForm strFrm
'create the new record in the newly opened form
DoCmd.GoToRecord acDataForm, strFrm, acNewRec
'reference the newly opened form inside the With block
With Forms(strFrm)
'and set the values of any of its controls
.ForeignKeyField = me.PrimaryKey
End With
Case "New Form 2"
Dim strFrm As String
strFrm = "frm_mynewform2"
'save pending edits
Me.Refresh
'open the "new" form
DoCmd.OpenForm strFrm
'create the new record in the newly opened form
DoCmd.GoToRecord acDataForm, strFrm, acNewRec
'reference the newly opened form inside the With block
With Forms(strFrm)
'and set the values of any of its controls
.ForeignKeyField = me.PrimaryKey
End With
Case "New Form 3"
Dim strFrm As String
strFrm = "frm_mynewform3"
'save pending edits
Me.Refresh
'open the "new" form
DoCmd.OpenForm strFrm
'create the new record in the newly opened form
DoCmd.GoToRecord acDataForm, strFrm, acNewRec
'reference the newly opened form inside the With block
With Forms(strFrm)
'and set the values of any of its controls
.ForeignKeyField = me.PrimaryKey
End With
Case "New Form 4"
Dim strFrm As String
strFrm = "frm_mynewform4"
'save pending edits
Me.Refresh
'open the "new" form
DoCmd.OpenForm strFrm
'create the new record in the newly opened form
DoCmd.GoToRecord acDataForm, strFrm, acNewRec
'reference the newly opened form inside the With block
With Forms(strFrm)
'and set the values of any of its controls
.ForeignKeyField = me.PrimaryKey
End With
Case "New Form 5"
Dim strFrm As String
strFrm = "frm_mynewform5"
'save pending edits
Me.Refresh
'open the "new" form
DoCmd.OpenForm strFrm
'create the new record in the newly opened form
DoCmd.GoToRecord acDataForm, strFrm, acNewRec
'reference the newly opened form inside the With block
With Forms(strFrm)
'and set the values of any of its controls
.ForeignKeyField = me.PrimaryKey
End With
End Select
End Sub
---------------
Last edited:
It sure as heck is, Pat. I have been working on the same issue with a series of forms that I need to open and autopopulate 3 or 4 fields. I was give the above code a while ago and it works great but you are right its a lot of coding. I have a seperate thread going on a related issue to this code and someone else suggested using OpenArg instead of my code. But I couldn't get it to work. I like yours a lot but not sure I want to rewrite 25 or so forms. But for 5 forms I would do it in a heartbeat.
