Not able to enter or change data in a forum

[Ben.Steinberg]

Here is my problem:

I created a Query to calculate an over all rateing useing other data that was already in the database. The Query worked out perfectly, and I then wanted to put the one field that gave me the overall rateing that I just calculated and put it into a form. So I added the Query with the rateing into the one that made up the other form and added the field to the Query. The problem was that when i went into that form and added the field, it made it so that i could no longer change any of the data on that forum. I thought that it might have something to do with the fact that there were no fields with math in them in that form untill i added the over all rateing one, could that have messed it up? Please send me some seggestions because I havent a clue at what could be worng. Thank you, Ben

 

[philipavery]

I am assuming that you created a form with a query (Q1) as a record source and that you could edit the data on that form. You then created another query (Q2) to provide a single piece of additional data and created a third query (Q3) to add this to the form by linking Q1 and Q2. The form's record source was then changed to Q3 and at that point the data became uneditable.

I would guess that Q3 is not an updateable query, wheras Q1 was. Check this out by running Q1 first to see if it has a New Record row at the bottom of the datasheet. Now do the same for Q3. If there is no New Record row, it is not updateable and you will have to approach the problem differently.

Let us know what you find.

 

[Pat Hartman]

As philipavery said, your query is probably no longer updateable. One option to get a single piece of calculated data is to use a domain function such as DLookup() or DSum(). Do not use these functions in queries. They add substantial overhead but you should be able to use them on a form for a single record without a problem.

 

[Ben.Steinberg]

ok, I just did what philipavery said and yes you were right, there was no New Record row. Pat Hartman, I've never used what you mention, but I think that I can think of some problems with that. You see the thing is, I am using a bunch of numbers from other forms in a query that then takes them and has about 15 or 20 math functions to obtain this final calculation. And then what I am trying to do now is to display that final number in one of the forms. One possible thing is that I do have to use some of the data that is recoreded in that form to calculate this number. Could that be an explination to my problems aswell? pls send me some possible solutions to this, thank you. Ben

 

[Rabbie]

It looks as if you are trying to store a lot of calculated fields. This is not usually a good idea in a relational database.

Do you really need to store these fields if you can calculate them. The problem with calculated fields is that is one of the input data changes the stored value is then invalid and you would have to take special steps to recalculate it.

 

[Ben.Steinberg]

Do you really need to store these fields if you can calculate them. The problem with calculated fields is that is one of the input data changes the stored value is then invalid and you would have to take special steps to recalculate it.

well, to answere your question, no I dont. The calculations are currently done each year at year end on an excel spreadsheet. But this is alot of work, and so I thought why bother when you could just do it all in access and have it automaticly update it self when we change the ratings (that input values for all the calcualtions). I have tested it by changeing the feilds used to in the calculations and it worked perfectly, just as intended. The only problem I have is not being able to display the final value in the forms like I have said. Your comment would be very helpfull if I wasnt try to do the oposite, lol.

You see, the ultimate goal of my improvements to this data base is that I want to be able to see the previous final rateing (out of 100) on the forms that I, or someone else, would take into the field. Allthough what I have already done has been very helpful as we no longer have to spend the time and efferet to transfer all of the data into excell.

Ben.

 

[Rabbie]

Ben

I think you misunderstand me. I did not say you shouldn't display the value on your form. I just said you dont need to store it in a table. I meant you should calculate it in the form and then your query would still be updatable.

If you can calculate the value in a query you can calculate on the form.

 

[Ben.Steinberg]

Oh, I see what you were saying. However, the thing is that there are to many other fields involved in the calcualtion so I cant do it on the form I want it to be displayed on. At first i did do all of the calculations on a form (made from the Query(Q1) i later did the math in). The problem with that was that it didnt update Q1 and therefor it would work when i tryed to display the rating in the other form by adding Q1 to the existing Query(Q2) and putting it in the form. Only when I did the math in Q1 would it even show up, and that was when I ran into my current problem, that nothing is able to be entered...

 

[philipavery]

Without knowing all the details, it's difficult to second guess, but I assume that the calculation query is a stand-alone, i.e. you can run it and the other query (original version) separately. It is putting them together as a record source that's causing the problem.

A possible brute force solution might be to use a Form/Subform with different record sources or two subforms.

HTH

 

[Ben.Steinberg]

Yes, thats right, you can run them both seperatly. One thing is tho, that I might have not made clear, is that I am actually adding the calculation quary INTO the origonal, and then trying to put one frield from the calcuation quary into the form. In other words the origonal query already had a form made from it before I started with the new one, and I'm wanting to just add this extra piece of information.

 

[Ben.Steinberg]

Hey, Philip, I've been trying to do what you suggested, make a subform and do it that way. And so heres how it went. I made a new form using the wizard, and added my two queries. I made the one with the rating my subform (and the only field I moved over was the one I needed), and the other the main form. When I created it and I was able to change any field!

However, in the subform it shows EVERY rating, not just the one that I would be currently looking at in the main form. I went into the relationship menu cause I'm pritty sure thats where I fix that, but I couldnt get it to work. I linked the primary key's of the tables that are the basses of the 2 querys, and then I also tryed to link the same fields in the 2 querys, but nether way worked. any ideas?

 

[philipavery]

Sticking with the Form/Subform approach, perhaps you should think of these as two distinct requirements. Construct a query to show the required form data and construct another query that will return just the current rating. The former needs to be updateable, the latter is for display only. Check the query results before constructing the form. If they return the correct data, construct the form. You can have the main form displaying the rating in its form header and the subform containing the data. You may need to make the rating query a parameter query if it requires any data from the subform, which will open and load befor the mainform. If the subform data changes in a way that will affect the rating, the main form will need to be requeried.

Once you get the form working, look for a more elegant solution!

HTH, Ben.

 

[Ben.Steinberg]

OK, Ill try that out and get back to you, but from reading what you sugest i know what you mean, and can imagine what it would look like. I am woried that it wont be sutible for what I want to do with the form.

P.S. I just now noticed that the title says "forum" and not "form" sorry people!!