Someone translate why this is not working

[JohnD]

This is what I have:

Private Sub GO_Click()
If [ARyear] = 2006 Then
    DoCmd.OpenForm "frm2006AR", acNormal
    DoCmd.Close acForm, "frmSelectYear"
    
Else
End If

If [ARyear] = 2005 Then
    DoCmd.OpenForm "frmFilterForm", acNormal
    DoCmd.Close acForm, "frmSelectYear"
Else
    
End If

End Sub

This is on a command button that references an unbound field (ARyear). Naturally when I enter 2005, it pulls up the proper form - however, when I enter 2006 I recieve an error: Run Time '2467'. However, it still opens the form if I click on 'END' on the error box.

My guess is that the code is not finished and thats why the 2005 works (because its at the end).

How do I fix this?

 

[ChrisO]

If you enter 2006 then this form frmSelectYear is closed.

The code continues to run, yes even in a form that is supposed to be closed, and it tries to reference ARyear in the next If statement. ARyear has gone out of scope.

Maybe something like this: -

Private Sub GO_Click()

    Select Case [ARyear]
        Case 2005
            DoCmd.OpenForm "frmFilterForm", acNormal
            DoCmd.Close acForm, Me.Name
       
        Case 2006
            DoCmd.OpenForm "frm2006AR", acNormal
            DoCmd.Close acForm, Me.Name
        
        Case Else
            MsgBox "No Form to Open."
    
    End Select

End Sub

Hope that helps.

Regards,
Chris.

 

[JohnD]

Works like a dream!

Thank you!

 

[ghudson]

This is what I have:

Private Sub GO_Click()
If [ARyear] = 2006 Then
    DoCmd.OpenForm "frm2006AR", acNormal
    DoCmd.Close acForm, "frmSelectYear"
    
Else
End If

If [ARyear] = 2005 Then
    DoCmd.OpenForm "frmFilterForm", acNormal
    DoCmd.Close acForm, "frmSelectYear"
Else
    
End If

End Sub

This is on a command button that references an unbound field (ARyear). Naturally when I enter 2005, it pulls up the proper form - however, when I enter 2006 I recieve an error: Run Time '2467'. However, it still opens the form if I click on 'END' on the error box.

My guess is that the code is not finished and thats why the 2005 works (because its at the end).

How do I fix this?

Here is how the If statement should have been written...

If [ARyear] = 2006 Then
    DoCmd.OpenForm "frm2006AR", acNormal
    DoCmd.Close acForm, "frmSelectYear"
Else
    DoCmd.OpenForm "frmFilterForm", acNormal
    DoCmd.Close acForm, "frmSelectYear"
End If

 

[ChrisO]

Just as a point of correction if I may…

The original code requires opening a specific form based on a specific value.

2005 -> frmFilterForm
2006 -> frm2006AR

The original code requires nothing else apart from an explanation of the error.
No default opening of a form and no closure of the current form under such default condition.

So this reply seems to be incorrect for four reasons…
“Here is how the If statement should have been written...”

1. It does not comply with the logical intention of the original question.
2. It is bolded to imply importance. (That is something only you can answer.)
3. The word ‘should’ would possibly have be better replaced with the word ‘could’.
4. It does not attempt to explain why the error occurred in the first place.

 

[DanG]

I'm gonna run with this one...addressing enum 2,3 & 4

