Creating an open form button using a composite key

[ItsMe]

You can upload a file. I am not sure I will have time to look at it. Did you try following the instructions in post #14?

It seems your concern is creating a form with a subform. post 14, along with the information provided should get you through that issue.

[dhollingsworth]

Hi ItsMe,

I'm reasonably confident working with main forms and subforms in the usual fashion. I don't rely on wizards for developing a database. In this case I want to be able to link two tables via their IDs but without using the normal subform route, as you cannot insert a subform in to a continuous form.

I need to get this resolved and am willing to cover your time to look at this. I can't post the database on the forum as it contains sensitive information; removing the data cripples its functionality.

[ItsMe]

Hi ItsMe,

......, as you cannot insert a subform in to a continuous form.

.

I did not understand you were trying to use a continuous form as a main form. let me see what is going on here..... I need to get a sandwich and I will take a look after lunch.

[ssanfu]

There are a couple of ways to accomplish what you want to do.

Just to make sure I understand, you have a form "Asset Condition Scoring" (frmInput) with a button ("Defects Info") on each record that, when clicked, should open a 2nd form "Asset Defects and Remedial Works" (frmASSET_WORKS) to add additional detail records.


First Method:

Insert a new record into the table for "frmASSET_WORKS", then open the form with the same syntax as above. The code for the button would look something like:

Private Sub btnAddDefects_Click()
   On Error GoTo Err_btnAddDefects_Click
   Dim stDocName As String
   Dim stLinkCriteria As String
   Dim sSQL As String

   'create a new record in the "frmASSET_WORKS" table
   sSQL = "INSERT INTO tblAssettDefects (Asset_ID, Criteria_ID) VALUES ('" & Me.[Asset_ID] & "', " & Me.[Criteria_ID] & ");"
   CurrentDb.Execute sSQL, dbFailOnError

   'open the form to the new record
   stLinkCriteria = "[Asset_ID] = '" & Me.[Asset_ID] & "' AND [Criteria_ID] = " & Me.[Criteria_ID]
   DoCmd.OpenForm "frmASSET_WORKS", , , stLinkCriteria

Exit_btnAddDefects_Click:
   Exit Sub

Err_btnAddDefects_Click:
   MsgBox Err.Description
   Resume Exit_btnAddDefects_Click

End Sub

------------------------------
Another method is to open the form then create the new record. When opening the form, the command string would use the "Open Arguments" clause.
The button code remains essentially the same.....but there is more code.

The code for the button:

Private Sub btnAddDefects_Click()
   On Error GoTo Err_btnAddDefects_Click
   Dim stDocName As String
   Dim stLinkCriteria As String
   Dim sSQL As String

   'create a new record in the "frmASSET_WORKS" table
   '   sSQL = "INSERT INTO tblAssettDefects (Asset_ID, Criteria_ID) VALUES ('" & Me.[Asset_ID] & "', " & Me.[Criteria_ID] & ");"
   '   CurrentDb.Execute sSQL, dbFailOnError

   'open the form to the new record
   If IsNull(Me.[Asset_ID]) Or IsNull(Me.[Criteria_ID]) Then
   Else
      stLinkCriteria = Me.[Asset_ID] & " | " & Me.[Criteria_ID]
   End If

   DoCmd.OpenForm "frmASSET_WORKS", OpenArgs:=stLinkCriteria

Exit_btnAddDefects_Click:
   Exit Sub

Err_btnAddDefects_Click:
   Select Case Err.Number
      Case 2501

      Case Else
         MsgBox Err.Description & "     Err Number: " & Err.Number
   End Select
   Resume Exit_btnAddDefects_Click

End Sub

The code for the form "frmASSET_WORKS":

Private Sub Form_Open(Cancel As Integer)
   On Error GoTo Err_FormOpen
   Dim arData

   If IsNull(OpenArgs) Then
      MsgBox "Missing data for " & vbNewLine & vbNewLine & "[Asset_ID] or Criteria_ID"
      Cancel = True
   Else
   'put the data into the array
      arData = Split(OpenArgs, "|")
      
      ' fill the controls from the array
      Me.[Asset_ID] = arData(0)
      Me.[Criteria_ID] = arData(1)
   End If

Exit_FormOpen:
   Exit Sub

Err_FormOpen:
   MsgBox Err.Description
   Resume Exit_FormOpen

End Sub

The form "frmASSET_WORKS" should have the properties:
Data Entry = Yes
Modal = Yes



Be sure and change the table, control and form names to your names. I had to guess on a couple of the names.
Hope I put in enough comments..

[ssanfu]

<snip>, as you cannot insert a subform in to a continuous form. <snip>

I have and do use a subform in a continuous form. Access 2K complains but allows it. I don't have A2007/10 available right now to test, but try this.

Put the subform in the form footer. View the form to see if the sub form is linked correctly to the main form. If all is well, go back to design view and change the default view of the main form to continuous forms. Save and open.

BTW - try this in a COPY of your dB - just in case.

[ItsMe]

I am reading it as you want to open another form that will display relative information, not a new data entry form. If this is the case, the following code will do this. Just make sure your form that you are opening has both modal and pop values to yes, also need to make sure there is a "Close" button available when using modal.

Place this code in the click event of your continuous form's control button

Dim stLinkCriteria As String
stLinkCriteria = "[Asset_ID]='" & Me![Asset_ID] & "'"
stLinkCriteria = (stLinkCriteria & " AND ") & "[Criteria_ID] = " & Me![Criteria_ID]
DoCmd.OpenForm "frmASSET_WORKS", , , stLinkCriteria