Modules & VBA :: Returning Values From A Class For Date

May 19, 2014

I have two class variables (both arrays) among about 10 other class variables, that are not returning any values but "" for the string or #12:00:00AM" for a date. Here are my class variables:

Code:
'UPSData Class Module
Private p_LetterArray() As String
Private p_date() As Date
Private p_LetterArraySize As Integer

My Properties

Code:
'Properties
Public Property Get LetterArray(index As Integer) As String
LetterArray(index) = p_LetterArray(index)
End Property
Public Property Let LetterArray(index As Integer, NewValue As String)

[Code] ....

As I said, tLet and tDate result in "" and #12:00:00AM# respectively. When I step through the code, the values for tmp.LetterArray(0) is assigned "src" and tmp.UPSDate(0) stores "12/25/2013" correctly.

When I assign tLet and TDate, the same thing happens when stepping through the code. I'll use the LetterArray property to describe what happens:

Get LetterArray is called. p_LetterArray(0) does equal "src"
Let LetterArray is called. NewValue is "src" and p_LetterArray is "src" when End Property is highlighted in the debugger
Scope returns to Get LetterArray with End Property highlighted. In checking the values, LetterArray(0) = ""

Same steps happen with the same results ("12:00:00AM" vice "")

View Replies


ADVERTISEMENT

Forms :: Returning Difference Between Date / Time Values In HH.MM Format

Dec 11, 2013

I want to be able to display the result of a difference between Date/Time values in "HH.MM" format (i.e. yes I want the result in decimals and I don't want Access to round up or down just because it feels like doing it!). I have used the following:

Example 1:

Dim ActualManHours as Long
ActualManHours = (txtEndTime.Value - txtStartTime.Value) * 86400
txtActualManHours.Value = ActualManHours

Example 2:

Dim ActualManHours as Long
ActualManHours = DateDiff("h",txtStartTime.Value, txtEndTime.Value) & "." & Format(DateDiff("n",txtStartTime.value,txtEndTime. value) Mod 60, "00")

Example 3:

Dim StartTime as Double
Dim EndTime as Double
Dim ActualManHours as Long
StartTime = CDbl(txtStartTime.Value)
EndTime = CDbl(txtEndTime.Value)
ActualManHours = EndTime - StartTime
txtActualManHours.Value = ActualManHours

View 7 Replies View Related

Modules & VBA :: Returning Values Into A Query Or A Table

Jun 18, 2013

what I am trying to do with pretty much create a search query through code. So what's happening exactly is that the user enters a part number and expects to get 2 values: total orders and total items associated with the part number he/she entered. I have 3 tables and 2 of them are related. So I began my creating a query relating table 2 and table 3. I cannot include table 1 in the query.

1) Part Number is input by user
2) Search that Part Number into Table1
3) Take returned Parent Values associated with Part Number and store in Array
4) Modify Array values
5) Search Array values using a For Each loop into the query
6) Take the returned values found in query associated with each parent that was retrieved from the part number (user input) and return that through a table or query.
7) Also return the total rows in a specific column (Order Numbers)

View 10 Replies View Related

Modules & VBA :: DLookup In Custom Function Not Returning Values

Sep 13, 2013

I made a custom function to look certain value from table based on couple of criteria that it gets from query where I want to use it. Function's code is below:

Code:
Public Function PotteryWeights(strLocusID As Long, nrPotSubID As Long) As Variant
Dim priSubID As Long
Dim priLocusID As Long
Dim priResult As Variant
priSubID = nrPotSubID

[Code] ...

However, when I use it in query it only returns Case else - option and everything else is empty.

View 7 Replies View Related

Modules & VBA :: Class Does Not Support Automation

Jul 31, 2015

I get the message "The expression On Click you entered as the event property setting produced the following error: Class does not support Automation or does not support expected interface"

I receive the message on a PC running Windows 7 Professional using the Access 2013 Runtime. This pc does not have Access 2013 installed.On my pc, I do not get the error. I have Access 2013 installed and run Windows 7 Professional SP1. The "code" which gives the error is as follows and is invoked by clicking a button on a form

Code:
MsgBox "1"
Dim rst As ADODB.Recordset
MsgBox "2"
Set rst = New ADODB.Recordset
MsgBox "3"

[code]....