If you don't have anything nice to say, don't say anything at all.
I got left high and dry with this post (post #5 Citrix question)..

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

All I got was "Wrong"...no expanation, no nothing. Now I don't mind learning while I'm earning and taking my licks, but gosh golly, If I am just too darn dumb to answer...just look the other way.

On the other side of the coin you have been very helpfuf in moments of stress..but I gotta say..whuz up with that man?

 

[ghudson]

My posting detailed how the If statement should/could/would have been written based on the original code posted in this thread. ChrisO posted his solution based on how he would have solved the problem. ChrisO usually does provide very detailed explanations with his postings for he has helped me out before when I needed a fix. I do not always have time to provide a detailed how-to with the solutions I post.

Your original posting asked "How do I fix this?" and I provided you with a working solution. It is your right to ignore the advice you are offered within this forum. I prefer to receive more than one option to play with and determine which ones works best with my situation.

Happy Holidays!

 

[JohnD]

Final code

Select Case [ARyear]
        Case 2005
            DoCmd.OpenForm "frmFilterForm", acNormal
            DoCmd.Close acForm, Me.Name
       
        Case 2006
            DoCmd.OpenForm "frm2006AR", acNormal
            msgbox "WARNING! Students information is current as of November 30th, 2005 and will likely change before the Annual Report needs to be submitted as outlined by ACCSCT."
            DoCmd.Close acForm, Me.Name
        
        Case 2007
            DoCmd.OpenForm "frm2007AR", acNormal
            msgbox "WARNING! Students information is current as of November 30th, 2005 and will likely change before the Annual Report needs to be submitted as outlined by ACCSCT."
            DoCmd.Close acForm, Me.Name
            
        Case Else
            msgbox "The Year you entered is invalid.  Please try another year"
    
    End Select

End Sub

Either way - this is a temporary system until I get the other database up and going.

Thank you GHudson for the information on the IF statement as well - its a little more knowledge that im sure I will utilize later.

John D

 

[ghudson]

The Case statement [as mentioned by ChrisO] is the way to go since you are now testing for more than two values.

At least you now know how to write a simple If ElseIf statement.

Good luck!

 

[Mile-O]

Select Case Me.[ARyear]
        Case Is = 2005
            DoCmd.OpenForm "frmFilterForm", acNormal
            DoCmd.Close acForm, Me.Name
       
        Case Is = 2006, 2007
            DoCmd.OpenForm "frm" & Me.[Aryear] & "AR", acNormal
            msgbox "WARNING! Students information is current as of November 30th, 2005 and will likely change before the Annual Report needs to be submitted as outlined by ACCSCT."
            DoCmd.Close acForm, Me.Name
        
        Case Else
            msgbox "The Year you entered is invalid.  Please try another year"
    
    End Select

End Sub

That will do the same thing.

The question is, however, why do you have a form for 2006 and, what I presume, is an identical form for 2007?

 

[JohnD]

Actually, another problem now.

A user from a remote cpu has an error now when they attempt to access the AR. The error is:

"compile error: cannot find library"

 

[ghudson]

Are they using the same version of Access?

 

[Mile-O]

Open a module, go to Tools -> References and reinstall whatever library is listed as MISSING.

 

[JohnD]

SJ
This is before I learned anything about Normalization. Everything was grouped into one table which housed all data for the 2005 Annual Report. In the meantime, I created another table for the 2006 AR (all same fields, just different data) and 2007 with seperate forms that reference the appropriate table.

This is certainly a temporary thing as we needed something fast and I had no idea what I was doing when I created this first one.

In the meantime - its serving its purpose until the other Database is up and running.

 

[Mile-O]

Everything was grouped into one table which housed all data for the 2005 Annual Report. In the meantime, I created another table for the 2006 AR (all same fields, just different data) and 2007 with seperate forms that reference the appropriate table.

Out of interest, does the data per year contain any dates? If so, you can combine all your tables into one and use a query selecting the relevant year (ie. Year([DateField]) ) for binding to your form.

 

[JohnD]

I will try to explain as best I can.

Each AR has between 250 - 350 students.

Each Record (student) has the students start date and has a field to which AR they belong to (2005, 2006 or 2007) and many many more things that relate to each student.

The start date of the student is what determines what Annual Report they belong to - Below shows the dates that the AR covers.

(2005) 7/1/2002 - 6/30/2003
(2006) 7/1/2003 - 6/30/2004
(2007) 7/1/2004 - 6/30/2005

Hopefully this answered the question.

Ghudson,
Yes, we are using A97

 

[JohnD]

I have found what was missing on the other staffs cpu.

ActiveSkin 4.0 Type Library

Now how do I add this on her CPU and where would it go?

...its amazing how computer illiterate you guys make me feel - but its with much appreciation

John D

 

[JohnD]

Okay, just did some checking, but before I do this can someone verify that I am doing this correct?

Under the references in A97 on my system, it specified that the file:

actskin4.ocx

was the file for the ActiveSkin 4.0 Type Library. It was located in my System32 file.

Can I just copy and paste this file over to the other staffs CPU? Same location as it was in mine?

After doing this (in theory) this should correct the problem right?

 

[ghudson]

I doubt it. ActiveX stuff needs to be registered on the users computer.

I ActiveSkin necessary for your application to work? Or can you remove the reference to it in your db so no other computers will error because they do not have it.

I avoid ActiveX ad-ins prevent those type of problems.

Search around for there are other posting related to installing and registering DLL and other ActiveX components onto a users computer but I can not offer any advice on how to do it.

 

[JohnD]

I removed the reference and it works fine now.

Thanks again Ghudson

John D