Treating each record of Continuous Form independently /w a control, value from sql

[smk224]

Hello everyone!



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
Fields:
CID (Primary, Autonum)
Name
TaxID
FYE <- (For Year End)

2) Table: TaxReturns
Fields:
TID (Primary, Autonum)
CID
ReturnYear
ReturnMonth

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.

The Issue:

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.

So much appreciated!

Thanks all~

[ranman256]

There's no need to put the button on every record. It can be put in the header and read the record the cursor is in. (same diff, but less busy)
The count can still be in the query to tell the button/box to enable or not.

[smk224]

Thanks very much for your input.

I'm wondering that would be different in a sense that user wouldn't be able to see which records have been added or not by just a screen glance, more of an overview approach.

Wouldn't it also be easier to miss certain records if it requires scrolling down records to see how the button reacts in the header?

[June7]

A textbox can act somewhat like a button. Examples of this is available in a number of MS database templates. And then with Conditional Formatting, the textbox can be enabled/disabled. However, it will still be visible.

[smk224]

A textbox can act somewhat like a button. And then with Conditional Formatting, the textbox can be enabled/disabled. However, it will still be visible.

It wouldn't really need to concern about reenabling or not because the record row also has a year on it.

So for example, if user added this year ReturnYear = 2013, ReturnMonth = 4, by inputting 4 on popup, and selecting 2013 on combo box,

by this time next year, the user would be putting 4, and 2014 so it would be different record anyway. I'm only trying to find a way to avoid duplicates that contain the same of "CID and ReturnYear and ReturnMonth"

Thanks for the additional questions all~!

[June7]

I wouldn't use popup, I would use unbound controls in form header to enter the parameters. Then textbox Conditional Formatting can reference the header controls to enable/disable for each record.

[smk224]

I wouldn't use popup, I would use unbound controls in form header to enter the parameters. Then textbox Conditional Formatting can reference the header controls to enable/disable for each record.

I agree, it is probably more user friendly just to add the month field as a textbox on form header instead of popup box.

I will make that change, and I apologize If I'm understanding incorrectly, but is that any different than obtaining the month value from popup in the grand scheme of what I'm trying to achieve? I ask this only because the month value being passed from popup is not the issue but trying to get the sql or dcount function to count each row separately

As for the textbox, I would include one in the detail section of the form, but how would I setup conditional format so each row yield different result when I can't populate the textbox with 1 or 0 independently based on the sql or =dcount()? They just all yield same result on each row based on the first row.

[smk224]

Hey June7, I was just doing all the edits you suggested, and I think we may be getting somewhere, I'll report back as things progress. Thanks!

[June7]

DCount is a domain aggregate function that must reference a field and table as arguments (filter criteria is optional but probably needed for your situation), it does not count records of the form, that would be Count().

What records need to be counted?

Use popup if you want but the input must be saved to textbox in order for the Conditional Formatting to reference.