populate an unbound control in a report

  • Thread starter Thread starter Warren
  • Start date Start date
W

Warren

I have been round and round with this and think I've exhausted this site or
at least myself with this; I am trying to populate 3 controls using vba (will
expand to five sets of three but once I get these three the rest should be
easy I hope) the recordset is based on a query used to compile original data
from multiple tables and also generate a ranking within subgroups - end goal
to populate the fields for each subgroup on a subreport via vba.
All of the fields referenced are either in the subreport or the query and
are long integer except the pilotvolt and pilottemp fields which are double.
I have tried multiple suggestions from this site and by now probably have
different methods blurred together if anyone can look at the code and perhaps
make any suggestions to make this work would be much appreciated.

After dealing with numerous errors the current problem seems to be
P.ReadingID is not found when the rs.FindFirst is run.

One thing that is puzzling is that Me.CellN1 should be Me.celln1 but keeps
defaulting back to the caps everytime I change it; the only place in the
entire database it's mentioned is on this report and when I check on the form
it is all lowercase but the vba keeps dafaulting back to caps?

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim db As DAO.Database
Dim rs As Object

Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT P.ReadingID, P.CellNumber,
P.PilotVolt, P.PilotTemp, P.Rank FROM Test1bPilot AS P WHERE P.Rank = 1;")

rs.FindFirst "[P.ReadingID] = '" & Me.ReadingID & "'"
If Not rs.NoMatch Then
Me.CellN1 = [P.CellNumber]
Me.cellv1 = [P.PilotVolt]
Me.cellt1 = [P.PilotTemp]
End If

rs.Close

Set rs = Nothing
Set db = Nothing

End Sub
 
correction
I might also add that I've tried using the Load and Open events of the
*subreport*
Warren said:
I might also add that I've tried using the Load and Open events of the subform

Warren said:
I have been round and round with this and think I've exhausted this site or
at least myself with this; I am trying to populate 3 controls using vba (will
expand to five sets of three but once I get these three the rest should be
easy I hope) the recordset is based on a query used to compile original data
from multiple tables and also generate a ranking within subgroups - end goal
to populate the fields for each subgroup on a subreport via vba.
All of the fields referenced are either in the subreport or the query and
are long integer except the pilotvolt and pilottemp fields which are double.
I have tried multiple suggestions from this site and by now probably have
different methods blurred together if anyone can look at the code and perhaps
make any suggestions to make this work would be much appreciated.

After dealing with numerous errors the current problem seems to be
P.ReadingID is not found when the rs.FindFirst is run.

One thing that is puzzling is that Me.CellN1 should be Me.celln1 but keeps
defaulting back to the caps everytime I change it; the only place in the
entire database it's mentioned is on this report and when I check on the form
it is all lowercase but the vba keeps dafaulting back to caps?

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim db As DAO.Database
Dim rs As Object

Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT P.ReadingID, P.CellNumber,
P.PilotVolt, P.PilotTemp, P.Rank FROM Test1bPilot AS P WHERE P.Rank = 1;")

rs.FindFirst "[P.ReadingID] = '" & Me.ReadingID & "'"
If Not rs.NoMatch Then
Me.CellN1 = [P.CellNumber]
Me.cellv1 = [P.PilotVolt]
Me.cellt1 = [P.PilotTemp]
End If

rs.Close

Set rs = Nothing
Set db = Nothing

End Sub
Click to expand...
Click to expand...
 
Back
Top