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.
View Replies
ADVERTISEMENT
Jan 16, 2006
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.
Thank you in advance for your help,
Dave
View 3 Replies
View Related
Apr 23, 2014
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?
View 7 Replies
View Related
Sep 21, 2005
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?
thanks in advance!
View 2 Replies
View Related
Feb 24, 2006
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.
Private Sub cmdCustHealth_Click()
DoCmd.OpenForm "CustomerHealth", , , "[MembershipNo] = " & Forms!CustomerForm!membershipNo, , acDialog
End Sub
The control source on the text box on the customer health form is "membershipNo" where am I Going wrong?
View 1 Replies
View Related
Jul 5, 2013
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:
=[Forms]![frm_SearchStudent_DailyCares]![Frm_Student_Demographics_DailyCares].[Form].[SD_Record_ID].[Value]
My tables are called Student Demographics(parent table) and Student Daily Cares(child table) and they are linked by Student ID.
I have done this before on another part of this database but I just simply can't seem to find what I missed.
Here is a sample of my forms and tables.
View 2 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
Jan 9, 2014
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.
View 4 Replies
View Related
Aug 1, 2005
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.
Below is function "Check" from the module
' sImp = importData
' sAns = answerData
' iOpt = option
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
lImp = Len(sImp)
lAns = Len(sAns)
sImpSort = OrderString(sImp)
sAnsSort = OrderString(sAns)
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)
Any suggestions would be greatly appreciated..
Thanks
salmonman
View 1 Replies
View Related
Feb 24, 2005
Hey long time browser, first time poster!
HELP!!!
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?
Mark
View 1 Replies
View Related
Jun 5, 2013
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.
Code:
DoCmd.TransferSpreadsheet acExport, , "qryUpdateWebmaster", _
"C:SubmarinersUpdates_Sent" & fname & ".xlsx", True, "Webmaster_Update"
View 1 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
May 29, 2015
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))
[Code] ....
View 3 Replies
View Related
Jan 30, 2014
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?
View 2 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
May 29, 2015
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 :
=DLookUp("StampName", "CatNameList", "[CatNumb] = " & [Catalog] & " And CName = '" & [Country] & "'")
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.
View 4 Replies
View Related
Mar 11, 2014
I have a query which contains figures. i have a search form based on this query and need to search using comparison operators such as <10000 or >500.
Is there a way to pass these value to criteria field in query ?
View 14 Replies
View Related
Feb 2, 2014
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]")
View 3 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
Oct 17, 2014
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.
View 8 Replies
View Related
Mar 21, 2006
Hello experts
good morning. here comes my doubt. i use =CurrentUser() in text box of form. but when am testing, the user name is not there in database.
i am using Access 2003 and Access security.
Can anyone tell how to resolve this
Thanks you:)
View 1 Replies
View Related
Apr 20, 2007
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.
View 3 Replies
View Related
Jul 25, 2012
Moving data from one table to another table. Mainly we are using "Insert into table2 select* from table with where condition".
But in this case data moved one table to another as copy paste.
Is it any way to go data as cut paste...
View 1 Replies
View Related
May 13, 2014
I have a table in Access 2010 and in one field i have multiple records of the same data as in the next field it has unique data for example:
NameColour
CarBlue
CarGreen
CarYellow
BusOrange
BusPurple
BusRed
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:
NameColour
CarBlue;Green;Yellow
How i would do this.
View 5 Replies
View Related
Jun 28, 2013
I created two functions to use when I'm pulling data using a query (QBE). I would like to combine the two into a 3rd Function that works like this:
BB_Only = DishNetActivityCode = 1 and VideoActivityCode = 0
Hybrid = DishNetActivityCode = 1 and VideoActivityCode = 1
V_Only = DishNetActivityCode = 0 and VideoActivityCode = 0
Function DishNetActivityCode(service_code_string As String) As Integer
Dim CodeArray, i As Integer
[Code] .....
View 14 Replies
View Related
Feb 8, 2008
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?
View 2 Replies
View Related