The error takes place after Msgbox "2" and before Msgbox "3".The strange thing is that I can run without a problem a sophisticated software package on the pc which gives the error, using Access 2013 Runtime. This package I converted from Access 2003.

View 3 Replies View Related

Modules & VBA :: Object Or Class Does Not Support Set Of Events

Mar 19, 2014

I have this below which some people cant run and some people can. All are using 2010 runtime version. Apart from the one guy who can run the macro who has full 2010 version. I have 2010 runtime installed and i can also run the macro fine.

Private Sub Option12_Click()
On Error GoTo Option12_Click_err
Dim CntlPay As String
Dim Lable As String
CntlPay = "D"

[code]....

View 1 Replies View Related

Modules & VBA :: Auto Initialize Class Module

Sep 16, 2013

How you automatically initialize a newly instantiated class?

So when you create a new instance it will run a procedure to set attributes.

View 4 Replies View Related

Modules & VBA :: Object Or Class Does Not Support Set Of Events

Oct 21, 2013

I have wrote a database in access 2010 and the database works fine for me (I am the db admin with full control).I gave the database to a group in which most of them also have no issue with the file. They are able to use the database with no issues..One of those members gets an error message when completing the initial step in the database: (select a drop down item from a combo box)

Code:
The expression After Update you entered as the event propoerty setting produced the following error: Object or class does not support the set of events

Something I cant seem to understand is why is this happoening on 5 machines but not on the 6th, they all have the same PC set up so there should be no error on one particular machine.

View 6 Replies View Related

Modules & VBA :: Collections Class On Continuous Forms

Jul 31, 2013

Any way to make objects to support adjusting continuous form controls making them unique in a sense to be able to change info like captions or whatever.

I've been working with collections for a while now tapping into event handlers such as the mouse inputs and or sizing of objects real time using collections but I can't seem to nail down any un-documented way to serialize controls on a continuos form.

I have tried when the new user creates the record adding another control to the collections class tying it to the id of the record but still this does not seem to be enough.

I do not know if there is a paint method or something of the sort I can override to get the desired results and most people without even trying will tell you this can't be done but how many have said that you can't move and resize objects in access but we are doing that now...

View 14 Replies View Related

Modules & VBA :: Referencing ActiveX Control In A Class

Jun 26, 2015

I'm using Access 2003. In "References" (Tools > References > Browse...), I've added in "Microsoft Windows Common Controls 6.0 (SP6)" (c:windowssystem32mscomctl.ocx).

I've then created/inserted an instance of the control "Microsoft ListView Control 6.0 (SP6)" on a Form ("MyForm") and given the listvew control the Name "MyListView".I wanted to decorate MyListView with some custom methods so I've created a new class module ("DecoratedListView") which contains a member field called "lvw".

I want 'lvw' to point/reference to MyListView, but I don't know what reference type to use in its declaration. Importantly, I also want to capture lvw's ColumnClick event.

I've tried:

Code:
Public WithEvents lvw As Object
Public WithEvents lvw As Control
Public WithEvents lvw As MSComctlLib.ListView.2
Public WithEvents lvw As MSComctlLib.ListView
Public WithEvents lvw As CustomControl

and none works when I

Code:
set lvw = Forms!MyForm.MyListView

The first try (Object) doesn't even compile. I get the exception "Expected: identifier"
The second try (Control) doesn't compile either. I get the exception "Object does not source automation events"
The third try (MSComctlLib.ListView.2) doesn't compile and throws the exception "Expected: end of statement"
The fourth try (MSComctlLib.ListView) compiles but fails at runtime with the exception "Type mismatch". Using TYPENAME() on the control returns "CustomControl".
The fifth try (CustomControl) compiles but fails at runtime with the exception "Object or class does not support the set of events".

I can't believe I'm the first person to have tried to capture an ActiveX Control's events in a separate class.

View 1 Replies View Related

Returning 0 Values

Jul 20, 2007

Hi anyone!
I have a table that tracks data for parts across different depots. Thus some depots might not have records for a part in a particular depot whilst others do on a given day. My table does not record a 0 value for parts in a dept that don't have a part on a given day, it simply does not record anything.

I would like to query all depots and have it return a 0 value where there are no records on a given day for a given part in that depot. Does anyone know how to do this? i.e. if no record return 0 as default.

Does anyone know how to do that? At the mo my query simply exlcudes all records on taht day for all depots?

Thanks in advance

View 2 Replies View Related

