Solved

Access 2007 Option button to update linked tbl

Posted on 2009-05-06
3
664 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

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…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

911 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now