Unanswered: Treating each record of Continuous Form independently /w a control, value from sql
The question I'm about to ask involves some general, query, vba, form design so I thought general category was the best place to put it.
I am current developing an access database for my Tax Accounting Office, and I have run into an issue of integrating independent controls to each record row of a continuous form.
Upon much searching, I now realized simple answer was that access itself doesn't allow this and many recommend using unbound textbox and conditional formatting to achieve similar effect. However, I think my situation is slightly different in that I'm trying to retrieve a value from a dynamic sql count query rather than a value from a table or a fixed query. Please allow me to explain the situation to the best of my ability, and I greatly appreciate anyone's input on this!
Situation & Table List
1) Table: ClientInformation
CID (Primary, Autonum)
FYE <- (For Year End)
2) Table: TaxReturns
TID (Primary, Autonum)
I first populated Table: ClientInformation with about 300 clients, and each have different FYE, which is the month the tax year ends for that client. (1-12)
I created a "Continuous Form" that is based on Table: ClientInformation, filtered by "FYE" Text Input on popup msgbox on "Form Open Event". Then the records return only clients that have matching [FYE] as the value inputted on popup.
So for example, if user types 5 in popup, only client records with FYE field value of 5 in Table: ClientInformation is shown.
Also on the header of the continuous form, I put a combo box of range of years 2009 to 2014 that user can choose. This combo box is not bound to any source in Table: ClientInformation.
What I originally wanted to be able to do was to put a command button alongside each client record row on the continuous form caption with "Add Tax Return", and on click event, sql is run in vba to: (please do not mind the sql syntax below)
INSERT INTO the Table: TaxReturns
a) CID as [CID],
b) ReturnYear as combobox value,
c) ReturnMonth as [FYE]
And then after the sql is run, I wanted to hide the Command Button for "that record only". (to avoid duplicate inserts)
Then I thought, it would be great to run a dcount() function or run sql in vba to count rows in Table: TaxReturns that have matching "CID and ReturnYear and ReturnMonth" for each individual record on the Continuous Form.
Then, if count result is 1 (In theory should be 0 or 1 only, exist or not type of deal), then hide the command button for that record only. The purpose of this is to let user know that this tax return row was already inserted into Table: TaxReturns to avoid duplicate entry into Table: TaxReturns and also easily allow users to quickly discern what records have not yet been added, but also would like to display all clients with matching [FYE] regardless of whether a row was inserted into Table: TaxReturns has been added or not.
I was hoping for the row that result in count of 1, hide the command box and display a text box that says "Already Added", and for count returning 0, show the command button.
Then I was in for rude awakening when after lot of research, many people have indicated that controls in continuous forms are not independent of each row, but rather one control repeated throughout all rows, so it was all or nothing type of deal. This is why I saw that even though some rows count 1 and some rows count 0, all rows displayed the same result, based on the first record.
Possibly by now, many responses may say this cannot be done, but I know there are many smart people out there that have found brilliant workarounds to nearly any complex situation, by only using the cards dealt to them. I really am hoping there's a workaround to achieve this task of logical steps, even if it doesn't involve the exact procedure as I had originally imagined.
If anyone can give an explanation of how this can be achieved, even suggestions that would allow it to work similar to my original intentions, even if it's using unconventional means, any help would be awesome!
I'll closely stick around to provide any other additional info as needed.
1. What you intend to do using a seperate Count() or DCount() function can be done in a query that can be used as the RecordSource of the continuous form. That way, a column available in this continuous form will hold the correct value for each row.
2. Instead of a command button, you can use a Label that can be made invisible using the conditional format feature (ForeColor = BackColor -> Invisible, ForeColor <> BackColor -> Visible).
Apart from that I don't see any easily manageable way of achieving what you want. It would be theorically possible to combine a continuous form with another form which would have the same number of controls than the number of rows in the continuous form but the alignment and the sychronization between each row of the continuous form and its related control in the simple form would be a nightmare, if not impossible.