Modules & VBA :: Class Event For Multiple Controls On Userform

Mar 5, 2014

Basically I have a class event that works for all controls on any userform.

Its all working fine but I can't add an event to trap the click or mouse move of the userform itself.

Basically so when the cursor moves away from a control the label clears.

User Form Code

Code:
Option Compare Database
Option Explicit
'Define a collection and initialise the commandbutton event class
Private col As New Collection
Private newCmd As New clsEvents

[Code] .....

And here is the class Module

Code:
Option Compare Database
Option Explicit
'catch the event of Commandbuttons
Private WithEvents C0 As SubForm
'Private WithEvents C1 As CheckBox

[Code] ....

View 11 Replies View Related

Modules & VBA :: Creating And Initializing Custom Class / Object

Jun 13, 2013

I've been playing around with creating my own class in VBA but I'm having problems calling its methods. My class is pretty basic, its called cDentist and the properties are just Name, Address, DOB etc and one method AddDentist. AddDentist will add the details to a sql server table.

So I create an instance of the class in a module called Dentist. At the very top of the code i put..

Code:
Option Compare Database
Global Dentist As cDentist

I have a method then in module Dentist, where I initialise the instance of class cDentist and i populate it's properities...

Code:
Sub RecordDentistDetails()
Set Dentist = New cDentist
Dentist.Name = Forms!frm_enterdetails!txtName

[Code].....

When I click 'Save' I get error, Method or data member not found. And '.AddDentist' is highlighted. I'm a little confused why it's not seeing AddDentist as a method of class cDentist.

View 14 Replies View Related

Modules & VBA :: Error Passing Variable To Class Module

Jan 30, 2014

I am trying to pass a boolean variable to a class module

Code:
Set rps.ViewS = View

the code in the module that this in theory is calling reads as

Code:

Private ViewC As Boolean

Public Property Set ViewS(ByRef ViewA As Boolean)
Set ViewC = ViewA
End Property
Public Property Get ViewS() As Boolean
Set ViewS = ViewC
End Property

However I am getting the error message

Quote:Definitions of property procedures for the same property are inconsistent, or property procedure has an optional parameter, a ParamArray, or an invalid Set final parameter.

View 6 Replies View Related

Select Not Returning New Values

Nov 28, 2007

Hello!

I've good a very very strange problem...
I do an insert of a new row into the table TRA. That insert creates an id for the new row.
Right after that I do a select of the new row, trying to get the new id created.
Well, the insert is working fine, but the select sometimes gives me values, sometimes not.
How is it possible?? I've tryied closing the database and opening it again, but is not working anyway...

Any idea??

My code right now is the following: (closing the database, setting recordset to nothing... and still not working properly)


sql = "insert into tra (semana, empleadoid) values('" & semana & "'," & empleadoId & "); "
DoCmd.RunSQL (sql)
db.Close
Set db = OpenDatabase(database)
sql = ""
Set rcset = Nothing
sql = "select * from TRA where (semana='" & semana & "' and empleadoId =" & empleadoId & "; "

View 10 Replies View Related

Returning All Values In A Query

Jan 21, 2006

Hi.
Can someone help me?
I am trying to make a query return all values (please see attached jpg)
It returns some of the values, but is not returning values that have nothing in the "material" field (Null Values?)
In this example, I would like it to return all values that fulfill the Completion_Date, CW_Department, Handed_Over and Chargable fields, whether they have any data in the Material field or not.

Do I need to use the NZ, IIF or IsNull function? I have seen this is previous threads, but nothing like this example.....I apologise if there is.

Many Thanks.

Frank.

View 2 Replies View Related

Modules & VBA :: Function To Get Fractions From Double - Error 91 With Custom Class

Jun 10, 2014

I haven't created classes in a while, and I don't see what is the problem

I'm modifying a function to get fractions from double. It worked well, but it returned a string.

I need it to be in parts, so I created this class

Code:
Option Compare Database
Public Entier As Integer
Public nominateur As Integer
Public denominateur As Integer
Public Function getText()
getText = Entier & " " & nominateur & "/" & denominateur
End Function

I changed the function type and the assignation:

Code:
Function GetFraction(ByVal Num As Double) As nombreEnFraction
Set GetFraction = New nombreEnFraction
If Num = 0# Then
GetFraction = "None"
Else
Dim WholeNumber As Integer
Dim DecimalNumber As Double

