Forms :: Result From A Query Being Passed To Text Box On Form
May 6, 2014
I am making a database for my work place where there is telephone counsellors and they need to complete a certain number of supervision hours and a few other categories of hours required for training and several other things. The manager wants to be able to see the total supervision hours and the other categories for a worker when the worker is selected and the date range for the queried time entered.
I have a form that has quite a few items on it. I have two text boxes that allow me to enter a start date and an end date, I have a combo box that allows me to select a worker and I have 3 text boxes that I want to populate with the sum of 3 separate columns in another table when the date is entered and the worker selected so the manager can see, at a glance, how many hours each worker has done on the separate items. Maybe I would need an update button at the bottom that, when clicked, would perform the required calculations.
I have 3 queries that return the required information but I need to get one of the columns from the query results put into each of the 3 text boxes. Basically, when I click on the buttons the right query appears, I just want column 3 (which is a sum column) to get put in the corresponding text box and I want all the boxes to display the sum of their corresponding queries when a date and person is selected.The form is called frmSearch, the 3 queries are called qry_sumisshours, qry_sumisshours and qry_sumtcshours. The 3 text boxes I need to get populated from the queries are called txt_ results_ sv_ hours, txt_results_TCS_hours and txt_results_iss_hours.
I have the need to display the return of my "Sum Query" to display in a text box.
I need the attached below value (40500) in the "SumofQuery" attachment, which updates every 5 minutes to display on the "StatusBoard" attachment text box.
I have been searching for a good 10 hours on how to do this and still cannot find it.
I have a big table, EquipmentDetails and separately I have job plans.Job plans can have many pieces of equipment. I store the relationship in EquipmentDetails_JobPlanDetails, but am feeling frustrated as I can't seem to get Access to build the SQL query from this in the way I want.
I am generating a List Box in a form which is populated from a query.The query calls on a table which lists ItemID from EquipmentDetails and JobID and in the current form (where the list box is) I want to pick up and display the ItemIDs associated with that JobID. The current JobID reaches my form correctly (I've proven this by displaying it in a text box) from the previous form.
The problem I have is that I can't seem to get the SQL query to only get ItemIDs that are linked to the current JobID. No matter what I try, it either gets all of the ones in that table, or I can't compose one.What kind of SQL do I need to only grab the ones relating to the current JobID please?I've tried building it in the design view but it says that it can't do it because the outer joins are ambiguous.
The VEH and POS are just going to be Labels in a form....no prob. Each Soldier's Squad and Team (for mounted Vehicle and Position) are stored in the Personnel Table. The below query is for vehicle A7 (ACTUAL would be the same as TC above). The query works. I just need to know how to get the result to display in a text box. What I'm planning on doing is creating text boxes for each posistion with these small select queries, so when I update the SQD and Team in the Personnel Table it updates on this form. Or is there an easier way to do this?
Dim strSQL as string strSQL="SELECT [tblPERSONNEL]![RANK] & " " & [tblPERSONNEL]![LAST_NAME] AS NAME FROM tblPERSONNEL WHERE (((tblPERSONNEL.SQD)="A7") AND ((tblPERSONNEL.TEAM)="ACTUAL"))"
Instead of the combo box value being passed into the target query as it should I *always* receive a popup that states "Enter Parameter Value"
I set my filter criteria as such in the target query: "[Forms]![Invoice_Form]![comboFamily]" but for some reason, despite there being a value in that combo box, I am always prompted for a parameter. VBA code associated with a "Generate Invoice" button on the form causes the query and the report to open.
I've attached the database, everything is dummy data.
The suspect form is the "Invoice_Form" the suspect query is the "Invoice_Query" and the suspect report is the "Invoice".
If a number is entered in the "Enter Parameter Value" field that matches the primary key of a family in the "Family" table then a report is generated correctly (as it is currently configured). For some reason it seems that the form with the combo box just cannot be accessed by the query.
Not sure if I am asking the right question, but....
I would like to pass a string variable to a query but it does not seem to work.
In the query, my criteria for the date field is (and works):
>=[forms]![frmDisposition]![FromDate] And <=[forms]![frmDisposition]![ToDate]
But, this is not working for the ID field criteria:
[forms]![frmDisposition]![ID]
In the forms code, I have a string based on the result of 3 check box. I tried using an unbound (ID) control to display the string so I know that the value of the string is correct. EX: "FW" Or "MA" Or "PD"
Is it possible to pass the value of the string to the query or do I need to try and pass the value of the unbound control to the query?
I have a continuous form it has a field (DateStart) and a field (DateStartCarryOver). There is an event on (DateStart) for the (DateStart.Value) to be passed to a new record in (DateStartCarryOver).
My probem is that the new record dosent get saved (i.e. it dosent get an ID so a record isnt created. How can I create that record once the data has bee pased to it?
I need to calculate the passed working days and show them in a form. I should be also able to use the number in a query later on. in excel I use the formula to get the days passed:
Code: =IF(ISERROR(MATCH(F10,Dictionary!C:C,0)),NETWORKDAYS(D10, TODAY(), Dictionary!$E$2:$E$43),"Status Excluded") D = "Date_uploaded" in access table "tbl_All_Cases" F = "Status_Case" in access table "tbl_All_Cases" c:c = dictionary case status E:E = dictionary holidays
"Status Excluded" will show up in the cell if a case has one of the status from the dictionary..I created a table: tbl_Dictionary where there are 2 fields: "Case_Status" and "Holidays".How can I translate the above formula into something that access will understand?
I am creating a form for 3rd-party service contracts, and I have two tables called tblServiceProviders and tblServiceDescriptions. tblServiceProviders has a 1-to-many relationship with tblServiceDescriptions; i.e., each Provider can have multiple Descriptions (real world: product offerings). So for example, ServiceProvider "Acme Corp" might have ServiceDescriptions "AcmeCare", "AcmePremium" and "AcmeInstall". My database is normalized, so tblServiceProviders has an ID field that is related to a field called ProviderID in tblServiceDescriptions.
Each ServiceContract includes, in addition to customer name and some other stuff, one and only one ServiceDescription. The ServiceDescription field in the ServiceContract form is a drop down box called comboSvcDesc and it contains all of the ServiceDescriptions in the database. I also have a non-editable text field on the form called txtSvcProvider.
What I want is to have the name of the ServiceProvider ("AcmeCorp") automatically populate in txtSvcProvider whenever the comboSvcDesc box is changed. So for example, if I change comboSvcDesc box from "WidgetCare" to "AcmeCare", I want txtSvcProvider to change from "WidgetCorp" to "AcmeCorp".My thought is that I should be running a SQL join query to get the related value "AcmeCorp",but I can't figure out how to make this work in VBA.
I have a form with combo boxes, each combo box further limits the criteria of a query as selections are made. I have a Me.Requery code for each combo box. - This is working great.
Once all of the selections are complete, I need the result of that query to show in a text box. - There will always be only one result.
I have a form with ID, town and address on it. I want to beable to type in an ID, press enter then if the ID exists in a query then populate the town and address text boxes with the correct info..
If the ID doesn't exist, then open another form so the user can choose the site from the list..
At the moment I think I am going about it the wrong way :
I have a form based on query. On form i am retrieving data from another table using DLookup in a unbound text box. So I want to save the result of DLookup function in another field/table on same form.
I have query that creates table with 2 records each with 2 columns (2x2) and they do not have indexed ID,and query is related to combo box in other form so results are not always same but it is always (2x2) and value types are always same,
So how to show those results in text box in form,lets say 4 text boxes ,every value in one text box, i assume that i need to use DLookup() but i was able only to show first record,did not know what criteria put to go to second record.
When i select that query and create report i get what i want but i cant copy those text boxes to form that i want.
Whats the best way to show the text result of a combo box selection when displaying the information through a query? I notice the table is also reflecting only the numerical ID result of the selection so I'm not sure how to take this result and display it as a text result to the user.
:D I have a form set up where a user chooses a page tab. Either statistical reports or detail reports. Then they have to choose between 2 toggle buttons. Candidate or hire.
So a user chooses detail, then candidate. An option group below that shows the different groupings of detail candidate reports that they can choose.
A couple of examples - detail report of candidates by office applied to, department, application source etc.
Once that is chosen a button is visible to continue. Now they are taken to the appropriate form. This is done by a global variable gstrformname defined in the afterupdate of the group option that they chose (office etc.)
Once they click to continue to the criteria form, they are choosing the criteria. (for example purposes) the choice below takes the user to a criteria form for choosing an application source ( email, monster.com etc) or click a button and all types are given. I have all of that set up. But now I want to change what report the command button is opening, to a variable. A variable that would be set by the below form when the type of report and grouping for the report are chosen...that defines a certain report.
I specified on this main report menu that it is a candidate report that I want, but when I get to the criteria form, I need a way to pass that choice on. I am not sure how to best do that.
I am trying to avoid making 2 copies of the same form 1 for candidates and 1 for hire. :crazy: I undertstand that I should pass a variable here.
So basically on the main form they choose candidate or hire report, then they choose group. The grouping information tells the command button what form to open (the application source criteria form, the office criteria form, etc). The thing is, this form is the criteria for the candidate report and the hire report. The command button on the criteria form would ideally be -
docmd.previewreport gstrreportname, acpreview
my question is, how or where do I define, from the point that they choose candidate or hire, to the next form, that they want the candidate by office or hire by office report? (define the gstrreportname variable as one of those too.)
Make sense?
Here is a pick of the report form (menu) http://www.geocities.com/misscrf/reportmenu.jpg
For the afterupdate of the frame for the grouping of the report, I have a select case. In that I define gstrreportname for the report it is, if the choice goes right to a report. If it goes to a form first (for criteria), I define that form with a global variable gstrformname. Can I also define the report there (gstrreportname)? If I do that, will the criteria form know that? Even if I close the main report form?
I have several Suppliers, each of whom holds several SalesEvents. At each SalesEvent I might buy none, one or more Bundles. Each Bundle then contains one or more Items. These relationships are all reflected in my Access 2013 desktop db as one-many relationships with cascaded updates.
My form structure for data entry reflects the Relationships, in that I have a Supplier form with a SalesEvents subform. On that subform I can enter data about each of that Supplier's SalesEvents, and each SalesEvent row has a button to call up a SalesEvent form which has a Bundles subform. Then each row of that subform has a button which calls up a Bundle form containing an Items subform. That Bundle form identifies the Supplier, Sales Event and Bundle number, and within its Items subform I can then enter the data for each Item.
Problem : All used to work fine, but I've clearly changed something because now when I select the Items button on the Items subform in the Bundle form, the system gives me the data for the first record in the SalesEvents table, rather than the one selected. I have checked the raw data and that's as it should be. I have tried to undo all the steps I took since it last worked properly, but to no avail. Restoring the last backup (taken when I'm sure it worked properly) still has the problem.how do I find what data is actually being passed between forms and subforms ?
I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg
In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.
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 am new to access. I have created a form in which I used a combo to show Main Area Name. this value is passed into a query to show only area under main area only. Created another combo to activate the query to show the area.
I'm having a spot of trouble trying to get conditional formatting to work. I have an overview form which displays current quotes going through the system, the QuoteStartTime field is generated from the Now() command.I Would like if possible to show, 1 hour = Green, 2 hours = Amber and 3+ hours Red.
I've tried variations of hour([QuoteStart])>1 DateAdd("h",1,[QuoteStartTime])
I've done this before with a date only field before, but working with time is taking too much time.
I am creating a driving school database and have four different tables. Student, Instructor, Lesson and Lesson Type.
In the footer of my subform which works out what lesson type the student has taken and from which instructor, I have created a calculation to multiply the number of hours a student does to what type of lesson they take.
=([LengthOfLesson]*[cost]) (This works ok)
I then want to add all of these options together. I have tried:
=sum([OverallTotal]) in the footer (This doesn't seem to work)
and then
=[Booking].[Form]![OverallTotal] to show it in the form
The name of the subform is correct (booking) and name OverallTotal is also correct but I keep getting #error message.
I am using Office 2007 and have a form that is linked to a query, also I have a sub form linked to the same query. I cant understand why both forms say in their properties that they are linked to a query but when I try and add a text box to show customer ID it tries to show data from a table
