I'm have a query with some fields but only one record. The values in the record depends on comboboxes in a form.
What the query is doing, is that is calculating percentage. So the problem is if you go from zero to something bigger than zero, then this this gives you an infinite value of percentage. I solved that by if it is a zero, then it's changed to null. Then the percentage will be zero.
What I want, or wonder, is if the value is zero, is it possible to to have a textbox or something "lightened up" in a form? What I mean is that if you do your choices in the comboboxes and this gives you a "bad zero" in the query then a text pops up and tells you that "the values are a little bit wrong" or something like that=)
More "shortly" said, can a texts visibility in a form depend on the value in a query?
I have a main form that I want users to populate before I reveal a sub-form. I've changed the 'visible' property to 'no' on the subform so it doesn't show by default but can't make it re-appear when I want it to.
I've tried the forms!whereform.visible=true code and it tells me it can't find the form 'whereform'.
As one is typing in a field with a lookup wizard - dropdown list - is there anyway to see the list without clicking on the down arrow? Perhaps there is a choice somewhere in the program where dropdown menus for the lookup wizard can be made visible?
I have a main form with several subforms within it. These subforms are navigation tabs. In the footer of the main form I have a textbox that I want to hide unless the 2nd tab is selected.
Details:
3 Tabs, 1 subnavigation subform, 3 tab indexes(0,1,2) tab index 1 the textbox on the main form(parent) should be visible. tab index 0 and 2 the textbox should be hidden.
I was thinking I could do something like when tab index 1 is onFocus then textbox = visible and vice versa on the other tab indexes?
I am one of the guys that the dept. drops 1k to send to the beginner through expert level courses for Access, so I ask patience as I am new to VB coding.
I am working on a database to track patient surgical information. I am using a toggle button to control "patient surgery site" visibility, depending on the number of sites associated with them. They are labeled Site A Site B, Site C
What I want is if a patient only has one site, I press the appropriate toggle (Site_A_Button, Site_C_Button, and Site_C_Button) and only fields pertaining to Site A appear. If they have 2 sites, I can press both of the toggles for A and B, but the C will remain hidden, etc.
Each toggle controls 3 fields - For the Site A button - Site_A, Diagnosis_A, A_Blocks_Check For the Site B button - Site_B, Diagnosis_B, B_Blocks_Check For the Site C button - Site_C, Diagnosis_C, C_Blocks_Check
The *_Blocks_Check also controls visibility for a respective fourth field - Site_A_Blocks , Site_B_Blocks , Site_C_Blocks
So far, I have A and B working just fine. By default, the toggle for A is set to -1 so it is visible by default. Site B and C are default 0. Both A and B work exactly as I want them to, however, site C does NOT. It has identical coding as B, however, it is visible by default (despite the "default 0") and the Site_C_Blocks visibility (controlled by the Check Box) also does not work properly. Here is the VB-
Private Sub A_Blocks_Check_Click() If A_Blocks_Check = 0 Then Site_A_Blocks.Visible = False End If
If A_Blocks_Check = -1 Then Site_A_Blocks.Visible = True End If End Sub
Private Sub B_Blocks_Check_Click() If B_Blocks_Check = 0 Then Site_B_Blocks.Visible = False End If
If B_Blocks_Check = -1 Then Site_B_Blocks.Visible = True End If End Sub
Private Sub C_Blocks_Check_Click() If C_Blocks_Check = 0 Then Site_C_Blocks.Visible = False End If
If C_Blocks_Check = -1 Then Site_C_Blocks.Visible = True End If End Sub
Private Sub Site_A_Button_Click() If Site_A_Button = -1 Then Site_A.Visible = True End If If Site_A_Button = -1 Then Diagnosis_A.Visible = True End If If Site_A_Button = -1 Then A_Blocks_Check.Visible = True End If
If Site_A_Button = 0 Then Site_A.Visible = False End If If Site_A_Button = 0 Then Diagnosis_A.Visible = False End If If Site_A_Button = 0 Then A_Blocks_Check.Visible = False End If
End Sub
Private Sub Site_B_Button_Click() If Site_B_Button = 0 Then Site_B.Visible = False End If If Site_B_Button = 0 Then Diagnosis_B.Visible = False End If If Site_B_Button = 0 Then B_Blocks_Check.Visible = False End If
If Site_B_Button = -1 Then Site_B.Visible = True End If If Site_B_Button = -1 Then Diagnosis_B.Visible = True End If If Site_B_Button = -1 Then B_Blocks_Check.Visible = True End If End Sub
Private Sub Site_C_Button_Click() If Site_C_Button = 0 Then Site_C.Visible = False End If If Site_C_Button = 0 Then Diagnosis_C.Visible = False End If If Site_C_Button = 0 Then C_Blocks_Check.Visible = False End If
If Site_C_Button = -1 Then Site_C.Visible = True End If If Site_C_Button = -1 Then Diagnosis_C.Visible = True End If If Site_C_Button = -1 Then C_Blocks_Check.Visible = True End If End Sub
Now I know this is probably sloppy coding, but it was the only way I could get this to work properly at all, so feel free to critique as well.
I want to actually add additional sites (Site D and Site E), but not until I can figure out why the problem is with just A B and C.
I apologize if this seems a bit confusing, but if anyone could help out, I would be most appreciative!
I am trying to get a subform to be hidden when the value of a textbox (txtAllocationID) is empty but when a ID number appears I would like it to become visible again.This is what I have tried so far but doesn't seem to work:
Code: Private Sub txtAllocationID_AfterUpdate() If Me.txtAllocationID.Value >= 0 Then Me.frmStockAllocated.Visible = True Else Me.frmStockAllocated.Visible = False End If
End Sub
My allocation ID is a primary key so all auto number generated and only an integer.
Scenario: When I enter a stock ID of 121 and search I want the subform to remain hidden as there is no Allocation ID related to that Stock ID (see attachment tblAllocated). If I was to enter a stock ID of 122 I want the subform to become visible as the stock ID has a relation with the Allocation ID 6 and therefore the textbox txtAllocation ID would display '6' in it.
I'm having trouble with syntax for using VBA on a report in Access 2007. I need to some hide 3 text boxes if one of my fields is empty in a table. The table name is: 'ALM-RESP' with rows grouped by: 'TAG NAME' and the field I'm checking is: 'Rev 002 Author'. I've tried having a rectangle to cover it up but I'm having trouble using the IsNull command and setting the visibility of the rectangle. Here's my code:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) If IsNull([ALRM-RESP]![TAG NAME]![Rev 002 Author].Value) Then REV2BOX.Visible = False End Sub
Basically I'm having trouble checking if the field is empty and then setting the visibility.
I have a form containing client demographics. One of the fields is the DateOfBirth. I would like to control the visibility of a subform based on this DateOfBirth field.
I have tried using the code below:
Private Sub Form_Current() If DateOfBirth > 11 / 11 / 1994 Then subEligibility.Visible = False End Sub
This works when the form is opened for the first record, but the visibility does not change as I scroll through the rest of the records.
On my form I have a number of buttons named A1 to A6. I want to be able to click on a button and bring up a report that thows all records A6 if that is the button I have pressed or A1 if that is the button I have pressed. I know I could make each query individually, each one looking for the appropriate data, but I was hoping I could make one query and have the criteria change automatically depending on the button that I press.
Is this possible or am I asking a little to much of Access!
" Is it possible to toggle the visibility of a picture on a form's detail section for each row based on a query whether a certain condition is met? "
I have a form, which fetches data from a database and populates the content as a number of rows in the detail section.
For each row I want to test if a certain 'yes/no' condition is met on each query and the toggle the visibility on a picture that I have placed in the detail section.
I've tried seveal possible solutions now, like macros and VBA but haven't got it working.
I have a music database in which I keep track of my CDs and how many times I've listened to them. I have a report that uses grouping to show me the total listens for each artist for each year - the totals are in a group footer, with nothing in the detail section of the report. It looks something like this:
The first line is the column header showing years (last 2 digits for space reasons).
The second and subsequent lines are created in the group footer section, first is the artist name, then the Sum of listens for all titles by that artist in the year given ([Sum of Listens1994]).
This report works perfectly.
Here's what I want to do, and I can't get it to work:
In the above lines of data, let's say that the earliest title for BAND1 is from 1975, and the earliest title for BAND2 is from 2001. My yearly counts start in 1994. Therefore, there were listens for BAND1 prior to 1994, but there weren't any listens for BAND2 prior to 2001.
GOAL: Instead of showing "00" on the BAND2 line for years 1994-2000, I want them blank.
I've put a conditional format code in OnFormat for the group footer section, telling Access to turn visibility off for the text box control ([Sum of Listens1994] etc) if the [Earliest] field (which contains 2001) is greater than the year being displayed (1994 first, and so on). This, which seems like it should work, makes no difference. Oddly, if I put a F9 code break on the If statement, it never breaks, which makes me believe the code is never even being executed.
I do have prior experience with controlling field visibility in forms and reports, so the concepts aren't new to me. I feel like the big difference with this one is that it's in the group footer section rather than the detail section. [Access 2013]
I would like a query to run, depending on a date..If the date in [Txt_Date_Account_Opened] is between todays date, and within the last 30 days.do CurrentDb.Execute ("Qry_Append_Units")else a msg box stating "account is not yet open"
I'm after a piece of code which works as described below.
I have a save button on a form
I have a append query ready to run.
When the save button is clicked, I want the code to see if a checkbox is true or not and if its true I want the append query to run if its not then I want the form to save and nothing else.
I have fought hard to try and figure this out and I have gotten 50% there.
I have a query that generates the following calculated fields through numerous calculations etc....:
[De-Merit Marks] [No Del with Major Faults]
Now the way it should work is that a supplier gets a rating (A-D) depending on both their De-Merit Marks score and the amount of deliveries with major faults. I used the following function in the query and it does the de-merit mark grading.
However I have no idea how to integrate the Major Faults part.
Basically:
0-10 = A 11-30 = B 31-50 = C 51-100+=D
If the supplier has one major fault, the rating should drop one from that reported using just the de-merit score. Two means it drops two ratings (so if an "A" on just the demerit, they would drop to a "C" and so on. Obviously a supplier cannot drop below a "D".
Can anyone suggest how these maybe acheived? I fear it requring VB code, but I am not that good with VB hence why I have relied on queries for this.
I am giving two tables and I need to create a macro that automatically updates these tables depending on the value of a Yes/No field. If it's No, it's in the 1st table TableOne, if it's Yes it automatically updates to TableTwo.
So, the best way I saw to go about is to set up an append query and then create a macro that runs it
So my tables have the values FirstName, LastName and isValid (more but keeping it short)
So for my append query, I put TableTwo in the pop up I get. Then, where it asks for the field I put it
I do this for all (it was autocompleted except the Criteria field). I tried to keep Criteria with data only for isValid but that didn't work. I wrote it for all the field names, still didn't work. Whenever I click run it says it'll append 0 rows.
I want to add a number to my results within a query depending on the month and how many results. For example I have 10 results in my query 3 from January, 5 from March and the rest from April. The 3 from January would be 1,2,3. The five in March would be 1,2,3,4,5 and so on. Is it possible to do?
I have set up a database that stores actions (i.e jobs). In the table; two of the fields are...'required completion date' and 'actual completion date'. I wish to lookup, by using a query, all of the open actions (those which havent yet been complete (i.e the 'actual completion date' is null)) and then later on all those which are overdue (i.e the 'actual completion date' is null And the 'required completion date' <today....this being the criteria for an overdue action).
However, I have used a form which has a combo box which contains the values open and overdue. When a selection has been made I want a form to display with the results depending on the selection that has been made. I am capable of creating a form based on a query, but am unsure of how to construct the query with the correct criteria based on the option that is selected from the form.
This should be fairly easy - I'm probably thinking too hard today.
I want to display a Quantity text control (and the related label in the form header) when the Serial control = "Bulk" OR when the checkbox chkBulk = -1 (checked). I don't need to do both, just one of the above, as I still haven't decided the easiest way to approach this problem.
I need some way to check the value of the checkbox or Serial control in the AfterUpdate event, and set the visibility of the Quantity control to true/visible. It is currently set to not visible. I have experimented a bit with the iif in VB, but my VB skills are rather nonexistent - i can read and comprehend code, but I can't yet write it on my own.
This seems like a relatively easy one or two line procedure. Can anyone offer some advice or point me in the right direction?
I am making a Invoice DB. I have made a combo for Suppliers which takes data from SuppliersTable and data is recorded in InvoiceTable. I have another Combo for Services which takes data from SuppliersTable and records up in InvoiceTable. And I have another field (PaymentPeriod) dependent on ServiceCombo
I want to make Services Combo dependent on Supliers.
How can I do that?
Combo 1 = SuppliersCombo SuppliersTABLE: suppliers data origin Field: SupplierMaster InvoiceTable: supplier data destination Field: Supplier
Combo 2= ServiceCombo dependent on Suppliers Combo SuppliersTABLE: suppliers data origin Field: ServiceMaster InvoiceTable: service data destination Field: Service
Form Field: PaymentPeriod dependent on ServiceCombo SuppliersTABLE: suppliers data origin Field: PaymentPeriodMaster InvoiceTable: paymentperiod data destination Field: paymentperiod
How can I do these dependent?
I hope I explainned myself clearly, I am access first timer.
I would like my form field to populate based on what the user selects from 2 combo boxes I have. I have a table set up with Region, Position, and Name. I would like when the user selects for example Eastern for the region and President for the position that John Smith would auto populate in the form field. Is this possible and if so, how can I get this to work?
Right, my first access database is coming along well, with the help of some people on here, but I have another question. The database is designed to track jobs in our small workshop.
We have a 'price matrix', that has our regular customers on the left, the material type long the top ( mild steel, brass, etc), and a cost in each area for each of four types of standard job, so i suppose its a sort of a 'cube' rather than a matrix, except not all customers and materials have an agreed price for each of the standard jobs (there are only four of them, a maximum of 3 per customer)
Now, my database revolves around a 'main' table, with a unique order ID, customer name (from lookup table ), and other details. Initially, I have created another 'cost matrix' table, with a primary key, customer ID (based on the same lookup table), material type and job type (both lookups), and price fields. What I want to do is that my guys can select material and job type, and the cost will be recorded somewhere. Now, I understand that you shouldn't have the same data twice anywhere in the database, so I figured of I am recording the material and job type in question on the main table, and given the customer ID is recorded as well, I should be able to reference this to my cost matrix table and then be able to populate a field on the main form, query against it on reports that sort of thing. However, there is a field on my main table for cost adjustments ( say we work 24 / 7 on a rush job), which is manually entered on a job by job basis, so should I record the total job cost in my main table, or always calculate it when needed using a unit cost + adjustment type of calculation?
Also, how do I populate the field on the main form that the cost should be 'x' from the'cost matrix' table, given the values of the customer, material type and job type fileds?
Hi Everyone I have been searching EVERYWHERE, but was unable to locate even the correct wording of my problem, so I decided to see if there is anyone here solve this riddle for me:
I have a simple table with 2 fields ie : NAME NUMBER George 5 John 3 etc... I have the need to make a report that makes stickers and make 5 stickers with the name George on it and 3 with the name John etc... I cant even think of a query that would produce 5 rows of George followed by 3 Johns (;}).. The actual problem is more complex than this ( many times ) but this is what is holding me back atm.
Thanks in advance people, and I would like to say that I have learned VERY VERY MUCH from this site alone , and within a month from starting to learn Access for the first time, I have succesfully made a database that is working just fine for invoices and keeping track of inventory....
I am searching the net for a response to my problem but no result; I have 1 BD access (Facture) in witch there is 2 tables (Entete_Facture and Detail_Facture) with a relation in Field (Numero_Fac).
It is very easy to use "INNER JOIN" to do a Query (Facture_with_date) fusionning the 2 tables, But I want to add to this query another column : " SUM(Qte_Article) GROUP BY Code_Article " as below:
