>update quit working

[billheath]

This was working until I added the msgbox statement. Now the !Lowest is not working. If I step through the code, it appears to be updating properly. However, when I look at the form, it still has the old value.


Dim rst As DAO.Recordset
Dim Low, High As Currency
Dim A, B, msg As String
A = "Select St_Sym, Current_Qua,Lowest,Highest "
B = "From Stock_Data Where ST_Sym = '" & fld & ""
A = A & B
Set rst = CurrentDb.OpenRecordset(A & "'")
With rst
If strPart < rst![Lowest] Then
Low = strPart
msg = "New Low 0f " & strPart & " for " & fld & ""
msg = MsgBox(msg, vbOKCancel)
If msg = vbCancel Then
Exit Function
End If

.Edit
!Lowest = Low
.Update
End If
End With

The msgbox statement is designed to stop the update if the user clicks on cancel.Thanks, Bill

 

[dhookom]

Without looking at this too much for logic errors:
1) "Dim Low, High As Currency" only creates High as Currency. If you want them both to be currency, you must use:

Dim Low As Currency, High As Currency

The same is true of A, B, msg. You then set msg to the return of MsgBox() which is numeric.

You should also check for a current record before attempting to edit.

Check faq705-7148.

Duane
Hook'D on Access
MS Access MVP

 

[billheath]

Thanks, Dhookom,
I changed all the dim statements as you indicated and added n integer, msg1 for the

msg1 = MsgBox(msg, vbOKCancel)
If msg1 = vbCancel Then
Exit Function
end if

If I remove those statements, the code works perfectly. It seems that the msgbox statement keeps it from working.

 

[dhookom]

What's you new code?
Do you get the MsgBox() prompt?
Have you debugged the code by adding a breakpoint?

Duane
Hook'D on Access
MS Access MVP

 

[billheath]

If strPart < rst![Lowest] Then
Low = strPart
msg = "New Low 0f " & strPart & " for " & fld & ""
msg1 = MsgBox(msg, vbOKCancel)
If msg1 = vbCancel Then
Exit Function
End If

.Edit
!Lowest = Low
.Update
End If
End With
Yes, I get the prompt. I have set a break point and stepped through the code. If I click on cancel, it cancels. However, if I click on OK, it will step to the .edit statement and assign !Lowest= Low as it should. It then goes through .update successfully. If I highlight "Lowest", the tip indicates that lowest has been changed. However, when I return to the form "Lowest" has not changed. If I erase the entire message part from the VB code, it actually changes the data on the form. However, I need to give the operator the right to cancel if he or she makes a mistake. So, I need the msgbox statement.

Thanks for your help

 

[dhookom]

Editing the data in the table doesn't mean it will be immediately visible in an open form. You may need to Refresh the form to see changes to existing records.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya billheath . . .

Agree with [blue]dhookom[/blue] only you need to [blue]requery[/blue].

I also want to show that if you reverse your msgbox logic, the code will run cleanly (without exit function) and you can clear (reclaim memory) your set variables:

[blue]   Dim db As DAO.Database, rst As DAO.Recordset
   Dim Low As Currency, High As Currency, SQL As String
   
   Set db = CurrentDb
   SQL = "Select St_Sym, Current_Qua,Lowest,Highest " & _
         "From Stock_Data " & _
         "Where ST_Sym = '" & fld & "';"
   Set rst = CurrentDb.OpenRecordset(SQL, dbOpenDynaset)
   
   With rst
      If strPart < rst![Lowest] Then
         Low = strPart
         msg = "New Low 0f " & strPart & " for " & fld & ""
         
         If MsgBox(msg, vbOKCancel) = vbOK Then
            .Edit
            !Lowest = Low
            .Update
         End If
      End If
   End With
   
   Set rst = Nothing
   Set db = Nothing[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MazeWorX]

Just a side note on declarations if you dont specify the type then access uses Variant as a default so in your original declarations

Dim A, B, msg As String

A-variant
B-variant
msg-String

This may sometimes be convent but if not intended then it can cause overhead in the processing of your code

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[TheAceMan1]

billheath . . .

Woops! ...

[blue]Set rst = CurrentDb.OpenRecordset(SQL, dbOpenDynaset)
   [b]should be:[/b]
Set rst = [purple][b]Db[/b][/purple].OpenRecordset(SQL, dbOpenDynaset)[/blue]

Sorry about the mess ...

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[billheath]

Thanks, everyone for your help.
Bill

 

[dhookom]

Thanks, everyone for your help.

Does this mean your question has been resolved?

Duane
Hook'D on Access
MS Access MVP

 

[billheath]

Yes, thank you. The answers helped a great deal.

Bill

 

[TheAceMan1]

billheath . . .

Can you post what you wound up using[purple] so others who have/had the same problem can benefit?[/purple]

[blue]Your Thoughts? . . .[/blue]


See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]