No Records Then

D

DS

I'm trying to run this code on the main form load event.
If the Subform has no records then the Listbox on the subform is empty
otherwisw populate the listbox with the appropriate records. It works
fine whenever there are records but when I open the main form with a new
record all of the records show in the listbox. The DCount always seems
to show how many records are there, disregarding my criteria. Any help
appreciated.
Thanks
DS




Private Sub Form_Load()
Me.Text307 = DCount("SalesID", "SalesDetails", "SalesID =
Forms!Buttons!SalesID")

If Me.Text307 = Null Then
End
Else
With Forms!Buttons.PrepSlipSub.Form!List52
.RowSource = "SELECT
LineID,ItemID,SalesID,Expr8,Expr7,Expr6,Quantity,Expr5,Expr4,ItemType" _
& " FROM PrepSlipQ " _
& " WHERE SalesID = " & Forms!Buttons.PrepSlipSub.Form!SalesID
& "" _
& " ORDER BY LineID,ItemType ASC;"
Forms!Buttons.PrepSlipSub.Form!List52.Requery
End With
End If
End Sub
 
D

DS

DS said:
I'm trying to run this code on the main form load event.
If the Subform has no records then the Listbox on the subform is empty
otherwisw populate the listbox with the appropriate records. It works
fine whenever there are records but when I open the main form with a new
record all of the records show in the listbox. The DCount always seems
to show how many records are there, disregarding my criteria. Any help
appreciated.
Thanks
DS




Private Sub Form_Load()
Me.Text307 = DCount("SalesID", "SalesDetails", "SalesID =
Forms!Buttons!SalesID")

If Me.Text307 = Null Then
End
Else
With Forms!Buttons.PrepSlipSub.Form!List52
.RowSource = "SELECT
LineID,ItemID,SalesID,Expr8,Expr7,Expr6,Quantity,Expr5,Expr4,ItemType" _
& " FROM PrepSlipQ " _
& " WHERE SalesID = " & Forms!Buttons.PrepSlipSub.Form!SalesID &
"" _
& " ORDER BY LineID,ItemType ASC;"
Forms!Buttons.PrepSlipSub.Form!List52.Requery
End With
End If
End Sub
Click to expand...

I did this instead and it works fine!
DS

Private Sub Form_Load()
If Me![PrepSlipSub].Form.RecordsetClone.RecordCount > 0 Then
With Forms!Buttons.PrepSlipSub.Form!List52
.RowSource = "SELECT
LineID,ItemID,SalesID,Expr8,Expr7,Expr6,Quantity,Expr5,Expr4,ItemType" _
& " FROM PrepSlipQ " _
& " WHERE SalesID = " & Forms!Buttons.PrepSlipSub.Form!SalesID
& "" _
& " ORDER BY LineID,ItemType ASC;"
Forms!Buttons.PrepSlipSub.Form!List52.Requery
End With
Else
Forms!Buttons.PrepSlipSub.Form!List52 = Null
End If

End Sub
 
K

Klatuu

DS said:
I'm trying to run this code on the main form load event.
If the Subform has no records then the Listbox on the subform is empty
otherwisw populate the listbox with the appropriate records. It works
fine whenever there are records but when I open the main form with a new
record all of the records show in the listbox. The DCount always seems
to show how many records are there, disregarding my criteria. Any help
appreciated.
Thanks
DS




Private Sub Form_Load()
Me.Text307 = DCount("SalesID", "SalesDetails", "SalesID =
Forms!Buttons!SalesID")

If Me.Text307 = Null Then
End
Else
With Forms!Buttons.PrepSlipSub.Form!List52
.RowSource = "SELECT
LineID,ItemID,SalesID,Expr8,Expr7,Expr6,Quantity,Expr5,Expr4,ItemType" _
& " FROM PrepSlipQ " _
& " WHERE SalesID = " & Forms!Buttons.PrepSlipSub.Form!SalesID
& "" _
& " ORDER BY LineID,ItemType ASC;"
Forms!Buttons.PrepSlipSub.Form!List52.Requery
End With
End If
End Sub
Click to expand...
 
K

Klatuu

First problem is a syntax issue:
If Me.Text307 = Null Then
Should be:
If IsNull(Me.Text307) Then

I am not a big fan of the End statement. The problem with it is that it
clears all module level variables and any local static variables. This could
cause an unexpected problem. Here is how I would code it:
If Not IsNull(Me.Text307) Then
With Forms!Buttons.PrepSlipSub.Form!List52
.RowSource = "SELECT
LineID,ItemID,SalesID,Expr8,Expr7,Expr6,Quantity,Expr5,Expr4,ItemType" _
& " FROM PrepSlipQ " _
& " WHERE SalesID = " & Forms!Buttons.PrepSlipSub.Form!SalesID
& "" _
& " ORDER BY LineID,ItemType ASC;"
Forms!Buttons.PrepSlipSub.Form!List52.Requery
End With
End If
 
D

DS

Klatuu said:
First problem is a syntax issue:
If Me.Text307 = Null Then
Should be:
If IsNull(Me.Text307) Then

I am not a big fan of the End statement. The problem with it is that it
clears all module level variables and any local static variables. This could
cause an unexpected problem. Here is how I would code it:
If Not IsNull(Me.Text307) Then
With Forms!Buttons.PrepSlipSub.Form!List52
.RowSource = "SELECT
LineID,ItemID,SalesID,Expr8,Expr7,Expr6,Quantity,Expr5,Expr4,ItemType" _
& " FROM PrepSlipQ " _
& " WHERE SalesID = " & Forms!Buttons.PrepSlipSub.Form!SalesID
& "" _
& " ORDER BY LineID,ItemType ASC;"
Forms!Buttons.PrepSlipSub.Form!List52.Requery
End With
End If

:
Click to expand...
Thank you, to the rescue once more!
Sincerely
DS
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Event Timing Problem? 0
Loop Question 2
Looping Code Question 0
Finding Records 7
Add new records to table 3
VBA code returns no records hwenKey ID is added 14
Sum of SQL Statement 1
Count subform records where txtCount = 0 1

Top