condition based on subform

  • Thread starter Thread starter Alan
  • Start date Start date
A

Alan

Hi there,

I'm trying to do something here and think (well I know) it's not working.

Essentially I want some code to execute based on whether there are any
records present on a subform. If there are no records present, code doesn't
run and things continue on normally. If there are records present in the
subform, then execute the code.

Here's the code I wrote:

Private Sub Form_Open(Cancel As Integer)

If Me![sbfmMFGPkgDetails].NewRecord Then

If Me![sbfmMFGPkgDetails]![HSTAT] = "I" Then
Me.Inv.SetFocus
Me.Inv = "Y"
Me.quoteID.SetFocus
Else

If Me![sbfmMFGPkgDetails]![HSTAT] = " " Then
Me.Inv.SetFocus
Me.Inv = "N"
Me.quoteID.SetFocus
Else

End If


End Sub

I'm guessing my problem is related to the top level IF statement..

Any help would be greatly appreciated.

Alan
 
Alan said:
Hi there,

I'm trying to do something here and think (well I know) it's not working.

Essentially I want some code to execute based on whether there are any
records present on a subform. If there are no records present, code
doesn't
run and things continue on normally. If there are records present in the
subform, then execute the code.

Here's the code I wrote:

Private Sub Form_Open(Cancel As Integer)

If Me![sbfmMFGPkgDetails].NewRecord Then

If Me![sbfmMFGPkgDetails]![HSTAT] = "I" Then
Me.Inv.SetFocus
Me.Inv = "Y"
Me.quoteID.SetFocus
Else

If Me![sbfmMFGPkgDetails]![HSTAT] = " " Then
Me.Inv.SetFocus
Me.Inv = "N"
Me.quoteID.SetFocus
Else

End If


End Sub

I'm guessing my problem is related to the top level IF statement..

Any help would be greatly appreciated.

Alan
Click to expand...

You need to reference the form _hosted_ by sbfmMFGPkgDetails:

If Me![sbfmMFGPkgDetails].Form.NewRecord Then

If Me![sbfmMFGPkgDetails].Form![HSTAT] = "I" Then

If Me![sbfmMFGPkgDetails].Form![HSTAT] = " " Then
 
Alan said:
Essentially I want some code to execute based on whether there are any
records present on a subform. If there are no records present, code doesn't
run and things continue on normally. If there are records present in the
subform, then execute the code.

Here's the code I wrote:

Private Sub Form_Open(Cancel As Integer)

If Me![sbfmMFGPkgDetails].NewRecord Then

If Me![sbfmMFGPkgDetails]![HSTAT] = "I" Then
Me.Inv.SetFocus
Me.Inv = "Y"
Me.quoteID.SetFocus
Else

If Me![sbfmMFGPkgDetails]![HSTAT] = " " Then
Me.Inv.SetFocus
Me.Inv = "N"
Me.quoteID.SetFocus
Else

End If
End Sub
Click to expand...


The form's Open event is too soon to do this kind of thing.
Try the Load event.

Note that is is extremely unusual for a field to contain
only a space character so I question the last If statement.

I also have no idea why you feel the need to use so many
SetFocus lines. Certainly the first SetFocus in each If
block does not accomplish anything useful. I would expect
that the quoteID text box would be first in the form's
TabOrder, which would make the second SetFocus redundant.
 
The two IF statements were written at different times when I was testing
hence the two SetFocus commands but you're right, I don't need that many.
I'll be cleaning up the nested IF statements up into something cleaner.

Thanks for the tips.

Marshall Barton said:
Alan said:
Essentially I want some code to execute based on whether there are any
records present on a subform. If there are no records present, code doesn't
run and things continue on normally. If there are records present in the
subform, then execute the code.

Here's the code I wrote:

Private Sub Form_Open(Cancel As Integer)

If Me![sbfmMFGPkgDetails].NewRecord Then

If Me![sbfmMFGPkgDetails]![HSTAT] = "I" Then
Me.Inv.SetFocus
Me.Inv = "Y"
Me.quoteID.SetFocus
Else

If Me![sbfmMFGPkgDetails]![HSTAT] = " " Then
Me.Inv.SetFocus
Me.Inv = "N"
Me.quoteID.SetFocus
Else

End If
End Sub
Click to expand...


The form's Open event is too soon to do this kind of thing.
Try the Load event.

Note that is is extremely unusual for a field to contain
only a space character so I question the last If statement.

I also have no idea why you feel the need to use so many
SetFocus lines. Certainly the first SetFocus in each If
block does not accomplish anything useful. I would expect
that the quoteID text box would be first in the form's
TabOrder, which would make the second SetFocus redundant.
Click to expand...
 
Oh yea, as for the space character comment, the source data comes from an
AS400 and for some reason blank fields are recognized as a space hence the
goofy second IF statement. It took me a few tries before I figured that out
as normal statements for empty fields weren't working. Stuck a space in there
and everything worked.

Alan

Marshall Barton said:
Alan said:
Essentially I want some code to execute based on whether there are any
records present on a subform. If there are no records present, code doesn't
run and things continue on normally. If there are records present in the
subform, then execute the code.

Here's the code I wrote:

Private Sub Form_Open(Cancel As Integer)

If Me![sbfmMFGPkgDetails].NewRecord Then

If Me![sbfmMFGPkgDetails]![HSTAT] = "I" Then
Me.Inv.SetFocus
Me.Inv = "Y"
Me.quoteID.SetFocus
Else

If Me![sbfmMFGPkgDetails]![HSTAT] = " " Then
Me.Inv.SetFocus
Me.Inv = "N"
Me.quoteID.SetFocus
Else

End If
End Sub
Click to expand...


The form's Open event is too soon to do this kind of thing.
Try the Load event.

Note that is is extremely unusual for a field to contain
only a space character so I question the last If statement.

I also have no idea why you feel the need to use so many
SetFocus lines. Certainly the first SetFocus in each If
block does not accomplish anything useful. I would expect
that the quoteID text box would be first in the form's
TabOrder, which would make the second SetFocus redundant.
Click to expand...
 
Back
Top