GTRR; If no Related Record, Create New In 2nd Form

[amywolfie]

I've been trying to do something for about 3 days – I get close, but
not 100%.
I am trying to:

Open frmRevisionHistory from a button on frmFeeInput.

If there is NO RELATED RECORD, then I need to create one in
frmRevisionHistory, and pass the PK (PK_Fees) on frmFeeInput to
FK_Fees on the 2nd form.

If there IS (OR ARE) RELATED RECORDS, then go to them on
frmRevisionHistory.

Right now, I am getting error message 2046 "The command or action
RecordsGoToNew isn't available now."

Here's the code:

Private Sub butGoToHistory_Click()

On Error GoTo Error_Handler:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmRevisionHistory"

stLinkCriteria = "[FK_Fees]=" & Me![PK_Fees]
DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria

If RecordsetCount = 0 Then
MsgBox "No history records. Will create one now", vbOKOnly,
"Create Record"
DoCmd.RunCommand acCmdRecordsGoToNew
'DoCmd.GoToRecord , , acNewRec 'Access Wizard code
Dim sSQL As String
DoCmd.SetWarnings False

sSQL = "INSERT INTO tblRevisionHistory ( FK_Fees)" & _
" SELECT Forms!frmFeeInput!PK_Fees AS intCode" & _
" FROM tblFees;"

DoCmd.RunSQL (sSQL)

DoCmd.SetWarnings True
frmRevisionHistory.Requery

Else
stLinkCriteria = "[FK_Fees]=" & Me![PK_Fees]
DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria
MsgBox "RELATED RECORD!"
frmRevisionHistory.Requery

End If

Exit Sub

Error_Handler:
MsgBox Err.Description & " " & Err.Number

[Jim Allensworth]

Amy, I get the feeling that there are some conceptual issues here.

First forms don't hold records. They are normally *bound* to a
RecordSource - sometimes a table but usually a query. I think is
notion is screwing up your approach to the problem.

Try it like this:

1.) Test the existence of the record with a Dlookup function.

2.) If it doesn't exist create it *before* you attempt to open the
other form

This ought to work...
============
Private Sub butGoToHistory_Click()
Dim stDocName As String

stDocName = "frmRevisionHistory"

'Lets see if the record exists
If IsNull(DLookup("FK_Fees", "tblRevisionHistory", _
"FK_Fees=" & Me.PK_Fees)) Then
'It doesn't so insert the record
sSQL = "INSERT INTO tblRevisionHistory (FK_Fees) " _
& "VALUES (" & Me.PK_Fees & ");"
CurrentDb.Execute sSQL, dbFailOnError
Else
'Record exists just open the form with WHERE Condition
DoCmd.OpenForm stDocName, , , "FK_Fees=" & Me.PK_Fees
End If

End Sub
=======================

- Jim
On 13 Jul 2004 15:33:39 -0700, am*******@verizon.net (amywolfie)
wrote:

I've been trying to do something for about 3 days – I get close, but
not 100%.
I am trying to:

Open frmRevisionHistory from a button on frmFeeInput.

If there is NO RELATED RECORD, then I need to create one in
frmRevisionHistory, and pass the PK (PK_Fees) on frmFeeInput to
FK_Fees on the 2nd form.

If there IS (OR ARE) RELATED RECORDS, then go to them on
frmRevisionHistory.

Right now, I am getting error message 2046 "The command or action
RecordsGoToNew isn't available now."

Here's the code:

Private Sub butGoToHistory_Click()

On Error GoTo Error_Handler:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmRevisionHistory"

stLinkCriteria = "[FK_Fees]=" & Me![PK_Fees]
DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria

If RecordsetCount = 0 Then
MsgBox "No history records. Will create one now", vbOKOnly,
"Create Record"
DoCmd.RunCommand acCmdRecordsGoToNew
'DoCmd.GoToRecord , , acNewRec 'Access Wizard code
Dim sSQL As String
DoCmd.SetWarnings False

sSQL = "INSERT INTO tblRevisionHistory ( FK_Fees)" & _
" SELECT Forms!frmFeeInput!PK_Fees AS intCode" & _
" FROM tblFees;"

DoCmd.RunSQL (sSQL)

