Select Form Record Set By More Than One Query
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 Replies
ADVERTISEMENT
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 4 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 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
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
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
Dec 1, 2007
I have database of roughly 15000 records. The records have a unique number and the majority of searches etc work OK. The problem is with a form used to display record details with various sub forms embedded on the form.
Scrolling through displays the correct details.
Trying to use a combo box to determine the record selected does not work correctly.
The combo box is made up of 4 columns of "NAME", "COUNTRY", "ROLE" and "UNIQUE NUMBER"
Typing in the combo box auto fills in the NAME until the correct one is found. If the name is unique it opens on the form correctly. But if the name is not unique it does not always open the correct record.
Is there any way of being able to use the NAME to search through the combo box entries but use their unique number to determine the record selected.
A further problem arises as there are apostrophes in the NAME as well
View 3 Replies
View Related
Feb 28, 2014
Attached is a snippet from my search form.
I have a search on Last Name, and in this example, I use Last Name beginning with "m". That pulls up two matching, filtered results, Mickey and Mimi.
All the controls in the detail section are disabled because I don't want the users to make any edits here. I want them to click on a row and that will take the Record Number from that row and open up a Detail form. This is where they will make their edits.
I've set the On Click property for the Detail section to run a procedure. The event procedure works fine if the user clicks on the selected row. Notice the record selector in the picture is pointing to Mimi. If the user clicks on Mimi, everything works fine. If the user wants to open Mickey's record, they have to move the record selector first and then click on Mickey (2 clicks). If they don't move the record selector first, nothing happens.
Is there a way to change the selected record when the user clicks anywhere in the row? Again, all the controls in the row are disabled, so this doesn't happen automatically like it normally would. I considered putting a command in the On Click property, but that event procedure seems to only run if the user clicks on the selected row. I just need the selected row to change if the user clicks on a row other than the currently selected row. How do I do that?
View 7 Replies
View Related
Nov 13, 2013
Form - record source - select a table that when the user enter a value in the field of the form it gets place into the designated table (select a table). my problem is that I have two fields in the form, one field from the data entry is suppose to go to the train table,and another field the data entry is suppose to go to the station table. if I have the form's record source have a designated table for the data entry.It doesn't have two tables for the data entry.
View 13 Replies
View Related
Nov 22, 2006
Upon closing my frmInventory the amount stock of stock is checked against a minimal stock value. If the stock amount is below a set minimal value a subsequent form is opened telling you that stock is low and an email message is generated to notify a manager. I have a checkbox on that form which is set to "True" upon close using an update query. The checkbox is there to give users the option to either send or not send a reminder message that stock is low when a message has already been sent earlier.
The problem is that using that update query ALL records are set to "sent=true" and not just the 1 record I intend.
This is my code in the "on close" event:
DoCmd.OpenQuery "qryUpdateEmailMinimal_True
and here's the SQL:
UPDATE tblInventory SET tblInventory.emailSentMinimal = True;
I assume what is missing is a reference to an inventoryID number. How do I do that?
View 3 Replies
View Related
Feb 22, 2005
Hello all, I have a couple questions. First I am trying to create a select query that will show the results that I have two fields , one is "Date" & the other is "Current" For the date I am using the a between statement "Between [start] and [end]. Under the "Current" I have "Applied" & below that I have "Overage" The problem is when I enter my date range, it shows all records with the Overage or Applied, but if I go & remove either "Overage" or Applied, then it works fine.
Is there somthing that I am missing
Second question is when using a find command box, is there a way to have it search multiple tables, if so how would I set the find box to search both tables, if the table names were called Let say Table 1 & table 2
Thanks!!!
View 1 Replies
View Related
Jan 3, 2014
I have created a form (races) with a subform (yachts in a race) to edit data in joined tables. It seems to be working well but I am having difficulty selecting which record to edit in the main form. I can scroll through the records until i get to the correct race but this won't work well when there are a large number of races to choose from.
I can see that I can use linked forms to select a race from a multi form or datasheet and call up my desired form by a double click on the ID field. This should work but isn't really elegant with users scrolling down a long datasheet to select the race record they want to work with. Is there a better way?
Races are uniquely identified by 3 attributes: SeriesDivision, Date & RaceNumber.I have created an index in the Race table for these 3 fields which forces them to be unique.I would love to create an interactive routine which asks users to select from a list for each of these 3 attributes and then returns the form for editing with the appropriate race record selected or even cascading - users select a SeriesDivision can then choose the valid dates for the selected SeriesDivision and can then select the valid raceNumbers for the selected date
View 1 Replies
View Related
Jul 3, 2014
I have a form that allows the user to select a record in a combo box. When this selection is made I want a 2nd form to open and give the user a place to enter additional info about that item selected. I have the forms working correctly but now I would really like to add the 2nd form to the first form as a subform but when I try this I get the message"The expressing is typed incorrectly or is too complex to be evaluated..." There are no fields on the first form that link directly to the 2nd form - because the combo box in an unbound combo (the selection in the combo box is what the 2nd form opens base on) I tried to add this as a subform with no parent/child info and I get the message stated.
I was hoping to make the 2nd form not visible until the selection was made in the combo box then make it visible for the info to be entered. Would really like it to be on the same form not as a separate form opening.I believe I just found that the reason for the message. It seems not to be a subform problem but is because the selection in the combo box needs to be made BEFORE the form can open. So now the question is - how can I make this a subform and avoid this error when the main form first opens. I will test the visibility and see if not visible until the selection clears this up.
View 10 Replies
View Related
May 12, 2014
I have a list box bound to a query. If the list box retrieves two records, I am not able to select only one of them. If I click, it gets both records selected ( highlighted ). Is there any way I can select only one record?
View 8 Replies
View Related
Nov 30, 2013
Based upon a specific date (varDate), I want to select the record that is active (who drives the lease car).
row 1 user X from 13/11/2013
row 2 user Y from 15/11/2013
row 3 user Z from 17/11/2013
I want to find the active user on a date. So on date 13/11/2013 (=varDate), user X is active, on 14/11/2013 (=varDate), user X is still active, on 15/11/2013 (=varDate), user Y is active, on 16/11/2013 (=varDate), user Y is still active and on 17/11/2013 and later, user Z is active.
View 1 Replies
View Related
Aug 25, 2015
I have 2 tables:
Query_Rates (Actually this is the result of a query):
Unit
R_Date
Sold_Rate
A
24-AUG-15
145
[code]...
So what I want is a list of all the records from the Query_Rates table where the absolute differences between the sold rates between Query_Rates & [Sent till date] (matching the unit # and the dates) is greater than 1 and the record shouldn't be displayed if it is already present in the [Sent till date] table. But if you notice the first record which has unit A is already mentioned in the "Sent till date" table and shouldn't be repeated again in the query result.
The desired out put should be:
Unit
R_Date
Sold_Rate
X
25-AUG-15
200
View 3 Replies
View Related
May 6, 2014
I have a subform containing a list of Funds and attributes such as Asset Type, Fund Manager, etc.
Currently, I have a textbox, where the the control source is set so that it will be updated with the Asset Type from the subform.
I also have an unbound combo box that contains a list of Asset Types queried from a table via row source, where user can select the Asset Type.
What I would like is when a record is selected from the subform, the Asset Type is selected on the combo box as a default value. User can select another Asset Type if required. How can I do this?
View 1 Replies
View Related
Aug 20, 2007
Hello everyone,
I have a query that runs from the main form when the command button is clicked. It displays the results in datasheet view. I want the user to double click a specific record and jump to that specific record in the main form. How do I do this? Sorry, if this is a dumb question. Please help, my boss is breathing down my neck. :eek: The main form is called Contracts. Each record has a unique ID.
Thanks,
Jason
View 11 Replies
View Related
Aug 9, 2014
I have a form which displays the records from a query (i have included a picture of this form) and allows me to filter them and create a report from the filter.
I'd like to be able to select a record with the record selectors (or preferably without) - and then go onto edit it in a new window and save the edit...
If possible also to select a record with or without record selectors and click a button to delete the record
searchdb.jpg
View 1 Replies
View Related
Oct 29, 2013
I have some code for a button on click event to open a form and select the record which corresponds to a value in an unbound text box. The code is:
Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmOpenPatientRecord"
[Code] ....
This works fine when I put in a 10 digit NHS number but opens a blank record when I enter a four digit or six character/digit PatientNumber. Both patient number and NHS number are text fields in the underlying table.
View 14 Replies
View Related
May 11, 2006
Hey Folks,
I have a form with five combo boxes on it and one button, when each combo box has an option set and I hit the button, it runs a query which looks up the values of the form items and gives me my chosen information.
However, if these fields are: FirstName, Surname, State, Town and Road, and I want to only select on 4, 3, 2 or 1 of these - it gives me nothing.
So how do i specify that if the combo box is left blank just to select all.
So by putting data only into FirstName, say Eric, it will select ALL Surnames, states, towns and roads where the firstname is Eric.
View 2 Replies
View Related
Sep 14, 2006
I have written code to write a query with parameters set from selections made in a multiselect list box on a form.
I now want to add a check box for possible fields to include in the query and put these on the bottom of my form to allow users to select which fields they want to be displayed in the query result.
Can anyone advise how I can do this?
Many thanks,
Gary
View 1 Replies
View Related
Dec 17, 2013
I have a form with split view and data it taken from different table using join query, is there any way i can add edit form data and original table data gets changed.
View 2 Replies
View Related
