Forms :: Get A Certain Column Of Query Result Pasted To A Text Box When Open Form?
May 7, 2014How do I get a certain column of a query result pasted to a text box when I open the form?
View RepliesHow do I get a certain column of a query result pasted to a text box when I open the form?
View RepliesI 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 table that holds the SQL texts for ca. 1000 Select queries (mostly minor variants that are used to programmatically swap out RowSource strings for combo boxes). I'd like to have a quick and easy way to open/review/modify these queries.
One strategy would be to display the SQL strings in a field on a Datasheet form, then use an onClick event on a text box linked to the SQL-holding field (or perhaps an onClick event tied to an unbound text box on the form) to open the associated query. That would allow me to view the SQL of the query that I want to open, allow me to quickly scroll through the list of stored SQL texts, and give me options for sorting or limiting the SQL-texts displayed in the datasheet form.
But, I can't seem to get the onClick event to work. The problem seems to be that I can't figure out how to pass the SQL string contained in the field to a function that will use that string to open the query .
I have a BE database, that when opened, opens a form for saving the results of a query to a text file on the desktop. It works fine, if the full path is entered.
The problem is, I want this saved on any users' desktop. I did some digging and found the %userprofile% variable, which when used, gives me the error.
I understand this should work in both Windows XP and Windows 7, which are the environments the full DB will operate in. So far the "EXPORT" button on the form has the following for the code:
Code:
Private Sub BTN_Export_Click()
DoCmd.TransferText acExportDelim, , "QRY_ExportPublicComment", "C:UsersMark N. McAllisterDesktopPubComExp.txt"
End Sub
When I tried this:
Code:
Private Sub BTN_Export_Click()
Dim strPath As String
strPath ="%userprofile%desktopPubComExp.txt"
DoCmd.TransferText acExportDelim, , "QRY_ExportPublicComment", strPath
End Sub
the error occurs.
My database has Main form and a Sub form. On main form i place one unbound text box named investigations. In subform of which datasheet there is a column named TestCode. I want unbound text box (Investigations) to display row values which selected in a column (TestCode) of subform.for example:Investigation field should display "CBC,HB,ALP".
View 5 Replies View RelatedHere's the form I'm trying to Create:
VEH POS NAME
A21: TC: CPT Somebody
G: SGT Someoneelse
D: PVT Noone
A22: TC: SFC Smith
G: SGT Jones
D: PVT Doe
and so on and so forth.
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"))"
Hello again.
I've got a query that takes a part number, strips off the un-needed prefixes and suffixes, and gives me just the meat & potatoes of what I need.
I'd like to insert these results into a particular column in an existing table. Say the column name is Part_Number and the table name is CompletedWork. What would the SQL look like for that? I think this is relatively simple but my SQL skills would never be found in the same sentence as 'good'.
TIA for the help. This forum is an incredible source of information.
-Matt
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 creating a customer database for an independent insurance agency. Within that customer database, we have a lookup field that references another table "Carriers". This is where we select the insurance carrier we have placed the clients insurance with (ex. Geico, progressive, etc). This field allows multiple entries since clients can have policies with different companies.
We also have contact detail forms for each of our insurance carriers where we store information like web logins, contact phone numbers, and other notes. These forms are based on the same table as the lookup field.
I would like to create a hyperlink so, if we are looking at a clients form with all of their contact info we can click the insurance company in the carrier field and it will bring us to the contact details form of that carrier.
How to take the results of query and copy them to a column in a table?
I am using sharepoint and ms access together, and Sharpoint in this instance will not reference the result of the query. I can only use table fields with Sharepoint...major pain....
Example, I need riders name, weight and height from the column in ALL INfo query to automatically fill the column table in ALL Info
queryreservations!AllINfo.value=tablereservations! Allinfo.value
and how to loop through records that are not filled out already?
seems like it should be a simple Do.command but I am not sure how to code it..
I have one table containing name of restaurant with its address etc. Then i created another table to list out the restaurant workers names and details. Just as an example,
Table:Restaurant
Restaurant name
Address line 1
Address line 2
Restaurant #
Website
Table:StaffContact
Staff Role
Name
speciality
email
phone
I have the main form that has all the restaurant details only. And i have another form containing the Staff information. Please note the two table have a relation and it works well.
Now to make it user friendly(basically easier for the lazy ones), I dragged the staff contact form on to my main form and displayed it as a datasheet(basically a sub form).
Now, my boss does not want users to add/delete on this sub form(datasheet). So,he wants me to create buttons to open new record of staff for each restaurant(new form)
My issue is with opening a new record to enter a new person to the staff list and give them a role as well in form view.The new form has
So i ran a Macro, with open form with Where condition
Code:
[Staffcontact]![Rest Name]=[Forms]![MainForm]![RestaurantName]
But, it does not work .
If I have a Query, and I want to list the sum of one of the columns of that query in another text box on a form, is this the correct way of doing it?
=Sum([Drivers Other Shifts].[Duration])
In this case, "Drivers Other Shifts" is the name of the Query, and "Duration" is the column header. When I try this I get #ERROR, so if it's just that something else is wrong, or that my expression is the wrong way to go about it...
I have a query based on 2 tables, joined on Memberid, the result showing :
Table1 Table1 Table1 Table2 Table 2 Table2
Category Association Memberid CustomerName E-mailAddress MemberID
Board Member(Lookup,integer) SAMGA(Lookup,integer) 44 Smith smith@abc.co.za 44
Board Member ADHTY 44 Smith smith@abc.co.za 44
Grower SAMGA 44 Smith smith@abc.co.za 44
I only want to show 1 row, based on the duplication of E-mail address. I know i should be using the row_number function, but cannot get to the result I want.
Query is based on 1 table" tblTimeCnv_AgeGroups
Fields:
AgeGroup Time Ranking
30-& under 11.22 1
30-& under 10.41 2
30-& under 9.22 3
30-39 11.32 1
30-39 9.53 2
30-39 9.34 3
30-39 9.30 4
See attachment
My Ranking field is:
Ranking: DCount("*","[tblTimeCNV_AgeGroups]","[AgeGroup]=" & [AgeGroup] & " and time <= " & [time])
In the Ranking column the result is: #error in the first 3 rows then zeroes
i want to open a report but only showing the result of one record in a sub form,
i have a field that is on all rows of the subform,[click to run] and what i want the user to be able to do is double click on this field and it will open the report with only the record information for that row displayed.
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
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.
hi,
I hope someone can help me here..
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 :
Private Sub ID_AfterUpdate()
intResult = DCount("*", "Q_findsite", "")
If intResult = 0 Then
Dim stDocName As String
stDocName = "Sites_listbox"
DoCmd.OpenForm stDocName, ,
End If
If intResult = 1 Then
Dim rst
--THIS PART FAILS WITH A RUNTIME 3061.. ?
Set rst = CurrentDb.openrecordset("Select town,address FROM Q_findsite ")
[Forms]![AddFault]![Town] = rst.Fields(0)
[Forms]![AddFault]![Address] = rst.Fields(1)
End If
End Sub
the Q_findsite query is expecting a parameter for ID = [forms]![addfault]![ID]
Is there an easier way of doing this ?
thanking you in advance.
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.
View 8 Replies View RelatedI 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.
I am trying to make use of the OpenForm command to open a form to a record where a textbox matches the value of a text box on another form.
On Form1, I have a textbox called txtRtnRef, and it contains a reference number in a similar format to SWR-9
On Form2, I have a textbox called txtReOrderRef (control source ReOrderRef) which also contains a reference number in the same format (SWR-9)
I am trying to have a button next to the text box on Form1 that has an OnClick Event that opens Form2 to the record containing the same text reference number.I have tried, to no avail, a number of variations of the OpenForm command, the most recent being
Code:
DoCmd.OpenForm "Form2", , , "ReOrderRef = '" & Me.txtRtnRef & "'"
The form opens, but to a blank record?
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.
View 7 Replies View Relatedi 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 am a novice ACCESS user, so my questions may be elementary. In Access 2007, I have a query that presents the user with a list of the contacts he/she is approved to access. I want the user to be able to "drill down" to the detail form for a specific contact by double clicking on his last name, but I have not been able to find a property sheet for the last name field in the query to which I can apply the code.
View 14 Replies View Related