With luck, this will be my last Access thread!
In the past week, I’ve created a new Access database for coworkers to manage some projects, and one of the things they asked for was a link to the search results on the database I’ve mentioned before, which contains document information. So I’ve linked the document information tables from the older database to the new one, I have created a subform which gets the DocNo, DocType, RevNo, Date and DocID (autonumber and primary key) from the older database, all based on the Country and Widget names for the new project (in this case, the documents serve as historical info for help in completing the project). I can explain this better if you need me to.
My query and form seem to work fine, getting the information that I want through the linked tables. Since there are several documents for each Country and Widget pair, the subform is a continous subform; it basically shows the results as a table. Now, what I want to do is add a calculated “field” to that subform display. I have created a text box on the form, which diligently creates a column in the results sheet, and I want to concatenate the DocNo, DocType, RevNo, DocID and Country, in order to generate the now infamous file name that allows people to actually go look at the document.
This works perfectly…but only for the first record found!
For example, the query results on the Subform look like this:
DocNo....RevNo....DocType......Country....DocID...Filename
ABC123....2........Letter.......CAN.......334......??????
G-345-2...3........Certificate..CAN.......121......??????
ABC099....NA.......Letter.......CAN.......034......??????
Looking at the first one, I should get a concatenated field with “CAN_Lett_ABC123R2_N334” (and I do!)
But for the second one, I get the exact same thing. The filename always displays the first one, and never updates for the data for subsequent found records.
I have the concatenation intructions in the Subform_Load() event. I tried OnCurrent, which changes every single Filename entry when I change rows on the result table, so that’s no good. None of the other events seem to be what I need either.
I was able to do this exact thing on the OnFormat event of the Details section of a Report, but I can’t seem to do the equivalent onto a continuous subform (it doesn’t work on the form itself, I haven’t even bothered trying to get it to work as a subform off of the main form!). Reports are so much easier than forms!
I’m beginning to think this is going to require some sort of a loop through the query’s record set, telling it to update the Filename for every record found, but I don’t understand how that works well enough to Google it. Can anyone help me with the code for that, or perhaps give me an other way to accomplish this?
The closest I can come up with is this, but it doesn’t work at all:
Private Sub Form_Load()
Dim rs as Recordset
Do while Not rs.EOF
-Instructions to concatenate_
Rs.Movenext
Loop
Rs = nothing
End sub
Thank you so much for any help or advice you can give me!
