Entering Result Of A Query In A Textbox Using VBA

Jun 14, 2005

I'm pretty sure there's a simple solution to this, yet I can't seem to find it.

I have a table that contains the Months of the year with an MonthID:
1|January
2|Febuary
...

I also have a form that contains a combobox with the Months.

When the user chooses the month, a query gets created, returning the MonthID.

I want to store the MonthID, in an invisible textbox, so that I can use it for my calculations.

If I use a textbox, I can't seem to display the result and if I use a listbox, I can't access that value.

How would I make it work?

This is the code used on the combobox:

Private Sub cbToMonth_Change()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("getMonthID")

strSQL = "SELECT Months1.MonthID " & _
"FROM Months1 " & _
"WHERE (((Months1.Month) In ('" & Me.cbToMonth.Value & "')));"

qdf.SQL = strSQL
Me.monthIDTo.Value = "getMonthID"
Set db = Nothing
Set qdf = Nothing
End Sub

View Replies


ADVERTISEMENT

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])); "

Make.Requery

Make.Value = Make.Column(0, 0)


Let me know if there is a better way to do this.

View 1 Replies View Related

Queries :: Displaying Query Result In Textbox On Form

May 19, 2013

Have a query which looks up an address using the text input into a textbox by the user.

What I'm now struggling with is getting the query result into the textbox.

Am still new to access and am hoping this is something fairly simple.

View 2 Replies View Related

General :: Show Query Result In Form Textbox Immediately After Updating A Record

Nov 24, 2014

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.

View 5 Replies View Related

General :: Entering Data And Then Seeing Result Of Calculations

Nov 13, 2014

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!

View 1 Replies View Related

To Put SQL Result Into A TextBox

Dec 25, 2005

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 ?

Thanks.

View 7 Replies View Related

Forms :: Copy Search Result Into Textbox

Aug 5, 2013

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:

Forms![quotes test].[company].Value = Me![COMBINED SEARCH subform].[company name]

View 1 Replies View Related

General :: Textbox Displaying Result Of Sum Function In Scientific Notation

Apr 3, 2014

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 .

View 3 Replies View Related

Queries :: Conditional Query To Post Result In Field And Filter Result Records?

Mar 5, 2014

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.

View 1 Replies View Related

General :: Adding Count To Result Of Query Depending On Month And Result

Aug 18, 2013

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 using access 2003.

View 4 Replies View Related

Forms :: Scan Barcode To Choose Item From Combo Box - Populate Textbox With Result

Jun 14, 2013

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.

View 14 Replies View Related

Queries :: If There Is No Result In Query Need To Have Default Result Zero

Oct 12, 2013

I there is no result in query, I need the default result zero in my form field. I only use query wizard to create queries.

View 5 Replies View Related

Entering Data Through A Query Combo

Jul 15, 2007

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.

is there any way around this.

jjames

View 1 Replies View Related

Entering Query Restriction Before Running Report

Dec 7, 2005

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.

View 5 Replies View Related

Modules & VBA :: Using Append Query After Entering New Record

Apr 5, 2015

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)?

View 2 Replies View Related

Odbc Call Failed When Entering Query Parameter

May 12, 2005

I have a query that uses an odbc table.
The query prompt for a date: [Enter Date]
After entering the date, I get error: "ODBC Call Failed"

Yet...when I set the criteria to the date I want: #01/01/2005# I can retrieve the data, no problem.

Any idea why I get the ODBC Call Faile error when prompting for the date?

Thanks....
BJS :confused:

View 1 Replies View Related

Queries :: Taking A Date From List Box And Entering It Into Query In VB

Sep 4, 2013

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

In (#1/07/2013#)

How can this be achieved?

View 5 Replies View Related

Forms :: Entering New Records In A Form Based On Query

May 8, 2014

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?

View 4 Replies View Related

User Entering Date Range For A Query With Sum Function Present

Jun 13, 2007

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. :)

Any advice would be greatly appreciated.

View 6 Replies View Related

Combo Box Query To Post Result To A Seperate Query

Sep 27, 2006

Hello, I have a combo box on a form which lists some names generated from a table.

I would like the selected name to be inputted into the 'critera' of another query called 'qryPBCustLevel' and for that query to be run.

I have tried to code this, but it is crashing at the point it trys to add the name into the query.

Can anyone help? Code listed below.

Sub cmbPB_AfterUpdate()

'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'Constant Select statement for the Query definition

strSQL = "SELECT DISTINCT tblTempPB.PB_NAME" & _
"FROM tblTempPB"

strOrder = "tblTempPB.PB_NAME;"

' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[PB_NAME] = '" & Me![cmbPB] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark

'Pass the QueryDef to the query
Set qryDef = dbNm.QueryDefs("qryPBCustLevel")
qryDef.SQL = strSQL & " " & strOrder

'Open the Query
DoCmd.OpenQuery "qryPBCustLevel", acViewNormal

End Sub

Thanks, Steve. :confused:

View 2 Replies View Related

I Want The Name To Appear Only Once In Query Result

Mar 1, 2006

Hi,

I'm designing this system in which each employee has different area of strength (i.e. Math, Languages,..)

if an employee has 2 or 3 area of strength his name appears in the query more than once. I want his name to appear once.

I tried "group by" but it gave me an error. I think I'm doing it wrong.

Please Help!

CS.

View 7 Replies View Related

ADO Query Result Set

Sep 5, 2011

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

[code]....

View 5 Replies View Related

Queries :: Use Result Of One Query To Generate Another Query

Apr 15, 2013

I have 2 tables, Event and Person Particulars.

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.

View 10 Replies View Related

Odd Averages Result From Query

Jun 15, 2007

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.

thanks

View 3 Replies View Related

Query Result To Text Box

Jan 5, 2006

Hi
I have a query with 2 fields, when it is run it returns a result based upon the result of the set criteria.

Can you get this value to be displayed in a text box after a button is pressed.

dave

View 3 Replies View Related

Single Result Query

Mar 1, 2006

Hi

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.

Thanks
Mikee

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved