Solved

Access 2007 Option button to update linked tbl

Posted on 2009-05-06
3
678 Views
Last Modified: 2013-11-27
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

0
Comment
Question by:stang1
  • 2
3 Comments
 
LVL 84
ID: 24320208
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
 

Author Comment

by:stang1
ID: 24328673
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
 

Accepted Solution

by:
stang1 earned 0 total points
ID: 24335694
ping.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question