Anyone know an efficient way of setting the value of a textbox to the result of an sql query?
I am using the following, but it seems to be slow when populating a large form:
Make.RowSource = "SELECT BarcodeDATA.Make FROM (Customers RIGHT JOIN CustomerSales ON Customers.CustomerID = CustomerSales.CustomerID) LEFT JOIN BarcodeDATA ON CustomerSales.ItemNum = BarcodeDATA.ItemNum WHERE (((CustomerSales.ItemNum)=[Forms]![FormCheckConsignmentStatus]![ItemNum])); "
I am building a Inventory Management Application for Tyre Shop. I have SaleMainTbl and SaleDetailTbl both used for preparing daily sale summary. I have Mainform based on SaleMainTbl with TxnDate and Total Amount (Sale) and the TxnDate is in one to many relation with SaleDetailTbl. FormSaleDetail is multiple row(continuous) form that makes billwise summary of each day having -TxnDate--BillNo--ItemSold--Company--Qunatity--Rate--Amount fields. I have inserted this form in FormSaleMainTbl.
So FormSaleMainTbl is Main form and FormSaleDetail is subform. TxnDate in FormSaleDetailTbl is automatically taken from SaleMainForm. I have further added text boxes in Main Form to show company wise sale for each day for which there is a query build one for each company that takes the currently loaded date from FormSaleDetail and calculates the Sale (Sum) of each brand (Company) of Tyres. All these objects are working very fine. However I have to close the MainForm and reopen it for result of query to appear in the appropriate text box in Main form.
Is there way to do this as soon as record is entered or at least at the end of completing the entry of each days sale transactions without closing the form. So the gist of the problem is realtime display of query result in text box on a form or updating the form screen immediately on updating any record or at the most after completing the updating of form but without having to close the form.
I want to get access to create amazon shipments of 15kg in weight made up of a variety of products (the shipment goes to amazon)
So I have a products table with all the weights per unit for each SKU in it.
I've just created a simple 'form', where I input the SKUs I want to send to Amazon & the quantity (this updates a temporary table), therefore I end up with this in my temporary table....
SKU QTY XX01 15 YY01 10
...& so on.
I've made a relationship from the SKU of the temp table (the table where I'm inputting my data to), back to the product table (where the weight per unit is held).
ok, what I seek, is that each time I enter a SKU & Quantity via my form, to see some onscreen data which tells me the ongoing sum of all SKU weights I've used. Therefore I guess my question is...what functionality in Access should I be using that allows me to input data & yet also provides me with onscreen real time data based on some 'behind the scenes' calculations? From my limited understanding, a form is to get data into Access, a report is to get data out....but I want to get data into Access & see the result at the same time!
I have a SQL query in a string of a VBA procedure (e.g. MyQuery = "select sum(Amount) from Investment")
and I want the result to be shown in a TextBox of my form Me.
I know several ways to run the query and to send the result into the TextBox (e.g. with QueryDef and RecordSet). However I guess Access should offer a more direct way to do such a simple operation.
Does anyone has a solution to do it the simplest way ?
I have a quote form that has a button which opens a simple search form with just one textbox which gives the results in a subform. (the search is for the company name and the subform results give the company name with the full address (company, add1, add2, town, county, postcode)
I would like to have a button on the search form, to copy the correct result into a textbox on the quote.Until now I have just had a cmbox on the quote with the companies and addresses listed. Unfortunately, the users are not checking this list to see if the company already exists and are adding a new company but with slightly different information, so I am getting multi companies. (i.e, smith ltd, smith limited, or Hants, Hampshire etc).
I need a button on the main search form that copies the company name from the search results subform and copies it into a textbox on the quote.I have tried this on a button but it doesn't like it:
I am maintaining an Access 2003 application that is running on Windows 7 64Bit Enterprise OS setup.
I have a form in the application that displays a number of records with each record having a unique id and a field called ClockHrs which is stored as a Number (Long Integer) type.
I have a textbox that displays the sum of the ClockHrs field.
The textbox contains no code behind.
The Control source property of the textbox is set to '= SUM([ClockHrs])'
ClockHrs 10 10 30 15 10 15 25 === 115
The textbox displays the result of the sum 115 in scientific notation as 1.15 +02 .
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'm working on a project that uses a barcode scanner. The user will scan a barcode which will choose an item from a combo box. The result of what it enters will populate a text box. I have that part working fine.
The next step is to get it to open a form based on the value in the textbox. The textbox will have about 7 different possible values and each one should open a different form.
The way this is supposed to work:
User approaches a machine and scans the barcode. He is presented with a form giving him options based on the type of equipment the machine is. A compressor for example would present him with a set of options for compressors (gauge readings, maintanence, etc).
I have an unbound form with two controls:
cboAssetNumber txtType
How to code this and which events to code it in. Users will be going from machine to machine and using the form over and over again so I need to somehow manage the clearing of the form to begin again with another barcode scan.
I saw another thread about a barcode scanner and one response was to program the scanner to send the barcode and then send an "Enter" press to move focus to the next control. I've looked through the documentation and haven't been able to find if that is possible. Right now it sends the number and that's it. It's a "Motion" Tablet.
hi, i have a query which when the user selects a value in a combo box it pulls up the other details of the item, such as price.
i have noticed though that if i try and enter a new item into the list, through the combo on the form and then enter a price, it gives me error 3101 (something to do with not being able to find the record.
i assume this is because it is trying to locate the chosen item of data within the main table and pull up a price.
I built a database off of the access wizard and it gives me the capability to enter date parameters before running my reports, than displaying the revised report. I.E. I am prompted to enter start and finish dates, and then after entering this info and hitting a button to preview the report, the only data displayed is that that falls into that date range.
I have created a new report that wasn't built into the wizard. This report "hits" an underlying query. I have also added a button on the switch manager to preview the report. I want a user to be able to click that preview report item on the switch manager, then be prompted to enter start and finish date information, then hit an ok button, then have the report with the relevant information displayed.
Any and all help would be greatly appreciated. Thanks.
The program I am working with has an option to add a new record to another table using a button. Not all the records, even new ones, need to be appended.
The append query works fine in all situations but one--when a new record is entered and saved, the append query button returns 0 records to be appended. However, if you go to a different record and come back to the one just added in the main table, the append query works fine. The query uses a TempVar to select only the record being seen at the time. The TempVar is declared prior to attempting to append the record to the other table.
Is there a way to make a just entered record act like its been around for a bit (well at least to save it by changing record to another and back)?
I have a process that lifts a highlighted date from a List Box and puts it in the Criteria of a query. The process manages to move the numbers from one place to another, but ends up giving me a data mismatch in the query. The process is as follows
Private Sub Command8_Click() 'Set it all up for Panel Meeting selection Dim db As DAO.Database Dim qdf As DAO.QueryDef Dim varItem As Variant Dim strCriteria As String
[Code] ....
The Immediate debug shows...
SELECT * FROM tbl_Panel_Meeting_Dates WHERE tbl_Panel_Meeting_Dates.PanelDates IN ( '1/07/2013' );
I'm pretty sure (althiough always stand to be corrected!) that I need it to come out as
I've created a student database with multiple tables that all relate to my primary table. I do not want anyone adding new student records to the primary table, so to make it as user-friendly as possible, I've created a form for entering new records. This form is based on a query that shows all the student information (from all tables) and now I find I cannot add new records. Is it possible to use a form based on a query for data entry? Or do I need to instead base the form on my primary table?
Let's see if I can explain this situation effectively...
I've been handed a database that is set up to track tutoring sessions of students. The original creator designed the database for 1 semester. I am trying to make it functional for x number of semesters. So here's my dilemma: I have several reports that are based on queries. I would like for the user to be able to run the report and it prompt for them to enter a date range, which would then output desired results for that range (I thought this would be more efficient than setting up the db to run reports by semester, so that the user would be able to narrow their results).
So in corresponding queries, I am adding the following criteria to the "Date" field: Between [Enter Beginning Date] And [Enter Ending Date]. The query produces the right results, just not exactly in the way I would like to see them. This is because the query tracks the number of hours per mentor (the person giving the tutoring) and does a sum.
So in my results, it is summing the hours for every change in date occurrence. For example, the results should look like this (on the report):
Student 1 Mentor A xtotal hours
Student 2 Mentor A xtotal hours Mentor B xtotal hours
Instead what I'm getting is a sum of hours for every different date, as follows:
Student 1 Mentor A xtotal hours Mentor A xtotal hours Mentor A xtotal hours
Student 2 Mentor A xtotal hours Mentor A xtotal hours Mentor B xtotal hours Mentor B xtotal hours Mentor B xtotal hours Mentor B xtotal hours
And so on...
Maybe I'm approaching this is the wrong fashion? I haven't had much luck searching google or access help... maybe I'm searching with the wrong strings. :)
I am trying to create a VB script to automate a mailing based on several query result sets from access. I have gotten to the stage that the output is correct but have a problem with the 5th and 6th record set query as they only return one record (When in fact there should be at least two for each).
I don't really understand why this is happeneing as the SQL is exactly the same as in the 2nd record set - which works perfectly. Also I've tested the SQL directly in an access query & there are no errors in the formatting that I can see... correct number of records returned.
Code: Public emailaddress, ccaddress, Subject, body1 As String Public baserow, toprow, countnumberofrows, emails As Integer Public tempdir, projectlistdir, WBPATH As String Option Compare Database Option Explicit
In an event, groups of 2-5 persons may be tagged to this event by a randomly generated number (using autonumber).
Let's say Tom (social security number: 12345X) is tagged to events 2, 5 & 6. There are of course other persons together with Tom in the above 3 events.
If I would like to find out who are the persons who are in events which Tom had participated in, how do I find them using a query?
Currently, I'm thinking of using a searchform where it would return his "associates" if I just query using his social security number, i.e. 12345X.
I am trying to calculate the average patients age from 2671 records using this SQL:
SELECT tbl_Customer_Details.DOB, CalcAge([DOB]) AS Age, DAvg("[Age]","qryAvgAge") AS Average FROM tbl_Customer_Details GROUP BY tbl_Customer_Details.DOB, CalcAge([DOB]);
why am I getting the result:
68.1131066106
I would have thought that it would have been 68 a whole number, has anyone got any suggestions why this should be.
I am new to VBA with access. Im wanting to get the result of a query called "qLastRotaDate" into a variable called "datLastRota". The query returns a single date, it is not possible to have more than one result for this query. I have tried different variations of : datLastRota = qLastRotaDate but cannot get any to work. I'm assuming this is very simple yet I cannot figure it out as am very new to VBA.
Any help would be greatly appreciated. Sorry if this has been dealt with in previous posts but I could not find any info by searching the post.