DoCmd.SetWarnings True
frmRevisionHistory.Requery

Else
stLinkCriteria = "[FK_Fees]=" & Me![PK_Fees]
DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria
MsgBox "RELATED RECORD!"
frmRevisionHistory.Requery

End If

Exit Sub

Error_Handler:
MsgBox Err.Description & " " & Err.Number

[Jim Allensworth]

Taking another look at this; the Else isn't needed but the open form
is. So...

Private Sub butGoToHistory_Click()
Dim stDocName As String

stDocName = "frmRevisionHistory"

'Lets see if the record exists
If IsNull(DLookup("FK_Fees", "tblRevisionHistory", _
"FK_Fees=" & Me.PK_Fees)) Then
'It doesn't so insert the record
sSQL = "INSERT INTO tblRevisionHistory (FK_Fees) " _
& "VALUES (" & Me.PK_Fees & ");"
CurrentDb.Execute sSQL, dbFailOnError
End If
'Now open the form with WHERE Condition
DoCmd.OpenForm stDocName, , , "FK_Fees=" & Me.PK_Fees

End Sub

- Jim

On Tue, 13 Jul 2004 23:56:16 GMT, "Jim Allensworth"
<ji****@datacentricsolutions.com> wrote:

Amy, I get the feeling that there are some conceptual issues here.

First forms don't hold records. They are normally *bound* to a
RecordSource - sometimes a table but usually a query. I think is
notion is screwing up your approach to the problem.

Try it like this:

1.) Test the existence of the record with a Dlookup function.

2.) If it doesn't exist create it *before* you attempt to open the
other form

This ought to work...
============
Private Sub butGoToHistory_Click()
Dim stDocName As String

stDocName = "frmRevisionHistory"

'Lets see if the record exists
If IsNull(DLookup("FK_Fees", "tblRevisionHistory", _
"FK_Fees=" & Me.PK_Fees)) Then
'It doesn't so insert the record
sSQL = "INSERT INTO tblRevisionHistory (FK_Fees) " _
& "VALUES (" & Me.PK_Fees & ");"
CurrentDb.Execute sSQL, dbFailOnError
Else
'Record exists just open the form with WHERE Condition
DoCmd.OpenForm stDocName, , , "FK_Fees=" & Me.PK_Fees
End If

End Sub
=======================

- Jim
On 13 Jul 2004 15:33:39 -0700, am*******@verizon.net (amywolfie)
wrote:

I've been trying to do something for about 3 days – I get close, but
not 100%.
I am trying to:

Open frmRevisionHistory from a button on frmFeeInput.

If there is NO RELATED RECORD, then I need to create one in
frmRevisionHistory, and pass the PK (PK_Fees) on frmFeeInput to
FK_Fees on the 2nd form.

If there IS (OR ARE) RELATED RECORDS, then go to them on
frmRevisionHistory.

Right now, I am getting error message 2046 "The command or action
RecordsGoToNew isn't available now."

Here's the code:

Private Sub butGoToHistory_Click()

On Error GoTo Error_Handler:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmRevisionHistory"

stLinkCriteria = "[FK_Fees]=" & Me![PK_Fees]
DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria

If RecordsetCount = 0 Then
MsgBox "No history records. Will create one now", vbOKOnly,
"Create Record"
DoCmd.RunCommand acCmdRecordsGoToNew
'DoCmd.GoToRecord , , acNewRec 'Access Wizard code
Dim sSQL As String
DoCmd.SetWarnings False

sSQL = "INSERT INTO tblRevisionHistory ( FK_Fees)" & _
" SELECT Forms!frmFeeInput!PK_Fees AS intCode" & _
" FROM tblFees;"

DoCmd.RunSQL (sSQL)

DoCmd.SetWarnings True
frmRevisionHistory.Requery

Else
stLinkCriteria = "[FK_Fees]=" & Me![PK_Fees]
DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria
MsgBox "RELATED RECORD!"
frmRevisionHistory.Requery

End If

Exit Sub

Error_Handler:
MsgBox Err.Description & " " & Err.Number

[amywolfie]

Jim:

You rule! This worked like a charm! Thank you very much. It's hard
to move from the mindset required for FileMaker to Access...

Thanks again,
amy
==

