Loop through subform records - Help required

D

Dave

I have a datasheet view subform on a tab control.
I am trying to show a warning lablel on the main form if the field
Currcosttotal on the subform = zero (not null)
I have the following code, but this only works when the first record in the
subform is zero, not if it a latter record.
Can any one please advise how I loop through all records on the subform?

Private Sub Form_Current()
If Forms!frmprojectstabbed!qrybomsubform!Currcosttotal = 0 Then
Me.test.Visible = True
Else
Me.test.Visible = False
End If
End Sub


Cheers


Dave
 
R

Rick Brandt

Dave said:
I have a datasheet view subform on a tab control.
I am trying to show a warning lablel on the main form if the field
Currcosttotal on the subform = zero (not null)
I have the following code, but this only works when the first record
in the subform is zero, not if it a latter record.
Can any one please advise how I loop through all records on the
subform?
Private Sub Form_Current()
If Forms!frmprojectstabbed!qrybomsubform!Currcosttotal = 0 Then
Me.test.Visible = True
Else
Me.test.Visible = False
End If
End Sub
Click to expand...

If there is more than one record where that field is equal to zero do you
need to know how many (and which ones) or do you just need to know when
there is at least one? For the latter...

(air code)

With Me!qrybomsubform.Form.RecordsetClone
.FindFirst "Currcosttotal = 0"
If Not .NoMatch Then
Me.test.Visible = True
Else
Me.test.Visible = False
End If
End With
 
D

Dave

Thanks Rick

The later will suffice,
However, when trying your code I get a compile error Runtime Error 3070 The
MS Jet database doesn't recognise "Currcosttotal" as valid field name?

Dave
 
A

Allen Browne

So you want the message to show if any one of the related records in the
subform contains a zero in the field Currcostotal?

It might be easist to do that by checking in the subform's table directly,
instead of writing code to loop through all the records of the
RecordsetClone of the form in the subform control.

The Control Source of the warning text box will be something like this:
=IIf(IsNull(DLookup("MySubID", "MySubformTable",
"(MySubFK = " & Nz([MainID],0) &
") AND ([Currcostotal] = 0)")), Null, "Warning")
where:
- MySubID is the name of the primary key of the subform's table;
- MySubformTable is the name of the subform's table;
- MySubFK is the name of the foreign key in the subform's table;
- MainID is the name of the primary key on the main form.

DLookup() returns Null if there is no match.
IsNull() test for that.
IIf() shows the message only if an ID was returned.
The Nz() in the middle avoids the #Error when the main form is at a new
record.
 
D

Dave

Thanks Alan
I'll give that a go.

Dave

Allen Browne said:
So you want the message to show if any one of the related records in the
subform contains a zero in the field Currcostotal?

It might be easist to do that by checking in the subform's table directly,
instead of writing code to loop through all the records of the
RecordsetClone of the form in the subform control.

The Control Source of the warning text box will be something like this:
=IIf(IsNull(DLookup("MySubID", "MySubformTable",
"(MySubFK = " & Nz([MainID],0) &
") AND ([Currcostotal] = 0)")), Null, "Warning")
where:
- MySubID is the name of the primary key of the subform's table;
- MySubformTable is the name of the subform's table;
- MySubFK is the name of the foreign key in the subform's table;
- MainID is the name of the primary key on the main form.

DLookup() returns Null if there is no match.
IsNull() test for that.
IIf() shows the message only if an ID was returned.
The Nz() in the middle avoids the #Error when the main form is at a new
record.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dave said:
I have a datasheet view subform on a tab control.
I am trying to show a warning lablel on the main form if the field
Currcosttotal on the subform = zero (not null)
I have the following code, but this only works when the first record in
the subform is zero, not if it a latter record.
Can any one please advise how I loop through all records on the subform?

Private Sub Form_Current()
If Forms!frmprojectstabbed!qrybomsubform!Currcosttotal = 0 Then
Me.test.Visible = True
Else
Me.test.Visible = False
End If
End Sub
Click to expand...
Click to expand...
 
D

Dave

Allen

Just a thought - the subform is based on a query - will the Dlookup work the
same?


Allen Browne said:
So you want the message to show if any one of the related records in the
subform contains a zero in the field Currcostotal?

