changing an entry in a form via VBA

[branston]

Hi,

I need some code that opens a form on a particular record, then changes one of the fields. I have this so far:

strLink = "[ProjectNo]= '" & Me![ProjectNo] & "' And [EmpNo]= " & Me![EmpNo] & " And [Rev] = " & Me![Rev] & " And [Discipline]= '" & Me.Discipline & "'"

CurrentDb.Execute "UPDATE TblReview SET TblReview.Review = 1 WHERE " & strLink
This code is in the after update event of a field on FrmA. When FrmA is closed it goes to FrmB which shows all the 'reviews'. The field that is updated should show on this FrmB.

At the moment it doesnt seem to be working - I dont get any errors it just doesnt make the change.

Am i missing something? Thank you!

 

[namliam]

Make sure that ProjectNo and Discipline are text fields
while EmpNo and Rev are number fields...
Also your fields CANNOT be blank (Null) because then it will not return anything

Why update your table anyway, why not open the form with strLink in the where clause?? That should achieve the same without the need to run an update query.

If that doesnt help insert a line before the execute:
Debug.print "UPDATE TblReview SET TblReview.Review = 1 WHERE " & strLink

Then take the SQL from the debug window and copy/paste that to a regular query designer to see what is going on.

 

[branston]

Why update your table anyway, why not open the form with strLink in the where clause?? That should achieve the same without the need to run an update query.

Not quite sure what you mean by that... maybe i didnt explain it very well. On FrmB I can select 3 different review status' (1, 2 or 3) against different disciplines. For example, I could set discipline a to review 2.
On FrmA I can add information, each record being assigned a discipline. If a discipline is updated I want the review status to be returned to 1.

Project no and discipline are indeed text, as rev and empno are numbers.

Ill try your suggestion with the debug. Thanks!

 

[branston]

Coppied the code into a query and it worked fine!
Can anyone think of another way to do it? or why it might not be working?

 

[namliam]

you have to check the debug/immediate screen (press CTRL+G)

 

[branston]

Brilliant, thank you. That pulled up a simple error i was missing before! (wrong object being called)
Ive never done that debug thing before, so thanks!

 

[namliam]

So you were able to solve it now?

 

[branston]

Yes, it works great. It was a silly mistake on my part - as usual! Thanks!