Ji****@datacentricsolutions.com (Jim Allensworth) wrote in message news:<40***************@netnews.comcast.net>...

Taking another look at this; the Else isn't needed but the open form
is. So...

Private Sub butGoToHistory_Click()
Dim stDocName As String

stDocName = "frmRevisionHistory"

'Lets see if the record exists
If IsNull(DLookup("FK_Fees", "tblRevisionHistory", _
"FK_Fees=" & Me.PK_Fees)) Then
'It doesn't so insert the record
sSQL = "INSERT INTO tblRevisionHistory (FK_Fees) " _
& "VALUES (" & Me.PK_Fees & ");"
CurrentDb.Execute sSQL, dbFailOnError
End If
'Now open the form with WHERE Condition
DoCmd.OpenForm stDocName, , , "FK_Fees=" & Me.PK_Fees

End Sub

- Jim

On Tue, 13 Jul 2004 23:56:16 GMT, "Jim Allensworth"
<ji****@datacentricsolutions.com> wrote:

Amy, I get the feeling that there are some conceptual issues here.

First forms don't hold records. They are normally *bound* to a
RecordSource - sometimes a table but usually a query. I think is
notion is screwing up your approach to the problem.

Try it like this:

1.) Test the existence of the record with a Dlookup function.

2.) If it doesn't exist create it *before* you attempt to open the
other form

This ought to work...
============
Private Sub butGoToHistory_Click()
Dim stDocName As String

stDocName = "frmRevisionHistory"

'Lets see if the record exists
If IsNull(DLookup("FK_Fees", "tblRevisionHistory", _
"FK_Fees=" & Me.PK_Fees)) Then
'It doesn't so insert the record
sSQL = "INSERT INTO tblRevisionHistory (FK_Fees) " _
& "VALUES (" & Me.PK_Fees & ");"
CurrentDb.Execute sSQL, dbFailOnError
Else
'Record exists just open the form with WHERE Condition
DoCmd.OpenForm stDocName, , , "FK_Fees=" & Me.PK_Fees
End If

End Sub
=======================

- Jim
On 13 Jul 2004 15:33:39 -0700, am*******@verizon.net (amywolfie)
wrote:

I've been trying to do something for about 3 days ? I get close, but
not 100%.
I am trying to:

Open frmRevisionHistory from a button on frmFeeInput.

If there is NO RELATED RECORD, then I need to create one in
frmRevisionHistory, and pass the PK (PK_Fees) on frmFeeInput to
FK_Fees on the 2nd form.

If there IS (OR ARE) RELATED RECORDS, then go to them on
frmRevisionHistory.

Right now, I am getting error message 2046 "The command or action
RecordsGoToNew isn't available now."

Here's the code:

Private Sub butGoToHistory_Click()

On Error GoTo Error_Handler:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmRevisionHistory"

stLinkCriteria = "[FK_Fees]=" & Me![PK_Fees]
DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria

If RecordsetCount = 0 Then
MsgBox "No history records. Will create one now", vbOKOnly,
"Create Record"
DoCmd.RunCommand acCmdRecordsGoToNew
'DoCmd.GoToRecord , , acNewRec 'Access Wizard code
Dim sSQL As String
DoCmd.SetWarnings False

sSQL = "INSERT INTO tblRevisionHistory ( FK_Fees)" & _
" SELECT Forms!frmFeeInput!PK_Fees AS intCode" & _
" FROM tblFees;"

DoCmd.RunSQL (sSQL)

DoCmd.SetWarnings True
frmRevisionHistory.Requery

Else
stLinkCriteria = "[FK_Fees]=" & Me![PK_Fees]
DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria
MsgBox "RELATED RECORD!"
frmRevisionHistory.Requery

End If

Exit Sub

Error_Handler:
MsgBox Err.Description & " " & Err.Number

[Jim Allensworth]

On 14 Jul 2004 09:27:51 -0700, am*******@verizon.net (amywolfie)
wrote:

Jim:

You rule! This worked like a charm! Thank you very much. It's hard
to move from the mindset required for FileMaker to Access...

Thanks again,
amy
==

Your'e welcome. And right it's a mindset thing. I can only imagine how
it might be for me working in the other direction.

- Jim