ACCESS FEED: Re: [MS_AccessPros] Re: option group error message

Jessica-

To further enforce what Duane, Bill, and Crystal have told you:

ME:

When referencing controls or fields in VBA code or queries, the Access object model has rules about how to make such references. Whenever a reference is unclear within a given context, it's always best to use a full reference to what you want. Furthermore, Objects are gathered into something called Collections, and Objects within Collections usually have several Properties and Methods. The general syntax is:

CollectionName!ObjectName.PropertyName

CollectionName!ObjectName.MethodName

Sometimes, Objects themselves contain other collections. For example, all forms have a Controls collection, and all controls have a Properties collection. Then the syntax is simply expanded:

CollectionName!ObjectName.CollectionName!ObjectName.PropertyName

For example: Forms!MyForm.Controls!txtSomeTextBox.Visible

.. checks the Visible property of the control called txtSomeTextBox on the open form called MyForm.

If you have the name of an Object saved in a Variable, there's an alternate syntax that lets you specify the name dynamically. For example, you might have some Public code that can perform some set of actions on any form that is open. That code can be called from anywhere, and the code that calls it will specify the name of the form that the code should work on. The alternate syntax look like:

CollectionName(<variable or literal containing the name>).PropertyName

Here's a procedure to close any form, discarding any changes that haven't been saved:

(Note that all forms that are open can be accessed via the Forms collection.)

Public Function CloseForm(strFormName As String) As Integer

' Trap any errors

On Error GoTo UhOh

' If changes haven't been saved (the Dirty property of the form will be True)

If Forms(strFormName).Dirty = True Then

' Undo any changes - execute the Undo method of the form

Forms(strFormName).Undo

End If

' Close the form

DoCmd.Close acForm, strFormName

' Close was successful - set OK return

CloseForm = True

Done:

Exit Function

UhOh:

MsgBox "Error closing form " & strFormName & ": " & Err & ", " & Error

' Set failure return

CloseForm = False

Resume Done

End Function

Within VBA code, you could always use the full syntax to make references to objects, like this:

Select Case Forms!MyForm.Controls!Frame332.Properties.Value

Ugly! Fortunately, there are lots of shortcuts. All Objects have a "default" collection and a "default" property. As you might guess - to make things easier - the default collection of a Form is "Controls", and the default property of a Control is "Value". When you want to reference the default collection or property, you can leave out the Collection name. The above example now becomes:

Select Case Forms!MyForm!Frame332

AND within code, all Forms have a property called "Me" that is a direct reference to the form in which the code is running. (The same is true for Reports and their code.) So, instead of

Forms!MyForm!Frame332

you can use:

Me!Frame332

I bet you noticed that sometimes I use ! and other times I use . to separate parts of a reference. The rule about when to use ! and . (also affectionately known as "bang" and "dot", respectively) is quite simple:

When the name is the name of something that you created (the name of a form you designed or the name of a control on a form, for example), use ! or a "bang".

When the name is the name of something that is built-in to Access and its object model (the Forms collection, the Controls collection, or the name of a Property or Method that Access defines), use . or a "dot". You created the form name "MyForm" and the control named "Frame332", so those names should be preceded with a ! ("bang"). Access created the Controls and Properties collections and the Value property, so those names should be preceded with a . ("dot").

Ah, but you might have noticed that we have also said you can use:

Me.Frame332

Wait a minute! Doesn't that violate the "bang" and "dot" rule? It turns out that to make it really simple, Access when it loads a form *adds* to the Properties collection of the form the name of every control you defined. This is one case where you can use "bang" or "dot" interchangeably.

Me!Frame332 — refers to the object named Frame332 in the default collection of Forms!MyForm (Me) or Forms!MyForm.Controls!Frame332

Me.Frame332 — refers to the Property named Frame332 in the Properties collection of Forms!MyForm (Me) - which happens to point to the control you want because Access created that property when the form opened.

I mentioned above that you can also use this syntax in queries. Let's say you have two forms - one to edit Customers and another to edit the customer's Orders. The Record Source of the two forms (respectively) might look like this:

