newb question on arrays and vba

hoda64

Registered User.
Local time
Today, 18:42
Joined
Jun 18, 2008
Messages
30
Hello,

I am a self learning access user and I have some very basic experience with vba with random tasks that have been assigned to me at work. This is the first time that I am trying to use arrays with Access and I am facing some challenges. I have a fundamental understanding of arrays from c/c++ courses, but I have never used them in Access. This is my basic code that I am using to play around with arrays:

Code:
Private Sub Form_Open(Cancel As Integer)
Dim Event_Date(2) As Date
Event_Date(0) = 3 - 1 - 2001
Event_Date(1) = 4 - 1 - 2002
Event_Date(2) = 5 - 2 - 1980
Debug.Print Event_Date(2)
End Sub

My set up is that I have a form called "arraypractice" and in the On Open event I have placed that code. When I change from Design view to Form view, I get a "module not found" error.

I think my main problem is that I do not know the proper way to use "Debug.Print" and I am having trouble googling clear instructions on how to use it.

I eventually want to be able to sort an unknown number of dates and use that sorted array to figure out how many dates are repeating.

Thanks for any guidance! :)
 
Well, in Access 2003 that does not cause the error.

I think you will find that doing a compile might show where the error is.
In Access 2003 from the VBA editor there is a Debug button on the menu.

If there is no other code in the module I would be inclined to create a new Form and copy/paste that code into it.

That code is not doing what you want but it should not cause a runtime error.
(You will end up with some strange dates.)

Chris.
 
Thanks for your response. I am using Access 07. I ended up having to import the database into a new database because there was a corrupt form and it was not letting me delete it.

But now that I do not get a runtime error, when I go from design view into form view I only get a blank form. There are no dates, let alone strange ones. How am I supposed to be using this Debug.Print command to be able to print out an array? Thanks in advance for your responses.
 
First, the way you set your date value is wrong.
3 - 1 - 2001 = -1999, date is 07/10/1894 (mm/dd/yyyy).

Your code doesn't write the values to the form, to do so you have to change it to something like:

Code:
Private Sub Form_Open(Cancel As Integer)
Dim Event_Date(2) As Date
Event_Date(0) = #3/1/2001# 'in VBA dates are US formatted
Event_Date(1) = dateserial(2002, 4, 1) 'Year , Month, Day 
Event_Date(2) = dateadd("d" , 10, #5/2/1980#) '
Debug.Print Event_Date(2) 'this writes in the intermediate window

'Output to the form
me.someControl1 = Event_Date(0) 'Replace someControl1 wit the name of the textbox
me.someControl2 = Event_Date(1)
me.someControl3 = Event_Date(2)
End Sub
 
PeterF, thanks so much for the clear steps you provided! I tried that code and it worked (obviously as you wrote it :))

As I mentioned in my first post, I want to sort the array. I understand that vba for access does not have a Array.Sort function. I found this function on this site through my googling:

http://www.access-programmers.co.uk/forums/showthread.php?t=194737

I am trying to implement that function like this:

Code:
Function SortArray(ArrayToSort() As Date) As Variant
 
    Dim First           As Integer
    Dim Last            As Integer
    Dim i               As Integer
    Dim j               As Integer
    Dim Temp            As Date
 
    First = LBound(ArrayToSort)
    Last = UBound(ArrayToSort)
    For i = First To Last - 1
        For j = i + 1 To Last
            If ArrayToSort(i) > ArrayToSort(j) Then
                Temp = ArrayToSort(j)
                ArrayToSort(j) = ArrayToSort(i)
                ArrayToSort(i) = Temp
            End If
        Next j
    Next i
 
        For i = 1 To UBound(ArrayToSort)
            Debug.Print ArrayToSort(i)
        Next i
End Function
 
Private Sub Form_Open(Cancel As Integer)
 
Dim Event_Date(2) As Date
Event_Date(0) = #3/1/2001# 'in VBA dates are US formatted
Event_Date(1) = DateSerial(2002, 4, 1) 'Year , Month, Day
Event_Date(2) = DateAdd("d", 10, #5/2/1980#)  '
'Debug.Print Event_Date(2) 'this writes in the intermediate window
 
SortArray (Event_Date())
 
'Output to the form
Me.Text0 = Event_Date(0) 'Replace someControl1 wit the name of the textbox
Me.Text2 = Event_Date(1)
Me.Text4 = Event_Date(2)
End Sub

I made some tweaks by changing the "As Variant" for the array to "As Date" and the Temp variable is also "as Date" from "As String". However I am getting a type mismatch error where I call the function. As always, any guidance is greatly appreciated. Thanks in advance.
 
When posting about code errors it is best to state on which line the error occurs.

This line is incorrect, an array can’t be evaluated:-
SortArray (Event_Date())

so try:-
SortArray Event_Date()

Chris.
 
Wow, what a simple mistake. Thanks so much!
 

Users who are viewing this thread

Back
Top Bottom