Modules & VBA :: How To Construct String For Variable
Sep 10, 2013
I am trying to set the following variable that will be used to create a PDF file in an already existing folder and name the file WorkorderIDddmmyyyy-hhnnss.pdf
The following variable setting creates the filenameddmmyyyy-hhnnssWorkorderID.pdf.
mFilename = "C:RPR AccessPDF Reports Emailed" & Format(Now(), "ddmmyyyy-hhnnss") & WorkorderID & ".pdf"
DoCmd.OutputTo acOutputReport, mReportName, acFormatPDF, mFilename, True
I just can't figure out how to construct it correctly.
Also, is there a way to dynamically create a folder if the folder doesn't already exist? I currently hard code the folder name but would really like to create the folder name based upon some other variable.
View Replies
ADVERTISEMENT
Jun 19, 2015
How to I get a portion of a string into a variable?
I want to capture the table name of a recordsource.
The problem is sometimes the recordsource is "Select * from mytable where ID = 6" or "Select * from mytable" or "mytable"
I want to put "mytable" in a variable for later use.
View 11 Replies
View Related
Jul 22, 2013
I am currently trying to create an update query (building a SQL String in VBA for a command button click event) to update a variable's value into a table.
Basically, there are 2 tables, displayed in 2 sub froms within the same main form. (OldTable and NewTable for arguments sake)
The basic method I want to implement is that a user highlights a record in the subform of "NewTable". (This value is stored as a variable "NewJPNUM" This value is then to be inserted into the highlighted row (or rows) of table OldTable on command button click.
So the basic idea is a user highlights a row in one table and this value is stored as a variable "NewJPNUM" . The user then highlights a row or rows in "OldTable" and the value from variable "NewJPNUM" is then written to field "NewJPNUM" in "NewTable" on command button click.
I am not experienced with Access but have decent experience in Excel / VBA so not really sure of best practice methods etc.
View 3 Replies
View Related
Aug 26, 2014
I am amending some Code I found online for an audit table, I need to store additional information in the table that is associated with some forms but not others.
I have researched about putting optional variables in, but I read this only works with the type VARIANT.
Is there a way to make a string optional as my fields contain text?
here is my code so far:
Sub AuditChanges(IDField As String, UserAction As String, Optional UserID As String, Optional DeviceID As String, Optional SimID As String)
On Error GoTo AuditChanges_Err
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim ctl As Control
Dim datTimeCheck As Date
[Code]...
View 5 Replies
View Related
Dec 7, 2014
I am trying to write vba code to enter a form. A am using an SQL statement in vba to enter the form with it.
I want to include a string variable in the WHERE clause of the SQL statement. The string variable is introduced in the DIM statement before coming up to the SQL statement.
The following is the SQL statement. numrukarta is the string variable I want to introduce. I need to know the exact punctuation to be able to insert it into this statement.
StrSq1 = " SELECT Employees.NName, Employees.SSurname, WorkItems.IDcardNo, WorkItems.DDate, WorkItems.EntryTime, WorkItems.FinishTime, WorkItems.Roster" _
& " FROM Employees INNER JOIN WorkItems ON Employees.IDcardNo = WorkItems.IDcardNo" _
& " WHERE (((WorkItems.IDcardNo)= numrukarta) AND ((WorkItems.DDate)=Date()) AND ((WorkItems.FinishTime) Is Null);" AND (Not (WorkItems.Roster) Is Null))
View 3 Replies
View Related
Feb 26, 2014
I am using a public function to feed a variable string to a query. So far I have got:
Code:
Public Function ClientStreetModule(firstLVar As Variant, streetVar As Variant, newFL As Variant) As String
Dim cslStr1 As String, newStreet As String
newStreet = Right(streetVar, Len(streetVar) - Len(newFL))
[code]....
However, I only need to use newStreet as the true part of iif, in which instance all are longer. At least I think this is the problem. I realise I might need to use NZ but am not sure how. Why it is evaluating and giving errors for all records and not just when the iif criteria is true as I want it to?
View 7 Replies
View Related
Jul 14, 2014
I'd like to expand me tree view automatically when a node matches a string variable from a table. In other words, when a node in a tree matches the given name (variable), the tree will expand all the way down to that name. I guess I need to use "For each node" and when the node and variable match, the tree structure will show up.
I have come up with something like this:
Code:
For Each nd In Me.tree.Nodes
If nd = level1 Then
nd.Child.EnsureVisible
Exit For
End If
Next nd
Me.tree.SetFocus
But this only shows the first level of my tree ( btw. I have a 3 level tree). I get lost inside the FOR when I want to make use of another two variables - level2 and level 3
View 1 Replies
View Related
Dec 16, 2014
I am trying to construct a query with a number of DCounts for a summary report, and each of those counts is to have more than one criteria.So far, the code below is an example of the code, which returns #Error in five rows
Code:
DCount("[ID]","Attendance","[Absent]= True & " And [Date]=" Between [Forms]![DateSelect]![txtStartDate] And [Forms]![DateSelect]![txtEndDate]")
I have a feeling that it is the criteria for the date that I have got wrong, but I cannot see the wood for the trees at the moment.If it makes any difference, all of the DCounts will refer to one table and will all be within the same date range.
View 11 Replies
View Related
Jan 9, 2008
Hello
Can anyone tell me how I use a variable I have defined within a SQL string.
e.g.
***code start ***
Dim ComputerName as string
Dim Sqlstr as string
ComputerName = Environ("ComputerName")
Sqlstr = "INSERT INTO ComputerName ("ComputerName") SELECT ***This is where I need help including the variable ComputerName I defined above ****
I would really appreciate some help.
Thank you :-)
View 6 Replies
View Related
Apr 3, 2006
Hey all, hope this question isn't too newbiefied :D .
I have a database where I must run 10 tests depending on which product I have selected (from a drop box). Each product requires different tests (of the 10) and I am hiding the tests that I do not need after the product is selected. The way I have it set up right now is I have 10 different If statements...one for each test on the "On Change" event.
I have been trying to reduce this down to one If statement nested inside a while loop, but my visual basic isn't very good. I have named the Test buttons Test3- Test13, and want to increment just the number of the test. However, when I try to plug in my concatenated string, I have problems. My code is as follows:
Dim testcount As Integer
Dim testcheck as Boolean
Dim testnumber as String
testcount = 3
While (testcount < 14)
testnumber = "Test" & testcount
If Me.selectedproduct.Column(testcount) = True
Me.testnumber.Visible = True
Else:
Me.testnumber.visible = False
End If
testcount = testcount + 1
WEnd
Me.testnumber is obviously not an object on the Form...so I guess my real question is, how do I get visual basic to plugin the variable testnumber so that the code reads Me.Test3.Visible.
Any help would be appreciated.
View 2 Replies
View Related
Jul 8, 2013
Error 91 - Object variable or With block variable not set
I am getting this error telling me that an object variable is not set.
I know which variable it is but when I step through the debugger it sets the variable and all is fine? Issue is that public variable of a class is not getting set when the VBA Editor is not open?
View 14 Replies
View Related
Oct 3, 2014
This code runs fine the FIRST time, however trows up a message the SECOND time it is run.
The error is on the line ".Range"
I am trying to sort records which have been exported to Excel.
Dim LR As Integer
LR = 5
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set wbRef = xlApp.Workbooks.Add
With wbRef
wbRef.Activate
.Worksheets("Sheet1").Activate
With ActiveSheet
.Range("A2", .Cells(LR, "O").End(xlUp)).Sort Key1:=.Range("C2"), Order1:=xlAscending, Header:=xlYes
End With
end With
View 3 Replies
View Related
Jul 16, 2013
I have a form (Datasheet). I need to define some variable in form as string, which can work with any event. E.G in column "A"on event after update, in column "B" on event after update...
I will try to explain with a simple example : form (datasheet) columns: "A", "B"
on event after update in coulmn "A", I could have some like this:
(variable what I need to define) = 3
on event after update in coulmn "B", I could have some like this:
If (variable what I need to define) = 3 then
msgbox "ok"
end if
Now I try to work around the problem and use another column "c" to keep the value from after update A - but I know that, it's bad solution -Right?
View 4 Replies
View Related
Aug 20, 2014
I look at a lot of files to see when they were last updated. I wanted to write a generic procedure to manage that so ..
Code:
Public fDate As Variant
Public vField As String
Public vFile As String
'GTSdata
vField = "txt_gts_data"
[Code] ....
What I hoped Me.vField would do is update the date field [txt_gts_data] on my form with the date the file was last saved.
i.e. me. txt_gts_data = fDate
What actually happens is the variable vfield gets updated from "txt_gts_data" to 19/08/2014 then later code falls over because the fieldname is lost .
Me.[vField] corrects itself to me.vField (and does not work)
Me!vfield falls over (cannot find the field vField, not surprising J)
How do I say update the contents of the variable, not the variable itself?
View 7 Replies
View Related
Jun 7, 2013
MS-Access VBA code to separate numbers and string from an alphanumeric string.
Example:
Source: 598790abcdef2T
Output Required: 598790
Source: 5789065432abcdefghijklT
Output Required: 5789065432
View 13 Replies
View Related
Apr 13, 2007
I'm working in the petro-chem and industrial service industries now, and am finding there are relationships I haven't had much exposure to in the past.
Can someone please help me with constructing the tables and relationships needed here?
This is something I should probably know, yet I've never created junction tables which must take into account nested data. I have created a relationship which works, but I feel it could be done in a simpler fashion.
By the way, I cannot find Pat Hartman's many-to-many sample db. It is either missing, or the restrictions on word length in Search just aren't letting me find it.
Table: WorkOrder is the primary table I'm working with. I need to store 4
pieces of information for every WorkOrderID, two of which have subs.
Here's a picture of the data I need to collect:
View 4 Replies
View Related
Mar 29, 2007
how do i use conditional statement in sql? im using access 2003 as frontend and backend.
i would like to count the number of records in a table (ex. ENROLL) where there is only 1 instance of any value in a field (ex. STUDENT_NO) plus another condition where a given value (ex. 1) should exist in another field (ex. SECTION).
example
Table name: ENROLL
STUDENT_NO SECTION FEE
16 ----------- 1 ------ 75
16 ----------- 2 ------ 75
17 ----------- 1 ------ 100
18 ----------- 2 ------ 125
19 ----------- 1 ------ 75
19 ----------- 2 ------ 75
19 ----------- 3 ------ 75
20 ----------- 1 ------ 150
Parameters:
COUNT
The number of records (count) in the table where there is only one instance of the STUDENT_NO value and the corresponding SECTION value is 1
VALUE
The sum of the FEE values in the table where there is only one instance of the STUDENT_NO value and the corresponding SECTION value is 1
The answer should come out like this:
count = 2
value = 250
Can anybody please help me to construct the SQL statement?
Thanks in advance!
View 4 Replies
View Related
Jan 26, 2005
Hi can anyone help me with this query?
'SQLTable "Resolutions Per Year", "SELECT ResName, COUNT(ResName) AS Total FROM Stats LEFT JOIN Resolutions ON (Stats.UserID = Resolutions.UserID AND Stats.ResID=Resolutions.ResID) WHERE Stats.UserId = '"&login&"' AND Date LIKE '%sYear%' GROUP BY Stats.UserID, ResName ORDER BY COUNT(ResName) DESC"
sYear is a variable
example sYear = 2003,2004,2005....
This does not work.
Is there anyway to assign the search string to be a variable?
View 2 Replies
View Related
Nov 27, 2014
I have a form with several fields and combo boxes on which the user will select different criteria to generate a report.The default value for each of these fields/cbo's is "*", so if the user changes nothing from the default, I execute a basic "SELECT *" query.
If however the user enters some value (selects a specific date range, customer number, salesman,etc), I would like to 'easily' modify my query to accommodate the entered search criteria.Trying to construct SQL when there are many search options like this is difficult. I took a stab at using QueryDefs but I'm struggling to find example VBA code as an example.
View 1 Replies
View Related
Sep 13, 2007
Hello all,
I have a temporary table with Yes/No fields (F101,F102,F103,....etc).
In another table i have a field called ProductCode with values (101,102,103,....etc)
Is it possible to append(or update) the records of the temp table with criteria on "F" &ProductCode field? (For example if ProductCode=101 then F101 sets to yes)
Thank you in advance.
View 4 Replies
View Related
Aug 26, 2014
If I set a variable inside of a subroutine, it is set to nothing upon the end of the sub. Can I set a variable outside a sub and set its value, so that you can use it within subs?
View 14 Replies
View Related
May 12, 2014
How can I define a variable which can be used by another Sub and of course the value stored in it?For instance:
Private SUB A ()
DIM A1 as String
A1 ="ABC"
END SUB
PRIVATE SUB B()
PRINT A1
END SUB
View 5 Replies
View Related
Apr 8, 2014
I have an application where several different procedures are run repeatedly every xx seconds to get live updates from a server (different procedures depending which form the user currently has open.) Within each procedure, a separate single procedure is called only if a stored boolean setting is set as true. The setting itself is only changed very infrequently, such as on application startup, but needs to be initially set (i.e. it cannot just have a default of False if it is not set.)Because of this I am storing the setting in a single record table and have a function 'UsingWPilot' to return the boolean value. However, given the frequency that the setting needs to be accessed, and the fact that speed is crucial in the running of the app, I am considering adding a global string variable and modifying the function to look at the variable (possible values 'N' and 'Y') and only going to the table if the variable has not been set.
I have heard various things about how you should never if possible use global variables. My question is: is it faster to use a global variable than to open the table record each time?
option 1 :
Code:
Public Function UsingWPilot() As Boolean
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("rbl_Settings_System")
Option 2:
Code:
Public Function UsingWPilot() As Boolean
Dim db As DAO.Database
Dim rs As DAO.Recordset
If Len(g_WPilot) > 0 Then
UsingWPilot = (g_WPilot = "Y")
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_Settings_System")
[code]....
View 4 Replies
View Related
Nov 6, 2013
I am just in the middle of writting a little bit of code that will go through each record in a query and export it into a word table. It's in the early stages but all was working yesterday. I've come to it this morning and ran it and now I get an error message;Run Time error 91: Object Variable or With Block Variable not set.
Code:
Private Sub Command15_Click()
Dim MyDb As DAO.Database
Dim rsLogin As DAO.Recordset
Dim ObjHead As String
Set MyDb = CurrentDb()
Set rsLogin = MyDb.OpenRecordset("query here")
[code]....
View 2 Replies
View Related
Nov 6, 2013
I have some code that will allow a user to pass to it the name of a report. The code will do various things to that report before presenting it for Preview, opening in Word etc.
At one point in the code I would like to refer to some control or property of the report. If I was to include this directly for one report, I might type:
Reports!Annual_Budget_Report.Recordsource = xyz
But instead of a specifc report, it would be the report as a variable (srReport_Name). However, if I type:
Reports!stReport_Name.Recordsource
The code complains, rightly, that there isn't a report called stReport_Name. The stReport_Name variable is currently set as String. I *think* the answer might lie in the variable type, but I'm still struggling.
View 3 Replies
View Related
Sep 19, 2013
I have a form in which am gathering information from the user to populate a table(Customer Master List) and at the same time (btn_Copy_Click Event)transferring the info to another form(Case) populating another table(Customer_Call). It is working fairly well, My issue is that before transferring the info I need to run a check(SQL Query) to make sure the customer or actually the Well ID don't exist in the Customer_Call table. I am trying to do this in pieces .....
1) capturing the well_Id in a variable(WellID) in the first form and using that to build the sql string and query the Customer_Call table.
2)once that works place it in a if / else clause to copy or not with appropriate messages
With that I am stuck in step 1
It works up until Set rst = CurrentDb.OpenRecordset(strSQL) the i get Run-time error '3061' Too few parameters. Expected 1.
Code:
Dim WellID As String
Dim strModel As String
Dim strSQL As String
Dim rst As DAO.Recordset
WellID = Forms!f_Customer_Lookup.Well_ID
MsgBox WellID ' testing to see if it picks up the correct box in form
strSQL = "SELECT Customer_Call.[Cus_Well_ID] " & _
"FROM Customer_Call " & _
"WHERE Customer_Call.[Cus_Well_ID] = WellID;"
Set rst = CurrentDb.OpenRecordset(strSQL)
strModel = rst!Cus_Well_ID
rst.Close
MsgBox rst ' Testing to see if the strSQL captured the data
Set rst = Nothing
End Sub
View 8 Replies
View Related
