lock value - keep values from updating

[ImLost]

Hello All!

Thanks again for all your help over the years. I have another issue that I hope you can help with.

I have a form that is linked as the many in a one-to-many relationship. On this form, I've used text boxes to link back to a subform of the main form in the relationship. So far, this is all working just great.

My problem is that if the data on the linked form changes, it will change in the text boxes on my new form as well, which will affect the data I'm gathering (which is for a specific time frame based). I was wondering if there is a way to lock a value once it is set, even if it is linked.

For instance - I know in Excel that if you determine a value using a function, you can copy the value to keep the value. Is there a similar option in Access so I don't need to manually enter information that is already in play?

Thanks for any and all assistance you can offer. It's greatly appreciated!!!

 

[JamesMcS]

If you're talking about relationships, can they be between forms? I thought it was tables only..... if it's 2 tables you've got linked in a one to many, will turning off cascade updates solve the problem?

 

[ImLost]

I don't have the cascades turned on. It's more about keeping the original value of the text boxes in question than about the relationships, I just wasn't sure what info to provide. I'm researching the OriginalValue or OldValue, and cancelupdate methods, hoping something like that will help, but I don't know VBA. I do everything with functions and macros.

 

[JamesMcS]

Oh sorry then, I've never heard of linking forms together, I'm out of ideas... Unless of course you create two tables - one containing everything, one for your specific time frame. Then, when you've finished doing whatever you need to with the data, just append one table to the other maybe?

 

[ImLost]

I don't think I explained it right. I'm sorry for the confusion.

I have a form and on this form I have three unbound text boxes. I used the control sources to equal specific date boxes on another form so that I can calculate data within a date specific time frame using bound text boxes on this form. That is all working just fine.

The unbound text boxes on this form are read-only. However, since they are linked, if a user changes the data in the original boxes on the other form, the dates will also change on this form, which will change the data I've collected. I'm seeking a way of keeping the original dates within these unbound text boxes so that my data won't change with any updates made to the other form. I need to store the orginal/old value and keep the boxes from updating.

I think I found a way to do in VBA, but like I said, I don't know VBA (I know a little, but really...not enough to be helpful). Thanks again.

edited to add - The forms aren't linked together. The tables have a relationship.

 

[JamesMcS]

Ah I understand now! And the bad thing is, when one value changes, it'll immediately change in the other form (I think). So, you might be able to use oldvalue, but I've had 0 experience with that...

Do you think it might be an idea to have a button on the main form to say "create sub-job" or whatever, that appends the data you want to work on into another table? Then you can fool around with it as much as you like without fear of changing data, then when you're done, update/append back to the main table?

Or if not another table, keep your text boxes unbound but have a bit of code to say textbox.value=[other text box].text, perhaps in a button - so there's no control source, it just looks at the data from the other form the once. That should do what you want it to do.

 

[ImLost]

You got it. That's my problem and I haven't had any luck with originalvalue, oldvalue, or cancelupdate. I think I understand what you're telling me to do, but I don't know how to do the append or create sub-job thing. I'll give the button a go and see what happens.

 

[ImLost]

The button thing didn't work. It worked like a label and didn't give me data I could work with. As far as changing the data to see if it works, I have a few set up that I can work with without worrying about messing up the originals. Short of manually re-entering the data on this form, I'm not sure I can do what I want to do...but it seems like I should be able to. Like once the form is created and activated, all the linked data stays exactly the same and is never updated. Maybe I should look into the form options and see if I can keep the linked text boxes from updating that way.

 

[JamesMcS]

OK - when I was tlaking about the sub job business, I assumed that there was a chunk of data in the main table you could basically copy out into another table, do your work, then put it back in the main table.

The other method would be to make a button to open the second form, and in that button put

 2ndformtextbox.text=forms![1st form name].form![1sttextboxname].text

or something like that... so when you click the button, it opens the form, looks at the textbox in the first form, then populates the 2nd form textbox with whatever's in there - rather than having the 1st text box as the 2nd text box's controlsource, which basically means there's a live link between the two - so changing 1sttextbox would immediately change 2ndtextbox.

 

[ImLost]

I found a workable solution by using the setvalue option in Macros. It actually works better than I had hoped, because I can set multiple values this way with the click of one button. My only problem will be if someone ever hits the button again and the original values have changed. ^_- Thanks for your help!

 

[JamesMcS]

Cool, glad it worked out - I'm sure that eventuality can be accounted for somwehere too....

 

[ImLost]

Clicked on your scales. Thanks again for offering your assistance!

 

[JamesMcS]

Aaaah aren't you nice gold star for you

Happy to help! Enjoy the weekend! (I'm missing friday out altogether)