Coding No Related Records?

[amywolfie]

Is there a simple way to code:

If (no related records)

in VBA?

I would be going from frmMain to frm2, where frm2 may or may not have
a related record.

I do have a PK_ID in frmMain and a FK_ID in frm2.

Thanks!
amy
=---

[Jim Allensworth]

Use the RecordsetClone of the form that you want to check. You can use
its Find method to look for the record. Along the lines of...

Dim frm As Form

DoCmd.OpenForm "frm2" 'Let's make sure it is open.

Set frm = Forms("frm2")
RecordsetClone.FindFirst "FK_ID=" & Me.PK_ID
If frm.RecordsetClone.NoMatch Then
MsgBox "Record Not Found!"
Else
frm.Bookmark = frm.RecordsetClone.Bookmark
frm.SetFocus
End If
Set frm = Nothing

- Jim
On 2 Jul 2004 11:19:58 -0700, am*******@verizon.net (amywolfie) wrote:

Is there a simple way to code:

If (no related records)

in VBA?

I would be going from frmMain to frm2, where frm2 may or may not have
a related record.

I do have a PK_ID in frmMain and a FK_ID in frm2.

Thanks!
amy
=---

[rkc]

"amywolfie" <am*******@verizon.net> wrote in message
news:33**************************@posting.google.c om...

Is there a simple way to code:

If (no related records)

in VBA?

I would be going from frmMain to frm2, where frm2 may or may not have
a related record.

I do have a PK_ID in frmMain and a FK_ID in frm2.

If frm2 is loading records based on the current record of frmMain
then you can just check the recordcount property of frm2.RecordsetClone.
If it returns 0 then there are no matching records.

If frm2 is not based on frmMain then you'll need to do a FindFirst on the
frm2.RecordsetClone for a record that matches FK_ID to PK_ID.

[amywolfie]

Thanks very much for your help!

amy
===
"Jim Allensworth" <ji****@Notdatacentricsolutions.com> wrote in message news:<40**************@news.west.earthlink.net>...

Use the RecordsetClone of the form that you want to check. You can use
its Find method to look for the record. Along the lines of...

Dim frm As Form

DoCmd.OpenForm "frm2" 'Let's make sure it is open.

Set frm = Forms("frm2")
RecordsetClone.FindFirst "FK_ID=" & Me.PK_ID
If frm.RecordsetClone.NoMatch Then
MsgBox "Record Not Found!"
Else
frm.Bookmark = frm.RecordsetClone.Bookmark
frm.SetFocus
End If
Set frm = Nothing

- Jim
On 2 Jul 2004 11:19:58 -0700, am*******@verizon.net (amywolfie) wrote:

Is there a simple way to code:

If (no related records)

in VBA?

I would be going from frmMain to frm2, where frm2 may or may not have
a related record.

I do have a PK_ID in frmMain and a FK_ID in frm2.

Thanks!
amy
=---

[amywolfie]

Hi:

Can't quite get this to work -- am getting Error Msg 3070 -- The Jet
Engine does not recognize FK_Fees as valid.
That is in fact the field name in tblRevisionHistory:

Private Sub BeginCode()

On Error GoTo Error_Handler:

Dim frm As Form

DoCmd.OpenForm "frmRevisionHistory" 'Let's make sure it is open.

Set frm = Forms("frmRevisionHistory")
RecordsetClone.FindFirst "[FK_Fees] = " & Me![PK_Fees]
If frm.RecordsetClone.NoMatch Then

MsgBox "Sorry, no History record exists."

Else

frm.Bookmark = frm.RecordsetClone.Bookmark
frm.SetFocus

End If

Set frm = Nothing
Exit Sub

Error_Handler:
MsgBox Err.Description & " " & Err.Number & "NO RELATED REC!"

End Sub

Thanks!!!
===
"Jim Allensworth" <ji****@Notdatacentricsolutions.com> wrote in message news:<40**************@news.west.earthlink.net>...

Use the RecordsetClone of the form that you want to check. You can use
its Find method to look for the record. Along the lines of...

Dim frm As Form

DoCmd.OpenForm "frm2" 'Let's make sure it is open.

