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.
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!
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:
Is there a way to have a different message box pop up depending on a "Yes" or "No" response? I have a message response that pops up now, but does so no matter what the response is.
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 am working on adding the function of adding a date to a field on a form On Exit from another field. If the field I am exiting has a value of "No" i want to add the current date and if it is "Yes" I want to make sure the other field is null. I can't seem to find how to do this effectively. Can someone help me here?
Lets hope that after finding this forum, my slight problems will begin to ease off a little.
I am unfortunately one of those newbies trying to get in well above my head and level of ms access workings, but we all have to start somewhere, right?!
My problem at the moment is as follows:
The scenerio is i work for an excursions provider in Cyprus and I am trying to set up an online excursions site for them.
Now with any excursion, the price flutuates through the year when its low season, high season etc. I have built a MS Access database with the following tables so far. Excursions details: this contains everything about the excursion on offer, along with additional columns for the price changes and dates that these apply for. ie. [adprice1][fromdate1][todate1], [adprice2][fromdate2][todate2] etc
Now what I am trying to acheive if at all possible is that when a viewer takes an interest in an excursion and selects the date they would like to go on the excursion, that the correct price is displayed for that specific time period i.e if it the date was betwen [fromdate1][todate1] or [fromdate2][todate2]
Is this at all possible and if so can someone please explain to me in real laymans terms what I need to do for this too occur within the database please.
Thank you in advance and sorry for waisitng anybody's time if this seems obvious to others and not myself!
I'm currently making a signup form for a 6day event.
Certain details are taken (Name, Age, Contact no etc).
I also have a registration form; so when someone comes back for the second day of the event I tick the 'saturday' checkbox, or 'monday', or whatever the day is...
When someone comes to sign up for the first time, I'd like the form to automatically check the box for that day (to indicate their attendance). This would save me opening two forms to signup one person, if I make sense.
So, when someone comes to register on a Tuesday, I fill in all their details - and then it automatically checks the 'tuesday' field box.
I have a query in which I try to calculate field values depending on the currency and foreign exchange rates. I have a currency field with the amount value and a combo box where I select the currency for that amount which can be (USD, Euro, BRL, or INR). I have got also another 4 fields with each of currency foreign exchange rates. I am trying to obtain in a new calculated field of Price in Euros if the amount I have in the 1st field is not already in Euros.
Say:
Field 1 Field 2 Field 3 amount currency F/rate
2000 in USD or Euro or BRL or INR 0.8754 depending what is selected in combo box.
Field 3
Result
field1 * field 3 in Euro depending currency selected in field 2
I wonder if anyone can help me out on a db i have inherited. Basically I need to add a field to a table and backfill the field with a number depending on a date field.
The new field (CallID) needs to record the order in which calls are received on a certain date (CallDate in the table). So for example the first call on 1/3/05 CallID=1, the second on 1/3/05 CallID=2. The following day CallID restarts at CallID=1.
So I'm looking for an update query which will set CallID for the several hundred existing records in the database. If anyone can help with a pointer or too, or example SQL, I would be very grateful
Hi, I know this is a fairly easy one. I really try to avoid asking answered questions, but I've honsestly searched through the forum with no results..
I have three statuses: 1 = Ongoing 2 = Complete 3 = Dropped
I want to force status 2, if IIf([EPsDL]/[TotalEps]=0;2;""), so that it would force on status 2, only when the calc. gives zero, and thus letting me change status freely, as long as the calc. is >0. I don't know whether to use IIf, nor where to put it at all. Would it work in a query, and if so - where to put it?
I am building a stock control database. I have a form called [products]. On this form are two fields. One called [ProdReorder], the other [UnitsOnHand].
[UnitsOnHand] is displayed form a subform called [ProductsSubform].
What I want to know is: What code would I enter (& where) so if the [UnitsOnHand] value is less than the [ProdReorder] value it will display the value in red?
Eg: Reorder value is 5 I have 10 motors on stock (UnitsOnHand) = Text is black. If I have 4 motors on stock = Text is Red.
How can this be done? any advice welcome. PS. my vba skills are weak.
Hi all! i am wanting to display a signature (jpeg image) when a combo box is displaying "Yes" and the picture to be hidden when the combo box is either empty or set as "No". i have before managed to change colours of other fields depending on combo boxes - but my coding isnt that great, so i dunno what code is req'd!all help much appriciated!thanksDave
Hi! I have a database and I have two groups of users: admins and users. The admins' switchboard is different than the users' switchboard. I cannot for the life of me figure out how to tell the db to open the admin switchboard if the admin logs in, or the user switchboard if the user logs in. Any suggestions? Is this even possible? I'm looking forward to your replies. Thanks!
I have a database in which 3 different areas can receive a payment. Prepay Amount, After Hours Charge, Previous Balance. Each one of these payments has 3 different payment types.... cash, credit, check. Now, when a cashier takes a payment, they have to choose whether it's cash, credit, or check, and they can feasibly enter payments into all 3 fields. The question I have, is how can I do a balance sheet whereas everything is broken down into cash, check, and credit for all 3 payment categories. Then I will need it to add up the cash, credit, and check for a grand total.
This is a basic example......each cashier handles about 100 customers per day. I don't want the cashiers to have to tab through a bunch of fields, and the cash/check/credit are on drop down lists.
The following code should sum a field depending on the date. The field IncidentYear is created and set to Last, This or "-" depending on the date. I should get three rows, one for each of the three values..
I get..... you tried to execute a query that does not include the specified expression as part of an aggregate function
I'm sure I've missed something obvious and I can't see it! If I remove the group by clause and the sum field..... it works.
SELECT IIf([Incident Date]>=#01 July 2012#And [Incident Date]<=#30 June 2013#,'Last', IIf([Incident Date]>=#01 July 2013# And [Incident Date]<=#30 June 2014#,'This','-')) AS IncidentYear, Sum(tbl_2c_Acc.[Vehicle Off Road Time]) AS VOR_Acc_Days FROM (tbl_2c_Acc LEFT JOIN tbl_Value_Proposition ON tbl_2c_Acc.Lessee = tbl_Value_Proposition.[Lessee No]) LEFT JOIN tbl_Vehicle_Type ON tbl_2c_Acc.[Vehicle Reg] = tbl_Vehicle_Type.[Reg No] GROUP BY IncidentYear;
right I have auditing system in my database that tracks any changes made to any records. it logs it on a separate table and i show it on a continuous form.
right my problem is that I have loads of check boxes and text boxes on my main form on the form that records the main form I have a field call "new value" this is a text box but if I change a checkbox on the main form it will show -1 or 0 in the new value field on the recording form but I would like it to say yes or no I have tried the following
Code:
Private Sub New_Value_AfterUpdate() If [New_Value].Value = -1 Then [New_Value].Value = "yes" End If End Sub
this only works if I enter -1 manually but the data is entered automatically after the record is saved on the main form ...
I have also tried on enter before update I get error but now I need it to change on new entry...
I have a form where we fill in information for supply of PPE to employees.
Each item must be signed for on a printed report.
I am encountering problems trying to create enough rows for my report detail for each signature of the number of items supplied.
For example, on the form I will select the "equipment" - 4 hats supplied and 3 boots. On the report I want the equipment set as the group and the detail to be a number or rows which equals the number of selected items. therefore under the Hats group heading I want 4 blank rows which are made up of 3 text boxes - Print Name, Signature & Date and another group heading for boots but with 3 lines.
The Query gives the results below: (Item name is linked to a PPE table containing Item ID, Item Name, Cost etc..)
IssueIDDateProvidedItem_Name AmountIssued 0001 01/11/2013 Gloves (Orange) 10 0001 01/11/2013 Hard Hats (Black) 2 0002 02/11/2013 Hi Viz (Large) 5
The report I aim to generate from this should look as attached ...
The query/report is set up as a parameter report so I will enter the issueID such as 0001 and only those items will appear on the report to print and sign.
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"
