Possible To Pull A Query Into Form Design?
Jul 4, 2014Is there a way to pull data into my form so that I can see current stats about my table in the same box as where I'm entering new data?
View Replies
ADVERTISEMENT
Forms :: How To Design A Form That Can Pull Multiple Invoices Data
Apr 17, 2015how to design a form that can pull multiple invoices data related to the same purchase order number?
View 14 Replies View RelatedModules & VBA :: Can't Pull Form Text Box Value Into Query
May 21, 2015I have a perplexing problem. I'm creating a simple db to enter borrower audit checklist data with the following tables:
Questions -- A list of 17 pre-defined audit questions, with fields QNum and Question
Audits -- One record for each audit (pk AuditKey is an Autonumber), and some borrower fields
AuditQuestions -- Linked to Audits, with pk of AuditKey and Qnum, and a Question and Answer (yes/no) field
The Audits form has the Audits table as its datasource, and an AuditQuestions subform. When I go to a new Audits record, there are initially no subform records attached. When I enter a borrower name, the field AfterUpdate event runs the following code:
Code:
DoCmd.OpenQuery "LoadAuditQuestions"
Me.AuditQuestions.Requery
The SQL for LoadAuditQuestions is
Code:
INSERT INTO AuditQuestions ( AuditKey, QNum, Question )
SELECT [Forms]![Audits]![AuditKey] AS Keyval, Questions.QNum, Questions.Question
FROM Questions
ORDER BY Questions.QNum;
I start out with both Audits and AuditQuestions tables empty. When I run the code by entering a borrower name, I get, "... can't append all the records in the append query ... didn't add 17 records due to key violations". It acts like it's getting a null value from Forms!Audits!Auditkey, but if I select the Debug option and check the value from the Immediate pane, it shows a valid number. Also, if I run the query manually with the form open, it loads the questions correctly.
The first Audits record is loaded with questions in the subform so you can see the desired results. To see the problem, go to a new record, enter a name, and hit tab. Another piece to the puzzle is, if you do this on an existing record, it works fine: Click No in response to the error message that appears, then click End on the Debug message. Go to a different audit record, then come back to the one you just created. Change the name, and it works correctly. It's as if it doesn't know what the AuditKey is the 1st time, but if you check it in debugger, it is loaded. In fact, I put in code in the AfterUpdate event to plug the LoanNumber field with the Forms!Audits!Auditkey value, and it worked, but the subsequent query still failed.
Queries :: Query Criteria To Pull Its Value From Control On A Form - IIF Function
Aug 16, 2014I want the Query Criteria to pull its value from a control on a form.The form control either has data or is null. (My problems occur when the form control is Null). The field in the table either has data, is null or is blank.
Code:
=IIf(IsNull([Forms]![FormName]![FormControl]),"" Or Is Null,[Forms]![FormName]![FormControl])
This works for the records with fields that are blank.
Code:
=IIf(IsNull([Forms]![FormName]![FormControl]),"",[Forms]![FormName]![FormControl])
This works for the records with fields that have data.
Code:
Like IIf(IsNull([Forms]![FormName]![FormControl]),"*",[Forms]![FormName]![FormControl])
This works for the records with fields that are Null or Blank if i drop the iif function but then i lose the ability to pull criteria data from the form control.
Code:
"" Or Is Null
Queries :: Form To Select Parameter - Causing New Field In Query Design
Jun 16, 2015I have a query with multiple fields that is being run off of 3 parameters (linked for selection in a form). The problem is, I wanted to enable a select all feature, so I included a "Or ... Is Null" part in my criteria section, so that when nothing is selected, the query/report returns all records.
Okay so the problem is whenever I run the query with nothing selected for the parameter and then return to design view for the query, a new field has been created in the query design, titled with the expression I use to pull the parameter value from the form. This is frustrating because then that is causing errors in another report I run that pulls values from that query.
Query Design.DB Design Question
Sep 10, 2007Hey,
I've got this problem and im 95% sure its going to need a query in order to achieve this answer im looking for.
I'm creating a Software Licensing Management db and its all working lovely. However my only problem remains is the graphical representation (text box within one of the forms) of howmany licenses are/aren't(doesnt matter if this number is a +/- number) available.
In order to achieve this answer I dont think you'l need the table structure of any of my tables other than these two:
tblLicenseInformation
License_ID
LicenseDescription
NumOfLicensesPurch
SoftwareOverview_ID
tblHardwareSoftwareLicense
HSL_ID
Hardware_ID
Vender_ID
Software_ID
AppEdition_ID
AppVersion_ID
SoftwareOverview_ID
License_ID
However I would like to add a column to either of these tables named 'Availability' or something similar which will show the licenses available.
I've tried a number of Update/Append queries but all have failed. I want this Available running variable held within the table due to it not changing to much of the current db design as i baisically finished the project and they asked for it! any ideas of how to efficiently achieve this will be much appreciated!
cheers
Design View Keyboard Shortcut - Expand Columns In Design View Of A Query
Jun 5, 2014I'm looking for a keyboard shortcut to expand the columns in "design view" of a query.
What I mean by this is rather than selecting all of the columns and double clicking to see the entire text, I'd like to be able to a shortcut.
The entire process as I see it involves 3 steps so I will need thesolution to the 3rd step.
(1) [ctrl+spacebar] to select initial column
(2) [shift+arrows] to select all of the columns I need
(3) [keyboard shortcut] will expand all of the columns "field" names to the size of the column heading
Alternatively, if you know of a shortcut that will expand the columns without having to select them first I'll take it!!
Ok Pull From Form Part 3
Aug 15, 2007Code:INSERT INTO [Status Log] ( Status, Edit_Date, Event, Claim_ID )SELECT [Status Lookup].Status, Now() AS Expr1, "3rd Party Denial" AS Expr3, [Claim Report Info].[Claims Header].Claim_IDFROM [Status Lookup] RIGHT JOIN ([Claim Report Info] LEFT JOIN [Status Log] ON [Claim Report Info].[Claims Header].Claim_ID = [Status Log].Claim_ID) ON [Status Lookup].status = [Status Log].StatusGROUP BY [Status Lookup].Status, Now(), "3rd Party Denial", [Claim Report Info].[Claims Header].Claim_IDHAVING ((([Status Lookup].Status)=[Forms]![claiminformation]![ReportForm]![reportstatus1]) AND (("3rd Party Denial")="![claiminformation]![ReportForm]![txthiddenvalue]") AND (([Claim Report Info].[Claims Header].Claim_ID)=[Forms]![claiminformation]![ReportForm]![Report_ClaimID]));
here is my current query.
i'm trying to append data to a table from my form.
"![claiminformation]![ReportForm]![txthiddenvalue]"
but since it's my value from txthiddenvalue isn't anywhere in my query how do i get this to work?
Pull Down Menu In Query
May 22, 2006I would like to build a query and ask it to choose a boat name out of a list of pre set names. When the pop up box appears to type the name, i would like it to be a combo box where the name can be pulled from the list. can this be done? thanks in advance.
- Andre'
Pull Down Query List
Nov 1, 2006I was wondering was it possible to have my queries in a pull down list and I hit a button and the query/report is ran? Or do I have to stay with about 6 or 7 buttons on a form?
I know it sounds crazy.....
Query To Pull First & Last Record?
Jan 4, 2007I am having trouble. I have a log table and I am trying to pull two records at once. The records im trying to pull is the most recent record update as well as the original record.
Example:
I want to pull for Toysrus the first log intry for this company when I first put the record in. And I want to pull the most recent update that I did for this company. Is there a way to pull both records at the same time? The first and the last?
Any help would be appreciated. THANK YOU SO MUCH!!!!!
Using A String To Pull Up A Record On A Form
Feb 28, 2005Sorry if this has been asked before but I have searched and couldn't find an answer
I have a string that is set on startup (username) and have another form(hidden) that loads on startup this form contains information on what forms and options the user can access. Just having a little trouble using the string to pull the correct record, the information in the string would be from the primary key field on the table.
Thanks
Ryan
Pull Down Box And Locked Text Box On Form
Jul 6, 2006On my first form I have a pull down box and when I select an item and click the add button, the item appears in a locked text box above the pull down box. After I click the submit form button, it goes to another form. On the other form is a similar pull down box and locked text box. So far I got the item selected on the first form to appear in the second form's locked text box.
Again, the second form also has a pull down menu and when I select another item from the pull down box and click the add button on the second form it deletes the first item selected in the first form from the second form's locked text box. The items selected are put in a table. I was wondering if there was some kind of code I can input so the second form's locked text box keeps the first value from the first form and can add additional values when selected through the second form's pull down box. Sorry if this sounds confusing. I really appreciate your help. Thank You.
Query To Pull Past 3 Months
May 22, 2007I have a query that I need to always pull data from the newest past 3 months, so if this query were ran today it would take from months 2,3,4 (Feb, Mar, Apr). Any clue on how to do this?
View 14 Replies View RelatedEliminating Duplicates From A Query Pull
Nov 3, 2004Hello,
I am building a query that pulls from one databases but I am running into duplicates.
Here is the skinny:
From the [tbl_databases], I am setting criteria that restricts a column containing “Visio”, more specifically, “Not Like “*Visio*”. I am also setting criteria that restricts a column containing “OLD” (it is a Yes/No field).
.
The problem is that there are duplicate rows that contain both “Visio” and “Old. So, simply suing my criteria restriction are pulling out the Visio and Visio/Old orders as well as Old and Old/Visio order.
Can I create a subquery that contain the combination of “Visio” and “Old” to restrict the subset from being extracted twice?
I think I have the logic but not sure not to set it up.
Thanks
Tuktuk
Query Updates Pull Down Menu
May 5, 2006Greetings,
I have a table with the following 2 fields: Make and Model.
When I am in data sheet view I have Make set up as a pull down menu with the various distinct options (i.e. Ford, Chevy, Toyota, etc.). I would like to set up the table so that when I click on Ford, for instance, it will query a different table (carclass) and output only the Ford vehicles on a drop down menu for the Make field to choose from. (i.e. Mustang, Freestar, Focus, etc.)
For Model, I have the following code for the Row Source:
select model from carclass where make=make(from the user input of the make).
Any help or suggestions would be greatly appreciated.
Thank you in advance for your assistance.
Cheers.
John
How Do I Pull Subform Data To A Query?
Sep 26, 2007Hello,
I have a form with a subform. I have the subform running a update query on a command button in the subform. The subform has the textboxes and I cant seem to get the query to pull the data from those textboxes. I have used the normal [forms]![form_name]![textbox_name] in the past but everything I try to pull the subform textbox data is not working.
I have tried
[forms]![form_name]![subform_name]![textbox_name]
[forms]![form_name]![subform_name].[form]![textbox_name]
[forms]![form_name]![subform_name].[form].[textbox_name]
Nothing works. What is the correct syntax for this? Any help would be great !!! Thank you in advance.
Joe
Pull Out Unique Records Form Two Fields
Sep 23, 2007I have this basic question (obviously not so basic for me): how to pull out only unique records from two fields.
More details - two fields with names of competitors in a tournament (Winner or Loser) and i need to build a query to have all players names participating in the tournament regardless if they have won or lost in one list.
Queries :: Pull Date Field From A Form
Jul 22, 2015I have a query that is pulling a date from a Form. In my Query Criteria, I can put:
Code : >=[Forms]![frmAdhoc].[Date]
or
Code : <=[Forms]![frmAdhoc].[Date]
or
Code : =[Forms]![frmAdhoc].[Date]
And it works fine, but I don't want to hardcode the ">=", "<=" or "="
I would like the user to be able to choose ">=", "<=" or "=", from another field on the Form, so I am trying to code it on the query like this:
Code:
IIf([Forms]![frmAdhoc].[Variable]=">=",>=[Forms]![frmAdhoc].[Date],IIf([Forms]![frmAdhoc].[Variable]="<=",<=[Forms]![frmAdhoc].[Date],IIf([Forms]![frmAdhoc].[Variable]="=",[Forms]![frmAdhoc].[Date])))
But it isn't working for the ">=" or the "<=". It just gives me a blank result.
How To Pull / Push A Value From Current Form To A Function Using VBA
Dec 26, 2012Using the code below I am able to open an explorer window from access and select most of a file path. The part I am not able to get is "LN" which is a field in my forms. I need to be able to pull the current LN number into my Modules file path. How do I do this? Is it easier to go by ID or the actual field I am looking for?
Code:
Function Loan_Folder_Search3()
Dim rs As Recordset
Dim LN As String
Dim Client_Name As String
Dim RetVal As String
Dim LFPath As String
[Code] ....
Currently, it keeps pulling the first record, no matter what record I'm on.
Query To Pull Most Recent Account Balance
Nov 6, 2007I am trying to run a query to select the most recent balance from a table that lists balance by date and account number. Basically I need to select the most recent balance for a given account number. Here are the details:
Table: CBALANCE
=========================
ACCOUNTNO
BDATE
AMOUNT
What works: This gives me the most recent account, but I cannot get the amount
=========================
SELECT Max(CBALANCEMIB.BDATE), CBALANCEMIB.ACCOUNTNO
FROM CBALANCEMIB
GROUP BY CBALANCEMIB.ACCOUNTNO;
What I want to work: This returns results which should not be in the result set (older balances)
==========================
SELECT Max(CBALANCEMIB.BDATE), CBALANCEMIB.ACCOUNTNO, CBALANCE.AMOUNT
FROM CBALANCEMIB
GROUP BY CBALANCEMIB.ACCOUNTNO, CBALANCE.AMOUNT;
Am I trying to do something too complicated for the query language?
Query To Pull Most Recent Account Balance
Nov 6, 2007I am trying to run a query to select the most recent balance from a table that lists balance by date and account number. Basically I need to select the most recent balance for a given account number. Here are the details:
Table: CBALANCE
=========================
ACCOUNTNO
BDATE
AMOUNT
What works: This gives me the most recent account, but I cannot get the amount
=========================
SELECT Max(CBALANCEMIB.BDATE), CBALANCEMIB.ACCOUNTNO
FROM CBALANCEMIB
GROUP BY CBALANCEMIB.ACCOUNTNO;
What I want to work: This returns results which should not be in the result set (older balances)
==========================
SELECT Max(CBALANCEMIB.BDATE), CBALANCEMIB.ACCOUNTNO, CBALANCE.AMOUNT
FROM CBALANCEMIB
GROUP BY CBALANCEMIB.ACCOUNTNO, CBALANCE.AMOUNT;
Am I trying to do something too complicated for the query language?
Queries :: Query To Pull ALL Records Whether Blank / Zero Or Value
Jun 24, 2013I have two tables: Table #1) [Request Table] Customers request our company to stock a certain Part Number with a minimum value. Ajax - P/N "NP4i" - Min "30".
Table #2) [Sales History] All sales purchased and the "Qty-Total" purchased. For simplicity Fields are limited.I created a CrossTab Query that displays from [Request Table] P/N's requested/ min values (Row Headings) then showing P/N's purchased [Sales History] Value="Qty-Total" Sum and
Column=Format([Invoice Date],"mmm/yyyy") group by, showing last twelve months.
Ex: Customer name, Part Number, Minimum, May/2013, Apr/2013, Mar/2013 and so on.
AJAX, NP4i, 30 4 21 5 and so on.
The Crosstab query list only requested Part Numbers that have at least One sale wihin the last twelve months.If there are NO sales, the crosstab query will NOT show this Part Number.My boss wants to see the requested Part Numbers that were never purchased along with the ones purchased.He wants to address the NO sales, yet they requested we stock that particular Part Number for them.
Is there any criteria I can use to force the Part Number field on the [Request Table] side to populate even though there were NO sales within the last twelve months?
Queries :: Query To Pull Previous Record
Jan 20, 2014I have a form that is used to book a new event for a client who is already in the database.Within that form I have a subform that is based on a query which displays information from that client's previous event. I did this using a solution found in the following thread: URL...It is based on pulling the second to last record that is related to the current client. It works perfectly when booking a new event that has taken place on a previous date. However, if the previous event occured on that same date (but at a previous time), it doesn't register.
I would prefer a query that would pull the record previous to the current one, instead of pulling the second to last record out of all that client's events.it would also mean that if more events are booked, then a past event is opened in a form, the sub forms in that form will display the event just prior to the current record.
Queries :: Adding Pull Down Menu To Query
Jul 28, 2014I have a query built that gives me info per employee. I built a question into the criteria that ask for the employees name. When a person types the name results are given. If the person mispells the name, results are not given.
I have everyones name on a seperate table. How do i create a pull down menu on the query to choose the names?
Queries :: How To Pull All Records In One Query If A Value Exists
Mar 18, 2014I have one table let's call is table1. It contains about 5 columns with standard employee data with about 10,000 rows of data containing about 1,150 employees. All employees have multiple rows of data. There is one column titled JOB. I need to pull all rows of data for each employee ONLY if there is at the minimum one value for the employee in the JOB column. I do not want to pull employees that have no values in the JOB column. They can be excluded from the query.
Example of all record for one employee. I need to pull all rows of data ONLY if the employee has a value in the JOB column.
Name EE_ID JOB CAT EFF_DATE
John Doe 1006 CLERK F 01-JAN-2010
John Doe 1006 P 21-JAN-2010
John Doe 1006 CLERK P 01-FEB-2011
John Doe 1006 F 01-MAR-2011
John Doe 1006 P 01-APR-2011
John Doe 1006 CLERK F 01-JUL-2012