Set frm = Forms("frm2")
RecordsetClone.FindFirst "FK_ID=" & Me.PK_ID
If frm.RecordsetClone.NoMatch Then
MsgBox "Record Not Found!"
Else
frm.Bookmark = frm.RecordsetClone.Bookmark
frm.SetFocus
End If
Set frm = Nothing

- Jim
On 2 Jul 2004 11:19:58 -0700, am*******@verizon.net (amywolfie) wrote:

Is there a simple way to code:

If (no related records)

in VBA?

I would be going from frmMain to frm2, where frm2 may or may not have
a related record.

I do have a PK_ID in frmMain and a FK_ID in frm2.

Thanks!
amy
=---

[Jim Allensworth]

Hmm, make sure that FK_Fees is a part of the *RecordSource* of both
forms. (It can be in the table but not a part of the RecordSource)

- Jim

On 6 Jul 2004 09:35:05 -0700, am*******@verizon.net (amywolfie) wrote:

Hi:

Can't quite get this to work -- am getting Error Msg 3070 -- The Jet
Engine does not recognize FK_Fees as valid.
That is in fact the field name in tblRevisionHistory:

Private Sub BeginCode()

On Error GoTo Error_Handler:

Dim frm As Form

DoCmd.OpenForm "frmRevisionHistory" 'Let's make sure it is open.

Set frm = Forms("frmRevisionHistory")
RecordsetClone.FindFirst "[FK_Fees] = " & Me![PK_Fees]
If frm.RecordsetClone.NoMatch Then

MsgBox "Sorry, no History record exists."

Else

frm.Bookmark = frm.RecordsetClone.Bookmark
frm.SetFocus

End If

Set frm = Nothing
Exit Sub

Error_Handler:
MsgBox Err.Description & " " & Err.Number & "NO RELATED REC!"

End Sub

Thanks!!!
===
"Jim Allensworth" <ji****@Notdatacentricsolutions.com> wrote in message news:<40**************@news.west.earthlink.net>...

Use the RecordsetClone of the form that you want to check. You can use
its Find method to look for the record. Along the lines of...

Dim frm As Form

DoCmd.OpenForm "frm2" 'Let's make sure it is open.

Set frm = Forms("frm2")
RecordsetClone.FindFirst "FK_ID=" & Me.PK_ID
If frm.RecordsetClone.NoMatch Then
MsgBox "Record Not Found!"
Else
frm.Bookmark = frm.RecordsetClone.Bookmark
frm.SetFocus
End If
Set frm = Nothing

- Jim
On 2 Jul 2004 11:19:58 -0700, am*******@verizon.net (amywolfie) wrote:

>Is there a simple way to code:
>
>If (no related records)
>
>in VBA?
>
>I would be going from frmMain to frm2, where frm2 may or may not have
>a related record.
>
>I do have a PK_ID in frmMain and a FK_ID in frm2.
>
>Thanks!
>amy
>=---

[amywolfie]

Jim:

Could you elaborate on this a little more? How can I insure that a
field is part of the RecordSource?

Thanks!

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

Hmm, make sure that FK_Fees is a part of the *RecordSource* of both
forms. (It can be in the table but not a part of the RecordSource)

- Jim

On 6 Jul 2004 09:35:05 -0700, am*******@verizon.net (amywolfie) wrote:

Hi:

Can't quite get this to work -- am getting Error Msg 3070 -- The Jet
Engine does not recognize FK_Fees as valid.
That is in fact the field name in tblRevisionHistory:

Private Sub BeginCode()

On Error GoTo Error_Handler:

Dim frm As Form

DoCmd.OpenForm "frmRevisionHistory" 'Let's make sure it is open.

Set frm = Forms("frmRevisionHistory")
RecordsetClone.FindFirst "[FK_Fees] = " & Me![PK_Fees]
If frm.RecordsetClone.NoMatch Then

MsgBox "Sorry, no History record exists."

Else

frm.Bookmark = frm.RecordsetClone.Bookmark
frm.SetFocus

End If

Set frm = Nothing
Exit Sub

Error_Handler:
MsgBox Err.Description & " " & Err.Number & "NO RELATED REC!"

End Sub

Thanks!!!
===
"Jim Allensworth" <ji****@Notdatacentricsolutions.com> wrote in message news:<40**************@news.west.earthlink.net>...

