New Row

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

I have a report with 20 text boxes in a column. txb_name1, txb_name2..etc.
There is code that loops thru a recordset and populates the text boxes.
What I would like to know is:
Can I use just 1 textbox on the report and add in a new row after the code
fills the textbox?
If so....what is the code to call for a new row?
 
Jeff said:
I have a report with 20 text boxes in a column. txb_name1, txb_name2..etc.
There is code that loops thru a recordset and populates the text boxes.
What I would like to know is:
Can I use just 1 textbox on the report and add in a new row after the code
fills the textbox?
If so....what is the code to call for a new row?
Click to expand...

The .AddNew method will do that. Here's a sample from the Help file:

Dim dbsNorthwind As Database
Dim rstEmployees As Recordset
Dim strFirstName As String
Dim strLastName As String

Set dbsNorthwind = OpenDatabase("Northwind.mdb")
Set rstEmployees = _
dbsNorthwind.OpenRecordset("Employees", dbOpenDynaset)

' Get data from the user.
strFirstName = Trim(InputBox( _
"Enter first name:"))
strLastName = Trim(InputBox( _
"Enter last name:"))

' Proceed only if the user actually entered something
' for both the first and last names.
If strFirstName <> "" and strLastName <> "" Then

' Call the function that adds the record.
AddName rstEmployees, strFirstName, strLastName

' Show the newly added data.
With rstEmployees
Debug.Print "New record: " & !FirstName & _
" " & !LastName
' Delete new record because this is a demonstration.
.Delete
End With

Else
Debug.Print _
"You must input a string for first and last name!"
End If

rstEmployees.Close
dbsNorthwind.Close

End Sub

Function AddName(rstTemp As Recordset, _
strFirst As String, strLast As String)

' Adds a new record to a Recordset using the data passed
' by the calling procedure. The new record is then made
' the current record.
With rstTemp
.AddNew
!FirstName = strFirst
!LastName = strLast
.Update
.Bookmark = .LastModified
End With

End Function
 
Jeff said:
I have a report with 20 text boxes in a column. txb_name1, txb_name2..etc.
There is code that loops thru a recordset and populates the text boxes.
What I would like to know is:
Can I use just 1 textbox on the report and add in a new row after the code
fills the textbox?
If so....what is the code to call for a new row?
Click to expand...

The direct amswer to your question is:
Me.NextRecord = False
which will force Access to call the detail section's Format
event procedure again with teh same data record.

BUT, there are many considerations to using that feature.
Depending on what else is in the detail section, you may be
better off with a subreport or haiving your code concatenate
the names into a single string value to be displayed in a
single text box.
 
Marchall,
Thanks for the reply. I am having trouble.... I have made some experiment
code to try out your idea. It worked the first time only. After that it
will "hourglass". Take a look at my code please. I am sure I am doing
something wrong.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'Return reference to current database.
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qry_Union_Single_Weekly_Monthly")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.name)
Next prm
Set rst = qdf.OpenRecordset()
Me.NextRecord = False

'Checks to see if there is at least one record in the recordset
If rst.EOF = False And rst.BOF = False Then
rst.MoveFirst
While rst.EOF = False 'starts at the first record and enters it in
the textbox
txt_TextTest.Value = rst!ClientID
rst.MoveNext 'moves to the next record
Wend
End If
rst.Close
Set dbs = Nothing
End Sub
 
Jeff said:
Thanks for the reply. I am having trouble.... I have made some experiment
code to try out your idea. It worked the first time only. After that it
will "hourglass". Take a look at my code please. I am sure I am doing
something wrong.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'Return reference to current database.
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qry_Union_Single_Weekly_Monthly")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.name)
Next prm
Set rst = qdf.OpenRecordset()
Me.NextRecord = False

'Checks to see if there is at least one record in the recordset
If rst.EOF = False And rst.BOF = False Then
rst.MoveFirst
While rst.EOF = False 'starts at the first record and enters it in
the textbox
txt_TextTest.Value = rst!ClientID
rst.MoveNext 'moves to the next record
Wend
End If
rst.Close
Set dbs = Nothing
End Sub
Click to expand...


That code will loop forever or until the system runs out of
resources.

Before we spend a lot of time chasing down a complex path, I
need to know what the report is trying to accomplish, what
its record source is and what data you want to display in
what sections.

The idea you're currently chasing is really only appropriate
to an unbound report or a "lightly" bound report with a
single record in its record source. Based on your apparent
level of knowledge about this kind of thing, I have my
doubts that this is right way to go about achieving whatever
goal you're trying to reach.
 
Back
Top