SELECT * FROM Customers

SELECT * FROM Orders

But when looking at a specific Customer of the CustomerEdit form, you'd like to provide a command button that opens the OrdersEdit form and shows orders only for the current customer. You could modify the Record Source query of the OrdersEdit form to look like this:

SELECT * FROM Orders WHERE CustomerID = Forms!CustomerEdit!CustomerID

When processing a query, the query engine tries to resolve all field references to one of the tables mentioned in the FROM clause of the query. When it encounters a reference that is not in one of the tables, it assumes that the reference is a Parameter that must be resolved outside the query. If you were to run the query above without having the CustomerEdit form open, you would find that the query engine prompts you for a value for "Forms!CustomerEdit!CustomerID". When the CustomerEdit form is open, the query engine sees if it can find a control called CustomerID on the form. If it can, it silently substitutes the value found in the control in the filter, so the query returns only orders for the specified CustomerID. Magic! This technique is also useful if you want to present the user with an unbound form containing text boxes in which the user can enter filter criteria. The form can then open another form bound to a query that has filter parameters pointing to the values the user entered. This is a lot cleaner than simply opening the form that uses the query containing parameters and have the user respond to two or three separate prompts.

TABLE DESIGN:

You can certainly jump right into Access and start defining huge tables willy-nilly and perhaps get something to work. What's not obvious, however, is you should really lay out on paper all the subjects, actions, and fields that describe those subjects and actions BEFORE you even open Access.

You haven't explained anything about what sort of data you're trying to collect, but it might be the case that you're recording employee info and certain proficiencies they've passed. There might be dozens of things that you can record about a given employee, and it sounds like you've tried to cram all those things in one table. This has two problems:

1) If you need to add a proficiency or some other categorization, you must physically change the design of your table and form

2) If, for example, there are 90 similar "things" you might record about an employee, you take up database for all 90 "things" for each and every employee even though each employee might have, on average, only 10 or 20 items specific to them.

To solve both problems, you need two tables:

Employees: EmployeeID, EmpLastName, EmpFirstName, EmpAddress, etc…

EmpProficiencies: EmployeeID, Proficiency

If a record appears in EmpProficiences, then it is assumed that the answer is "True" for that proficiency.

You might also have a third table that encodes all the potential proficiencies. This gives you a way to present the user with a combo box to select from available values.

So, if you need to add a proficiency, you simply create another row in the third table. And EmpProficiencies contains data for each employee for the proficiencies that apply, not all of them for each employee.

I strongly encourage you to read Crystal's book. If you feel you need more in-depth knowledge, a good place to start is Database Design for Mere Mortals by my good friend, Mike Hernandez. It walks you through designing tables correctly without getting bogged down in a bunch of academic gibberish. (Hence the name for Mere Mortals!)

Best of luck...

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Oct 4, 2014, at 5:54 AM, Crystal strive4peace2008@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hi Jessica,

great!

adding on to what Duane said ...

> "what is the significance of Me. when naming controls?

none -- when NAMING. When referring to controls, however, you need to preface the controlname with where the control is. If you are in code behind the form where the control id displayed, you can skip the forms!Formname stuff and simply say Me. Hence:

me.controlname
refers to a control named controlname on the form that the code is behind.

a nice thing about typing "me." in code is that intellisense kicks and and prompts you with most of the things you can choose

> " get all of the information on one form? "

perhaps you should rethink your display ... if the form is too long for the screen, perhaps pages would be easier to navigate and work better with Access

> "still keep all of the fields in the same table "

if your table has more than about 15-20 fields and is not an exceptional table that needs to store point-in-time information such as invoices, perhaps you should (re-)think your structure. There are some videos on planning a database in the beginning of this playlist that would be beneficial for you to watch:

Learn Access Playlist on YouTube

http://www.youtube.com/view_play_list?p=1B2705CCB40CA4CA



				Learn Access By Crystal - YouTube The "Learn Access" series takes an idea from concept to application. First is planning -- do not neglect this important step. http://www.youtube.com/playlist...

				View on www.youtube.com	Preview by Yahoo

