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

stang1Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
stang1Connect With a Mentor Author Commented:
ping.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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 ...
0
 
stang1Author Commented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.