[Code] ....

For some reasons, when I get to the end of the function, I get a 91 error, like if it was nothing

But when I use a spy, I see values in the object until the end.

View 1 Replies View Related

Modules & VBA :: Class Variable Resets When Adding New Record In Subform

Mar 30, 2015

I have a parent form which has a class variable (class module instance) to store the form' status and more.... and when i add a new record to the subform it resets the class variable field' data. but this only happens on first transaction, but if i re-run the steps (re-set the variable field value) it's not happening again.

View 3 Replies View Related

Returning Multi Select Values

Sep 29, 2005

I have a problem that I haven't seen discussed here yet. (I know this is not good practice, but we're doing this for transfer to another application that wants and accepts data this way)

1) I have a multi select list box.
2) I have code that saves all the values in a semi colon delimeted list in a single field in the DB. (As I said, the application that this data is going to (and from) wants and accepts data in this way)

3) My problem is this. When I re-open my form, how do I get the values that are stored in the database to be highlighted in the multi select list box. I have code similar to this running, but still can't figure it out.

Dim lst() As String

rst.Open sSQL, cnn, adOpenStatic, adLockOptimistic
lst = Split(rst(0), vbCrLf, -1) 'Split the recordset into an array
For i = 0 To List96.ListCount
For j = 0 To UBound(lst) 'lopp until the upper bound of the array

'If the item in the list box = the item in the array then highlight the item in the list box
If List96.ItemData(i) = lst(j) Then
List96.ItemsSelected(i) = True
End If
Next j
Next i

In a nutshell, that's the problem. My list box contains a list of about 5 or 6 things, the user can select any number of them, and I store it in a single field in the DB semi colon delimited. However, I don't know how to return that field to my list box with the selected values highlighted. IE if the list contains A, B, C, D and the user selected A, C the field in the DB looks like this A;C. When I open the form again, I want to highlight A and C in my list box since those were the fields the user selected.

What happens is A;C gets passed to another application (which wants it that way). However, in that other application those values may or may not change, and then they will be passed back to me. (IE the other application may change those values to A;B;D and pass it back to me (which I accept no problem). However, when the user opens the form I want A B and D to be highlighted in the multi select list box

Any help is much appreciated

Robbo

View 4 Replies View Related

Query Not Returning Null Values

Oct 10, 2011

2007 version - my child query is not returning null values. I have 2 fields, both pulled from a parent query. One is "DaysSinceRecd", the other is "AmountPaying". The parent query returns multiple results, which is fine. For example, the parent query might have 10 different records for "0" "DaysSinceRecd", with different "AmountPaying" for each of those records.

In the child query, I am trying to condense that down, so that I only have one "AmountPaying" for each "DaysSinceRecd". It's working fabulously, except that there are records in the parent query that have a value for "AmountPaying" but a null value for "DaysSinceRecd". The null value for "DaysSinceRecd" cannot be changed to another value, it needs to stay null. My child query is not returning the null records. I tried adding to the criteria: IsNull Or IsNotNull, >=0 or <=0. That's not working.

View 5 Replies View Related

Modules & VBA :: Subtotal Data Access To Excel - Range Class Failed

Jun 6, 2014

I'm trying to subtotal data in an excel file but I am getting a subtotal method of Range class failed in the red text below. I have been trying to get this to work with no success.

Code:

Public Function SUMMARY()
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
With xlApp
Dim wb As Object

[Code] .....

View 3 Replies View Related

Iif Statement To Read Fields From Form Not Returning Values

Nov 4, 2006

Hey there!
I've been searching about this forum and found a lot of threads on this sort of issue but haven't been able to figure it out from those alone, so feeling a bit useless :o long winded explanation by the way... I'm useless at explaining! :D

Well basically I have a query that is supposed to power a flexible search form, where you choose the fields you want to search by and enter your criteria. The form contains labelled fields with checkboxes next to them, which you tick if you want to search for something in that field. The results are displayed in a subform which reads from the query.

The problem is with the query! My solution seemed to work fine when I put my criteria in just one field when testing (that is the fields in query design builder, not the form), but when I applied the same to two fields it didn't. Instead, it would return nothing unless I specified exact and correct criteria for BOTH fields I had set up with my solution. So whereas before I could tick "Customer" and search for a customer account number and it would display ALL relevant records, now if i just ticked "Customer" and entered the same number, it would display nothing, unless I ALSO ticked "Booking" and entered a correct booking number that matched a record for that customer in the tables.

I've heard iif statements don't do too well in the criteria fields in the query design builder?? But I know nothing about using SQL really.

For example for the invoices field have used:
IIf([Forms]![frmFindInvoices]![ChkInvNo]=True,[Forms]![frmFindInvoices]![InvNo])

So:
IIf(form checkbox = true, form text box value)

However it seems to think that if the checkbox is not ticked, I want it to only display EMPTY invoicing fields. This is never the case and so no records are shown! If the tick box is not shown, there is not supposed to be criteria for this field, in other words it should allow ANYTHING through.

What I don't know how to do is tell the bloody thing that if the box is NOT ticked, it is NOT to check for anything in this field, because the user does not want to specify any criteria for this field! Things like "Is Null" produce the same results, probably because the query still thinks I want it to find records that have empty fields.

Any suggestions to specify to the query what I want? I really need to get this done! Thanks! :)