It might be easist to do that by checking in the subform's table directly,
instead of writing code to loop through all the records of the
RecordsetClone of the form in the subform control.

The Control Source of the warning text box will be something like this:
=IIf(IsNull(DLookup("MySubID", "MySubformTable",
"(MySubFK = " & Nz([MainID],0) &
") AND ([Currcostotal] = 0)")), Null, "Warning")
where:
- MySubID is the name of the primary key of the subform's table;
- MySubformTable is the name of the subform's table;
- MySubFK is the name of the foreign key in the subform's table;
- MainID is the name of the primary key on the main form.

DLookup() returns Null if there is no match.
IsNull() test for that.
IIf() shows the message only if an ID was returned.
The Nz() in the middle avoids the #Error when the main form is at a new
record.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dave said:
I have a datasheet view subform on a tab control.
I am trying to show a warning lablel on the main form if the field
Currcosttotal on the subform = zero (not null)
I have the following code, but this only works when the first record in
the subform is zero, not if it a latter record.
Can any one please advise how I loop through all records on the subform?

Private Sub Form_Current()
If Forms!frmprojectstabbed!qrybomsubform!Currcosttotal = 0 Then
Me.test.Visible = True
Else
Me.test.Visible = False
End If
End Sub
Click to expand...
Click to expand...
 
A

Allen Browne

Yes, it will be fine, assuming the field actually exists in the table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dave said:
Allen

Just a thought - the subform is based on a query - will the Dlookup work
the same?


Allen Browne said:
So you want the message to show if any one of the related records in the
subform contains a zero in the field Currcostotal?

It might be easist to do that by checking in the subform's table
directly, instead of writing code to loop through all the records of the
RecordsetClone of the form in the subform control.

The Control Source of the warning text box will be something like this:
=IIf(IsNull(DLookup("MySubID", "MySubformTable",
"(MySubFK = " & Nz([MainID],0) &
") AND ([Currcostotal] = 0)")), Null, "Warning")
where:
- MySubID is the name of the primary key of the subform's table;
- MySubformTable is the name of the subform's table;
- MySubFK is the name of the foreign key in the subform's table;
- MainID is the name of the primary key on the main form.

DLookup() returns Null if there is no match.
IsNull() test for that.
IIf() shows the message only if an ID was returned.
The Nz() in the middle avoids the #Error when the main form is at a new
record.

Dave said:
I have a datasheet view subform on a tab control.
I am trying to show a warning lablel on the main form if the field
Currcosttotal on the subform = zero (not null)
I have the following code, but this only works when the first record in
the subform is zero, not if it a latter record.
Can any one please advise how I loop through all records on the subform?

Private Sub Form_Current()
If Forms!frmprojectstabbed!qrybomsubform!Currcosttotal = 0 Then
Me.test.Visible = True
Else
Me.test.Visible = False
End If
End Sub
Click to expand...
Click to expand...
Click to expand...
 
D

Dave

The field Currcosttotal is calculated text box on the subform.
Does this mean I would have to query the record set?

Thanks for your help

Dave

Allen Browne said:
Yes, it will be fine, assuming the field actually exists in the table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dave said:
Allen

Just a thought - the subform is based on a query - will the Dlookup work
the same?


Allen Browne said:
So you want the message to show if any one of the related records in the
subform contains a zero in the field Currcostotal?

It might be easist to do that by checking in the subform's table
directly, instead of writing code to loop through all the records of the
RecordsetClone of the form in the subform control.

The Control Source of the warning text box will be something like this:
=IIf(IsNull(DLookup("MySubID", "MySubformTable",
"(MySubFK = " & Nz([MainID],0) &
") AND ([Currcostotal] = 0)")), Null, "Warning")
where:
- MySubID is the name of the primary key of the subform's table;
- MySubformTable is the name of the subform's table;
- MySubFK is the name of the foreign key in the subform's table;
- MainID is the name of the primary key on the main form.

DLookup() returns Null if there is no match.
IsNull() test for that.
IIf() shows the message only if an ID was returned.
The Nz() in the middle avoids the #Error when the main form is at a new
record.

