Modules & VBA :: Update Contents Of Variable But Not The Variable Itself?
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 Replies
ADVERTISEMENT
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
Apr 19, 2014
I have defined a recordsource based on a predefined query. Now I intend to use the SQL statement which works to produce other recordsources based on other queries. More concretely here is my code as of now:
Code:
Dim J1 As Long
Dim SQLRecordSource As String
J1 = Forms(ParName).MemID
SQLRecordSource = "SELECT DISTINCTROW [MembersTbl].* FROM [MembersTbl]" & _
"INNER JOIN [ActiveMembersQy] ON " & _
"[MembersTbl].[MemID] = [ActiveMembersQy].[MemID] " & _
" WHERE [ActiveMembersQy].[MemID] = " & J1 & ";"
In the future instead of writing it like this:
Code:
Select Case QryName
Case "NonMembersQy"
SQLRecordSource = "SELECT DISTINCTROW [MembersTbl].* FROM [MembersTbl]" & _
"INNER JOIN [NonMembersQy] ON " & _
"[MembersTbl].[MemID] = [NonMembersQy].[MemID] " & _
" WHERE [NonMembersQy].[MemID] = " & J1 & ";"
[Code] ....
I wish to write it like this (which does not work):
Code:
SQLRecordSource = "SELECT DISTINCTROW MembersTbl.* FROM MembersTbl" & _
"INNER JOIN QryName ON " & _
"MembersTbl.[MemID] = QryName.[MemID] " & _
" WHERE QryName.[MemID] = " & J1 & ";"
Is this possible ie put the name of the variable that holds the name of the query in quotes or sometimes put the name of the query instead of the contents of the name. If there is a contents function for variables in VBA that can be used by I doubt whether it exists.
View 3 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
Apr 27, 2015
I'm trying to include some text and the contents of a date field but having trouble with the syntax.
It wont display the contents of the date variable
I have
"Effective Date = " & #" & Me.DebtPrincipalDateFld & "#
I've tried a number of combinations and searched all over but to no avail
View 2 Replies
View Related
Apr 16, 2013
I am creating a 2 level report to confirm an order. Main report already created, runs successfully called as subform/subreport under "OrderDetails" form. Linked to master using Order.ID. There are two versions of the confirmation report that have different layouts for different program types.
The hangup comes when I try to add a "Class Dates" subreport. It lists dates of individual classes and Skip dates. I have created the subreport as "srClassDates". When I add it to the main report, it lists the records. However, when I try to link it to the Main report, an error message box appears with the "object variable or With block variable not set".
I have tried rebuilding both the main and subreports, rebuilt the query, have not found anything that changes the result.
Linker has been working successfully on other subforms. Report with groupings works fine, but I need data from 2 tables both linked to order.id.
View 2 Replies
View Related
Apr 15, 2015
Runtime error '91'
Running Access 2010.
I have two reports running off of the same crosstab query. I copied one report to make the second report, then modified the second report to change the background of column fields satisfying certain conditions. These lines of code were added to the Detail_Format section, in color below. The report with the extra code lines does not error--the original report errors.
Code:
Option Compare Database
' Constant for maximum number of columns EmployeeSales query would
' create plus 1 for a Totals column. Here, you have 9 employees.
Const conTotalColumns = 11
[Code] .....
If I say OK (rather than debug) after the error message, I can then click the button for the report again and it runs without complaint. And, as I said, the report with the added code never errors.
View 4 Replies
View Related
Apr 23, 2006
I have a networked database. It is accessed with computers that have both Office 2002, and office 2003. I get the following error message on computers with Office 2003:
"Object variable or With block variable not set"
It happens occasionally and the problem generally fixes itself so I dont think there is anything wrong with my coding.
And when it happens on the computers with office 2003 the ones with office 2002 can open the database fine.
What can be causing this?
Thank you in advance
View 1 Replies
View Related
Dec 14, 2006
Good afternoon all,
The following block of code was working great until I came into work this morning. This routine is called from the OnClick event of several combo boxes. When it does, I recieve the error listed above (Object variable or With block variable not set) on line:
If Me.cbxAss_Filter <> "All" Then
Any ideas? I'm running on no sleep in the last 24 hours so I'm sure I'm missing something simple
The code itself checks the contents of a form and builds a string of conditions which I apply to the Form's filter property to filter records.
Code:Public Sub CreateFilter() '************************************************* **** 'Name: CreateFilter 'Purpose: Generate a string to filter the form 'Inputs: None 'Outputs: None 'Instigates: Me.Filter ' 'Updated: 11/30/06 'By: Chris Lounsbury '************************************************* **** 'Vars Dim strFilter Dim lngLength As Long strFilter = Null strFilter = "" 'Each filter box has its own check for contents If Me.cbxAss_Filter <> "All" Then strFilter = "assigned = '" & Me.cbxAss_Filter & "' AND " End If If Me.cbxAction_filter <> "All" Then strFilter = strFilter & "action = '" & Me.cbxAction_filter & "' AND " End If If Me.cbxStatus_filter <> "All" Then strFilter = strFilter & "status_rsrch = '" & Me.cbxStatus_filter & "' AND " End If If Me.Combo34 <> "All" Then strFilter = strFilter & "rims_flags = '" & Me.Combo34 & "' AND " End If If Me.cbxAAMB_filter <> "All" Then strFilter = strFilter & "aamb = '" & Me.cbxAAMB_filter & "' AND " End If 'Check if filter string was built If strFilter = "" Then Me.FilterOn = False Else 'Determine length of Filter String 'minus the trailing ' AND' lngLength = Len(strFilter) - 5 'Chop off ending ' AND' and set the form filter If lngLength <= 0 Then Else Me.Filter = Left(strFilter, lngLength) Me.FilterOn = True End If End If 'debug 'MsgBox (strFilter) End Sub
View 1 Replies
View Related
Sep 1, 2006
Apologies if this is a basic question. Here's my situation:
I'm trying to update rows in a table based on a count of items in the same table. The table in question contains order line items. On each line item, I'd like to store the total number of items attached to that order (because it affects how the individual line items are processed).
I have a query that seems like it should do the trick, but Access doesn't like it:
UPDATE sales AS S1 SET S1.EXPC = (select count(*) from sales S2 where S2.order_id = S1.order_id AND S2.product_code = "EXPC");
Here's the error I get:
Operation must use an updateable query.
Thoughts?
View 5 Replies
View Related
Oct 7, 2005
hello,
newbie needs help!!!
I have a globally defined variable (gsUser) which I defined in a module.
The idea is that when one of my users creates a record then , I will update a column called "Created by" with the current users value.
The issue is how to get the current user name.
In my main form , in the "beforeinsert" and "beforeupdate" events I have triggered a call to a function in my module (function WhoAreYou)
This function checks the value is gsUser and if blank it then does a DoCmd.OpenForms to my popup form.
The popup form needs to contain a combo box whoses recordsource is pointing at a table of users I have created BUT whose source control is not pointing to a table of any kind.
How can i set up this combo box so that a user can be selected, but the selected user then populates a variable instead of a data column??
I would then user the variable to populate gsUser.
if you could give me an example I would be gratefull
Alternatively if you can think of another way of making the user pick his name from a list of usesr before updating data I would be glad to here it
Thanks in advance for your help
MartyK
View 12 Replies
View Related
Aug 28, 2007
I am trying to create a table to show the parent/child relationships between assemblies, sub-assemblies and detail parts. The table I start with has 2 fields a "Part Number" and an "Indenture".
Please forgive all the periods, couldnt think of how else to seperate the values to depict the tables.
Example:
IND..PN
00....A
01....B
01....C
02....D
03....E
03....F
01....G
00....H
01....I
02....J
I can get the first step to completeing the new table of parent/child relationship like this:
PN....00....01....02....03
.A.....A
.B....."".....B
C......"".....C
D......"".....""......D
E......"".....""......"".....E
F......"".....""......"".....F
G......"".....""......""....G
H......H
I......""......I
J......"".....""......J
Where "" are null values
My problem is making it so that every part number has its own array, aka [family tree], so that for part "F" the table would hold "A,C,D,F"
I though an update query where for example:
*"Zero" is a single field string.
IIF([00]<>null(), Save [00] to Zero, Write Zero to [00])
Would work but have been unable to find any information about passing a field value to a variable for later use or comparison. I would have already done this in excel if the table had less that 65000 records.
Example of final table:
PN....00....01....02....03
.A.....A
.B.....A.....B
C......A.....C
D......A.....C......D
E......A.....C......D.....E
F......A.....C......D.....F
G......A.....C......D....G
H......H
I.......H......I
J.......H......I......J
Any help is greatly appreciated.
BsK
View 1 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
Jan 8, 2015
I use a macro that attaches a document automatically to an e-mail address ready to send this report is governed by a query of which the criteria is " loss order number " this is in a pdf format of which I need if possible
The problem I have is the same pdf file keeps attaching itself to the e-mail no matter which loss order number I select. The reason I think is because the report is called NCR so it is not changing from the previous one. What I think can solve it but not sure and don't know how to do is get the " loss order number " within the report name.
If I just send the report to a file direct it does work as I am prompted for a file name
Can the loss order number get into the file name within a macro/vba
Code:
Name="EMailDatabaseObject
Name="ObjectType">Report
Name="ObjectName">SEND NCR
Name="OutputFormat">PDF Format (*.pdf
Name="To">=[E-mail Address] & IIf(Nz([E-mail Address
View 2 Replies
View Related
Jan 21, 2015
I have the following simple variable assignment...
Dim PolRef As String
Let PolRef = Me.Policy_Ref_Num
But when I run my code I get "Invalid Use of Null" and PolRef is set to ""
This field is populated however, so I'm confused to why I cant set my variable to it.
If I don't use a variable and simply have let XXX = me.Me.Policy_Ref_Num then it still wont return the value from the field.
View 5 Replies
View Related
Mar 15, 2015
I want to get the output of a vba query (only one solution possible) in to a variable but the variable stays empty.
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT info FROM evaluationtable WHERE evaluation= " & evaluationchoice & " ")
var = rst(0).Value
rst.Close
some explination: evaluation and info are fields of evaluationtable evaluationchoice is a field in an accessform where I can choice a value from the evaluation field
the table is build as this (only two fields) evaluation - info
View 5 Replies
View Related
Aug 18, 2013
Why does this work when the text box is used and not when the variable is used directly?
Code:
Private Sub Command61_Click()
Dim strDBName As String
strDBName = getDBName()
Me.Text59 = strDBName
Me.Text62 = DLookup("[ModuleName]", "tblModule", "[DatabaseName] = text59")
'Me.Text64 = DLookup("[ModuleName]", "tblModule", "[DatabaseName] = " & strDBName)
'Me.Text64 = DLookup("[ModuleName]", "tblModule", "[DatabaseName] = strDBName")
'Me.Text64 = DLookup("[ModuleName]", "tblModule", "[DatabaseName]" = strDBName)
End Sub
Text62 returns the correct value
Text64 failes on everyone of the examples
View 7 Replies
View Related
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 8 Replies
View Related
Dec 29, 2013
I have a form which load data from variables in record set where i can make insert update and delete i need to load sub form when the form load the problem is that all text boxes in main box load from variable which are in rerecord set and how can navigate using navigation buttons in main form.
View 2 Replies
View Related
Sep 4, 2013
Code:
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim SQLstr As String
[Code]....
I created this about 1 hour ago but my laptop crashed and didnt save. So, I open a record set and rs is now loaded with the record I want,
how do I assign the value of "Status" as a vba variable. when I try StatusInt = rs I get the "Type Mismatch" error...
View 4 Replies
View Related