Use the RecordsetClone of the form that you want to check. You can use
its Find method to look for the record. Along the lines of...

Dim frm As Form

DoCmd.OpenForm "frm2" 'Let's make sure it is open.

Set frm = Forms("frm2")
RecordsetClone.FindFirst "FK_ID=" & Me.PK_ID
If frm.RecordsetClone.NoMatch Then
MsgBox "Record Not Found!"
Else
frm.Bookmark = frm.RecordsetClone.Bookmark
frm.SetFocus
End If
Set frm = Nothing

- Jim
On 2 Jul 2004 11:19:58 -0700, am*******@verizon.net (amywolfie) wrote:

>Is there a simple way to code:
>
>If (no related records)
>
>in VBA?
>
>I would be going from frmMain to frm2, where frm2 may or may not have
>a related record.
>
>I do have a PK_ID in frmMain and a FK_ID in frm2.
>
>Thanks!
>amy
>=---

[Jim Allensworth]

You can look at the RecordSource of the form.

In design view of the form open the properties window for the form and
select the Data tab. Select Record Source and click the ellipsis
button to the right [...]. If a message box appears that asks if you
want invoke the query builder on the table, then you have a table as
the RecordSource. If that is the case then all of the fields that are
in the table are in the RecordSource. Otherwise the QBE window will
open and show which fields are selected from the SQL expression.
Examine that to see what is in the RecordSource - and other
information like ordering and selection criteria.

- Jim

On 7 Jul 2004 09:36:07 -0700, am*******@verizon.net (amywolfie) wrote:

Jim:

Could you elaborate on this a little more? How can I insure that a
field is part of the RecordSource?

Thanks!

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

Hmm, make sure that FK_Fees is a part of the *RecordSource* of both
forms. (It can be in the table but not a part of the RecordSource)

- Jim

On 6 Jul 2004 09:35:05 -0700, am*******@verizon.net (amywolfie) wrote:

>Hi:
>
>Can't quite get this to work -- am getting Error Msg 3070 -- The Jet
>Engine does not recognize FK_Fees as valid.
>That is in fact the field name in tblRevisionHistory:
>
>Private Sub BeginCode()
>
>On Error GoTo Error_Handler:
>
>Dim frm As Form
>
> DoCmd.OpenForm "frmRevisionHistory" 'Let's make sure it is open.
>
> Set frm = Forms("frmRevisionHistory")
> RecordsetClone.FindFirst "[FK_Fees] = " & Me![PK_Fees]
>
>
> If frm.RecordsetClone.NoMatch Then
>
> MsgBox "Sorry, no History record exists."
>
> Else
>
> frm.Bookmark = frm.RecordsetClone.Bookmark
> frm.SetFocus
>
> End If
>
> Set frm = Nothing
>
>
>Exit Sub
>
>Error_Handler:
>MsgBox Err.Description & " " & Err.Number & "NO RELATED REC!"
>
>
>
>End Sub
>
>Thanks!!!
>===
>
>
>"Jim Allensworth" <ji****@Notdatacentricsolutions.com> wrote in message news:<40**************@news.west.earthlink.net>...
>> Use the RecordsetClone of the form that you want to check. You can use
>> its Find method to look for the record. Along the lines of...
>>
>> Dim frm As Form
>>
>> DoCmd.OpenForm "frm2" 'Let's make sure it is open.
>>
>> Set frm = Forms("frm2")
>> RecordsetClone.FindFirst "FK_ID=" & Me.PK_ID
>> If frm.RecordsetClone.NoMatch Then
>> MsgBox "Record Not Found!"
>> Else
>> frm.Bookmark = frm.RecordsetClone.Bookmark
>> frm.SetFocus
>> End If
>> Set frm = Nothing
>>
>> - Jim
>>
>>
>> On 2 Jul 2004 11:19:58 -0700, am*******@verizon.net (amywolfie) wrote:
>>
>> >Is there a simple way to code:
>> >
>> >If (no related records)
>> >
>> >in VBA?
>> >
>> >I would be going from frmMain to frm2, where frm2 may or may not have
>> >a related record.
>> >
>> >I do have a PK_ID in frmMain and a FK_ID in frm2.
>> >
>> >Thanks!
>> >amy
>> >=---