I think what you want is something similar to what I have done, I had:
Text boxes to enter criteria into a search query
Checkboxes to select fields from the search query and build a new results query in VBA
End Product: The results query would have only the specified fields but also only the records that were specified in the search query from the texboxes in the same form
So what you would have to do is make a normal seach form and on the same form add checkboxes, then on the on-click of a command button enter the VBA
This is the code I used, you would obvioulsy change it a bunch
Code:
Private Sub ProjectSearchCommand_Click()
DoCmd.OpenQuery "ProjectSearchQuery"
DoCmd.Close acQuery, "ProjectSearchQuery"
DoCmd.Close acQuery, "ProjectSearchResults"
Dim strSQL As String
strSQL = "SELECT "
' Check ProjectCode box
If Check1 = -1 Then
strSQL = strSQL & "[ProjectCode],"
End If
' Check Trade box
If Check2 = -1 Then
strSQL = strSQL & "[Trades],"
End If
' Check Title box
If Check5 = -1 Then
strSQL = strSQL & "[Title],"
End If
' Check Status box
If Check9 = -1 Then
strSQL = strSQL & "[StatusCode],"
End If
' Check ClientAgency box
If Check6 = -1 Then
strSQL = strSQL & "[ClientAgencyCode],"
End If
' Check FundingAgency box
If Check7 = -1 Then
strSQL = strSQL & "[FundingAgencyCode],"
End If
' Check AcceptDate box
If Check13 = -1 Then
strSQL = strSQL & "[AcceptDate],"
End If
' Check BidDate box
If Check14 = -1 Then
strSQL = strSQL & "[ProjectBidDate],"
End If
' Check CompDate box
If Check15 = -1 Then
strSQL = strSQL & "[ProjectCnstCompleteDate],"
End If
' Check EICName box
If Check10 = -1 Then
strSQL = strSQL & "[EICFullName],"
End If
' Check Remark box
If Check12 = -1 Then
strSQL = strSQL & "[Remark],"
End If
' Check TeamLeaderNamebox
If Check11 = -1 Then
strSQL = strSQL & "[TLFullName],"
End If
' Check ChangeOrders box
If Check3 = -1 Then
strSQL = strSQL & "[ChangeOrders],"
End If
' Check Contractors box
If Check8 = -1 Then
strSQL = strSQL & "[Contractors],"
End If
' Check BonusWithTrades box
If Check16 = -1 Then
strSQL = strSQL & "[BonusAmountAllTrades],"
End If
' Check Field Orders box
If Check4 = -1 Then
strSQL = strSQL & "[FieldOrders],"
End If
' Check TotalContractAmount box
If CheckTOtalCA = -1 Then
strSQL = strSQL & "[SumOfContractAmount],"
End If
' Check ContractAmountByTrade box
If CheckCAByTrade = -1 Then
strSQL = strSQL & "[ContracAmountByTrade],"
End If
' Check to make sure that they have made at least one selection
If Right(strSQL, 1) <> "," Then
MsgBox "You have not selected any fields to show.", vbOKOnly, "Please select one."
Exit Sub
' If they have, drop the final comma and add From clause
Else
strSQL = Left(strSQL, Len(strSQL) - 1) & " FROM [ProjectSearchQuery];"
End If
' Do view code before applying, uncomment line below
'' MsgBox strSQL
' Assign SQL code to Query
CurrentDb.QueryDefs("ProjectSearchResults").SQL = strSQL
' Open query to view results
DoCmd.OpenQuery "ProjectSearchResults", acViewNormal, acEdit
End Sub
Edit: I think I just made it way too complicate do you want to have the same fields show up every time?