code works but will not compile

[jeds]

Happy Holidays!

I have an option group on a form. It is unbound, but the form uses a query to pass the selected radio button value to a table with:

[Forms]![RemoveMembers]![Frame935] AS Expr2

where Frame935 holds the option group

I expect this is where part of my problem is as searching here I may not have the name for the field correct. So 1st question is how to find the name or value for an option group?

Testing the form the selected option is passed to the table and works exactly as intended. However the code does not compile and I get the error:

Compile error: method or data member not found

As a non-coder; if it is not found how come it works???

Here is the VB:

Private Sub RemoveMember_Click()
On Error GoTo RemoveMember_Click_Err
DoCmd.RunCommand acCmdRefresh
Dim reason As String
Select Case Me[COLOR=red].Frame935[/COLOR]
    Case Is = 1
        Frame935 = "Resigned"
    Case Is = 2
        Frame935 = "Probation Vote"
    Case Is = 3
        Frame935 = "Dismissed"
    Case Else
        Frame935 = "Deceased"
End Select
Dim Member As String
Member = [FirstName] & " " & [LastName]
Beep
If MsgBox("Are you sure you want to remove " & Member & " from the active member list? This cannot be reversed!" & vbCrLf & "Click Cancel to add a comment.", vbOKCancel, "Please Confirm") = vbOK Then
DoCmd.OpenQuery "PastMemberQ", acViewNormal, acEdit
DoCmd.OpenQuery "RemoveFromMembers", acViewNormal, acEdit
Else
Exit Sub
End If
RemoveMember_Click_Exit:
    Exit Sub
 
RemoveMember_Click_Err:
    MsgBox Error$
    Resume RemoveMember_Click_Exit
End Sub

".Frame935" is highlighted when I debug and get the error.

Is this just an Access anomoly to be ignored? I'm thinking (for what it's worth) that it shouldn't be, and as there is a lot more to build in this db I want the code to be compiled.

I think Bob Larson has part of the answer here:
http://www.access-programmers.co.uk/forums/showpost.php?p=1167289&postcount=5

yet I have not enough grasp of what he has going there to apply it to my situation.

jeds

 

[mahenkj2]

would it be possible to post a sample dB?

 

[jeds]

This is the db, I have removed all info from the members and life members tables as they contained personal information.

The past members table only includes ficticious people used to test.

Thanks,

jeds

 

[mahenkj2]

Sorry, but this says unrecognized database format.

I use Access 2007.

 

[mahenkj2]

If this was A2010, please convert to 2007 or earlier. Most of people here may not have 2010.

Also, remove specific features of A2010 (calculated field, navigation control etc.), just keep simple form and then import in blank database.

 

[jeds]

mahenkj2, see if you can open this one.

I have followed the instructions on:
http://msdn.microsoft.com/en-us/office/cc907897.aspx

The one thing I am not sure is that it says to "pick an Access 2007 sort order in the New database sort order list box", It was set to general, now I have it set to "general legacy". There was no "2007" sort order option.

Let me know, thanks

jeds

 

[jeds]

I may not have set the sort order before importing, so here is another just in case

jeds

 

[jdraw]

I expect this is where part of my problem is as searching here I may not have the name for the field correct. So 1st question is how to find the name or value for an option group?

Go to design view, put your mouse pointer into one of the option fields within your "Reason" frame, click, then look at the name in the Properties window. That is the name of that option.

With your code as it is a user could click the Remove button without ever identifying a member. You can check to see if the First and Last name have been valued before making the Remove button visible for example.

From a design perspective, have you considered a MemberType? With a memberType table, you could have a MemberTable with a MemberType field (getting a single value from theMemberType table. It might remove some of the complexity?? Just a thought.

 

[mahenkj2]

Jeds, although you might have taken care of MS advise carefully, unfortunately it still does not open.

anyway, you now have jdraw recommendations, so please follow with him and others.

best regards.

 

[Pat Hartman]

So 1st question is how to find the name or value for an option group?

Click on the frame control to select it. Its name will appear in the dialog box or you can look at the Name property on the Other tab. Individual members of the option group are NEVER referenced when you are looking for a value. You just refer to the name of the frame control.

As a non-coder; if it is not found how come it works???

Code that doesn't compile, doesn't work. You are probably never reaching the code with the compile error. Make sure that Frame935 is actually the name of the control. My advice is to change the control name to something meaningful like fraMemberStatus. Once you do that, any existing code will be orphaned so you'll need to change all Fram935 references to fraMemberStatus.

And finally, you shouldn't have different lists in different tables. The status should be sufficient for you to pull up active or inactive members. That way you don't need to delete the entry from one table and add it to another. You just change the status value and the member automatically changes lists.

 

[jeds]

From a design perspective, have you considered a MemberType? With a memberType table, you could have a MemberTable with a MemberType field (getting a single value from theMemberType table. It might remove some of the complexity?? Just a thought.

I had considered doing so, ended up with the 3 tables as I thought I could wrap my brain around it all better.

With both you and Pat suggesting to put all in one table I may go ahead.
I currently have an Active member status table with 14 different statuses just for the members of that (current) table. The other 2 member tables add 5 more [statuses] to that list. So you would suggest putting all these into the member type table, correct?

The current active member table will be used to track attendance at several diferent types of activities, by type of activity, which the other 2 member types (the additional 5 statuses) are not eligible to participate in. So a form to enter those activities needs to load all the members meeting 1 of the 1st 14 statuses.

An active member's status can change at any time to one of the other 5 statuses. At the time of the change that member needs to remain on some reports to the end of the calendar year while being removed from other reports at the time of the change. I am not sure how to handle that.

Thanks to all for the help.

jeds

 

[jeds]

Click on the frame control to select it. Its name will appear in the dialog box or you can look at the Name property on the Other tab. Individual members of the option group are NEVER referenced when you are looking for a value. You just refer to the name of the frame control.

The name property is Frame935 (copied and pasted from the other tab). I understand it would be good practice to name it something meaningful, I'm thinking it will not change the compile error.

Code that doesn't compile, doesn't work. You are probably never reaching the code with the compile error.

So would that mean there is something else in my design that is giving the desired end result over multiple tests? I.E., the member is moved to the correct table with the selected case (resigned etc) in the correct field.
Very puzzling.

And finally, you shouldn't have different lists in different tables. The status should be sufficient for you to pull up active or inactive members.

As both you and jdraw have suggested it looks as my time would be best spent doing so. Any suggestions you might have are appreciated, I have spelled out my questions/concerns in my reply to his post.

Thank you for your time.

jeds

 

[Pat Hartman]

If you step through the code you will see that the code with the compile error won't run. What is the problem with fixing the compile error?