Modules & VBA :: Replace Function Using Form Control Value
Jun 11, 2014
I am trying to use the string value from a form control as the criteria for a query. I first need to replace the "," in the string with "AND".
So far I have a module using the Replace function, but it doesn't seem to be working. I am not sure I can reference the string inside the form control directly....if I can, I might have a syntax error.
Here is my code so far:
Dim result As String
result = Replace("Forms!Processing!Dataset_Acreage_Query", ",", "AND")
I am not experienced and having trouble finding the search terms to answer this question.
My company recently upgraded our MS Office from 2007 to 2010 (except for Access).
Previously, when I had Access 2007 and Outlook 2007, I had a process that generated 50+ dynamic emails from an Outlook template file (.oft).
The code would loop through a listbox and replace the template's default text to a string of text specific to the selection in the listbox by utilizing the Replace() function on the MailItem .HTMLBody.
Since the upgrade to Outlook 2010, the code is able to run, however, the Replace() function is no longer working; Instead, each email that is generated maintains the template's default text.
The only thing that is not working is the Replace() function, all other aspects of the code work fine.
I've provided a simplified version of the code below:
Dim myOlApp As Outlook.Application Dim objMailMessage As MailItem Dim stBody As String Set myOlApp = Outlook.Application Set objMailMessage = myOlApp.CreateItemFromTemplate("C:UsersDesktop emplate.oft")
[Code] .....
I've recreated the template file in Outlook 2010, thinking that the template created with Outlook 2007 would be the culprit, but to no avail.
What could have changed from Outlook 2007 to Outlook 2010 that would render my previously valid code ineffective?
Are there certain references I need to enable in both Access and Outlook to allow VBA in Access modify the content in an Outlook email?
I am trying to read the value of a control on an open form from a Public Function in a Module with no luck. I suspect the issue is syntax.
Code:
Public Function MyFunction(varFormName As String, varControlName As String) If Forms(varFormName).Controls(varControlName) = True Then ' = 0 even when control is -1 Else End If End Function
The variables pass through fine but the If statement equals zero regardless of the state of the form's control. I tried several variations to no avail.
If Forms(varFormName).Form.Controls(varContactControl ) = True Then If Forms(varFormName)!Form.Controls(varContactControl ) = True Then Etc.
I want this function to populate value from a any field selected from any table to any text box in any form ...... Lets say in a Database named TestDB we have a Table named tblTest , a Form named frmTest and in this form( frmTest ) we have one Bound Combo Box named cmbTest and one unbound TextBox named txtTest
We assume that the table tblTest has three fields : TestID , FName and LName .
We also assume that there are already some records in the Table tblTest .
If the function F() is already programmed it should take as arguments as it follows :
F(FormName as??? ,ControlName as ???, TableName as ???, FieldName as ???, ID_Field_Name_of_the_Table as ???, Combo_box_selected_ID as ???)
In result the function should (probably) DLookup (FieldName , TableName , ID_Field_Name_of_the_Table = Combo_box_selected_ID ) and then store the value in a variable ( probably Variant ???) , lets say called varSetValue.
The problem is IF this is the correct way to handle that issue , than what should i do next.... I really don't know how to pass a Control's name to a function from the Event module of the ComboBox -
I mean - how can I obtain the value so it is usable for the function .... What data type should be the function arguments so I can use them to set a value for a control in any form .
In example : If the Dlookup() is somehow successfull then I want to assign the varSetValue to the txtTest what should I do : FormName.ControlName.Value = varSetValue >??????
I have read a lot materials but couldn't find a good answer for that , and aswell what data type should be the arguments that the function accepts and how do I obtain Controls and Forms names so I could use them in the function as described above .
I'm currently migrating a lot of Excel processing to Access and really enjoying the transition. I am, however, having big problems with the Replace function in Access.....
In Excel I use a macro to relace anything in a cell contents that follows a space with nothing......i.e. replace " *" with ""
The fields that i need to do this on do vary in length- they are product descriptors.....e.g
abcdefg 123456 xzy 987
The replace feature (ctrl + h) in table view will do this correctly if "any part of cell" is specified in "match" selection but I cannot seem to replicate this in a query.
I'd be grateful for any suggestions here. I thought about exporting the fields in the columns to Excel and doing the replace there, but I can't get it to work. Similarly I tried writing some VBA for this, but again no joy!
On a more general note: Has anyone ever actually finished a database - ie got to the point where no changes needed to be made? 18 months after this started, and I'm still tryng to accomodate what my manager wants!
I'm trying to help someone with some text functions in Access, and I have used the Replace function to strip out spaces in a postcode. I created a dummy database in Access 2003 but in 2000 format since she is still on Access 2000. However, she is getting the above message. Incidentally, I don't get the message when I run it in Access 2000.
She has checked her references and has no missing ones. She has:
Visual Basic for Applications Microsoft Access 9.0 Object Library OLE Automation Microsoft DAO 3.6 Object Library Microsoft ActiveX Data Objects 2.5 Library
I have attached the DB - I'd be really grateful if someone could try opening it in Access 2000 to see if they get the same error.
I am running a query for an apparel manufacturing facility. In my query I have a table called 'OrderForm' which is where the orders are put in. There are more than one type of fabric that can be a part of an apparel item, so as a result, there are multiple fields pulling from the 'FabricType' table. In order to get this to work in my query I created 'SubTables' for the different fabric fields. For example, I have tblFrontfab, tblbackfab, tblsleevefab, and tblcollarfab which are just extra copies of the 'FabricType' table. The actual question is that when I want to replace characters like ,./& in the fabric field but I can't use the replace function. It says that it is too complex to calculate.
I used this notation Frontfab: Replace([tblFrontfab].[fabric],".","") and the error was that it is to complex to evaluate.
I have a database that I've split and have deployed to 7 persons.
The thing is, I have three (3) forms:
Form 1 Form 2 Form 3
...that require changing a portion of a line in each form's respective VBA codes. I need to change the directories to where an excel workbook is stored after exporting, and another is where same excel is moved to after being uploaded.
At the moment, during deployment I have to manually change the directories.
I was thinking of maybe a pop-up form with four (4) fields that allows the user to select his/her name at the start-up of their frontend app, and paste the directories in the other text boxes provided.
This information will then be used to replace the directories in the VBA.
If I were to make changes and have to redeploy, I would have to do everything manually again.
I 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.
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.
The company I work for is consolidating regional server space onto a single server. I have relinked all of my tables, but my ~180 queries are still pointing to the old server.
My question: Is there a script or other process that can be executed that will search through all my queries within the database to find the string "dbo_tbl" and replace with "dbo_vwtbl"? I would very much like to avoid taking each individual query to a notepad...
I have a text box in a report, with it's control source property set to =[BegDt]. Upon loading the report a message box pops up and asks the user for the value of [begDt]. This occurs 3 more times for 3 other parameters.
Now I want to change the design of the report so I decided to put these 4 parameters on a form so the user can fill in the 4 values all at once rather than responding to 4 message boxes that pop up when you run the report.
The problem is now the control source properties in all the different text boxes need to be changed to [forms]![frmABC]![txtBegDt] etc.etc. There are over 100 text boxes in this report that make reference to the 4 different parameters. Is there a replace command I can use to change all the occurrences in the text boxes or perhaps a simpler way would be to pass the values to the report through code.
I tried placing this in the report header's format event and it doesn't seem to have any effect.
Code:Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)begytd = [Forms]![frmABC]![txtStDt]endytd = [Forms]![frmABC]![txtEndDt]curmo = [Forms]![frmABC]![cboMo]curyr = [Forms]![frmABC]![cboYr]End Sub
I should mention that the report uses a table (tblRecap) as it's record source. I tried using a query that refers back to the form for the parameter values but that means I have to use the query as the reports record source and that creates a problem because the report needs fields from the table to perform calculations.
Is there a copy and replace existing records function in access?
I.e. I would want to copy records from one table to another (with same structure) and replace similar records with in original table in the new records (which have minor ammendments made).
Code: INSERT INTO TB_SISTEMAS ( LOGIN, SISTEMA, PERFIL, DATA ) SELECT Left([dbo_BACKUP_ACESSOS.LOGIN],255) AS LOGIN, dbo_BACKUP_ACESSOS.SISTEMA, Left([dbo_BACKUP_ACESSOS.PERFIL],255) AS PERFIL, dbo_BACKUP_ACESSOS.DATA FROM dbo_BACKUP_ACESSOS WHERE (((dbo_BACKUP_ACESSOS.SISTEMA)<>"ACTIVE DIRECTORY") AND ((dbo_BACKUP_ACESSOS.DATA)="2014-03-23"));
But Iwant to be able to use a set of data to be used in the Replace Statement, so I create a table to add each string I would like to have replaced by "nothing", and trying to make the replace query to look there in order to find what to replace.I also created a table where I will list the systems that I dont want in the select, so I removed the "ACTIVE DIRECTORY" and replaced by the colum that have the list of system I dont want listed.This is the result:
Code: INSERT INTO TB_SISTEMAS ( LOGIN, SISTEMA, PERFIL, DATA ) SELECT Replace((Left([dbo_BACKUP_ACESSOS.LOGIN],255)),[PREFIXOS_E_SUFIXOS]![Valor],"") AS LOGIN, dbo_BACKUP_ACESSOS.SISTEMA, Left([dbo_BACKUP_ACESSOS.PERFIL],255) AS PERFIL, dbo_BACKUP_ACESSOS.DATA FROM dbo_BACKUP_ACESSOS WHERE (((dbo_BACKUP_ACESSOS.SISTEMA)<>[SISTEMAS_EXCLUIDOS]![Sistema]) AND ((dbo_BACKUP_ACESSOS.DATA)="2014-03-23"));
The thin is that this keeps asking me to enter the parameter value for "PREFIXOS_E_SUFIXOS!Valor" and for "SISTEMAS_EXCLUIDOS!Sistema"
I have this code below that is working however the calculation are updating on my form late.
Basically, I have some calculation that are performed on a "After Update" event on some controls on my form. I wanted it to do the calculation after I update the control on the form.
The code is in a module and I just call the function after update on the control But the form is not updating when I change the value in a field. I have to change the field again for it to update.
Control
Code: Call GeraAuditCalc DoCmd.RefreshRecord
Function
Code: Function GeraAuditCalc() Dim fHrs As Double Dim Ttl As Double Dim Ttl1 As Double Dim Ttl2 As Double
I have main form (frmProcedures) that has a tab control with 2 pages. One of the pages I have a subform that contains a listbox control controlled by a query.The subform is a search and display form. When I open the subform on it own, it opens up fine. However when I try to open up the mainform, it always prompts me to enter the parameter value. I have to atleast hit "Enter" for the form to open up.Even after hitting the enter key, when I try to navigate to that page, I keep getting the parameter prompt.
Here is the query that populates the listbox control. And the parameter that is prompted is Forms!frmSearchMulti!SrchText that you see in the query below
SELECT Procedures.ProcedureName, Procedures.BusinessLine, Procedures.Manager, Procedures.ApprovalDate, Procedures.NextApprovalDateFROM ProceduresWHERE (((Procedures.ProcedureName) Like "*" & forms!frmSearchMulti! SrchText & "*")) Or (((Procedures.BusinessLine) Like "*" & forms!frmSearchMulti!SrchText & "*")) Or (((Procedures.Manager) Like "*" & forms!frmSearchMulti!SrchText & "*"));
I do have a searchbox that when updated with search string issues the requery to update the values in the listbox.
A main form - named [formMain] On [formMain] I have a tab control - named [tabMain] [tabMain] has 3 pages named [pageOne], [pageTwo] and [pageThree]. On [pageThree] I have created a subform named [formSub]. On [formSub] there is a tab control named [tabSub].
Now what I would like is to make [tabSub] (in)visible if the user switches from [pageOne] or [pageTwo] to [pageThree].
Code: Sub tabMain_Change()
How do I refer to [tabSub] from within this subroutine? I am trying something like:
Is there a code that will replace the empty fields by shifting the bottom fields up for access? I am planning to write a code that will delete unnecessary items in my database, however, that will cause the database to have "holes" in it.