I have a datasheet view subform on a tab control.
I am trying to show a warning lablel on the main form if the field
Currcosttotal on the subform = zero (not null)
I have the following code, but this only works when the first record in
the subform is zero, not if it a latter record.
Can any one please advise how I loop through all records on the
subform?

Private Sub Form_Current()
If Forms!frmprojectstabbed!qrybomsubform!Currcosttotal = 0 Then
Me.test.Visible = True
Else
Me.test.Visible = False
End If
End Sub
Click to expand...
Click to expand...
Click to expand...
 
R

Rick Brandt

Dave said:
The field Currcosttotal is calculated text box on the subform.
Does this mean I would have to query the record set?
Click to expand...

That is why my code doesn't work. The FindFirst only works on *Fields* in
the subform's RecordSet. What is the calculation that produces the result
on your TextBox? We might be able to modify the code to work with that.
 
A

Allen Browne

Yes, you could substitute the name of the query instead of MySubformTable.

Or you might be able to substitute the calculation in place Currcosttotal.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dave said:
The field Currcosttotal is calculated text box on the subform.
Does this mean I would have to query the record set?

Thanks for your help

Dave

Allen Browne said:
Yes, it will be fine, assuming the field actually exists in the table.

Dave said:
Allen

Just a thought - the subform is based on a query - will the Dlookup work
the same?


So you want the message to show if any one of the related records in
the subform contains a zero in the field Currcostotal?

It might be easist to do that by checking in the subform's table
directly, instead of writing code to loop through all the records of
the RecordsetClone of the form in the subform control.

The Control Source of the warning text box will be something like this:
=IIf(IsNull(DLookup("MySubID", "MySubformTable",
"(MySubFK = " & Nz([MainID],0) &
") AND ([Currcostotal] = 0)")), Null, "Warning")
where:
- MySubID is the name of the primary key of the subform's table;
- MySubformTable is the name of the subform's table;
- MySubFK is the name of the foreign key in the subform's table;
- MainID is the name of the primary key on the main form.

DLookup() returns Null if there is no match.
IsNull() test for that.
IIf() shows the message only if an ID was returned.
The Nz() in the middle avoids the #Error when the main form is at a new
record.

I have a datasheet view subform on a tab control.
I am trying to show a warning lablel on the main form if the field
Currcosttotal on the subform = zero (not null)
I have the following code, but this only works when the first record
in the subform is zero, not if it a latter record.
Can any one please advise how I loop through all records on the
subform?

Private Sub Form_Current()
If Forms!frmprojectstabbed!qrybomsubform!Currcosttotal = 0 Then
Me.test.Visible = True
Else
Me.test.Visible = False
End If
End Sub
Click to expand...
Click to expand...
Click to expand...
 
D

Dave

Sorry Rick for not making things clear.

Currcosttotal =IIf(Nz([totalsk]=0,0) And
[Adjustprice]>0,[Adjustprice]*[qty],[exchange]*[qty])

[exchange] is not in the underlying query

Thanks

Dave
 
R

Rick Brandt

Dave said:
Sorry Rick for not making things clear.

Currcosttotal =IIf(Nz([totalsk]=0,0) And
[Adjustprice]>0,[Adjustprice]*[qty],[exchange]*[qty])

[exchange] is not in the underlying query
Click to expand...

I would modify the query for the subform so that the calculation is
performed in the query instead of on the form. Then my suggestion (as well
as Allens') will work.
 
D

Dave

Thanks Rick and Alan

I know see my obvious mistake!!
Both work well when I perform the calulation in the query.

Cheers

Dave

Rick Brandt said:
Dave said:
Sorry Rick for not making things clear.

Currcosttotal =IIf(Nz([totalsk]=0,0) And
[Adjustprice]>0,[Adjustprice]*[qty],[exchange]*[qty])

[exchange] is not in the underlying query
Click to expand...

I would modify the query for the subform so that the calculation is
performed in the query instead of on the form. Then my suggestion (as
well as Allens') will work.
Click to expand...
 

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

How to position records in a scrolling subform 5
check subform for records 7
Refresh Subform Current Record? 3
keep subform synchronized with main form in Microsoft Access 2003 1
Subform Navigation 3
Do...Until Loop Code 2
search subform on open 3
Deleting records from a subform 2

Top