Display Table Elements as a Paragraph with Numbering?

[DHavokD]

That looks like it may be what I am looking for, though looking at the code I am, a beginner with Access / VBA, thoroughly confused.

Would this be called in the query itself, or on the query after it has been run?



What would the value of qryname (name of query assuming...), keyname, and keyvalue be?

[DHavokD]

Ignore that last question for now, while I check out the sample database, seems to show it at work.

[DHavokD]

Well I am getting errors trying to use this code. This line gives an error:

Set rs = dbs.OpenRecordset(qryname, dbOpenDynaset, dbReadOnly)

(this is from the Serialize function inside the same database that was provided in that link.)

It says "Too few parameters, expected 1." Tried getting rid of the two options and running it with just the first parameter, still didnt work. Trying to figure this bug out now.

[RuralGuy]

Can you run "qryname" by itself? I believe that is the source of the error.

[DHavokD]

I did try that, with no success.

[RuralGuy]

Indicating that the query is the source of the problem, right? I doubt it is Stephan Lebans code that is the problem.

[DHavokD]

I dont really know, but I'll keep trying to get it to work.

[RuralGuy]

Do you get an error when you run the query by itself? If so, what is it? How about posting the SQL for the query so we can see what you are doing.

[DHavokD]

When I run the query it switches over to the Visual Basic screen and shows the error.

Here is the SQL...

SELECT "::" & Consolidated.Title & ":: " & IIf((Consolidated.Description<>""),("DESCRIPTION: " & Consolidated.Description & "; "),"") & IIf((Consolidated.[Significant Events - Past]<>""),("SIG EVENTS PAST: " & Consolidated.[Significant Events - Past] & "; "),"") & IIf((Consolidated.[Significant Events - Future]<>""),("SIG EVENTS FUTURE: " & Consolidated.[Significant Events - Future] & "; "),"") & IIf((Consolidated.Description<>"") And (Consolidated.[Significant Events - Past]<>"") And (Consolidated.[Significant Events - Future]<>""),"","No Significant Events; ") AS UpdateInfo, Serialize("Sig Events Query","Title",[Title]) AS NewId
FROM Consolidated INNER JOIN Projects ON Consolidated.ProjectTitle = Projects.Project
WHERE (((Consolidated.ProjectTitle)=[Reports]![IPT Projects Report]![ProjectTitle]))
ORDER BY Consolidated.Title;

[DHavokD]

I have tried using a few different fields in the Serialize Function, all of which throw an error.

[RuralGuy]

I've sure seen this query before. Is there only one field named Title in your query? I would start slow and get the Serialize to work first and then start adding in the IIF's.

[DHavokD]

For no apparent reason, Access can suddenly no longer see the Function Serialize...

Undefined function 'Serialize' in expression...

WTF? Have been using this function for like two hours!

FIXT. nvm this.

[DHavokD]

Woo! Got it. The trick was to link the Serialize to something that was pre-existing in the table. Also think that I had an issue because I used [Title] and not [Consolidated].[Title] in the Serialize call.

Note: If I uncheck the box for displaying title in the query, it breaks the serializing, but thats fine cause the query will be used to populate a report.

[DHavokD]

Okay it started giving me the error again... I changed nothing and stopped working...

[DHavokD]

Alright, I have finally made a design that is stable! Doesnt keep complaining about missing functions that exist and doesnt give errors... But now I have another question.

The report is sorted by "Project." So ProjectA will have all its entries, then ProjectB, etc. Query1 is a make-table query that populates with the "paragraph" of information, the title, and the project, filtered by the current project listing in the report. Query2 then adds the numbering using the Stephan Leban code, Query2 is the Data Source for the sub-report that displays this information. The "Project" portion is in the main report. So is like:

Project: Project A
[Project A filtered sub-report]
Project: Project B
[Project B filtered sub-report]

Now since Query1 is based on the project, it needs to be requeried each time Query2 is called, how would I do this?

TLDR: Query2 is based on a table that is made by Query1 and display data in a sub-report. Query1 filters the table based on a field in the main report. Query1 needs to be requeried everytime just before Query2 is queried.