asked on

Access 2007 Option button to update linked tbl

On a split form, I am trying to use an option button to see if the user wants to check in or check out a file... Based on selection I want to update fields on an existing record on a linked table. I want to commit the updates when the user selects the 'Save' button not when they click the option button:

Can this be accomplished with a CASE statement? What would you suggest?
Thanks!

Private Sub Save_UpdateFileInformationtbl_Click()
   Dim rs As dao.Recordset
   Dim MyDate
   MyDate = Now()
Set rs = CurrentDb.OpenRecordset("dbo_TableName", dbOpenDynaset, dbSeeChanges)
With rs
     .AddNew
-- if option 1 selected update these fields
     !UserName = Me.CurrentUserName
     !CheckOutStatus = Me.CheckOutStatus
     !CheckedInBy = Me.CurrentUserName
     !CheckInDate = MyDate
    !LastUpdatedBy = Me.CurrentUserName
     !LastUpdatedDate = MyDate
--  if option 2 selected update these fields
     !UserName = Me.CurrentUserName
     !CheckedOutBy = Me.CurrentUserName
     !CheckOutDate = MyDate
     !LastUpdatedBy = Me.CurrentUserName
     !LastUpdatedDate = MyDate
     .Update
End With
rs.Close
End Sub

Open in new window

Scott McDaniel (EE MVE )

Unless you have code running in the Change or AfterUpdate event of the Option group, Access shouldn't be saving anything - unless, of course, you're working with a bound form. The code above should only run if your user clicks the button.

However, I may not correctly understand what you're talking about here ...

stang1

ASKER

LSMConsulting,
Sorry, I'm a new user of both Access and VBA, as you probably noticed... Please let me try again.
I'm attempting to update 7 columns(none of which are key fields) on an existing record on a linked table. Since I posted my question I tried to establish a local variable to assign the option button selected by the user, and based on the selection, update the appropriate rows. I think I'm close, but no data is updated based on attached code... Do you see anything missing?

Option Compare Database
 
Public gbl_in_option As Boolean
Public gbl_out_option As Boolean
Option Explicit
   
Private Sub Option28_AfterUpdate()
  If Option28.Value = True Then
     gbl_in_option = True
   End If
End Sub
 
Private Sub Option31_AfterUpdate()
  If Option31.Value = True Then
     gbl_in_option = True
   End If
End Sub
 
Private Sub Save_UpdateFileInformationtbl_Click()
 
   Dim rs As dao.Recordset
   Dim MyDate
   MyDate = Now()
Set rs = CurrentDb.OpenRecordset("dbo_FileInformation", dbOpenDynaset, dbSeeChanges)
 
With rs
   If gbl_in_option = True Then
     .AddNew
     !UserName = Me.CurrentUserName
     !FileStatusID = 4
     !CheckOutStatus = Me.CheckOutStatus
     !CheckedInBy = Me.CurrentUserName
     !CheckInDate = MyDate
     !LastUpdatedBy = Me.CurrentUserName
     !LastUpdatedDate = MyDate
     .Update
   End If
   
   If gbl_out_option = True Then
     .AddNew
     !UserName = Me.CurrentUserName
     !FileStatusID = 5
     !CheckOutStatus = Me.CheckOutStatus
     !CheckedOutBy = Me.CurrentUserName
     !CheckOutDate = MyDate
     !LastUpdatedBy = Me.CurrentUserName
     !LastUpdatedDate = MyDate
     .Update
  
End With
rs.Close
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

stang1

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial