Forms :: Passing Text Box Data To A Function Via A Query
Jan 28, 2014
I have a function in a module that looks like this (it takes 2 dates as arguments):
Code:
Function get_KPIScanAgeRange(in_ScanDate As Date, KPIDate As Date) As String
Dim ret As String
ret = "Invalid"
' return value, by default is because age is negative number
[Code] .....
I'm trying to pass the date in a text box which exists in a form called "d3FormAging". The text box name is "KPIDate". The query looks like this:
Code:
SELECT Query_d3_Open.[Company No], get_KPIScanAgeRange([Scan date],[Forms]![d3FormAging]![KPIDate]) AS KPIScanAgeRange, Count(Query_d3_Open.[Scan date]) AS Scans
FROM Query_d3_Open
GROUP BY Query_d3_Open.[Company No], get_KPIScanAgeRange([Scan date],[Forms]![d3FormAging]![KPIDate])
ORDER BY Query_d3_Open.[Company No], get_KPIScanAgeRange([Scan date],[Forms]![d3FormAging]![KPIDate]);
For some reason Access doesn't recognize the "[Forms]![d3FormAging]![KPIDate]" when I pass it to the function. I get run-time error 3070 with information that Access can't recognize this expression even though the code should be correct as it's made with the expression builder and I'm sure the form that contains the text box is open when the query runs.
I have built a function which passes values into the criteria of a saved query. It has worked very well until I recently built a query builder based on this same concept. The user can select from a list of operators "<=", ">=", ">", "<", "=".
Example, the function will pass into the criteria the following: <150
Unfortunately, I have exhausted all avenues for passing the "<" less than or greater than signs into the criteria. The Access SQL forces an equals sign before the passed in criteria and thus treats the "<150" as the entire criteria.
I have overcome no criteria by defaulting the function value to pass in the wildcard "*" and putting "Like" in the criteria.
Has anyone every handled a "variable" operator in a saved query in Access? :confused:
I am aware that I can change the QueryDef, use temp tables, and do several other longer methods, but the success of this concept will reduce an large amount of coding effort as well as make the software much easier to transfer knowledge on.
This overall method is really a great way to reuse criteria based on a form without hard coding.
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:
[code]...
I'm passing the value incorrect into the sql code?
I have a multi-select list box that runs a "For intCounter" and builds a sting into a text box. That works fine the text box will populate as designed <"Closed" OR "On-Going">.
(I know that I should be dynamically creating the query in the first place ... normally I would ... but this is a quick fix that I want to get it into an existing application, while the redesign requirements are being written.)
Now I want to pass that string to a pre-existing query, but I'm not sure what to write in the criteria section.
I've tried... Like "*" & [Forms]![ViewReports]![TextPickList] & "*" IIf([Forms]![ViewReports]![TextPickList]="","",[Forms]![ViewReports]![TextPickList])
and just plain old ... [Forms]![ViewReports]![TextPickList]
but my query is coming up blank. What do I need to write into the criteria section of this saved query?
I know there are other threads on this but I have tried all the solutions and none have worked.
I currently have 2 forms, a customer form and a customer health form.
I want to pass the membershipNo from the customer form to a newly opened customer health form.
this is the code I am using but it isnt working, actually it is, but when i delete all the current customer entries and enter new ones it doesnt pick up the details and all the controls on the customer health form are greyed out.
I have a search form that searches for student ID from the student demographics table. I have it set when I click on the student ID (Frm_Student_Demographics_DailyCares) it will open the student visit form. When the student visit form opens it will show the student ID on the Student Visit Form but it is not being recorded in the student visit table.
I have Student ID control source on the Student Visit Form set as:
I have a form that displays a stock inventory, one of the fields I use is called [ItemCode].
On the Stock_Inventory form I have 10 command buttons that open other forms to display additional product information.
These were previously sub_forms that sat in a tabbed control box within the Stock_Inventory Form but as it now takes a while for the form to load (lots of queries running each time you search for a product) users are getting slightly frustrated.
So, what I decided to do, was move each subform from the tabbed control into a new form and open that form when it is needed by the user.
OK, when I open Stock_Inventory and search for a product [ItemCode = "ABC123"] and then want to see the Sales_History, I have to open my new form, Stock_Inventory_Sales_History, within this form are two sub-forms, each subform has a field called [ItemCode].
However, when Stock_Inventory_Sales_History opens it asks me to input the parameter [ItemCode] twice, I presume this is because the Stock_Inventory_Sales_History does not have a record source and that the subforms need [ItemCode="ABC123"] to run each query and return the data.
I need to pass the [ItemCode] from the Stock_Inventory form to each of the 2 Subforms that are on the Stock_Inventory_Sales_History form automatically.
I have written a simple module that sorts and compares two strings. Next I am trying to create an event procedure for a button that will send all the values from three selected colums to that function for comparison.
Function Check(sImp As String, sAns As String, iOpt As Integer) As Integer
Dim lImp As Long 'Imported values (unsorted) Dim lAns As Long 'Entered values (unsorted) Dim lAcnt As Long Dim lCcnt As Long Dim sAnswer As String 'Correct Dim iMini As Integer Dim sImpSort As String Dim sAnsSort As String
For lAcnt = 1 To lImp sAnswer = Mid(sImpSort, lAcnt, 1)
' option Select Case iOpt ' all Case 1 Check = 1 Exit Function
' or Case 2 For lCcnt = 1 To lAns If Mid(sAnsSort, lCcnt, 1) = sAnswer Then Check = 1 Exit Function End If Next
' and Case 3 If sImpSort = sAnsSort Then Check = 1 Exit Function End If Case Else End Select
Next
End Function
What would be the best way to send these values to the function? loop through a recordset? (Note: Not sure how many records the DB will eventually store. Lets just say 10,000 for now)
Ok, here is the problem that I can't seem to find any posts on here talking about. I have a form, let's call it FormA and a subform (subformA). I am using a non-activeX calendar control, which I did not write. I was informed it could be used on subforms but its not working. In reviewing the code it is looking for the activeform name to be passed to it. This works when the calendar function is called in the FormA, but when it is called in SubformA, it can identify the field that I want the date selected to be entered into, but it does not recognize the subform as being the active form.
The code it's using to determine activeform name is: Screen.activeform.name
From my research I have found that when the subform has the focus, the activeform.name returns the name of the parent form.
The question: How can I determine the subform name and pass it as a variable to the calendar program?
I have a function that when called transfers a query recordset to an excel spreadsheet then emails it. At the end of the function I use code to write the date sent to a table. Each time the function is called I only need records in the query that have been modified since the last time the function was called. I have a field in the query 'LastModified' with a criteria '>[Enter Date]'. I then look up the date in the table and enter it manually. I know how to look up the last date sent in table using code but getting the >#SomeDate# in the query with VBA.
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,
I have never tried passing variables while calling a function so I don't know what the heck I'm doing. I'll give a simplified example of what I'm trying to do. The second variable vRank is reporting properly but the first one vID gets "stuck" on whatever the first item in the listbox is.
Code: Dim vrt As Variant Dim upSQL As String For vrt = 0 To Me.List1.ListCount - 1 If Me.List1.Selected(vrt) = True Then Call ChangeUp(Me.List1.Column(0, vrt), Me.List1.Column(1, vrt))
I need to pass values of my two check boxes on my Form
Check box US and Check Box Canada if both checked the criteria would be "UD", "ud',"b","B","us","US","CD","cd" if Canada check box checked criteria would be "cd","CD" if US only checked criteria would be UD", "ud',"b","B","us","US"
how to write the criteria statement on the query?this probably would be a complicated iff statement?
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.
DLookup function. (this is for a stamp collection database).
On my form ("InventoryInput") I have a text box called "Catalog" for a numeric entry and a text combo box for selecting a "Country" in drop down list.
I want to query a table called "CatNameList" to get the "StampName" of the item (based on the entries of Catalog and Country) and populate that name in the text box. The fields in this table are called "StampName", "CatNumb" & "CName" respectively.
I have successfully placed the following expression in the control source of that textbox and able to populate the StampName I need based solely on the catalog number alone.
That express is : =DLookUp("StampName", "CatNameList", "CatNumb = Form![Catalog]")
So it will populate the "StampName" data to match the "Catalog" number entry just fine.
However, I need to add a second layer to incorporate the Country.
Example : There is a catalog "1" for "USA", and a catalog "1" for "Canada" but both have different "StampName".
I have been attempting to get that second piece added with no success. Here is the expression I have been trying to get to work :
Right now, the text box is just blank with the above expression. I thought it may be because there was no match found, but I have triple checked to ensure I have the spelling correct on the country name in both places.
Basically, I just need the dlookup to take the "catalog" and "country" off the form and match it to the "CatNameList" table fields of "CatNumb" and "CName" to give me "StampName" field back on the form.
I need to pass a criteria to a query from my option group control to my query.
It contains three options 1,2 and 3.
If option 3 then Pricing Type 1 and 2
How do I make the code below working?
IIf([Forms]![FrmUserSelection]![PricingType] Like 3,([dbo_AGPricingDiscounts].[PricingType])="2" Or ([dbo_AGPricingDiscounts].[PricingType])="1",",[Forms]![FrmUserSelection]![PricingType]")
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.
I have a form based on query. On form i am retrieving data from another table using DLookup in a unbound text box. So I want to save the result of DLookup function in another field/table on same form.
Hi--I am working on a database which is a modification of the following MS Access 2003 Order Management template: (URL address blocked: See forum rules)=CT101426031033
In my database, I have a subform called sbfOrderDetails, which in its Form Footer has a text box that sums the items in the order and is called OrderDetailsTotal.
I am trying to pass that value to a text box in the main form called OrderSubtotal.
I have three questions:
1) What is the proper syntax to use to get the value of the subform control into the OrderSubtotal control on the main form? I have tried various permutations and continue to get a #Name? error.
2) If you look at the expression in the Order Subtotal control of the Add and Manage Orders form in the original Order Management template, it looks like this:
=[Order Details Subform].Form!OrderDetailsTotal
When I try to replicate that in my database, I cannot--if I add brackets around my subform name and press Enter, Access adds brackets around everything in the expression, i.e. =[sbfOrderDetails].[Form]![OrderDetailsTotal] (which returns a #Name? error). If I edit out the brackets I don't want and then press Enter, it puts the brackets back in. In other words, Access keeps trying to "fix" my expression by either having brackets around each item, or no brackets at all! This is really annoying; is there any way to turn it off??
3) When sbfOrderDetails is set to Single Form view, I can see the Form Footer (and the value in the OrderDetailsTotal text box as well). When it is set to Datasheet view, I cannot. Why?
Thank you very much, lcnlit
PS>I cannot attach my database since I am a new poster, and the forum is disabling the URL of the Microsoft template as well.
I am trying to run a query which will effectively group up the "Name" field and combine the "colour" field against the name using a ";". so it would look like this:
I have a bit of a dilemma in my related tables (screen shot of relationships attached).
Basically at the moment I am using a 1-1 relationship between my tables using the primary key of CYID. The CYID is populated using an auto number at the moment but the problem I have is if the tables become out of sync with the numbering.
What I would like to do is when I create a new record in tblClientDetails is create a new record in each corresponding table that is linked and then pass the CYID across to each table. Can anyone offer any assistance or give me any suggestions for better ways of doing this?
