Full Version: Simple Scripting Problem
UtterAccess Forums > Microsoft® Access > Access Forms
Mink
I am using an onLoad event to set some data in a box for a printed form that I will need. Below is the code. The problem being it is only ever returning an "RM" in the field, no matter which checkboxes are checked. Can anyone help me fix this? It is imperative that I get it corrected swiftly.
Private Sub Form_Load()
If [Model Change] = -1 And [Running Change] = -1 Then
[MYdata].Value = "RM"
End If
If [Model Change] = -1 And [Running Change] = 0 Then
[MYdata].Value = "M"
End If
End Sub
Jack Cowley
Welcome to Utter Access!
Try moving your code from the On Load event to the On Current event...
hth,
Jack
Mink
Thanks for the quick reply. I switched it to On Current and it still is just throwing out RM instead of being either RM, M, or nothing. It seems like is should work just fine to me, and works if I have it only checking one box, but not both boxes. :(
Jack Cowley
Hmmm. Try this:
If Me![Model Change] = True And Me![Running Change] = True Then
Me![MYdata] = "RM"
ElseIf Me![Model Change] = True And Me![Running Change] = False Then
Me![MYdata]= "M"
End If
Mink
No, it's still kicking "RM" out for a general answer, though on two of the records in my example it has only the Model Change checkbox returning true. I guess the code is not the problem but how I am linking it somewhere?
Jack Cowley
I assume you have two checkboxes on your form and that is what the code is referring to. If that is not the case then more details are necessary. If there are two checkboxes on the form then put a Stop in your code and step though it and see what values are being shown for those two controls....
ack
Mink
I don't quite understand what you mean. A stop? And how to display?
Thanks for your help, it's quitting time here but I hope you will respond. frown.gif
Jack Cowley
Place the word Stop just above your code in the subroutine. When the form opens the code editor will open with the word Stop highlighted. Using the Step-Into icon on the toolbar will step you through the code one line at a time. Placing the cursor over a variable in the code will tell you what value is in the variable. If you place cusor over a checkbox name it will tell you the value of the checkbox. Just stepping through the code will show you how Access is executing the code...

hth,
Jack

PS. I will be gone from 8:30am Pacific Time until about Noon Pacific Time tomorrow...
Mink
Thank you so much for dumbing it down for me. There are some things I can do just fine but when it comes to coding it just confuses me. When I place my cursor over the name of the checkbox nothing happens so I think something is wrong with the way the code is calling the buttons. And when place my cursor over Me![Mydata] on either line it will say Null. I am going to try renaming and fixing the names of the checkboxes in the code, and hopefully that will fix it.
dit: I just finished changing the names to something simpler (mc and rc) and when it stops it is giving all the values as -1, and lising the Me![Mydata] as Null. Running it without the stop is still RM only. What do you think could be the trouble?
Jack Cowley
Is MyData a bound or unbound control? I assume the check boxes are bound otherwise they would always be unchecked when you opened the form. I cannot duplicate your problem so would it be possible for you to post a demo of what you are doing? Compact and zip the demo and post it here (500k max. size) and I will take a look at it. I will be gone until noon Pacific Time, but I will look at it when I return.
ack
Mink
MyData is an unbound text box. The checkboxes are bound to a query that pulls the data from a table. I am unable to send the database to you, it has company data in it and that would get me in much trouble. However to solve the problem I think I am just going to delete and redo the query and the form and see if I redo it step by step it will work. The problem has to be somewhere. frown.gif Thanks for your help.


Edit: I figured out the ultimate problem, it is only using the checkboxes for the first record, and filling them all in with the values of the first record though the checkboxes will appear correct for each record. How can I fix this?
Edited by: Mink on Tue Nov 23 12:06:00 EST 2004.
Jack Cowley
If your form is bound to a query then the checkboxes should show correctly as you use the navigation buttons to move through the records. I am not sure what you mean by, "...it is only using the checkboxes for the first record..." What is "it"?
suggest you create a form based on the query and only add the two checkboxes to the form and your unbound MyData control. Add the code to the On Current event and using the navigation buttons move through the records. You should see the correct data in the MyData control...
Jack
SerranoG
Since Model Change and Running Change are checkboxes that can only hold TRUE or FALSE (checked, unchecked) then you can simply write on the ON CURRENT Event...
!--c1-->
CODE
If Me.[Model Change] Then
    If Me.[Running Change] Then
        Me.[MYdata] = "RM"
    Else
        Me.[MYdata] = "M"
    End If
End If
Jack Cowley
Nice piece of code! I think there are other problems as the 'long' code is not working, but your suggestion of shorter, more efficient code is much appreciated!
ack
Mink
Well, the code is still not working correctly. What happens is, even if I switch to your code SerranoG the code only checks to see the value of the checkbox for the first record reported by the form, then fills all of the rows of data with the value it calculated for the first record returned, instead of recalculating it for each record returned by the query. At the moment I have it returning 4 rows as a test, two with both boxes checked, one with one box checked, and one record with neither box checked. One of the records with both boxes checked is in the first row. All of the "Mydata" boxes are returning RM for each record. When I change the first record to having only the Model Change checkbox checked, all "Mydata" fields report an M. When I change the first record to no checkboxes checked, I return a blank box for all "Mydata" Fields. Each record has it's own set of checkboxes that I want to return it's own result in the form. This would save time over having to enter the reuslts manually.
Could it be the query? I have it querying 2 different tables, but I thought that didn't matter so long as the form itself was bound to the query and not to the tables.
Jack Cowley
Can you compact and zip your db and post it here (500k max size)? If necessary create a db with the table(s), queries and the form and post that so we can see what you are doing....
ack
SerranoG
Ah, it sounds like you have a continuous form. You didn't mention that. Continuous forms behave "all or nothing" when it comes to code like this. My code will only work correctly for a single form's ON CURRENT event.
oding for continuous forms the way you want is a PAIN in the BUTT and will usually not give the results you want. That's why I tend not to use them. I use single forms with subforms set to datasheet view in them.
Mink
Well, I really could get in trouble for this but here is a very stripped down copy of the database that has only the tables and queries. I took out all of the data, so I had to create a couple of test entries. Your help is appreciated but please don't spread this around. frown.gif
Jack Cowley
You forgot to mention that the form is in Continuous Form mode... Continuous mode is really one record repeated many times so what you do to one you do to all. I would suggest that you save the RM or M in a field in the table and that will take care of the problem or forget the field all together.
nother observation... Your table 'ECN Data' is not normalized as you have many repeating groups. If you want this db to work you will need to properly structure your data or you are going to have more and more problems as the database evolves. I would suggest that you search the Archives here for Normalization as there are a number of good articles on the subject. I would not go any further with the db until you get the structure correct as you will need to do it sooner or later and better now than later, in my humble opinion....
Jack
Mink
I did not know that Continuous Form mode would affect the preformance of the code. This is the most complex database that I have ever worked on, and what you saw of it is just the very tip of the iceberg. Unfortunately I know I have many redundant macros and queries, but I don't know any other way of making it work. You see my programming experience is very limited, and this is taxing my knowledge somewhat beyond what I know. I've resigned just to generate another text field to store the results in and that will let me complete the preliminary version of the database as they want it.
read up on normalization, but i am not sure how to apply it to my database. Also I have 167 macros and queries to control how my data is viewed, and quite a few forms which are all based off the three tables you saw. I hesitate to do anything that will cause me to go back and redo the queries and macros, especially since I estimate I have approximately 120 more to add. This database is very complex, and the company I work for wants to use it to create many redundant print-outs and reports that they currently have scattered widely over their network on Excell sheets. Even the database you saw was not as complex as it is in the full version, I stripped it down by at least 20 fields to make it easier for you to view as well as removed all the queries and macros.
To sum it up, I don't understand how I could apply normalization to my database and retain the ease of use that the database has for my department. The form that you saw ("2005 Model Year") is designed specifically so that the persons in my department use minimal time filling it out based on paper forms they recieve, and little time referencing the information. I am open to suggestions about better structuring the database, however changing the form too much will cause problems.
Jack Cowley
Part of the reason you may have so many queries is that fact that your data is not normalized. Having all those Yes/No fields in your ECN Data table is not the way to easily find out which items have been selected. What you want is a single record for each Yes/No not a single field in a record for each question. If there are 20 checkboxes there should be 20 records, in a related table, not 20 fields, as you have now.
ormalization can be a tough nut to crack and at this stage of the game it sounds like you have committed yourself to a difficult approach. It would take you a great deal of time to normalize your database as not only are the tables involved but your existing forms and queries. At some point in time you may need to start over, but for now it sounds like you have to go down the path that you have started on.
Good luck with your project.
Jack
Mink
Thanks alot for all your help, but I guess I still do not quite understand normalization. Being as I have the rest of the week as vacation I think I will read up on it more to see if I can better understand what you mean, because what you said about the checkboxes just went way over my head. But if it can lessen the number of queries and reduce the number of forms that it is definately worth reading up on. Franky the only reason I have quite so many queries is because I cannot seem to correctly build macros or filters that filter more than one thing at a time, or that react differently to different actions. Thus many queries with only one slight difference. (example - Queries that filters several fields for a form, one for V350, one for S350, one for S330... etc) Can you point me in the direction of any good reading besides this message board?
Thanks again for all your help. Also, I did get the code working, but on the "2005 Model Year" form as a hidden field that stores the value and then displays it later via the query. Since that is a single record form it works just fine. frown.gif
Jack Cowley
You say, "Queries that filters several fields for a form, one for V350, one for S350, one for S330... etc". With your current setup your query must look in a specific field to filter the form. If your data is saved as records and not fields in a record then one query can filter on S350 or V350 or whatever you like as all you have to do is have a form where you enter the value you want to filter on and the query will get the data from there as only one field is involved for V350, S33 and S350, not 3.
realize that this is confusing, but once you sort it out in your head it makes sense. You are used to working with paper forms and Access is rather different than a paper form consequently a new set of rules have to be learned.
I have attached a VERY simple demo that shows you how you can store information like you S350, S330, etc. in records and then use one query to find all the people that use that item. Look at frmSearch and also look at frmCustomersAndForms to see how you add forms to a customer...
Good luck!
Jack
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.