Forms :: Multiple Different Queries As Recordsource For One Form
Apr 5, 2014
I have a query that I am using as recordsource for a form. I also have a list box that I am using to change records on the form.
My issue is I need to use the same form for similar queries. For example, I want to open the form with only "Manager1" records displayed. So I need to do a different query. I don't want to use parameters or have employees type the manager in. It needs to be just a button they click.
So my two options are - make 10 copies of the form each with their own new query recordsource for each manager, or use one form and dynamically input the recordsource before the form opens..
I want to do that -- but when I try the list box gets all screwed up. Because the list box is relying on another SELECT query to populate. I have 10 buttons each with a different recordsource query feeding into the form when I click but I get so many errors in the fields when the form opens.
In Access 2010, I'm writing VBA code that assigns an ADO dataset as the recordsource for a form. The query itself varies and is constructed from a search term typed in a textbox by the user. The code below works perfectly until the dataset is empty, when no records satisfied the WHERE criteria. In that case, there is a very long pause (presumably a timeout?) and I eventually get the error message "ODBC call failed." I tried to get around this by testing the number of records in the dataset (see toward the end) before assigning it, but it makes no difference in the behavior, so I'm thinking the actual assignment isn't the issue ... something else is.
If it matters, the "FROM qry_beneficial_owners" in this case is predefined query local to my Access db. That query is based on a linked view from a SQL server.
Code: Public Sub RunSearch(SearchTerm As String) Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim sql As String Dim cols As String cols = "[acct], [acctname], [planid]"
I would like to populate form fields with records from a table/query so that they are bound to the recordsource and the record to be displayed can be selected from a combobox. I am hoping that in this way, any changes made are propagated to the original table.
I have created a combobox from which the primary key can be selected and added all the fields from the table appearing on this form as values.
In my Change event for the combobox I have this code:
Private Sub cboID_Change() Me.RecordSource = "SELECT b01_Participants.*FROM b01_Participants WHERE (((b01_Participants.ParticipantID)= " & cboID.Column(1) & "));" Me.Refresh End Sub
However, I receive an 3075 error when this code runs which seems to relate to my syntax or an extra ")".
I currently have a Job Management System where the Customer Order Form displays all jobs via a main form and an order details subform. This form is used for inputting new jobs and for viewing existing jobs via links from other reports such as a job schedule or customer orders report. It is always reached via a clicking of a "New Customer Order" Button where the form is filtered to new record or via double clicking on an order number in another screen and filtered via the order number.
I am now in a position where the database is growing too large and I want to archive the data in the Orders / OrderDetails Tables into Financial Years and put them in a separate backend database. However the jobs will still need to be viewed via the customer order form in order for them to be duplicated the following year.
I have created a union query of the PreviousFY and Current Orders to allow the "search jobs by customer" query to work. This works fine, but I now need to make the record source of the Customer Order form dynamic.
I have created a CurrentFY query to feed the form if it is a current job and a PrevFY query if it's from the previous FY.
I have written code to check the OrderDate against the financial year to work out which database the job is from and set recordsource according to that result. This is currently in the OnCurrent event as I also have other code there that checks details of the order to determine if fields should be editable.
However I am finding that when I parse the order data between the forms the "new record" command or the order number is not being retained or checked. If this because I don't have a recordsource set when it opens?
I tried setting the recordsource as the currentFY on open and then do the check but that didn't work.
I've got a DB, which has 3 forms ( all 'under' 3 navigation buttons/tabs ). The main form ( Client's form ) is set to show first, and currently has a recordsource of about 1350 records. I think the size of the recordsource, and the fact it 'loads' the other two forms all 'in one go', is slowing it down ( between 5 and 10 secs. to load ).
I read I should set the Visible properties of forms, not shown immediately, to false, only setting them to True in the On_Load event - when Tab is selected. Ideal I thought. However, I can't find this property in the Form's property sheet.
I have a simple add form that will add an entry to one of two tables: Box 1-1, or Box 1-2.
They have the exact same fields. In the add form, I want the user to specify a Rack field and a Box field. Box 1-1 would be Rack 1, Box 1. Box 1-2 would be Rack 1, Box 2 (there will be more tables later, but just using two for now until I get it working).
But right now my form always adds to the table Box 1-1 - I can't get it to switch.
I've added the code I have so far below. The first part is what I'm having trouble with, the second part just uses a Submit button to add a new record - I'm just including it in case it's interfering in some way I'm not seeing. I'm running Access 2007.
Code: Private Sub Switch_BeforeUpdate() If Me.Rack.Value = "1" And Me.Box.Value = "1" Then Form_Add.RecordSource = "Box 1-1" ElseIf Me.Rack.Value = "1" And Me.Box.Value = "2" Then Form_Add.RecordSource = "Box 1-2"
I have a form with a (large) number of controls which acts as a kind of read-only dashboard of data. The data is split across a number of tables (4, for now, but may grow) I've created a query which amalgamates all of the data from the tables into a single dataset and set that as the RecordSource (Snapshot) of the form. I now want to bind each of the controls to a particular field returned by that query.
However - I have a combobox (same form) which allows users to select a date and I need the controls to update with the relevant data for that date whenever the combobox is changed. The query already returns values for each field over a range of dates so the data is available within the form's RecordSource - I just don't know how to include the date criteria, as specified by the combobox, when binding each individual control?
Now, let's say the user wanna choose Annually Method.
So, he will choose to run Step 1 - Age (Annual Mode) & Step 2 - Premium (Annually) and delete the other 2.
Of course, you may say "Why don't you just double click?" I don't do so because there are like 10+ Steps to do.
So i need the form to run these queries 1 by 1 according to the sequence. And it needs to be flexible as the users may choose different combinations of steps to run. Is it a way that i could use the form to run these queries?
I have a form that I want to filter by different fields on different occasions. i.e., I might look at all records that have a review date of earlier than today on one occasion and I might look at all records that have a Yes in a Yes/No box on another occasion (date then being irrelevant.)
I have finished the form which has a query as it's Record Source.
The only way I can figure out how to accomplish my goal is to copy/paste the form with a new name, copy/paste the query that is it's record source, with a new name, and then put the criteria I want in the new query and make that query the new form's record source.
Is there a more elegant way I can do this. For example. I have a form consisting of buttons that I use as a Navigation Form. Could I, in the VBA that opens the form when I click on it's button, stipulate which Record Source the form would use?
Is there a way to set the recordsource for a form before it opens? I've got Me.recordsource = "QryNewCaseForm" on the On Open event of the form but I toggle between recordsources once the form is open and if I close it while the recordsource is "QryCaseForm" it won't open as "qryNewCaseForm" the next time I open it.
I have a recordclone combo box for navigating between 240 company records, so the form will go to the correct record on updating the combo. Instead of clicking on the down button and scrolling through the list, I would like to enter letters into the box and update the record source automatically as I enter them.
I'm not sure what events to use since there appears to be very subtle differences between them (eg between 'on dirty', 'on change' and 'before update').So when I type in 'T', only those companies that start with 'T' are viewed in the combo box.I have discovered the VBA 'dropdown' command which would be useful for the user.
Code:
company.dropdown Table_Company Company_ID: autonumber Company_name: text
The name of the form is "form1" and the combo box name is "company".The record source for the combo box is "query_company". In the form the ID field is hidden and the company name is not hidden.The criteria I have for the combo would be something like this.
Code:
SELECT Table_Company.Company_ID, Table_Company.Company_name FROM Table_Company WHERE (((Table_Company.Company_name) Like [forms!]![form1]![company] & "*"));
so I have a commandbutton on a form, when I click on the button I would like an inputbox that lists the subforms and subforms recordsource/ controlsource.
I am assigning a recordset using vbs code. The variable "CB_proj" is a combobox value. The problem comes when the combobox is null, the program errors out and even if I try to exit the routine with an if statement, it still compiles the line of code and errors out because the object doesn't exist. how I can get it to not compile that error if my combo box value is null?
I didn't have this problem when I had used subforms, but there were little bits of thins I had to polish. There are basically 2 tables, and the form is based on the table PKing. It shows the PK and a few irrelevant fields. However, when the recordsource now inner joins the two tables based on the PK, it only turns up 1 record.There are no filters, and nothing is spelled wrong. I checked all of the control sources, and nothing's off. What am I doing wrong?
I want to write Visual Basic language in the form with one button:
I have one form named "setForm" with one button and one text box: Let's set the new table "frm2", the new form "frm2"
Dim sql As String sql = "SELECT [model].* INTO frm2 FROM [model]" CurrentDb.Execute sql DoCmd.CopyObject CurrentDb.Name, "frm2", acForm, "frm1" Forms!frm2.RecordSource = "frm2"
The last statement code doesn't work: It is said that Microsoft Access cannot find the form "frm2" referred to in a macro expression or visual basic code.
But I don't know what to put for an expression. I would like to use the same form but base the RecordSource on a query called "Receiving Search by Date". :eek:
I have a subform within a form that displays filtered data based on dynamic selection criteria which is assigned to the RecordSource/OrderBy of the subform. On this form, I have a "Print Results" button -- OnClick, I would like the RecordSource/OrderBy of the subform to be copied to the Report, then run the report. What I have that doesn't work is:
I need to be able to update tblManifestData with a new manifest number and manifest comments, along with assigning it a TSDF. how to be able to enter a new manifest number and the associated data without having it create two lines in tblManifestData. I thought that I could enter a new manifest number, then requery the table and form so it shows the complete list of manifest numbers (including the recently entered one) while staying on the newest entry.
Currently I have over 600 columns I need to use in a query and since the limit is 255 columns per query, I need to create multiple queries. Currently the form shows the record source of the single query I have created. Now that I need to create the other queries, how do I have the forms record source to recognize the other queries as sources as well?
I have a form with about 10 subforms on it, all of which have queries as source objects. All of these queries are based off the same table which contains a field for Customer. What I would like to do is have a toggle button on this form that, once pressed, essentially adds a "not like" criteria to each of these subforms' queries EXNot Like "Customer_A"). When depressed, the queries/subforms should exist as is.
My first issue is that when I add a toggle button, the control wizard does not start. It starts up fine for any other control, however. For example, I imagine that on the After Update event of the toggle button I would requery each of the subforms, but I'm not sure how to add the "not like" criteria to each of the underlying queries.
I have multiple sub forms and want to add specific text boxes of different sub forms into one another sub form. Then all sub forms are incorporated in one main unbound form.
I am trying view the query output's in different text on the form and I do not want to see them again in query window.How do I hide this.. comments ???
I have 2 main tables. One with Data that I get from an outside source and cannot be changed. The other is one that needs to be updated as needed. I have a few look up tables so I can identify the codes with the descriptions. In 2003 I was able to make a big query and link my forms to the query to make modifications. I am not able to edit any information in the 2010 query. The people want it to work the way it did in the past but I am not skilled enough in access to do that. Is there an easy way to use a query when updating the form? I used to be able to edit the query itself and it would feed back into the existing tables.
I am trying to set up a query by form with multiple criteria.For the majority of the criteria I'm using the format: Like [Form]![formName].[txtInputboxName] &"*" .
I copied the Like (...) &"*" from someone else in order to allow for multiple, optional criteria, which does do the trick, however I don't understand why.
I now want to set up a criteria on my query to search for values between two input values to gives the records in the range e.g between 50 and 100 Following the above format, I want to put something that achieves this:
Between Like [Form]![formName].[txtInputboxName1] &"*" AND Like [Form]![formName].[txtInputboxName2] &"*"
with Inputbox1 being 50 and Inputbox2 being 100, however that code doesn't work and I don't know how to get around it as it keeps giving the 'incorrect syntax' error.