View 11 Replies View Related

Forms :: Returning Values From Tables Based On Criteria

Jun 27, 2013

I have two tables that will contain the information that I need the third table that will be used for constant data entry to auto fill in two of the fields (Class, Rate)based on four criteria from the first two.

Table 1
EmpID Name
1 EMP1
2 EMP2

Table 2 (A Subtable of Table1 based on EmpID)
EmpID ACDate Class Rate MJob SJob
1 1/6/13 A 15 100 1A
1 2/6/13 B 20 100 1A
2 1/6/13 A 18 100 1B

Table 3
Name WDate MJob SJob Class Rate
EMP1 1/7/13 100 1A A 15
EMP1 2/5/13 100 1A A 15
EMP1 2/6/13 100 1A B 20
EMP2 1/6/13 100 1B A 18

View 2 Replies View Related

Queries :: Listbox Filter Not Returning Empty Values

Jun 14, 2013

I have a form in which users can enter data in several textboxes to filter the listbox below it, this works great except for the fact that when a record lacks certain data it doesn't show up

Basically there are 4 filters, one for the name/id which works great, as the entries without id's show up just fine but this filter needs to be checked against the other 3 filters, for their group, education and type.

Lets use group as an example.

When a student has no group it should only show up when the group filter is an empty string. AFAIK the wildcards should see to that.

Currently however, when a student has no group, it doesnt show up at all unless I remove the | Like '*' & [groepFilter] & '*' | part from the query.

I've never used complicated WHERE's like this so it might be something very simple. I think I could get it to work using VBA and modifying the rowsource of the listbox from there but this would be a lot easier and I'm curious as to why this doesn't work.

The SQL:

SELECT Student.Studentindex, Student.studentid, Student.studentvn, Student.studenttv,
Student.studentan, Student.Groepcode, Opleiding.opleidingsnaam, Opleiding.type
FROM Student LEFT JOIN Opleiding ON Student.opleidingid = Opleiding.opleidingid
WHERE (((Student.studentid) Like '*' & [naamFilter] & '*') AND ((Student.Groepcode) Like '*' & [groepFilter] & '*')

[Code] ....

View 2 Replies View Related

Queries :: Calculating And Returning Values From External Database?

Jan 2, 2014

I have created a link to a CRM system that we have, and am using access as an interface for a specific task with the data contained within this CRM.

I have written a query that pulls to fields of data together but cannot get the two to calculate.

Within SQL server management studio I use the following query to give me a numerical response.

SELECTPRCPRICE,SUM(CAST(VL2BENEFICIALASNUMERIC (20,6))*CAST(PRCPRICEAS MONEY)))ASHOLDINGVALUE

Access doesn't appear to accept the cast functionality / how I can perform a similar functionality with access?

View 9 Replies View Related

Queries :: Count And Sort Criteria - Returning On Blank Values

Nov 18, 2013

I have a list of employees and sort criteria. for example

empID....Criteria
1234......T
1234......F
1234......T
1234......F
1235......F
1236......T
1236......F
1236......F
1236......F
1236......T
1236......T
1237......F

The output I am looking for is a count of the number of times T appears by an employee, BUT is there is no record it would return 0

E.G.
empID......Count
1234...........2
1235...........0
1236...........3
1237...........0

I can get it to return:
empID......Count
1234...........2
1236...........3

using Count and the criteria Where Criteria="T" but not returning zeros.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved