Efficient Way Of Setting Value Of A Textbox To The Result Of A Select Query
Apr 19, 2005
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])); "
Between [Forms]![Benefits]![date1] And [Forms]![Benefits]![date2]
(Benefits being the form). If I enter date range 01/01/2005 to 01/05/2005 I receive all data from 01 January to 01 May - but including different years i.e 2002, 2003, 2004 etc.
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 have searched and search and I can't find the answer to this question when I thought it would be simple. Hopefully you can put me out of my misery!
I have a access 2007 split form with a series of search boxes and a query result window. Enter info into one or more of the search boxes, click search and you get the matching results in the query window below. That works fine but I want to work with the results of the query, how can I do this?
I was looking for the user to be able to select a record and then click a command button to open that record in a form. Or in another instance select a record in the result window, have the current from close and have the primary key passed to a another form which I use to open the split form.
I just can't see how to do it but I expect there are several ways. Happy to use VBA but my experience is with Excel VBA so I'm still getting used to the objects.
I have written a query to calculate how many hours we have worked for our clients. This has a number of sub-queries which sum to make different columns in the main query ie: Hours invoiced so far, Hours to be invoiced, Hours remaining from their allocation, etc. I am refreshing the queries due to a design change and now have a question..
Which is the better design:
To have those sub-queries with selection criteria from a join to the same table in each (to filter out records at a lower level) OR To have that same selection criteria and join just once in the main query? (which of course means its processing many more records but the join to the selection criteria is specified once)
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 textbox called Odours, there are four buttons below the textbox btn_Cadaver, btn_Drugs, btn_Explosives and btn_Money.
On licking one of the buttons a value will be put in he textbox i.e. pressing the btn_Cadaver will put the value "Cadaver ()" in the textbox.
The reason I want this is that all of the entries must begin with either Cadaver, Drugs, Explosives or Money. The brackets are there to allow the user to put some free text between the brackets.
I have some code to set the cursor (on click) between the brackets
Code: Me.Breed.SelStart = 9
I want to prevent the user from typing anywhere in the textbox except between the brackets.
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 new to access. I have created a report form a query and have fields with a value of "0" or "$0.00" I would like to set a static textbox to Null. I belive it can be done with the IFF function but I get a syntax error.
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.
I am building a form that uses list box selections on the form to make a temporary query and open the results in Excel.
It mostly works in just trying to make it more functional.
Currently the listbox that contains the first and last names of the personnel also has a unique shorttext 'number' as a primary key for the table (bad choice in my opinion but I didn't design the database I just have to work with it).
The short text primary key is hidden by an option given in the listbox wizard that let me set that field width to zero but still search on it. This is how I currently build the query's where clause when it involves names. I search the primary key.
I would like to update the names list box based upon which cities and locations are selected (both are multi-select).
Do I need to change how I make the "where" clause to use first and last names or an I use the same query and just hide the USER_ID
The query looks like this
Code:
SELECT DISTINCT t_asset_personnel.LAST_NAME, t_asset_personnel.FIRST_NAME, t_asset_personnel.USER_PHY_ID FROM t_asset_personnel INNER JOIN t_asset_master ON t_asset_personnel.USER_PHY_ID = t_asset_master.ASSIGNED_TO ORDER BY t_asset_personnel.LAST_NAME;
And the list box wizard hides USER_PHY_ID
By setting the width to zero
If I make my own query in the City_After_Update()
Can I do something like this
Code:
SELECT DISTINCT t_asset_personnel.LAST_NAME, t_asset_personnel.FIRST_NAME, t_asset_personnel.USER_PHY_ID .fieldWidth(0)FROM t_asset_personnel INNER JOIN t_asset_master ON t_asset_personnel.USER_PHY_ID = t_asset_master.ASSIGNED_TO WHERE [forms]![myform]![citylist].[itemsSelected]'obviously more code is needed here this is just for conceptual illustration ORDER BY t_asset_personnel.LAST_NAME;
Exit_cmdSearch_Click: Exit Sub Option Compare Database Option Explicit
Private Sub List2_DblClick(Cancel As Integer) Dim rs As Object
DoCmd.OpenForm "frmquery"
Set rs = Forms!frmquery.Recordset.Clone rs.FindFirst "[RETURN NUMBER] = " & Str(Nz(Me![List2], 0)) If Not rs.EOF Then Forms!frmquery.Bookmark = rs.Bookmark
When i scan by barcode scanner not validate data, then i get info - Bla, bla, bla data isn't validate and window with OK button.
When i press OK, then cursor stops at end of scanned number. How to select (mark all scanned number in this TextBox1 for new info scanning) after this OK button pressing?
On that form load I want to use the following SQL statement to populate that text box.
SELECT TOP 1 tbl_QA_Check_Sheets.Machine, tbl_QA_Check_Sheets.The_Date, tbl_QA_Check_Sheets.Time FROM tbl_QA_Check_Sheets ORDER BY tbl_QA_Check_Sheets.ID DESC;
I'm thinking of 2 different ways, but not sure how Access will handle them.
1) A table that maintains the start and stop date of the relationship (i.e. employee has a job title from a start date to an end date).
This is the ideal, but I'm concerned about the number of records. The database will store 3,000 employees and I'd estimate around 2000 changes a month can occur to the employee data (transfers, hires, promotions, terminations and all cascading changes on dependent information).
2) A different database for each month/year. (i.e. Employees_March2013, Employees_April2013)
I don't have concerns about the number of records, but I'm not sure how the front-end will work with multiple back-end databases. Is there an easy way to setup a form to choose which "effective date" of employee information you'd like to choose and have it link to the correct back-end at that point before running a query/report?
I am wondering if there is an efficient way to compare two fields from one table to another two fields from another table. So basically
Code:
If targetTable.Field1.Value = sourceTable.Field1.Value And targetTable.Field2.Value = sourceTable.Field2.Value Then targetTable.Field3.Value = sourceTable.Field3.Value
The problem is that I need to run this for all entries in targetTable. The only I could think of was to use 2 nested for loops (one for target table and one for source table) as outlined in the following (my data is currently in Excel, but I want to import it to Access)
Code: For i = 2 To 5754 For j = 2 To 3500 If targetSheet.Range("I" & i).Value = sourceSheet.Range("AR" & j).Value And targetSheet.Range("K" & i).Value = sourceSheet.Range("AS" & j).Value Then targetSheet.Range("I" & i).Value = sourceSheet.Range("AT" & j).Value
The above code works but it is really slow (takes about 12 mins on a high-end CPU).
I am looking to have a combobox on a unbound form to select a product code (this i can do). However I want a textbox to auto populate with the description as well - both in the same table tblproducts - product_id, pcode, pdesc....
I am totally new to ms access form development. I have to construct a form with a text box a command button and listbox. listbox contains the list of items.I have populated the list box with the items in the tables.Now on the same page I have to add new item via text box and when click command button it should be added(all these thing I have done). Problem is that I have to do some thing like if a type any letter in the text box all listbox item are arrange like that like
If Items are abcd,aaaa,aabb, aa,abb,bba,bcd,bab I enter ba in the textbox then itme should be arrange like
