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.
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.
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.
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
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))
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?
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
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.
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 ****
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.
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?
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
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?
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.
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).
'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?
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.
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)
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?
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")
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")
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.
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