Warm Regards,

Crystal

(: have an awesome day :)

On Friday, October 3, 2014 4:23 PM, "'Bill Mosca' wrmosca@comcast.net [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Hi Jessica

You should never name controls using Me. The "Me" is just a way to further identify a control, method or property of the form and to force Intellisense to automatically open a context menu in the code window so you can easily select those things.

If you can't fit all your fields on a form your tables are not normalized. Read Crystal's free eBook "Access Basics". You can download it from my site ThatllDoIT.com

Regards,
Bill Mosca,
Founder, MS_Access_Professionals
That'll do IT http://thatlldoit.com
MS Access MVP
http://mvp.microsoft.com/en-us/mvp/Bill%20Mosca-35852

My Nothing-to-do-with Access blog

http://wrmosca.wordpress.com

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, October 03, 2014 2:18 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Re: option group error message

Thanks everyone. I got it fixed. Apparently I had a problem in my table so I deleted the problem field and created a completely new one and it's fine now. I will rename my frames from here on out which leads to another stupid question: what is the significance of Me. when naming controls?

And another question: Apparently I won't be able to make my form long enough to include all of the fields I will need. Is a subform the best (AKA: easiest) way to get all of the information on one form? If so, can I still keep all of the fields in the same table or should there be a different table for each form? I'm hoping to keep this as simple as possible since I have only a basic understanding of Access.

Thanks,
Jessica

---In MS_Access_Professionals@yahoogroups.com, <strive4peace2008@...> wrote :

Hi Jessica,

it is a good idea to RENAME controls before adding code to them. Instead of 'Frame332', name the control something like 'framePsych'

What if the control has no value yet?

If IsNull(me.framePsych) then exit sub

Select Case nz(me.framePsych,2) 'assume No is the default value

~~~

for fields that have a Yes/No answer, I would recommend storing as Yes/No data type or Integer data type. If you set to Yes/No, you can set the display to Yes/No instead of True/False. Then you could make your option values -1 and 0 to correspond to the numeric values for True and False so you do not even need code.

Warm Regards,

Crystal

(: have an awesome day :)

On Wednesday, October 1, 2014 3:57 PM, "Duane Hookom duanehookom@... [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

When I click reply from outlook.com, the previous information from the thread is automatically included so it is the default for me ;-)

There are some tips about how to debug code at http://www.tek-tips.com/faqs.cfm?fid=7148.

It would be interesting to find out which line causes the error message. What happens if you add a new, blank text box (nothing in the control source) to your form named "txtMyNewControl" and then changed your code to:

Private Sub Frame332_AfterUpdate()
Select Case Frame332.Value
     Case 1
       Me.txtMyNewControl = "Yes"     'Psych.Value = "Yes"
     Case 2
        Me.txtMyNewControl = "No"      'Psych.Value = "No"
End Select
End Sub

This might narrow down your problem to see if the value of Psych can be set or not.

Duane Hookom, MVP
MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 1 Oct 2014 14:11:41 -0700
Subject: [MS_AccessPros] Re: option group error message

Now I don't even know if i pushing the correct reply button. I apologize if you don't have the message history, I have it so I'm assuming you do.

"Psych" is a unique name for the text box which has as it's control source a text field. I try very hard to give everything a unique name so I can tell what I'm working with - probably no one else can tell what I'm doing but it works for me. I am certain I am refering to the right text box in my code. I don't know all of the lingo - I am extremely new to Access and I don't have anyone to consult with but all of you (my IT dept dislikes Access).

Just in case I didn't reply correctly:

Private Sub Frame332_AfterUpdate()
Select Case Frame332.Value
Case 1
Psych.Value = "Yes"
Case 2
Psych.Value = "No"
End Select
End Sub

Thanks!
Jessica

__._,_.___

Posted by: John Viescas <johnv@msn.com>

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (14)

Visit Your Group