Queries :: Passing Results From A Form Into Query Errors
Nov 14, 2013
I am working on a database that contains patient demographic information. I have a form that prompts the user to enter either the medical record number or part of the patient name. Once you click search it'll then display a 2nd form with a list box outputting the results. Then from there the user can click on one of the entries in the listbox and it'll display the full demographic information on a 3rd form.
Now my problem is on the search part, it completely ignores if I have a medical record number entered. It continues to search by name only.
In my query for med rec # I have
[Forms]![frmSearch]![txtHistn]
where txtHistn is the text box field passing into the query. And for patient name column in the query under OR I have the following:
Like "*" & [forms]![frmSearch]![txtPname] & "*"
So I am confused why it ignores the medical record number entirely.
I have a test database attached. This is just a sample with dummy data entered and not designed pretty. Through this up for another issue I had yesterday that has been resolved but now discovered this query one. The frmSearch is how it begins. If you search by Smith it'll bring up the two Smiths I have entered. If I leave the field blank and enter 1 for the medical record number it treats it as null and displays everything. How can I fix this?
And while on the topic of query, the true database I am working with resides on an Power I series (formerly AS400s) and only linking to their tables. The data is entered in all Caps in the tables. How can I force whatever the user enters into the search screen that it will automatically uppercase the letters before performing the search? Without having the user to remember to enter with their cap locks on.
View Replies
ADVERTISEMENT
Jun 19, 2013
I have a table that stores the criteria that the query is supposed to pull from so that when a user logs into my database, it reads that the person is part of a sepcific unit and then pulls that specific criteria and places it in a text box on a form. When they click a button on that form, it then opens the form that is connected to the query. The data type for the field I need criteria for is a number, so the criteria that shows up in the text box is this "1 or 3 or 5" so that it shows those specific numbers. If i put that directly into the query, it works just fine, but if I try to connect it from the text box in the form, it gives me a "data mismatch error" or an error explaining that criteria is too vast for access to pull the information. How do I make it read the information in there so that I dont have to create 1000 forms for each different unit.
Example:
Field: Unit
Table: Master Log Data
Criteria: 1 or 2 or 3
Works fine!
Field: Unit
Table: Master Log Data
Criteria: [Forms]![Selector]![Criteria]
The text box that is referenced in the criteria in the query says 1 or 2 or 3 and then I get an error... If the information is the same, why does it work when I place it in the query specifically, but not when its in the text box?
View 1 Replies
View Related
Sep 27, 2013
I got a database that has a multitab control. In the first tab there is the data and second one is a search tool. It contains a field where to type the text to be searched and a listbox where the results should show up.So far I only managed to create the query and link it to the field (textbox) where the user puts the search word. I just have no clue how to display the results on the listbox once the text is entered and the button pressed.
View 8 Replies
View Related
Jun 24, 2014
I have a simple database with a query that "SUM"s an amount.
I created a form with a sub form that is linked by Account number. I want to be able to open the form in datasheet format and it to display the SUM value from the query.
When I open the form it responds with #Type until I click the + symbol to open the linked query then it updates the #type field with the data from the query.
View 14 Replies
View Related
Dec 3, 2014
How does one handle function errors when running a query? Specifically:
(a) I have a user-defined function that lacks any error handling code except for an Exit Function that gets triggered if there is an unacceptable input parameter (e.g. typo in a flag value).
(b) This works fine when the function is simply part of VBA code or used in the Immediate window of the VBA editor. However,
(c) when the function is used in a SQL Select Query, I get bumped into the de-bugger, and Exit from there does not get me out of the Query. It just gets me out of the current record being processed and then hangs up again on the next.
It seems, therefore, that the function needs to have some instructions that abort the entire query when the function aborts the very first time.
View 9 Replies
View Related
Oct 29, 2014
I have 2 form controls one a combobox and the other a text box. The text box select the site (txtLocation) where the user can enter part of the name of the site and all sites with those characters are returned. I've done this by adding:
Code:
Like [Forms]![frmSearchDB2]![txtLocation] & "*"
into the criteria on the Site field in the query design editor.I also want the combobox to have an affect on the query. I want it to query on user status. However if the combobox reads "All Users" I want it to return all status's and all null values. In the criteria field I put:
Code:
iif([Forms]![frmSearchDB2]![cbxUserStatus] = "All Users",like "*",[Forms]![frmSearchDB2]![cbxUserStatus])
It kind of works but no null values are pulled back. Should it be an expression?Do I need to do it in VBA?
View 1 Replies
View Related
Aug 7, 2015
I have a Inventory DB and i want to integrate scanning. So far i have a table called StockInfo, this table holds information about bar codes (device type, make, model). I have a form called Scan_Barcode, on this form I want to be able to scan a bar code into a text box (text0) and use a afterupdate and requery function to display the results onto a form called EnterInventory.
Not only just the results but I want certain columns from the query to display in certain text boxes on this form. I have a query called FindProduct. that filters the criteria by whats entered on the Scan_Barcode text box (text0). So when i scan a certain bar code i can get the query to display certain product information for the bar code. I just cant get the results onto the HarwareStock form.
View 8 Replies
View Related
Mar 23, 2006
Good Afternoon,
I am trying to create a form where a user will enter in a value into a text field. Afterwards, when the user clicks "Enter", a query will run and will LOOK FOR THE VALUE THAT WAS ENTERED INTO THE TEXT FIELD. i.e.
User enters their address into the field and clicks the enter button.
Afterwards, a query will run like
select * from customers where address = @address <== the value the user entered into the text field. This is where the mystery lies. How do you pass values?????
Thanks,
Nervous Jervous
View 6 Replies
View Related
Jul 9, 2014
I wrote this module
Option Compare Database
Option Explicit
Public Function OdometerInput(varodometer As Variant) As Long
Dim varKilometres As Variant
varKilometres = varodometer * 1.609344
OdometerInput = CLng(varKilometres)
End Function
It works fine in the immediate window (although I haven't just fathomed what to do with null values and such) But my question which I am sure will be 'easy when you know' is how do I pass the variable to it from a text box on a form and retrieve the data in another text box on a form.
View 4 Replies
View Related
Feb 2, 2014
This is my passing criteria to query field:
IIf([Forms]![FrmUserSelection]![PricingType]=3,1 Or 2,[Forms]![FrmUserSelection]![PricingType])
My field is Pricing Type and holds records 1 or 2..I would like to pass value from my group option from a form.It works fine if group option has a value 1 or 2 but it displays no records if option value is 3 What is wrong with my code.
View 4 Replies
View Related
Jun 5, 2013
I have form with a button on it that launches a parameter-based Select query (which served as the source for a report). I didn't have any validation measure in place, so if the User supplied a bogus value, a blank report was generated. While not technically an error, it would seem more polished to generate a warning message if the User supplies a bad query value and prompt them to re-enter.
Having read other posts along these lines, I've added an unbound text box to the form which the User fills in first before clicking the button. When the button is clicked, it executes code that uses the DCount function to make sure the text box value is in the source table, and if it is then it runs the parameter-based Select query. My question is how to pass the value in the text box to the query as a parameter. Below is a sanitized version of the code that I've generated so far.
Private Sub SingleItemRptB_Click()
If DCount("[FieldName]", "[Table]", "[FieldName]=[TextBoxValue]") = 0 Then
MsgBox "Item not in database. Please check value and re-enter."
Else
DoCmd.OpenQuery "SingleItemQ", acViewNormal, acReadOnly
End If
End Sub
View 4 Replies
View Related
Jun 16, 2015
I have a form which users can toggle whether they want to only see entries that has data in a certain field. Previously I have set up a separate query with a hardcoded 'Is Not Null' in the criteria, and set the form to call the different queries based on the status of a toggle button. This time there is 22 queries that need to be modified so I'm hoping there's a better way.
I have a hidden textbox on my form that has value "Is Not Null" or Null based on the position of the toggle button.
In my query for the field criteria I have [Forms]![MainForm].[txtCriteria] where txtCriteria is the textbox previously mentioned.
When I run the query no data is shown.
View 10 Replies
View Related
Apr 23, 2014
I'm having a very simple issue (I think) trying to pass a textbox value as parameter in a sql query.I have a multiline textbox and I use it to find several values in the database, so in this textbox all the values are pasted each per line and I have a small code to convert it to single line comma separated values.This is an example:The user enter the data:
[Text0]
A1C556CC3C-TNNN
C010070H13
The code convert this data to a single comma separated string and runs the query: ex: "A1C556CC3C-TNNN","C010070H13"
Code:
test = """" & Replace([Forms]![Search]![Text0], Chr(13) & Chr(10), """,""") & """"
[Forms]![Search]![Text0].Value = test
DoCmd.OpenQuery "FindPartNo", acViewNormal, acReadOnly
In the SQL code I use the IN operator to find the exact value for each record:
FindPartNo sql query:
Code:
SELECT Classifications.BU, Classifications.WisperPlantID, Classifications.PartNumber, Classifications.PartDesc, Classifications.US_CL_Code, Classifications.MX_CL_Code, Classifications.TARIC_CL_Code, Classifications.COEProject, Classifications.Supplier, Classifications.BrokerRequest, Classifications.CreatedBy
FROM Classifications
WHERE Classifications.PartNumber In ([Forms]![Search]![Text0]);
The problem here is, the query doesn't return results but if I modifiy the query and I put:
Code:
WHERE Classifications.PartNumber In ("A1C556CC3C-TNNN","C010070H13");
... the query returns the correct results.I'm passing the value incorrect into the sql code?
View 1 Replies
View Related
Aug 29, 2013
SUMMARY:
In Access 2007, can I pass a Column Name as a parameter from a Combo Box in a form?
DETAILS:
I would like to use a String value from a Combo Box as a parameter in a Inner Join query:
...
WHERE (((AAA.HSC) Like Forms!My_Form!My_TextBox)
AND((CCC.Forms!My_Form!My_ComboBox)="X"))
Where "CCC is a Table from my Join Query
I'm able to run the code above without generating any errors. However, instead of the query accepting the value from the Combo Box, it opens a pop up asking for the value of "CCC.Forms!My_Form!My_ComboBox", not once, but twice! After it finally runs, the result set is empty when it should not be. I also find it strange that it is asking for the combo box value with the Table name appended to the front.
View 4 Replies
View Related
Aug 5, 2014
I have a search form that uses a query to show results of a search, but everytime I press search everything comes up even though I have entered search parameters, even though my search requeries every time and the search used to work before I added new records today. Also when I press the query alone on the navigation pane it asks me for the parameters and then it actually works but it won't when I use my form.
View 2 Replies
View Related
May 24, 2013
I have a filtered form with a sub form displaying only non-printed invoices.
The sub-form has a check-box (which is how the form is filtered) to only show unchecked (not yet printed) invoices.
All i want to do is print only the invoices visible in the sub-form.
I've setup the report to link to a query and the report is all setup, i just can't get the query to function how i want it to!
I've got this SQL code for the Query.
Code:
SELECT tblOrderForm.OrderNo, *
FROM tblOrderForm INNER JOIN tblOrderDetail ON tblOrderForm.OrderNo = tblOrderDetail.OrderNo
WHERE (((tblOrderForm.OrderNo)=[Forms]![Export Orders]![ExportSub].[form].[OrderNo]));
However this only shows the field with focus. I would like it to display ALL data in the subform.
View 1 Replies
View Related
Sep 3, 2007
I know this is probably a basic question- but Im not finding a clear answer here.
Basically- I have a value that I want to select from a drop down box on a form (not created yet). That value will get inserted into my query for a calculation I am doing. The form will pop up the results of the query in a table/dataset.
How do I designate the variable in the query that is being inserted from the form? I am using Access 2002- is there a way to visually perform this task (ie- drag/drop type thing)?
Thanks guys!
View 6 Replies
View Related
Nov 13, 2007
Dear All, I have a problem which I need to solve and am in need of a clue!
I have a table which contains lots of line items relating to quotes. Each quote usually contains three line items and is prefixed in all cases with a number which relates to number of users. For example
5 5 User Software Details Price Cost Note
in the table there are up to 100 users and each has line 3 items. I automate quote generation by using a form. In the form a quote reference is generated and a text box for the number of users. I have a command button which then, based on the value of users text box, selects from the line items table and sends the records to my TblQuotes.
My Tbl quotes is exactly the same structure as my line items table except it also has a field for the quote reference which is in the form.
Where I am stuck is I cant seem to get Access to send the quote reference into the TblQuotes as part of the append query.
Please help! :confused:
View 9 Replies
View Related
Nov 15, 2005
I have a hidden field on my form called "key". I can reffer to it as [forms]![MyForm]!key Is that right?
I also have a subform on this form that uses an sql query. I want this query to match on the key from above.
Shouldn't this work? select field1, field2 from table1 where keyvalue = [forms]![MyForm]!key
It doesn't. It returns a blank record.
Any help would be greatly appreciated.
View 1 Replies
View Related
May 2, 2013
I have a form - RunQuery And I have a query - Q2.
The form has text boxes for dates txtStart2, txtEnd2.
On button click I have a macro.
Query Name Q2
view Datasheet
datamode edit
The query will open
The query has a field Assigned which is Date/Time
In my query design view I have this as the criteria:
>[Forms]![RunQuery]![txtStart2] Or <[Forms]![RunQuery]![txtEnd2]
I also have filter on load set to yes.
However the filter does not seem to work.what is missing or wrong?
View 6 Replies
View Related
Oct 20, 2005
I'm currently working on a database which provides copier and postage activity in my department, using Access. I'm trying to debug a query which is called by a form that accepts input for a particular month and year. Whenever the OK button is clicked, it gives a message "The Microsoft Jet database engine does not recognize 'Combination Query' as a valid field name or expression."
"Combination Query" is a union query which calls two intermediate queries, each of which in turn calls a base query. Only one, the query responsible for gathering copier activity, is a crosstab query.
The problem I believe lies in either or both of the base queries only when the form is used. When I use a regular parameter query, I receive no error messages.
Here is the SQL code I used for each:
Query 1: Copier Activity
Code:PARAMETERS [Forms]![frmObtainMonthlyInfo]![cmbMonth] Long, [Forms]![frmObtainMonthlyInfo]![cmbYear] Long;TRANSFORM Sum([big table].Copies) AS SumOfCopiesSELECT [big table].[Copy Code], [User codes].Alias, Sum([big table].Copies) AS [Total Of Copies]FROM [User codes] INNER JOIN [big table] ON [User codes].[Copy Code] = [big table].[Copy Code]WHERE (((DatePart("m",[big table].[Time]))=[Forms]![frmObtainMonthlyInfo]![cmbMonth]) AND ((DatePart("yyyy",[big table].[Time]))=[Forms]![frmObtainMonthlyInfo]![cmbYear]))GROUP BY [big table].[Copy Code], [User codes].AliasPIVOT [big table].[Copier Location];
Query 2: Postage Activity
Code:PARAMETERS [Forms]![frmObtainMonthlyInfo]![cmbMonth] Long, [Forms]![frmObtainMonthlyInfo]![cmbYear] Long;SELECT [big Postage Activity].sAcctNum AS Account, Sum(Nz(cBaseRate*lNumPieces,0)+Nz((cRegisteredFee+ cCertifiedFee+cReturnRecFee+cReturnRecMerchFee+cSp ecDeliveryFee+cSpecHandlingFee+cRestrictedDelvFee+ cCallTagFee+cPODFee+cHazMatFee+cSatDeliveryFee+cAO DFee+cCourierPickupFee+cOversizeFee+cShipNotificat ionFee+cDelvConfirmFee+cSignatureConfirmFee+cPALFe e+cResidualShapeSurcharge)*lNumPieces,0)) AS TotalFROM [big Postage Activity]WHERE (((DatePart("m",[big Postage Activity].[sSysDate]))=[Forms]![frmObtainMonthlyInfo]![cmbMonth]) AND ((DatePart("yyyy",[big Postage Activity].[sSysDate]))=[Forms]![frmObtainMonthlyInfo]![cmbYear]))GROUP BY [big Postage Activity].sAcctNum;
I edited this to make it look similar to a recommendation posted on another forum, but sometimes I would get the message "The Microsoft Jet database engine does not recognize '[Forms]![frmObtainMonthlyInfo]![cmbMonth]' as a valid field name or expression."
I also included PARAMETERS [Forms]![frmObtainMonthlyInfo]![cmbMonth] Long, [Forms]![frmObtainMonthlyInfo]![cmbYear] Long; as the first line in the intermediate tables, but I still receive the same error message.
I'm wondering if there was something I overlooked or if there was a way around this. I can post the other queries if you need those. Many thanks in advance for your help.
View 3 Replies
View Related
Aug 15, 2006
I would like to have a user enter a start date and an end date into two
textboxes on a form. The two dates will be used to query a table. I
would then like to print a report that was created from that query.
Here is the query created as a stored procedure:
SELECT Transactions.*, Hoods.*
FROM Hoods INNER JOIN Transactions ON
[Hoods].[ID]=[Transactions].[BoxID]
WHERE ([Transactions].[Date] Between [@StartDate] And [@EndDate])
ORDER BY [Transactions].[Date];
What would be the best way to pass txtStartDate to @StartDate and
txtEndDate to @EndDate in the VBA code of the form? How would I open or
print the report created from that query filtered on that date range?
Any suggestions? Am I going about it wrong? Should I have created the
report from the above query, or should I do it another way? Can anyone
direct me to some code that does all of the above or something
similiar?
Thanks.
View 14 Replies
View Related
Jan 7, 2014
I have a form with a date field, when the user creates a new record, I would like the date field to automatically fill with the most up-to-date date from the Orderdate table.
Basically I need the code to do the following when a new record is created;
Search the Orderdate table for the most recent date and then auto fill the date field on the form with that date!!!!
My thinking so far...
Private Sub Command34 - where would you set this event on the properties i.e. Before update or On Got Focus ?
Docmd.OpenQuery "QryFindMaxDate"
I'm not really sure how to pass the date to the text box on the form,
End Sub
View 7 Replies
View Related
Aug 4, 2014
I have a navigation form that will have 6-8 tabs. We were using about that many databases, but we are finally consolidating them into one. The result of us using so many databases has been the multitude of forms and reports that were necessary for each database prior to merging them together.
The problem: There will be anywhere from 12-20 (text boxes) that the user can use to search anything in our database. What we need to have happen, if possible, is for those search parameters to show up in the header of our report if they have text in them. If the text box is blank, it should not show up in the header of the report.
I have read how to to do the start/end date technique, but I do not know if that would work for what we are doing since the boxes would only show up if they are populated by the user.
View 4 Replies
View Related
Mar 13, 2014
I get a negative value when i run the below expression.
The expression picks the month from the field txtmonth in form "frmYearToMonth" and search's the crosstab query "ctbYearToMonth" for the corresponding value in txtMonth.
=DLookUp([Forms]![frmYearToMonth]![txtMonth].[Value],"ctbYearToMonth","")
The value is correct but there is a negative sign before the value how do i eliminate the negative sign.
View 5 Replies
View Related
Mar 24, 2013
I am trying to do the good 'ol sales report (query) to include customers with no sales.
I have a customers table, account number table, sales table & sales (line) detail table. (all linked in that order)
If I run a query to show customers (in the customer table) with account numbers, that works
An unmatched query to show customer without an account number works (but of course the unmatched account number field isn't shown).
How can I get the two two be shown together with the "unmatched" having a null or 0 for their account number?
I am guessing in principle, the resulting solution can be modified to show customers without sales alongside those with sales?
View 3 Replies
View Related
