I need to declare a variable (max_nog_leveren ) but that variable may not be rouded or only after 5 digits so as example 15,xxxxx
I am now using it as a "Integer" but that is not working very well..
Code:
Dim iRet As Integer
Dim strPrompt As String
Dim strTitle As String
Dim max_nog_leveren As Integer
Dim strQuery As String
Dim nettohoeveelheid_toevoegen As String
[Code] ....
I want to define a public variable and i am using the following code but it gives me Compile error Invalid outside procedure.
Code:
option explicit
public ABC1 AS VARIABLE
ABC1="FALSE"
Is it possible (and how...) to declare a module-specific form variable (or any variable for that matter) at the top of said module, so it doesn't need to be set at the start of each subsequent procedure?
I have a module of code specific to one form with a number of procedures, each one of which requires me to Dim / Set the form variable. It would be much neater if I could do it once at the start.
Code:
Option Compare Database
Option Explicit
Public Sub Populate(lngParameter As Long)Dim frm As Form
[Code] ......
we upgraded to 2003 access. Now in a code getting the msg that I need to
declare a variable for:xlexcel9795
here is how it is in the code. what do I put?
FileFormat:=xlexcel9795
I am trying to get Access to round numbers to the third decimal place and I can only get it to round up to the nearest whole number. I have used the help function and tried several things but can not get ACCESS to round as I need it. Any HELP is greatly appreciated!!
Thanks,
DOUG
Hi
Does anyone know how to round a number in the query designer.
Thanks
Dharmesh
This is what i have typed into my field in the query designer. what am i doing wrong.
Base price: Round([base price],1)
when enetering data into a field on a form it automatically rounds it up the number up to the nearest whole number. this is really frustrating, does anyone know how i can solve this problem?
thanks
I am looking to round numbers to specific values 2, 4 and 6.
I was thinking of a code in a query something like:
if <3 but >0 then 2
if <5 but >3 then 4
if >5 then 6
can anybody convert this into queery code, not SQL tho because I'm rubbish at that. I just want to type it into the criteria on my field box on the query.
I also just tried to do it with:
IIf(>0 And <3,2,IIf(>3 And <5,4,IIf(>5,6,IIf(0,0))))
but this only seems to return results of 4 and doesn't show all entries, like entries with no value entered. I also think i'm missing out the numbers 3 and 5 because it's less than and greater than but not the number itself:
if 5 or < or > (to include the 5)
thanks
Ok I've now got
IIf(<1,0,IIf(>1 And <3,2,IIf(>3 And <5,4,IIf(>5,6,IIf(0,0)))))
which I think is doing roughly what I need but i have stumbled on a new problem.
These results are based on the average result of another query but i need to add a section before the results are averaged that says
if(=0 or no entry then all results are 0 for that unit else avg but iif(<1 etc. (the original statement above)
Thanks for your help
I can not have any negitive numbers show up in my field so I need to round up and negatives to "0" ...
View 10 Replies View RelatedCan't find any way to declare variables in the sort of module that Access uses by default.Having changed the default, I now get 'Option Compare Database' as the first line (previously nothing was there). Adding anything at all in that section, or changing it to 'Option Explicit' has the consistent effect that all the procedure names in the module are not recognised and nothing works.
Variables declared in procedures work only in the procedure even if declared as Public. Can't get a Static variable to work in more than one procedure. However many variables I declare, there are never any in the Declarations list at the top of the Code Window.
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 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?
I have 2 fields that I would like to automate if possible
One field is called "p/o number" and another field called "line no"
These fields are part of an ordering database
Let say I have 200 items to purchase form 10 suppliers
And form example 20 items from each supplier
What I do at present is put the order number on each line item and the line number
example
p/o number line no
1 1
1 2
1 3
2 1
2 2
2 3
2 4
What I want to do is just put the first po number in the required line . Put the first line number in i.e. "1" and the macro will complete all the p/o numbers and line numbers for me as per the ones marked in red.
Example
1 1
2 2
3 3
2 1
2 2
2 3
I have 2 similar numbers in a table with the following parameters:
Double, Fixed, DecPlaces = 2
One number (GLminor) = 0.10
Second Number (GLmaj) = 0.50
When i use the following code to create and pull the numbers from a recordset the (Gmin) is OK and displays as 0.1 but the (Gmaj) has a problem and displays as 0. So when i'm attempting to use it in an equation (OtherNumber) / (Gmaj) i'm getting a divide by zero error.
Code:
sSQL1 = "SELECT * FROM tblRScales WHERE ID = " & RScaleID
Set rs1 = CurrentDb.OpenRecordset(sSQL1, dbOpenDynaset)
rs1.MoveFirst
Gmaj = rs1!GLmaj
Gmin = rs1!GLminor
I've tried using CDec(OtherNumber) / CDec(Gmaj) and still get the same error.
As both numbers have the same properties in the table parameters i'm completely lost as to why one seems OK while the other seems to round down to 0.
Is it possible to declare a reference as an object, this way we could avoid our issues with broken references? I would just try it, but it seems like there is a quite a lot of testing it would require.
View 11 Replies View RelatedIf 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 RelatedHow 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
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]....
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]....
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
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
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.
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
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