Select Record In Split Form Query Result
Mar 25, 2008
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.
Thanks in advance!
View Replies
ADVERTISEMENT
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
Mar 17, 2013
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.
View 6 Replies
View Related
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
Mar 15, 2006
Hi,
I don't quite know if I am heading down a blind alley here. I want to filter a form to show a subset of the records via a toggle button. Click again and you go back to the full record set.
The form is based on a query and the filter is to be based on a copy of the query with several criteria and sorts added. This will result in a record set of active projects comprising about 20% of all records (65 out of 253). The sorting sets the record order to match the main management report and so the Planner can update the active records by navigating through the record set rather than having to search for each record using the Find button.
If what I am trying to do is not the way to achieve the desired result, I'd be grateful for any pointers elsewhere.
Regards,
Keith.
View 12 Replies
View Related
Jun 13, 2006
Hi,
I have a query that I would like to run from a form. I have created the button and the query, but want to add in a clause that will use the user selected record / field as the criteria for the query when its run.
Got any examples of code I could have a play with
I am struggling here so need any help I can get!!
Cheers
Paul
View 1 Replies
View Related
Aug 22, 2013
I have a form that using "Query A" as data source. The Form need add (edit) a field value before save that Query Result to another Tabel. Is it possible to do that?
The PROCESS simply like below: Tabel A --> Query A --> Form -->Edit value a field -->Save to Tabel B
if it is possible, are there some requirements that have to be fullfilled?
View 2 Replies
View Related
Nov 18, 2014
I'm trying to use the job number field, which is my primary key in my table, to auto assign the new job number on my Forms. Currently, I have to enter a new job number manually, and it has to be unique because that is the way I have it set. I can't use autonumber because Access does not allow you to select what number you would like to start from, which would not play well with my current job numbers.
Basically I need Access to get the job number from the last record and add 1 to it, or just find the last / highest current job number and add 1 to it.
I created a query (qryFindJob#s) that list all of the Job Numbers, but I'm not sure how to add the query results to my Form to display the Job number + 1. I created a text box, typed a simple expression in the control box to see if I was on the right path (=[qryFindJob#s]) but I keep getting a #Name? error in the txt field.
View 7 Replies
View Related
Jan 15, 2014
Access 2013 on a Window 8.1 system.
I have a combo box based on a look-up table which I use to filter the records in the form. Seems after closing the form and reopening it, the filter remains.
I'm not sure if this is referred to filtering or restricting the records.
The value in the record source doesn't get changed but the records displayed don't reflect this. The records shown are the last filter I selected in the combo box.
The only work around I have found is I must explicitly set the record source in the on open of the form.
The was not occurring with access 2010. And I have made no changes what-so-ever.
View 5 Replies
View Related
Aug 11, 2014
I have a split DB that is in its (hopefully) finally stages of development. Using Access 2010. It is being developed both at work and at home on a 64 bit machine. The remainder of the machines are 32 bit.
All of a sudden one of my most basic sets of code doesn't want to work, but only on my machine at work. It works fine on my home computer and on other computers at work. It is the simplest of codes, run from frmNameA:
DoCmd.OpenForm "frmNameB", acNormal, , "[SequenceNumber] = " & Me![SequenceNumber], acFormEdit, acWindowNorm
The desired sequence of events is to open frmNameA, find the active clients and then click on the [SequenceNumber] to bring up the related record on frmNameB.
When I do this on my computer at work, it asks for the query criteria for the [SequenceNumber] and the criteria for another field. On the home computer and others at work, it works just fine.
In a related problem, I have the following code on the OnLoad event for frmNameB. It opens all the related records for active clients that are in frmNameB. But if there are no related records, it is supposed to cancel the event. Again, it works fine on my home computer and others at work, but not my work computer.
Private Sub Form_Open(Cancel As Integer)
'code when opened from frmNameA and no record exists in frmNameB
Dim MyReply
If ClientID = "" Or IsNull(ClientID) Then
MyReply = MsgBox("No record exits in frmNameB, Do You Want to Exit?", vbOKOnly)
If MyReply = vbOK Then
DoCmd.RunCommand acCmdUndo
DoCmd.Close acForm, "frmNameB", acSaveNo
End If
End If
End Sub
What I can't figure out is why this may be happening, where I might look for errors, and what I might do to correct this.
View 9 Replies
View Related
Jun 21, 2013
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.
View 1 Replies
View Related
Dec 21, 2013
1. I created a table that contains information about people and their details (mainly numerical info).
2. I created a form containing a command button and a label.
3. I have written a VBA script under the button so that when the button is pressed, the result of the calculation appears as the caption on the label.
My problem is...How do I get the script to run so it does the calculation for every record and places the result as a field in a query.
View 2 Replies
View Related
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
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
Sep 17, 2006
I have a simple database I am trying to put together. One table, one form, very vanilla. I created a query that ranks values that I enter in a table. I would like to put the result of the query (the rank) next to the value in the form itself. I thought if I created a text box and entered the rank field in the text box, that would be it, but when I run the form, I get "#Name?".
Any thoughts? Is this even possible?
Thanks in advance.
View 8 Replies
View Related
Nov 9, 2006
I have a query the SQL view is
SELECT Max([customerID]) AS Total
FROM tblCustomer
WHERE (((Left([CustomerID],3))=[forms]![frmNewCustomer].[txtFirst3]));
I run the query and the result is
Total
BIS007
How do I get Total into a text box on my form so that I can use it?
View 2 Replies
View Related
Aug 19, 2014
I have a bound form which is from tblEmployee, I'd like to have a dataset below (like a splitform but not a split as they have limits) so when i search in the box it gives me say all the smiths - i select for example david smith and it displays his information in the form objects above so they can be edited?
View 1 Replies
View Related
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
Jun 10, 2005
Hi All,
I hope I can explain what I am trying to achieve..
I have a "Top 1" query (Qry_Avail_StockItem), which selects the next available record in a table.
I have a main form (Frm_MasterStock) with a command button "Add Record".
If the user clicks the "Add Record" button, I need the form to go to the record that the query has selected.
Thanks in Advance..
View 11 Replies
View Related
Jun 15, 2013
Is there an easy way of entering a value in a text box, passing to a query to do a count function and then return the value of count function in to anther text box?
View 5 Replies
View Related
May 25, 2006
Does anyone know if it's possible to show the results of a query on a form? I have 4 people entering various "outcomes" into a table. There are a possible 8 different outcomes and the one i'm interested in is the "completed" outcome. I would like to be able to show on the form used to enter the data, the total number of "completes" so far.
View 3 Replies
View Related
Jan 9, 2014
I have created a search query that allow users to search existed record in database. The query work completely fine, and it gives me the data that I want when I fill in my criteria. However, the query result is not showed in a form, but show in a query window. The form is like that
Company (drop down with a list of company)
Employee (drop down with a list of employee)
Program (drop down with a list of program)
Others (some other fields)
...
Go Query (a button)
How do I pass the query result into a form that I made? So the users can edit and update the record.
View 6 Replies
View Related
Jul 30, 2006
Hi,
I have Access 2002 on Windows XP.
The last version of Access I've used was 97 but I'm getting back into it. I've read a couple of things that recommend creating a form based on a query, not a table, especially if a calculated field is involved.
When I create a select query based on 1 table, I can change/add/delete records right in the results of the select query, which will carry over to the form just fine.
However, when I use an additional table and join them in my select query, I can no longer update any of the fields that show in the query result. The link I'm using is just a 1 to 1.
How can I get around this? I'm using the second table just for lookup purposes (use the value of one of the fields in a calculation), but I want to be able to update the fields from table 1 from the form.
Thanks.
View 3 Replies
View Related
Nov 15, 2007
Let's say I have a query on a form that returns results. Is it possible to double-click a record in the recordset and open a form.
I am currently doing this just fine from a listbox, but am wondering if it is possible from a query.
View 1 Replies
View Related
Sep 2, 2006
I'm not sure how to search on this, even in the advanced search. If this has been answered, could you point me in the right direction?
I have a main form AddNewCompany (the infamous tab control that now works - thanks to your combined efforts). On it I have a command button that pops up a form of continuous forms with all the companies in the table (the query calls the CompanyID and CompanyName fields only). I have attached an image of the interface.
This means the user can see if the company already exists and doesn't enter it again. (I'm sure there are more effecient ways of doing this, but this is simple and it works. I also know key fields should be autonumbers instead of text, but I have reasons for doing it this way).
What I would like to be able to do is click on the CompanyID field and have the companies information show up in the main form.
View 14 Replies
View Related
Sep 4, 2006
Can anyone see what I am doing wrong?
I have a main form that has a tab control on it. The main form is called frm_AddNewCompanyContacts. It is opened in edit mode. To see if a company exists I use a command button to call a popup form with a list of all companies' IDs and names. I then want the user to be able to click on a commad button on the popup form to take the main form to that record. After an intial post, and subsequent search, I found the appropriate code. This is what I am using for the onclick event of the command button on the popup form:
Private Sub cmdGoToCompany_Click()
Dim rst As Recordset
Set rst = Forms![frm_AddNewCompanyContacts].RecordsetClone
rst.FindFirst "[CompanyID]='" & Me![CompanyID] & "'"
If rst.NoMatch = False Then
Forms![frm_AddNewCompanyContacts].Bookmark = rst.Bookmark
End If
End Sub
When I click on the command button I get an error message:
"The expression On Click you entered as the even property setting produced the following error: A problem occurred while Microsoft Office Access was communication with OLE server or ActiveX Control (I don't have an ActiveX Control).
*The expression may not result in the name of a macro, the name of a user-defined function, or [Even Procedure].
*There may have ben an error evaluating the function, event or macro."
View 10 Replies
View Related
