How to select the first three Records and another one record pending on user request

[Moah Scout]

Hi there?
I have a table say tblChoose with up to 40 records and about 10 fields. I want to choose the first three Records and the forth record on user choise. Eg One user decided to select exactly forth record and another may choose the 23th record so that the total records selected per user is 4, three by default and forthone by user request.
After the selection I want to transpose this result to a specific report`s Template.
Please help?

[ADezii]

If I understand you correctly, you want 3 choices made randomly for the User, then allow the User to make the 4th choice for him/herself? You then want a Report generated for the User based on these 4 choices including all the Fields in tblChoose?

[Moah Scout]

Yes! Exactly
But three Records must be there and the forth one is depending on user decision and then Transpose the resulted four Selections

[Moah Scout]

The main problem in this is the transpose part of it. I want all rows to be Changed to Column and all Column to Rows just after the selection, and this resulted query has to be reported on the same template always

[ADezii]

I'll need some more explicit details, showing data representing both before, then after results, Field Names, Types, Sample Data, etc.

[Moah Scout]

I have attached the sample db and its related form.
I can manage to choose the records but the question comes on how to transpose it in the same templates or as the whole selection procedure should be changed.

[ADezii]

Sorry Moah, but I am not using Access 2007. I've Attached a Demo for you that I created awhile ago for another Member. It will Transpose the Data in a Table or Query to a TAB Delimited Text File as requested by the User. Hopefully, it can be of some use to you.

[Moah Scout]

Thanks!
It can transpose the table or query but how about parameter query? it gives error that !TOO FEW PARAMETERS. EXPECTED 1!

[Moah Scout]

If it can, let it be in a table or query format

[ADezii]

If it can, let it be in a table or query format

Please clarify - not sure what you mean...

[Moah Scout]

I mean the modules you have given can transpose the table to another format as you have said but not table or query. What about parameter query? it end up with errors as TOO FEW PARAMETERS EXPECTED 1. Is it possible to transpose the parameter query with some modification on this Code and gives the results as a Table or query so that I could easily create my final reports?
By Parameter query I mean the query that is created depending on the user parameter entered in a form or somewhere as. It always gives a prompt if you attempt to open it. Let me attach this db again in 2003 format so that you can be sure of what am talking about.
In this db, open the form named frmstd and select any number between 3 to 7 and the query will be opened with that number on it. Or as try to transpose the query qrystudents it cant.
Please help

[ADezii]

The Code can be modified to Transpose the Data to a Table, probably not too easily, but there is still the issue of the Parameter Query. The Parameter must be resolved prior to the Data being transposed. How are you resolving the Parameter(s) currently?

[Moah Scout]

May be by using a MAKE TABLE commands on the access 2007?
But the problem is how to automate it?
You can make table by open the query in design view and then just click the MAKE TABLE Commands on the Ribon

[ADezii]

This is not as simple as initially thought, give me a little time, and I'll see what I can come up with.

P.S. - I'm getting an Unrecognized Database Error which will not allow me to Open the Database, but that is not important since I now truly recognize the problem (I think! [LOL]). I'll recreate the scenario and see what comes up. The hardest part will be in dynamically recreating the Table for each Transpose Operation. I'll get back to you at some point.

[Moah Scout]

Thanks Adezii for your contribution. with this time frame I have come with this transpose code attached below but the question remain their,
1. How about parameter query?
2. How about to keep the same templates on the reports?
here is the example of transpose code

[ Function Transposer(strS ource As String, strTarget As String)

Dim db As Database
Dim tdfNewDef As TableDef
Dim fldNewField As Field
Dim rstSource As Recordset, rstTarget As Recordset
Dim i As Integer, j As Integer

On Error GoTo Transposer_Err

Set db = CurrentDb()
Set rstSource = db.OpenRecordse t(strSource)
rstSource.MoveL ast

' Create a new table to hold the transposed data.
' Create a field for each record in the original table.
Set tdfNewDef = db.CreateTableD ef(strTarget)
For i = 0 To rstSource.Recor dCount
Set fldNewField = tdfNewDef.Creat eField(CStr(i + 1), dbText)
tdfNewDef.Field s.Append fldNewField
Next i
db.TableDefs.Ap pend tdfNewDef

' Open the new table and fill the first field with
' field names from the original table.
Set rstTarget = db.OpenRecordse t(strTarget)
For i = 0 To rstSource.Field s.Count - 1
With rstTarget
.AddNew
.Fields(0) = rstSource.Field s(i).Name
.Update
End With
Next i

rstSource.MoveF irst
rstTarget.MoveF irst
' Fill each column of the new table
' with a record from the original table.
For j = 0 To rstSource.Field s.Count - 1
' Begin with the second field, because the first field
' already contains the field names.
For i = 1 To rstTarget.Field s.Count - 1
With rstTarget
.Edit
.Fields(i) = rstSource.Field s(j)
rstSource.MoveN ext
.Update
End With

Next i
rstSource.MoveF irst
rstTarget.MoveN ext
Next j

db.Close

Exit Function

Transposer_Err:

Select Case Err
Case 3010
MsgBox "The table " & strTarget & " already exists."
Case 3078
MsgBox "The table " & strSource & " doesn't exist."
Case Else
MsgBox CStr(Err) & " " & Err.Description
End Select

Exit Function

End Function
]