And it works great, I click a button next to the record I want to select (records displayed on continuous form) and it opens the Lead Details form on that particular record.
However now I am trying to accomplish the same thing, except instead of a continuous form I need to draw from a continuous sub-form. The form "BrokerMgmt" contains a sub-form named "BrokerSearch." The RecordSource for the sub-form is set once the user enters data into a few textboxes on the main form and clicks the search button:
Code: Private Sub brkSearch_Click() Dim argCount As Integer On Error GoTo Err_Handler If IsNull(brkFirstName.Value) And IsNull(brkLastName.Value) And IsNull(brkCompany.Value) Then MsgBox "You Need To Select Some Values", vbCritical, "Lead Tracking"
[Code] ....
The sub-form then displays basic info such as Name, Company, State, and the "BrokerID" which is the primary key of the "Broker" table. This "BrokerID" is displayed in "Textbox 20" so I set a button next to each result to on_click perform the procedure:
Code: DoCmd.OpenForm "BrokerDetails", acNormal, "Point_To_Broker2", , , acDialog, """" "Point_To_Broker2" is set up in the same style as "Point_To": Field: BrokerID Table: Broker Criteria: [Forms]![BrokerSearch]![Text20]
However instead of the BrokerDetails form opening to the appropriate record I get a msg box asking for a parameter. Why is this? Even if I enter the correct BrokerID as the parameter it still gives me an error saying you can't go to the specified record.
I have a form that displays an updatable subform. The main form has search fields and a search button that when clicked will reset the record source of the subform and then do a refresh. The problem is that after the search the subform stops becomes locked. Here is the code:-
Me!sbfDepartmentSub.Form.RecordSource = MySQL Me.Refresh
Code: SELECT ContainsTracks.albums, ContainsTracks.tracks FROM ContainsTracks WHERE ContainsTracks.albums LIKE [VALUE IN TEXTBOX1 FROM MAIN FORM]
As you can see in the brackets, I would like to use the value in textbox1 on my main form in this query (so I can select only the tracks from the album that is typed in textbox1, disregarding the others).
I have a SubForm "assignments" based on a Query, which has criteria to filter dates and also to filter 0 and 1 of the checkbox ...
The question is:
How do I put in that SubForm one or more Checkbox to "enable" and "disable", only the criteria of such query? So, toggle, for example, those jobs that are not completed (Checkbox of the query=0) and those that do ...
I have saved query object named qrySearchBill. I wan to call this query through vba and display the result in a subform named subQrySearchBills in datasheet view. Here's how I want it to work:
When the main form loads, I want all unfiltered records to be displayed in the subform initially. The user may then decide to filter based on date range, so he enters startdate and enddate parameter values in their respective textboxes in the main form. Then click search button to run the saved query based on the date range parameter taken from the textboxes.
I have this code so far:
SQL of the saved query object:
Code: PARAMETERS [StartDate] DateTime, [EndDate] DateTime; SELECT tblInvoice.BillNo, tblCrdCustomer.CstName, tblCrdCustomer.CstAddress, tblCrdCustomer.Island, tblInvoice.Date, Sum(tblInvoice.[TotalPrice]) AS Amount FROM tblCrdCustomer INNER JOIN tblInvoice ON tblCrdCustomer.IDNo = tblInvoice.NameID WHERE tblInvoice.Date Between [StartDate] And [EndDate] GROUP BY tblInvoice.BillNo, tblCrdCustomer.CstName, tblCrdCustomer.CstAddress, tblCrdCustomer.Island, tblInvoice.Date;
vba code to call the query and its parameter:
Private Sub btnSearchBill_Click() Dim qdf As DAO.QueryDef Dim rst As DAO.Recordset
Set qdf = CurrentDb.QueryDefs("qrySearchBills")
[Code] ...
This code works fine except that when the main form loads, a prompt window appears to ask for the value of dateStart and dateEnd. I don't want it to prompt because it's suppose to get these values from the main form's textboxes (txtStartDate and txtEndDate respectively), plus it should initially display all the unfiltered records.
I have a form, a couple of comboboxes and text boxes on it. When these are filled out, the SQL of a query is changed using these parameters.
There are three subforms on the form, all pivot charts, all based on the query being changed.
The goal would be to update all three according to the user-given parameters.
Right now the subforms only update if I close and open the form, which is probably not the best solution, since it's too slow.
I've also tried to requery and refresh them, with no result.
Then I tried to overwrite the recordsource of the subforms with the same text that was originally there. This got them to refresh their data, but then all of the charts disappeared and had to be built again, so this is a no go too.
I have a username combobox and a number combobox filtering a subform. If there is a userName but no number I want to pull all the records for that userName regardless of number and vice versa with a number and no userName. And if they both have something then both are being used for the filter.
I have been trying iff statements and isnull in query criteria and cant seem to find what I need.
I have a database that tracks Construction projects. Each project can have multiple Managers, so I have a many to many relationship set up with tblProjects, tblProjects_Managers and tblManagers. I created frmProjects and sbfrmProjects_Managers. sbfrmProjects_Managers has a combo box control and is continuous. Its record source is tblProjects_Managers, its control source is ManagersID (the foreign key in tblProjects_Managers) and its row source is SELECT tblManagers.ManagersID, tblManagers.Managers FROM tblManagers ORDER BY tblManagers.Managers;. The bound column is 1, the column count is 2, and the widths are 0";4". When adding sbfrmProjects_Managers to frmProjects I made the child-master link with ProjectsID, the PK of tblProjects and the other foreign key in tblProjects_Managers.
I need the Managers to be able to do a search that will return only the projects that they manage. I've tried making a query composed of tblProjects, tblProjects_Managers, and tblManagers. I used the field ProjectsID from tblProjects and ManagerName (with the criteria Like [Manager's Name] & "*") from tblManager. The query worked when run by itself, but when I use it as a filter in a macro to open frmProjects, I get two prompts to enter a value, one that asks for tblManagers.ManagerName and one that asks for Manager's Name. No matter what I do I can't get only the second dialouge box to appear, and regardless I no matter what I enter into the two dialogue boxes my results aren't filtered at all.
I have two combo boxes in a subform that use lookup queries. I can get the combo boxes to work correctly out of sub form in a regular form but can not get the combo boxes to work in the sub form. I have narrowed the issue to the logic in the queries in the secondary and tertiary combo box queries. Here is the part of the secondary query. I think I have to add the primary form name to this part to correct my issues. How would I do that?
primary form = Lookup_fm sub form = master_tbl_sub_fm
I am currently building a main form with a combo box control, two subforms and a hidden text box (optional). There are many different references to be made between tables, queries and forms to get to the result I am after, and I have tried many different codes from other people’s queries that seemed close to mine; however, had no luck getting my subforms to work as I’d like.
The names of my controls/forms/queries are as follows…
Main Form: frm_UReport Subform 1: sbfrm_Query1 (refers to Query1, which refers to and calculates on tbl_C) Subform 2: sbfrm_Query2 (refers to Query2, which refers to and calculates on tbl_S) ComboBox: cmbo_ProductList (refers to tbl_ProductList) TextBox: txt_ProductCode (refers to cmbo_ProductList, column 0)
I want the two subforms to run their respective queries after a Product Code has been chosen from the ComboBox. The relationship between the tables is one-to-many, from tbl_ProductList to each of tbl_S and tbl_C; with ProductCode as the primary key in tbl_ProductList.
Currently, I can choose a Product Code from the combo box, and it populates the text box successfully, but nothing happens in the subforms.
The codes I have worked with so far are as follows…
sbfrm_Query1 (Record Source): SELECT Query1.ProductCode, Query1.PurchaseDate, Query1.ExpiryDate… FROM Query1 WHERE (((Query1.ProductCode)=[Forms]![frm_UReport]![txt_ProductCode]));
sbfrm_Query2 (Record Source): SELECT [Query2].[ProductCode], [Query2].[ProductDescription], [Query2].[PurchaseDate], [Query2].[AverageCost]… FROM Query2;
[Code] ....
I have started working on integrating sbfrm_Query1 into the main form first, which is why the code looks different between the two subforms. Once I have it working, I will translate the same format to sbform_Query2.
Due to all the different levels of references that need to be made, I am having trouble identifying which (or if all) of the codes are incorrect.
I have a form that has five subforms on it. On Open all the subforms are unbound (so i can prevent the queries from running before the On Open event to speed up). Then i can enter my criteria for all five subforms in txtbox and click run. After I establish the source object for the subforms, i cannot establish the controlsource for several text boxes that pull the data from the subform, i get #Name?. her is a sample from the on click event... the top links the unbound subform and the bottom should then link a control in the subform to a control on the main form.
Me.Child167.SourceObject = "query.RP Sum Fuelman F1" Me.Text71.ControlSource = "=[RP Sum Fuelman F1 Subform]![cntRecords]"
I have a simple form that has a subform with its datasource being a query that is a multi-table select (complex joins)... the subform is set to allow dataentry, edits, etc. I have a checkbox that displays a value from the query (0, -1). Is there a way I can enable this checkbox to write back to one of the tables? Temp table to hold the contents of the query?
Database that will show both his side income information as well as his regular income and expenses. I have constructed a tables for the categories, his rec/payables which has information for both his personal & business, the other table he wants to filter only information regarding the business. I set up a form with a subform (business). I need the combo box in the main form to auto populate the matching fields in the business subform, everything but the client name and id. I have tried creating a query for criteria business, it works in query mode but not when I try to use it on the form.
The main form: IncSrc IncAmt ExpSrc ExpAmt ActionDate
The subform IncSrc IncAmt ExpSrc ExpAmt ActionDate ClientName ClientID
We currently manually run 5 different queries then copy and paste this data into 5 separate tabs on 1 workbook, I'm trying to automate some of this process if possible.
I am trying to use the 'transferspreadsheet' action within a macro to run a query and post it into a template excel file, using this code:
Trasfer Type Export Spreadsheet Type Excel 8-10 Table Name (query Name) FIle Name (FIle location) Has field names No Range Blank ---- This does seem to work and puts the data on a new tab on the specified workbook.
However I have a few questions:
1. Can you specify which query gets put onto which tab in excel? The tabs have different fixed names.
2. Can you specify which Cell the data gets pasted into to? As each tab has a set of headers and titles which need to remain.i.e would need to get query 1 to start in cell A4.
3. How would you expand the above out so that it runs all 5 queries, would you just add in multiple transfer spreadsheet actions in the same macro?
I am trying to add calculations to queries based on columns in the query... it seems to randomly expect 'Expression' or 'Group by' as column types, and Im having to create 3 sets of queries following on from each other to de-dupe data and allow filters on calculated values.
Also I've got a function which turns a date into a quarterly cohort, e.g. Oct 2013 -> 20134. I use ot on a lot of dates. I created a VBA function, CohortQ used as follows in queries:
Code: Function CohortQ(InputDate As Date) As Integer If InputDate = 0 Then CohortQ = 0 Exit Function End If
[Code] .....
But when I run it on a date field, it gives me a data mismatch error. I can't step through as it's working on 600K rows. If I put the function into the query,
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"));
Basically, what I'm looking to do is, when someone clicks on either command button, then the unbound subform will display a datasheet of the table in which the information is looked.
I have a table and ran 2 query's on the table.. One for showing all records with the department "Admin" and one to show all records with the department "Collections".
So when clicking on the command buttons, it shows these query's in the subform.
Was wondering what code to put behind the buttons to do this. Or even if there is any easier way to do what I'm looking for.. I will have about 10 departments, and each button will run a query, is this the best way to do this. ?
I have a MainScreen Form in my Database which links to a subform giving a note of the Updates on the account. I have set this up as a Single Form with Navigation Buttons to move through these. All of this is fine except whenever you open an account it always shows the first update for the account.
Is there any way to change this so that the Navigation starts on the last account rather than the first. I know I could filter this but then you could not just flick back to look at previous updates.
I've made a subform which is based on a query. One of the fields from this "Many" table is "Product Type", and the query asks to list all the product types except "Delivery" (in other words, 'not "Delivery"'). I don't want to see the Delivery items in this list.
The trouble is, the subform no longer has the blank row with the asterisk which allows you to add a new record into it.
I need to have a subform query embedded in my main form that when the user selects an item from a drop down box it will display the query results in the sub form. I have my query set up to work off of this drop down box I just can't get it to update on the subform when selected. Any ideas?
I am making a form that will be used to enter info, then spit out an employee's pay for the time period. On the form are some goals or qualifications (I am assuming I will have to make queries for those but thats another story) that, if reached, bonus' will be added to final pay. The form will look up records for a specific date or time period for an individual and determine the amount of the bonus. I was wondering how to get a form to show the records in a subform like view. In other words, I want the user to be able to select say, phone calls made on the 10th of last month, then on the right hand side that record would show up just so he could see. (this is more beneficial when there is a date range but for ease sake I am going with one date right now) I am thinking it is a subform/query but am not sure and would like some help b4 I start wasting time on the wrong thing.
