Forms :: Hiding Certain Fields Based On Number Amount
Jun 13, 2013
I search around online and did come across much info on code to how to hide fields. But, the problem is I cant seem to find a way to hide a certain amount of fields based on a number amount.
example: Lets say I have a 12 text fields to show 12 months of monthly payments, and lets say a client only has a 3 payment term. How can I list 3 under installment field and have remaining 9 fields auto hide since they wont be necessary? this way I only see what applies and not additional text fields.
How can I auto populate fields based on a payment term & amount?
example: client has 9 monthly payment of $150 term due every 15th of the month. (I would like it to be able to max out at 30 months)
So what I want to do is populate 9 monthly fields with dates and $150 payments fields next to it. Now, the payment fields even after auto populate, must be flexible in the sense for me to add a different amount just in case client makes an over payment that month too. In, addition I would like a check mark box to auto populate along if possible based on the 9 month term, this way I can manually check TRUE when payment is received in office.
1 with product info 1 with customer info 1 with incoming stock 1 with outgoing stock (orders)
I also have 3 query's:
Total incoming stock per product Total outgoing stock per product Total current stock per product (based on the 2 above queries)
I'm trying to make a form through which orders can be placed, and I want to limit the amount that can be ordered based on how much stock there is.
I've been told I can use the 'before update' event on the form to achieve this, but where to start. Do I use a Macro, an Expression, or do I need code?
Also, currently the record source for this form is the outgoing stock table, but I guess I'll need to add my 'current stock' query to the sources before this can work? And maybe even my other 2 queries because my 'stock' query is based on the other 2? But I can't really seem to get that to work either.
I have frmIncident that has a 2-column combo box (cmbIncidentType). Column (0) is IncidentType, Column (1) is YES/NO. This combo box feeds from a Query and currently has about 15 "types" of which 4 have a "Yes" tied to them, then rest "No".There are situated on a TabControl (tabIncident) with 5 sheet tabs. If the Incident Type selected in the Combo Box has a corresponding "Yes" in Column (1) then all 5 sheet tabs need to be visible. If it is "No", then only three of the five are visible. Here it he current code I am using for the AfterUpdate event on the combo box:
Private Sub cmbIncidentType1_AfterUpdate() If Me.cmbIncidentType1.Column(1) = "Yes" Then pgEmerIncidentRpt.visible = True pgNarrClose.visible = True Else
[code]...
When I open the form is will set the proper sheet tabs that need to be visible, however once I cycle to the next record, or select a different Incident Type, even one with a "YES' in column (1), it will only show the (3) sheet tabs. I can never get it to show all 5.
In the code, the only (2) sheet tabs that need to visible = true or visible = false are shown, starting with "pg".
Best way to display "notes: field that gets updated/added to multiple times, in a form. (Access 2007)
I've got a DB to track information about product changes. Each month we have a meeting and discuss problem products. I'm concerned with a notes/comments section. Currently it is a memo field in the source table that can be added to, so the notes are cumulative. But we want to auto-add a date to each new entry (in case the person inputting the data forgets, and so that notes can be sorted by order on a report).
There may be many comments on a certain product or there may be none. Sometimes the note may be very long, other notes on the same product may be very short. I created a new table with a one-to-many relationship to case ID (to link it to the main form), auto-populate a date field, and field for notes in order to link multiple notes to each case (and of course an autonumber PK).
I can put the subform in the original form, but then the note field will be the same size for every note, though notes are not all uniform in size/length, and it ends up taking up a LOT of room on the main form. So I created a button to click to open the subform, but it brings up all records for all cases, not just the currently selected case. Again, have the problem of one uniform field size for all notes, regardless of size/length.
Then I considered a report to view past history (field size can shrink or grow as needed) then a button on the report that opens a form for data entry/entry of new note. But again the report displays all records for all cases, not just the currently selected case.
I want to use VBA to hide columns in a subform based on what is checked in a multi value look up field.
I am creating this DB for use with sharepoint as a web database, which is why I am using the lookup field to begin with. There will be a client DB to use with some VBA code
So what I have is a lookup field with tests "Test 1, Test 2, etc" on form sample.
There is a subform called results, and I want to hide certain columns based on what tests are performed.
I tried using an If Then statement (code is being run on subform load)
If Me.Parent.fieldTest = "Test 1" Then Me.Test1Col.ColumnHidden = False Else Me.Test1Col.ColumnHidden = True
That is basically the code I was trying to use. I am getting an error 13, which I assume is because fieldTest can not = something since it is a multi value look up field.
While doing a rabbit DB for the missus I have hit a problem, if one of the does gives birth to 3 babies i wish to have the details entered onto a form (not a problem) but i would like to do this:
if number of babies = 3 then fields to display to enter details is 3 on the form, i have my table setup with up to 10 seperate field for the babies per record but am stuck trying to do this
I want my db to generate our reference # automatically based on input from other fields. The number is the date record entered, the users initials, and an incremental number. Ex. 120505GMM02
My form is based on a query and I have the query generating the first two components from the date entered in reqdate and the initials entered. How can I get an incremental number generated to go with the rest.
The number should start with 01 for each day and also for each user. Therefore when GMM is entering his third record on the 5th it should generate 120505GMM03 even if there were 10 other records entered on the fifth by other users. Each users numbers would start at 01 for each day.
I asked this earlier but thought I would restate my question in hopes of getting my thoughts around this. I appreciate your patience with me.
I have an excel data file that is updated monthly (and I am not able to have the source file format changed of course) that will be my linked table for Access 2007. The file reports monthly data and adds the new month to the right of the historic in a layout like this:
Tree | Orchard | Province | Jan % red | Jan % green | # Limbs | Feb % red | Feb % green... etc so each month it adds 3 new fields to the data.
I have queries (rightly or wrongly) that will calculate the number of apples that month and report on those higher than a certain percentage. I would like to do this without having to create a static table to append to each month as the new data file will always show the full YTD results anyway (and I don't like to store data in my database).
I have a query that counts the number of fields in the raw data file and am wondering if there is a way to have access only run the number of queries required based on the number of fields. i.e. if 11 fields then run queries 1 thru 5, if 14 run 1 thru 6 etc.
I have a query that creates counts of fields based on the data in other fields, basically it tells me that in a table there are two entries with value ABC????? and three of DEF????? , the query works perfectly.
When I create a form to display this data and base the form on the Query I keep getting a message box asking for the ID (key field) from the base table.
If I type * in the box (to denote all values) and press enter I get the results expected.
On my form frmEmployeeDetails I have a text field that is named SSN for social security numbers. I have used the mask on it so it automatically inputs dashes. I am attempting to hide all but the last four digits of the ssn. This is on the on current event of my form.
Code:
'Hide SSN Dim strLSSN, strSSN As String strLSSN = Right(SSN, 4) strSSN = ("***-**-" & strLSSN) Me.SSN = strSSN
I am trying to count how many of the "same" and "differences", as well as calculate the percentages of the number of "same" over the total amount. To clarify, I work at a nursing home, and I need to calculate the number of people who were admitted to our facility and then to the hospital for the same diagnosis, and a different diagnosis. Then, out of the total number of people who were admitted to the hospital from our facility, I need to calculate how many of those people had the same diagnosis or a different diagnosis.
Also, I need to categorize these diagnosis by each type of diagnosis.
Now my question, im trying to hide the rows of those who age is older than 26. If thats not possible maybe highlight the entire rows of those under 26, i used conditional formatting to highlight the age, but not the entire row.
On a form, what is the best way to hide a label or text box? I'm guessing that I can do a checkbox which would work for me and just write a macro to hide on a false value?
Hi can you help. I am developing an access database for an alarm installation company. The database will consist of five tables (stock, supplier, customer, stocksupplier) all with forms.
The customer table holds data for three customer types business, private, caravan. Each customer type has different data stored for it for example the business customer has more than one contact address whereas the private has one.
The problems is on the form I do not want all the fields present as this would look a mess. Is there a way of clicking a radio button or choosing from a drop down list that would make the fields relevant to that customer type appear or grey out the fields that are not needed.
I would appreciate any help as the only alternative I can come up with is have them all on one form. I do not want separate tables as this would complicate the design. I hope this makes sense and thank you in anticipation Kind Regards :) :) Peter
I have a report where I number pages based on the Store (muliple stores in report but page numbering resets when Store changes) I do this by manipulating the page number field on the report by code in the report sections to reset it and to increment it.
Code:
Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer) Me!PageNum = 0 End Sub Private Sub PageFooter_Format(Cancel As Integer, FormatCount As Integer) Me!PageNum = Me!PageNum + 1 End Sub Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer) Me!PageNum = 0 End Sub
The report has a Page Header with various fields and labels and that's been fine however I've had a request to hide a field on subsequent pages (i.e. print it on the 1st page of each store then hide it).
So I added the following code to various sections, but can't get it to work correctly. The fields (label and text box) appear on Page 1 for the first store then get hidden but never return even though when I step through the code it is functioning 'correctly'. The GroupHeader code is performed but the fields don't get displayed on the report. I think is isn't re-painting that section but adding code to the Paint event does nothing, and in fact doesn't actually get run, so not sure when that event is triggered.
Code:
Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer) Me!PageNum = 0 lblMemberNo.Visible = True MemberNo.Visible = True
I have One table(and designed form from it) in which i have recording the daily transactions(it is like a daily book). Daily transactions took place like Purchase of items of Amount 45, sale=70, sold on account=100 etc.
What problem i am facing is: I would like to Add Cash In Hand amount and would like to show it on my form. When day end I would like to move remaining cash In Hand on separate column of that date. The next day i would like to take the previous Remaining Cash in Hand as Cash In Hand at Start for next date and so on.
I am trying to make a report in which I can see the amount of money that I have to pay weekly to an employee. To explain myself clearer, I have different rates for each employee. For example:
NameNormalPayRateVacationPayRateSickPayRateOvertimePayRateStandbyPayRate John Smith$15$10$15$30$8 Ken Jonson$10$8$10$20$8
I am doing this in a query. So what I want to be able to do is, for example, take 4 hours that the employee worked at NormalPayRate and multiply it. But if tomorrow the employee worked at a different rate, for example 3 hours overtime, I want those 3 hours multiplied by OvertimePayRate.
The way I saw it on my Google researches, is that they have only one rate and the amount will be [SumOfHours]*[Rate]. But how can I tell my query to skip, for example, one rate because the employee didn't work at that rate that day. So I can have different amount for the different rates.
By the way, entering the information I can divide at what rate the employee worked x day. So if the employee work 6 hours at regular, and later on 4 hours at standby, I have those information separated in my table.
Results that I am getting: I can easily get the amount in money multiplying the whole hours by a rate, so my result in my report will be multiplied by the same rate.
I am looking for an elegant way to retrieve a number from a table and display it in a form, where the number is determined by two values that will be input by the user to the form.
In particular, I want to be able to enter into a form:
- a date; and - a class of asset
And have the relevant rate of depreciation for that asset class and that year appear in the form.
Sounds trivial and I guess it can be done in a number of ways. I have tried DLookup. It looks like it should work, but I have not been able to get the syntax such that it will accept a control value as a field name in the first argument of DLookup. (This would involve a table much like a spread sheet with a column for each year. I guess such tables are frowned upon and I have also heard negative comments about Lookups.)
I have experimented with concatenating the two fields into one such there is only one field to search upon. Seems to work. The table would only be updated annually and would only be a few thousand records.
I have experimented with having two joins between tables, but that did not seem ideal.
Perhaps a query of a query?
Perhaps a macro?
(Assuming I can get the form to work I would also like to create a report that lists assets and their depreciation.)
I have a few input tables and I am trying to combine the fields from 2 tables into one query. How do I go about doing it without having the project amount (highlighted in red) being duplicated?
Firstly, thanks to everyone for help so far. My first access project in a proffessional environment has been successful and mostly down to the people who have helped me here. I know i didnt really do it all myself but i have learned things that will hopefully serve me well going forward!
Secondly, i apologise for the ambiguously titled thread, i'll explain what i mean.
Basically i have my form that looks like the below
What i'm trying to do, is make the questions below ("Risk in Authority" and "If Not, Risk Referred?") hidden, unless the answer to "Are All Policies Acceptable?" is No.
I have managed to make it happen, but it's far from perfect. As below:
I'd set the value for Visible to No in the properties for every object in the Questions (the pretty boxes are all named things like Box123 and Label 53, all set to hidden). Then i set the "On Click" event of the No in "Are All Policies Acceptable" (called Option168) to
This works ok, but if you click on the same option twice it reverses the action.
Is there a more effective way of doing this? maybe through Validation?
Thanks for any help once again. (I've also recommended this board to a couple of guys in the office who are already proficient with Access and SQL who can hopefully contribute to the community )
Edit: As an Addendum - this formattign should only apply to one record at a time. So if the question are visible on record 1, they could still be hidden on record 2. If that's even possible.
