I have several (about 10-15) queries that I have designed to run monthly reports. Various pieces of the criteria for each query need to change regularly. For example, in design view, I currently have a 'Where' field for each of our eight products. Where 1= include the product in the query analysis. Where 0= do not include the product in the query analysis. We would like to have a form that has entry boxes for each product where the person doing the analysis could type '1' or '0' and then the query could update the criteria entered before running.
We also need dates to update via the form. I currently have [Current Period End Date] and [Prior Period End Date] pop up boxes on each query that allow the person running them to enter their desired dates. But again, my boss wants to limit their involvement with the actual queries. Plus, you end up entering the dates for all 10-15 queries, which is a hassle. We would like to have entry boxes for the desired dates in the form and have the queries pull date criteria from the form.
If we can pull query criteria directly from a form, is it possible to create a table from a form and then pull the query criteria from the table?
I'm trying to update a record in a table, from a query that is run as part of an event from a command button on a form.
I have a table called 'Assets', a table called 'Disposals', and a form called 'Disposal Entry'. I would like the user to select an Asset ID from a combobox on the form, then when the button is clicked it adds a record to the 'Disposals' table, and updates the Status for that specific Asset in the 'Assets' table to "Disposed".
It adds to the Disposal table fine, but I can't get it to update the Asset table.
My query looks like: Field: Status Asset ID Table: Assets Assets Update To: "Disposed" Criteria: [Forms]![Disposals Entry]![Asset ID]
I've checked the spelling and everything looks ok.
The [Asset ID] control on the form is bound to the Assets table. If I edit the control and clear out what is in Control Source, then it updates the table and works fine.
However, I want to keep it bound as I have a subform on my home page showing the latest disposals.
How I can get the query to use the Asset ID on the form as the Criteria?
I am trying to pull the sum of document count from the SumTotalPerf query where the datereceived in the query matches the date on the form and the BookedInID in the query matches the BookedInID on the form, at the moment Text27 just displays as blank with no error messages displayed so I am lost as to what im doing wrong, Ive double checked all the spelling for my column names etc and all is correct.
I have a table/form [tblStdAdv] [frmStdAdv] that contains all advisers a student had during their program. that form pulls from refAdv which has:
Type 1- initial 2- current 3- previous
and then an Adviser field, per record
I need to pull the adviser onto the student's main page [tblStdInfo][frmStdInfo] either "current" (if they have had changes) or "initial" (if they went through without an adviser change) .
I would like to pull the info into the main page so that I only have to change, or add to, the one form able and the main form stays up to date.
i tried using a query and was able to pull either "2" or "1" or "both" but could not make it look for "2" Current first and if not found then use "1" Initial.
I'm querying off of a teradata table in Access. I have the teradata table linked to a table I made in Access which will only pull specific cars(example). How can I make the query pull.. like car* for every record I have in the table?For example..My made table has20120220321Q24Retc...the teradata table shows this201G101201G102201P202203A12021QP131the record means ...the first 3 characters is the car name, the middle two char is the location, and the last 2 is the day of the week.Btw in the query I use in my other querying system it has an option "Begins With" which works, I don't know if acces has this? Can anyone assist me on this? If I am unclear let me know!!
The new database that I am building will be used as a point system based on attendance. This means that I will have a main form that a few users will utilize to look up an employee's score. So far, I have a combobox (cmbEmployeeDropDown) that lists the query for each employee using the following:
Row Source:
Code: SELECT [Name] FROM MSysObjects WHERE [Type]=5 AND [Flags]=0 ORDER BY [Name];
Row Source Type: Table/Query
From this combobox selection I would like the totals from the Total row in the query to appear in textboxes on the form (txtTotalPoints, for example).
Is this possible to do or is there a better way to do this?
I have a contributor tracking table that is linked to a form of the same name. I created a make table from a query that calculates the total to date for each contributor (based on their contributor ID in the tracking table). I want to place this sum to date, in read only mode, on each contribution record for each contributor in the tracking table and on each master record in another table with the contact information for each contributor.
The contact table is in the one and the contributor tracking table is the many. If this isn't clear, I can upload the database. I essentially want to link a field from one table to a form with a different table source. The sum to date should only show for the record with a matching contributor ID.
So, I've been searching through this forum and can't seem to find the answer to this one. I would like to capture a value from a main form and have it used as a value in an append query, in order to populate a subform based on the main form, like so:
INSERT INTO tblTakenSurveys ( VisitID, SurveyQuestionID, ResponseCodeID) SELECT Forms![frmMyFormName]!VisitID, tblSurveyQuestions.SurveyQuestionID, 66 FROM SurveyQuestions WHERE SurveyID = 3;
Might help to explain some of the terms in this statement: tblTakenSurveys is where I need the new data to be entered via the subform. Forms![frmMyFormName]!VisitID is a textbox control bound to a PK in another table that has a one-to-many relationship with tblTakenSurveys. tblTakenSurveys.ResponseCodeID is a foreign key that represents respondents actual answers to questions. 66 is a value for a ResponseCodeID that stands for a dummy value meaning "data not yet entered"
As per advice I received from others on this forum, I have set a query like the above to run from a command button to populate the subform (in theory). But I'm sure I've done something wrong within the query because it will not return a value from the form "VisitID" control and therefore will not append the rows. Without the appended rows, my subform will not populate. And this has me running in circles...
I pasted the link to another thread below, where I originally received a lot of input as far as the table structure. I did not start this one, but my posts are the most recent (as of now anyway). Pat Hartman had given me a lot of the guidance here.
I am attempting to create a toggle on a form that would instruct a query which criteria to use.
Specifically they are date criteria. I want to be able to toggle between evaluating on a start basis verse a ship basis.
If the toggle is set to 'Start Date', the query would use the start date as its criteria. If the toggle is set to 'Ship Date" it would use the ship date.
The other complication is that within the query, "Start Date" and "Ship Date" are two separate fields always contained in the query, so how do I write the query so that it only applies criteria to the appropriate field based on the toggle setting?
I am working on a warehouse database and i have run into a small problem. I am using access 2010 and 2007 depending on location.
I have attached a picture of my database relation ship so you can see where i am coming from. What I am trying to do is make a main form called products and use that as the sours of the information that my queries look for and then have linked pages to my main form showing the results of that.
Example of the above would be as follows
Product on main form lets say tennis ball red then on my link forms show stock plus location and quantity
On another linked form product on main form and orders for that product and qty
I have a query that i need to run from a form. From form I need to pass criteria
1 for canada 2 for us 1 and 2 for us and Canada
The criteria needs to go into field Pricing Type. If user selects 1 then only Pricing Type 1 will display. If user selects 2 than only Pricing Type 2 should display. I user selects both , Pricing Type 1 and 2 should be listed.
I want to create a form that allows a user to enter criteria that will be passed to an existing make table query. suggestions on a user friendly book on Access 2010 programming, I'd be really interested. I'd like to be able to do more with Access 2010.
how the Forms work I am trying to put some of them in Navigation Form and my commands does not work as they did in Form."Home app" is the navigation form, "Apeluri_neinchise" is the subform of "Home app" and "Combo1" is the criteria based on which a query shows a specific data.
What is the proper formula for query criteria?
[Forms]![Home app]![Apeluri_neinchise].[Form]![Combo1] (I tried this but it's not workink)
I have a form which lets the user enter a contract ID and then a combo box that has a list of reports which run against whichever ID is entered.
What I want to know if I put the ID's in a table if theres a way to allow the user to select multiple ID's in one go and run the report against them the range rather than 1 at time?
I'm thinking about utilising the check/tick boxes would this be possible? or another way?
I have created a form that searches a table to show certain criteria. I am having trouble with a part where the table uses a checkbox and I am trying to use a combo box on the search form to return the results based on is it checked? Yes/No or both. When I test it I keep getting Runtime error 3075 missing operator.
The sql I am using is below and when I try and see where it is going wrong it highlights the line.
Code : Me.Filter = strWhere
Code: Private Sub cmdFilter_Click() Dim strWhere As String Dim lngLen As Long Const conJetDate = "#dd/mm/yyyy#"
I have a couple different reference files that get updated each week. Sometimes there are missing data elements, so I'd like to structure a select query to show me those records that have blank elements but I'd like the similar records to be pulled in as well, so I can make a determination as to how to populate the blank records..
See attached example: I have a client ID reference table that gets populated with forecast owner names (individuals responsible for the customer) from a couple of different sources. Sometimes there are names attached and sometimes the field is blank.
How can I structure a query to show me just those Client ID's that have multiple entries with blank AND non-blank forecast owners? I'd also like to exclude single/multiple records where there are only blank records...
I have a form and a subform. I want to pull the data (current record) from a field (Unit) from the main form and use it in a report query, which is connected to my subform.
I thought I could create a variable (strVarUnit) then call the variable in query but I'm doing something wrong.
On the main form in the open event I created the variable ... Dim StrVarUnit as String
On the form after updating the Unit field I placed an event ... StrUnit = Me.Unit
Hi, I was wondering if someone could check this code over. I am trying to pull a query (titled: Codes_+_Conduct:_General)which was already created in Access. Here's the code: (the red is where the error message I get is)
----------------------- <% Set objCon = Server.CreateObject("ADODB.Connection") objCon.Open connectionstring Set objRS = Server.CreateObject("ADODB.Recordset") strQuery = "SELECT * from Codes_+_Conduct:_General order by subject asc" objRS.Open strQuery, objCon IF rs.EOF Then Response.Write("<tr><td colspan = 3>No Tips</td></tr>") Else Do While not rs.EOF x = x + 1 %>
I have a sub form that shows me a companies history. This subform is used on 2 different Parent forms. The record source for the sub form uses a criteria that looks at a companyID field on the parent to determine which records to return. My question is how do I have the query criteria depend on which form is currently opened? The criteria would look something like this:
[Forms]![frmCompanyHistory]![txtCompanyID] OR [Forms]![frmCompany_Project_Details]![txtCompanyID]
If I open the form with this criteria, I will be asked for a value in txtcompanyID for whatever parent form is not open. How do I properly do this without making multiple queries that are almost identical?
with the Form, You select a Product Name (From table 1) and you can enter a date, which is entered into the Date column in Table 2. The form will also transfer the Product Name to Table 2.
Now, the problem is I can't seem to get the Catalog Number from the Form to enter into Table 2 automatically. It enters the Product Name just fine. I can, in Table 2, click the field and select the Catalog Number from a list, but that's not what I'm trying to accomplish. When a Date is entered into the Form, I'd like it to update Table 2 with the Date, Product Name AND the Catalog Number.. automatically.
is this possible? and if so, how?
I sorry I'm really new to Access, so my terminology is all out of wack. I hope the question is somewhat clear.
I've been trying to figure out why my query only pulls record Object ID 10011399 and not the other one, but I can't figure it out. Can anyone please help me see what's wrong?
Apologies if this is really basic - I am trying to write a query to show all records from table A linked to table B so that if a field is blank in table B, it will show. At the moment, every time I run the query, it only shows me records which have entries in both tables. (They are linked by ID no). ie there are 77 records in table A. Only 23 in table B but I want a query showing which ones in table A do not have something in field "x" on table B. Does that make any sense ? As ever, I'd be ever so grateful for help.
While I'm at it, does anyone know anything about getting a database to produce reminders ie based on date entered, highlight when 3 month review is due ?
I am currently trying to pull a query off from a table that needs to have 3 columns side by side, linking to the same ID number. I seem to have managed to do this by making three seperate queries for each column I need, however, when I put it into one query, it is only pulling through the data form the second column (a total of 273 rows), when it should be pulling through the data from the first column (800+ rows).
I have a textbox & button that when entered & pushed - run a query for the top values randomly depending on the value entered into the textbox.
My code is...
strSQL = "SELECT TOP " & Me.Text140.Value & " [CAN - NAME].Name, [CAN - CPT/VOUCHER].Voucher_Number, " & _ "[CAN - CPT/VOUCHER].Procedure_Code, [CAN - CPT/VOUCHER].Service_Date_From, [CAN - CPT/VOUCHER].Patient_ID, [CAN - CPT/VOUCHER].service_id, Rnd([service_id]) AS RandomNum " & _ "FROM [CAN - CPT/VOUCHER], [CAN - NAME] WHERE [CAN - CPT/VOUCHER].Service_Date_From Between " & Me.StartDate.Value & " And " & Me.EndDate.Value & " ORDER BY Rnd([service_id]) DESC "
It runs like it should but doesn't populate any records...that is until I go into the design view, deselect one of the fields and then reselect the same one - only then does it populate.
Do I need to open the query first and then update/append instead?
