Add record in subform using button on main form

[WalterInOz]

Hi all,

I have a frm (frmmain) that holds a subfrm (subControlIN_table) in wich incoming messages (correspondence about projects) are stored. To retrieve a message I click on the record selector and a new form (frmIN) opens that displays the message and further details. This works fine. A problem arrises when I'm trying to add a new correspondence record in the subform. I've made a button on frmmain with this code behind it:

Private Sub btnAddIN_Click()
Dim StrMsg As String
Dim intRespons As Integer
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmMain"
DoCmd.Save

StrMsg = "You are about to add a Received Message to the project: " & vbNewLine & " " & vbNewLine & Me!WorkingTitle & " " & vbNewLine
StrMsg = StrMsg & " " & vbNewLine & "Continue?"

intRespons = MsgBox(StrMsg, vbInformation + vbYesNo + vbDefaultButton2, "Store received message for this Project")

If intRespons = vbNo Then
DoCmd.RunCommand acCmdClose
Exit Sub
End If

If intRespons = vbYes Then
stDocName = "frmIN"
stLinkCriteria = "[ProjectID]=" & "'" & Me![ProjectID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord , , acNewRec
End If

With Forms!frmIN
.WorkingTitle = Me.WorkingTitle
.SFNumber = Me.SFNumber
.ProjectID = Me.ProjectID
End With

End Sub

I expected this to open a blanc record of frmIN but it doesn't. I cannot find the mistake. I get this error message: Run-Time error 2501 the OpenForm action was cancelled. In the code page this line is high-lighted: DoCmd.OpenForm stDocName, , , stLinkCriteria

So I suppose that's where the problem lies, but could be wrong?
Help would be appreciated.

Thanks,
Walter

 

[RuralGuy]

Try this:

[COLOR="Red"]'--If intRespons = vbYes Then  <-- NOT NEEDED[/COLOR]
stDocName = "frmIN"
[COLOR="Red"]'-- stLinkCriteria = "[ProjectID]=" & "'" & Me![ProjectID] & "'" <-- NOT NEEDED[/COLOR]
'--DoCmd.OpenForm stDocName, , , stLinkCriteria <-- Original code
DoCmd.OpenForm stDocName, , , , acFormAdd '-- New code
[COLOR="Red"]'--DoCmd.GoToRecord , , acNewRec  <-- NOT NEEDED[/COLOR]
[COLOR="Red"]'-- End If  <-- NOT NEEDED[/COLOR]

 

[WalterInOz]

Thanks RG, your editing has made some difference but it's not doing all it is supposed to do. I get an error on .projectID line in:

With Forms!frmIN
.WorkingTitle = Me.WorkingTitle
.SFNumber = Me.SFNumber
.ProjectID = Me.ProjectID
End With

If I take that line out the frmIN opens nicely except that it doesn't hold the projectID which is essential to store the new message with the correct project.

The error message I get is attached. I have no idea what to do with that. The key (projectID) is on both forms and has a unique value on the frmMain. I don't understand what else it might be that the error message is getting at.

 

[RuralGuy]

Try putting the WhereCondition back in:

stDocName = "frmIN"
stLinkCriteria = "[ProjectID]=" & Chr(34) & Me![ProjectID] & Chr(34)
[COLOR="Red"]'--DoCmd.OpenForm stDocName, , , stLinkCriteria <-- Original code[/COLOR]
DoCmd.OpenForm stDocName, , , [b]stLinkCriteria[/b], acFormAdd '-- New code

Your WhereCondition assumes the ProjectID is a text field. Is that correct?

 

[WalterInOz]

Try putting the WhereCondition back in

Tried your suggestion (and several variations on it) but no good result. Still error messages

Your WhereCondition assumes the ProjectID is a text field. Is that correct?

No, the ProjectID field is a number field. Initially ProjectID is generated as an autonumber on the form in which new projects are entered.
I don't get it. I've used this code before in another database and there it works like a charm. As far as I can tell, all parameters are similar so I don't understand what the problem can be.

 

[WalterInOz]

GOT IT!

The mistake was that I referred to the open frmMain first to save it and not directly to frmIN. Don't understand why that was a mistake but since I've changed it the button acts as I want it to. Code is now as follows:


Dim StrMsg As String
Dim intRespons As Integer
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmIN"

stLinkCriteria = "[ProjectID]=" & Me![ProjectID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord , , acNewRec

StrMsg = "You are about to add a Received Message to the project: " & vbNewLine & " " & vbNewLine & Me!WorkingTitle & " " & vbNewLine
StrMsg = StrMsg & " " & vbNewLine & "Continue?"
intRespons = MsgBox(StrMsg, vbInformation + vbYesNo + vbDefaultButton2, "Add Official Action for this Patent")

With Forms!frmIN

If intRespons = vbNo Then
DoCmd.RunCommand acCmdClose
Exit Sub
End If

If .NewRecord Then
.ProjectID = Me.ProjectID
.WorkingTitle = Me.WorkingTitle
.SFNumber = Me.SFNumber
End If
End With

 

[RuralGuy]

Outstanding! Glad to hear you got it working. Thanks for posting back with your success.