updating form data with macro access 2003

[revkev6]

Hey all this is my first post here but I have found many of my answers to google searches on this forum so hopefully you can point me in the right direction!

I have a linked form setup where a person enters a due date and the number of weeks it will take to accomplish. A macro subtracts the number of weeks from the due date to determine the start date, if the start date is earlier than date() an email is sent with the offending record to alert the correct people. my problem is that the form data isn't being picked up by the macro until I do a requery of the form. when I requery the form the form goes back to the first record. I have tried to make a requery vba code that puts the focus back on the correct record number but I can't seem to get it to utilize the "CurrentRecord" function. if I manually enter the correct record number my code and macro work correctly. when I try to set my long variable = Currentrecord it doesn't have a value. I get an error when I try to do Me.currentrecord as well.

here is my VBA code, when I replace the "2306"(my test record) with currentrecord I get nothing. it's like it's not recognizing I have a form open.


Public Function test()
Call bRecalc

End Function


Public Sub bRecalc()

Dim recordn As Long

recordn = 2306
DoCmd.Echo False
DoCmd.Requery
DoCmd.GoToRecord acDataForm, "process development1a", acGoTo, recordn

DoCmd.Echo True
recordn = 0


End Sub



my macro is triggered by the "after update" in the text box. it runs a query that updates the form data.

 

[Mr. B]

Have you tried saving the data in your form instead of doing a requery of your form?

 

[revkev6]

yes, I initially tried a save and a repaint/refresh. with no luck.

 

[Mr. B]

Am I correct that this is a bound form? If it is then you should be able to read the value from the form, if it is not then you may have to set focus to the control that has the value you need and then reference the "text" property of that control rather than the "value",

The only code I see is the code that you are using to requery and reset your form. Could you post the code you are using to try to read the value you need from your form?

 

[revkev6]

Am I correct that this is a bound form? If it is then you should be able to read the value from the form, if it is not then you may have to set focus to the control that has the value you need and then reference the "text" property of that control rather than the "value",

The only code I see is the code that you are using to requery and reset your form. Could you post the code you are using to try to read the value you need from your form?

yes, the form is bound.

I am calling a query in my macro that updates the records in the table. I am able to use
[forms]![process development1a]![recordnum]

in the query to get the current record I am accessing from my form. I guess that is why I need the form to add the record to the table. I can't get any code to come up with a current record on my form?? thoughts on why my code won't get the currentrecord??

 

[Mr. B]

It still sounds like you have a bound form and you are entering data in that form, but then you are attempting to run a query against the data before actually saving the data entered into the form before actually saving the record to the table.

Try adding the following code before calling your query:

if me.dirty = true then
     me.dirty = false
endif

Once you are sure the record has been saved you should be able to access it.

 

[revkev6]

It still sounds like you have a bound form and you are entering data in that form, but then you are attempting to run a query against the data before actually saving the data entered into the form before actually saving the record to the table.

Try adding the following code before calling your query:

if me.dirty = true then
     me.dirty = false
endif

Once you are sure the record has been saved you should be able to access it.

that worked perfect!!! now, what did it do?? I'm mostly self taught, I have no idea what "dirty" means!

 

[Mr. B]

"Dirty" means that some modification has been made to the data that is being represented in the form.

The code first checks to see if any change has been made to any field represented by any control in the form. If that is true, the changes are saved with the statement:
Me.Dirty = false.

Just because data has been entered into the controls in the form does not mean that the data is saved to the table. With that said, if you move off the current record without specifically canceling any modifications, those changes will be automatically be saved to the table. You can use the same code to check for changes and if you (or your users) do not wish to save the changes you can change the Me.Dirty = False statement to: Me.Undo.

Hope this helps.

 

[revkev6]

"Dirty" means that some modification has been made to the data that is being represented in the form.

The code first checks to see if any change has been made to any field represented by any control in the form. If that is true, the changes are saved with the statement:
Me.Dirty = false.

Just because data has been entered into the controls in the form does not mean that the data is saved to the table. With that said, if you move off the current record without specifically canceling any modifications, those changes will be automatically be saved to the table. You can use the same code to check for changes and if you (or your users) do not wish to save the changes you can change the Me.Dirty = False statement to: Me.Undo.

Hope this helps.

it does, thank you but somehow I managed to screw it up and I have no idea what I did.

I had been playing around with where I should have the code. I initially put it in "module1" but when it wouldn't work I put it in the Form module. I forgot to remove the code from module1. I added the code you suggested to the brecalc() in module1 and everything worked great. I went around to make sure I cleaned up anything that shouldn't be there and found that when I deleted the duplicate Brecalc() (that didn't have the "dirty" code in it ) in the form module I got a an error again! I tried putting it back (should have saved it before I cleaned it up) and it still won't work!

my error is on the setfocus line it says "method or data member not found"




Public Function test()
Dim gtest As Long
gtest = CurrentRecord
Call bRecalc

End Function

Public Sub bRecalc()

Dim recordn As Long

If Me.Dirty = True Then
Me.Dirty = False
End If


recordn = [recordnum]
DoCmd.Echo False
Me.Requery
Me.recordnum.SetFocus
DoCmd.FindRecord recordn
DoCmd.Echo True
recordn = ""

End Sub

 

[revkev6]

playing a bit more, if I move the brecalc() from the form module to module1 it doesn't like the Me. reference.

Should I have the function call and brecalc() in the form's module or in a separate module?? it is very odd to me that it worked fine just a few minutes ago....

 

[Mr. B]

It is not clear to me exactly what event you are using to call your code. I would assume that you have a command button that is calling you code. If you have a command button that is being clicked, put the code I provided at the very start of the code in that event. For example if you are using the OnClick event of a command button the code would go in that event.

 

[revkev6]

It is not clear to me exactly what event you are using to call your code. I would assume that you have a command button that is calling you code. If you have a command button that is being clicked, put the code I provided at the very start of the code in that event. For example if you are using the OnClick event of a command button the code would go in that event.

I am using the "after update" event in a text box to call a macro. the macro first calls the function to requery..... wait a minute...


the lightbulb just went on. with the dirty code saving the data I dont need to requery! duh! I just pulled all the requery code out and left the dirty code and like magic it works again!!!!


thank you sir for the help and the lesson.

 

[Mr. B]

First, I am glad to hear that the light just came on.

Also, glad to here you got it working and I am always glad to help.

Good luck with your project.