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.
1 I have a delivery method column and I want to only show 1 type for each method. How would I do that? Is it in expression builder?
View 1 Replies View RelatedI 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 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 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.
Hi Folks
More questions from the newbie :eek:
I have a very simple table with a field that will have only the values open or closed
I created a query based on this table and used the count function (to count open or closed cases) and in the next querie field I used the group by open.
This gives me a count of all open cases
I can do the same for closed cases.
I want this count to be displayed on a Switchboard form but i cant get my nehad round how to get it there.
Can anyone help me out ?
Many thanks
Jimmy
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
I currently have a search form that when criteria is entered it then opens up a query with matching results. What i would like to have it do is have a macro that runs the query so that it gathers the results and then displays the results into a form with a textbox called search results.
View 2 Replies View RelatedI 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.
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.
I have a form, which is bound to a table, with an unbound textbox.
I am trying to get the number of months an employee has/had worked.
In the table are two dates, Company Start Date, and Resignation Date.
There are three scenarios in which I need to calculate the months:No Resignation Date
A Resignation Date in the future
A Resignation Date in the Past
See attached PDF
The Employees are under a one-year contract which ends the day before the one-year anniversary.
I originally tried using DateDiff in the Control Source, but the IIF seamed too limited for my needs.
Code:
If [Resignation Date] = "" Then
If Month([Nichii Gakkan Start]) = Month(Date) Then
If Day([Nichii Gakkan Start]) < Day(Date) Then
MIS = DateDiff("m", [Nichii Gakkan Start], Date) - 1
[Code] ....
MIS = The name of the textbox in question.
I'm simply trying to display the data from an open form named frmEventInput fields: [EventName] and [Description] on a new form that includes 2 text boxes with the control sources and names: [txtEventName] and [txtDescription]
It says Error 2448, You can't assign a value to this object!
Private Sub Form_Load()
Dim LResponse As Integer
LResponse = MsgBox("Do you wish to open 'Event Participant Registration Form'?", vbYesNo, "Continue")
If LResponse = vbYes Then
DoCmd.GoToRecord , , acNewRec
[Code] ....
I have a simple database with a query that "SUM"s an amount.
I created a form with a sub form that is linked by Account number. I want to be able to open the form in datasheet format and it to display the SUM value from the query.
When I open the form it responds with #Type until I click the + symbol to open the linked query then it updates the #type field with the data from the query.
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 RelatedI have a Login form, I set a query retrieve information from Login form's Text3 (username)
After that , if the user Successfully logged in then the Login form will close .
I set the query because i need to retrieve what is His/Her Department .
But , after I closed the Login Form , the query result disappear ...
Then I try open Login Form again and login again ... the result come out again ...
How to solve this problem ? Everytime the Form closed the criteria cannot work ad ... It will prompt me the parameter box want me to type again the criteria ...
I have a Inventory DB and i want to integrate scanning. So far i have a table called StockInfo, this table holds information about bar codes (device type, make, model). I have a form called Scan_Barcode, on this form I want to be able to scan a bar code into a text box (text0) and use a afterupdate and requery function to display the results onto a form called EnterInventory.
Not only just the results but I want certain columns from the query to display in certain text boxes on this form. I have a query called FindProduct. that filters the criteria by whats entered on the Scan_Barcode text box (text0). So when i scan a certain bar code i can get the query to display certain product information for the bar code. I just cant get the results onto the HarwareStock form.
how do you take a date from a textbox on a form into an Append query and increase the date entered into the textbox by 1 day.This is what I have so far but not working??
MealDate: [Forms]![FrmSwitchBoard]![txtweekend]=DateAdd("d",1,Date())
Hi all,
First timer here, so please forgive any daftness on my part. I'm a novice to access setting up my first database. This forun has been brilliant and helped me a great deal. However despite lots of searching I've not been able to sort out this problem.
I have a Form (New Client Details) with a primary key (ClientID), bound to a table (Client Details) and a Subform (Episode of Care Subform1) with a primary key (EpisodeofCareID) bound to another table (Episode of Care). They have a Master/Child link e.g. a client can have multiple episodes of care.
Using a search method I found on this forum I have created a search form (Client Search) which searches on First Name, Surname and Date of Birth. The search is operated by a command button (CmdSearch) with results shown in a Listbox (SelectSearchClientInfo). So the list could contain a number of entries for the same client if they have had multiple episodes of care.This works fine, however I also wanted to be able to select from the list and display all details for that selected record on the 'New Client Details' Form. I've used code found on this site but when I run it the form opens but will only display the first record for that particuler client. This is the code I've tried.
Can anyone help me out and show me whay I'm doing wrong?
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "New Client Details"
stLinkCriteria = "[ClientID]LIKE" & "'*'&" & "'" & Me.[ClientID] & "'" & "&'*'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.OpenForm stDocName, , , "[ClientID] = " & [Forms]![Client Search]![SelectSearchClientInfo], , acDialog
Many Thanks, This is such a good site!!
John
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.
Hi,
I have a form with a textbox and I want to fill that text box with the calculations from values of other text boxes. However when I put the equation
= Sum (box1 + box2 + box3) into the textbox's control source I don't get any results even though box1, box2 and box3 have values in there.
I also need the result to be saved in a field on my table, but I got no idea how to do it.
Please help!
Thank you
dfuas
i I have two queries.. What i'm hoping is to combine the result into one query but not in one column only but instead the result of the second query should be beside the first query.. The result of the second query should be added as a new column.
First Query
SELECT tbl_uSers.UserName, Count(tbl_rEceived_eMail.EntryID) AS eMailReceived
FROM tbl_rEceived_eMail INNER JOIN tbl_uSers ON tbl_rEceived_eMail.UseriD = tbl_uSers.UseriD
GROUP BY tbl_uSers.UserName;
Second Query
SELECT tbl_uSers.UserName, Count(tbl_rEceived_eMail.EntryID) AS eMailProcessed
FROM tbl_rEceived_eMail INNER JOIN tbl_uSers ON tbl_rEceived_eMail.UseriD = tbl_uSers.UseriD
GROUP BY tbl_uSers.UserName, tbl_rEceived_eMail.ProcessedYN
HAVING (((tbl_rEceived_eMail.ProcessedYN)="Y"));
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]
I am designing a book collection database where each book can contain multiple authors. I used three tables; tblBooks, tblAuthors, tblBookAuthorJOIN. The tblBookAuthorJOIN allows me to create a subform with drop down combo boxes in the Books form linking multiple authors to each books indexed field. The Author table contains the usual FirstName, LastName, MiddleName fields. I would to be able to display the names of each author in the forms header but I am having difficulty.
For example a book may have two authors such as:
Authors table would contain values such as
Last Name: Grisham
First Name: John
Last Name: Twain
First Name: Mark
Textbox in header would display: John Grisham; Mark Twain
The authors would be displayed in the following format in the same order as listed in the subform datasheet.
Attached is a graphic of the form.
I have a combo box which queries a table and then stores said tables primary key field in a field for my main table. The table queried has two fields; one for the primary key and the other for the string, such as "chris".
My problem is when I run new query from the main table it shows the bound column, which is the primary key, instead of the string column which would display my name. I can't figure out if there is code that I should deposit in the criteria field that would tell the query to display the name field (ala "Chris") instead of the bound column field (ala primary key).
Do I need to add something to the criteria field or run an expression that says display column 1 instead of column 0 in your quey?
I currently have the sql below...
UPDATE tbl_Node_List INNER JOIN qryUpdate_P1 ON tbl_Node_List.Zip = qryUpdate_P1.[Zip Code] SET tbl_Node_List.[Date Sent] = (SELECT [MinOfAudit Date] FROM qryUpdate_P1), tbl_Node_List.[Date Recv'd] = (SELECT [MaxOfAudit Date] FROM qryUpdate_P1)
WHERE (((tbl_Node_List.Zip)="35243"));
but I get an err.msg stating the operation must use an updateable query.
What I am trying to do is update my dates in 'tableA' with the max and min values stored in 'tableB'. I have read some of the posts but still can't quite get it. As always all help is appreciated.
I'm struggling with the correct syntax for this, but ideally what I want to achieve is:
SELECT (SELECT Count(Status) FROM P_T WHERE 'criteria are met') / (SELECT Count(Status) FROM P_T WHERE 'different criteria are met')
They are both fairly simplistic select statements, so I imagine this is not that difficult to achieve, but I'm not sure how to structure the query.