Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Location
    Bucharest, Romania
    Posts
    16

    Unanswered: new table with specific no of records

    Anyone have an ideea how can I make a new table, that based on a given number (ex: 20) to have specified number of records (20). Something like:

    given number from a form = 20

    table

    no text1 text2
    1 empty empty
    2
    3
    4
    .
    .
    .
    .
    20

    Thanks.

    DNY

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Below is a Function I created to basicly do the task you request.

    A Little History:
    Quite some time ago I developed a Database to keep track of Employee Time (hours worked within specific Pay Periods). When Previewing/Printing the Time Sheets for all Employees, I wanted the format of the report pages to be complete whether there were 20 employees listed (a full page) or 1 employee listed. By format, I mean all the lines and boxes within the report (they were record dependant). Therefore, if a full report page would hold 20 employess but the current report to be printed only contained 6 employees, then to fill the page in order to give it a clean Complete look, I needed to provide 14 blank Rows (records).

    To do this....I created a Temporary Table (with VBA) which would contain the 6 Employees and then added 14 Null records to provide a total of 20 records in order to complete the report. And I say Temporary Table because the table is deleted when the report is done. A new fresh Temp Table is created for each report.

    -----------------------------------------------------------------------

    This may seem cumbersome, but it works flawlessly for me and it provides the visual output I desire.

    Here is the Function code:
    Code:
    Public Function CreateTmpTable(RecsPerPage1 As Integer, RecsPerPage2 As Integer, OrigTable As String, TmpTable As String, Optional SQLString As String)
    	 'RecsPerPage1 must hold a total integer number of records that will fit
    	 'onto the first page of a report.
     
    	 'RecsPerPage2 is only really used if there is no Report Header on any page past page 1.
    	 'Because there is no report header on any page greater than page 1, more records can be
    	 'held on those pages. If RecsPerPage2 contains 0 then it is ignored.
     
    	 'OrigTable is to contain the name of the source table.
     
    	 'TmpTable is to contain the name of the Temporary table we will create.
     
    	 'SQLString is optional but would generally be used in order to pull the necessary
    	 'records we want to be placed into our report. SQLString would contain a SQL query
    	 'statement for the records we want. If SQLString is not used, then all records in the
    	 'Source Table are copied to the Temporary Table.
     
    	 'CREATE A TEMPORARY TABLE by copying the all the fields contained within
    	 'the Source Table
    	 Dim dbs As Database
    	 Dim rstSourceTable As Recordset
    	 Dim SourceField As Field
    	 Dim tdf As TableDef, fld As Field
    tryAgain:
    	 Set dbs = CurrentDb
    	 Set tdf = dbs.CreateTableDef(TmpTable)
    	 'Open the Source table Recordsett.
    	 Set rstSourceTable = dbs.OpenRecordset(OrigTable)
    	 'Enumerate through the Source table fields
    	 For Each SourceField In rstSourceTable.Fields
    		On Error Resume Next
    		If SourceField.Name <> "" Then
    		 Set fld = tdf.CreateField(SourceField.Name)
    		 fld.Type = SourceField.Type
    		 fld.Size = SourceField.Size
    		 tdf.Fields.Append fld
    		 tdf.Fields.Refresh
    		End If
    	 Next SourceField
    	dbs.TableDefs.Append tdf
    	If Err Then
    		DoCmd.DeleteObject acTable, "TmpTable"
    		GoTo tryAgain
    	End If
    	dbs.TableDefs.Refresh
    	rstSourceTable.Close
    	dbs.Close
    	Set rstSourceTable = Nothing
    	Set dbs = Nothing
    	Set tdf = Nothing
    	Set SourceField = Nothing
     
    	'FILL THE TEMPORARY TABLE WITH THE DESIRED RECORDS.
    	'==================================================
    	Dim DestRST As Recordset, SourceRST As Recordset, X As Long
    	Dim PageAdd As Integer
     
    	PageAdd = 0
    	Set dbs = CurrentDb
    	Set DestRST = dbs.OpenRecordset(TmpTable)
    	If SQLString > "" Then
    	 'Only specific records are copied to the new Temporary Table.
    	 Set SourceRST = dbs.OpenRecordset(SQLString)
    	Else
    	 'All records are copied to the Temporay Table.
    	 Set SourceRST = dbs.OpenRecordset(OrigTable)
    	End If
    	SourceRST.MoveFirst				'Go to the first record in the source table.
    	Do Until SourceRST.EOF			 'read to the end of Source table.
    		DestRST.AddNew				 'Add a new record to the Temp Table.
    									 'Copy the field Data from source table to the temp table.
    		For Each fld In SourceRST.Fields
    			DestRST(fld.Name).Value = SourceRST(fld.Name).Value
    		Next fld
    		DestRST.Update				 'update each record.
    		X = SourceRST.RecordCount	 'get the record count so far.
    		SourceRST.MoveNext			 'Move to the next required record in the source table.
    	Loop							 'Loop until all required records have been copied.
     
    	'If the total record count so far is less that what is required to
    	'fill a report page then Add NULL records to the temporary table
    	'until the page is full. Note, If there is more records than reuired to fill
    	'the first report page and the second report page contains no report header
    	'then the number of null records is increased to that contained in RecsPerPage2
    	'because without the report header, more records will fit on the page.
    	Dim RptPages As Integer
    	If X > RecsPerPage1 Then
    	 If RecsPerPage2 > 0 Then
    		 If (X Mod RecsPerPage2) > 0 Then PageAdd = 1
    		 RptPages = (X \ RecsPerPage2) + PageAdd
    		 RecsPerPage1 = RptPages * RecsPerPage2 '+ RecsPerPage1
    	 Else
    		 If (X Mod RecsPerPage1) > 0 Then PageAdd = 1
    		 RptPages = (X \ RecsPerPage1) + PageAdd
    		 RecsPerPage1 = RptPages * RecsPerPage1
    	 End If
    	End If
    	If X < RecsPerPage1 Then
    	 Do Until X = RecsPerPage1
    		 DestRST.AddNew
    		 For Each fld In DestRST.Fields
    			DestRST(fld.Name).Value = Null
    		 Next fld
    		 DestRST.Update
    		 X = DestRST.RecordCount
    	 Loop
    	End If
     
    'Clear variables
    dbs.Close
    Set SourceRST = Nothing
    Set DestRST = Nothing
    Set dbs = Nothing
    End Function
    Here is a function to Delete the table or any table:

    Code:
    Public Function DeleteTable(tbl As String) As Boolean
    	'This procedure deletes the Supplied Table
    	'and returns TRUE if successfull.
    	On Error Resume Next
    	DoCmd.DeleteObject acTable, tbl
    	If Err = 0 Then DeleteTable = True
    End Function
    Hope this helps you...or others out there.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •