Solved

Access 2007 Option button to update linked tbl

Posted on 2009-05-06
3
653 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
Comment Utility
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
Comment Utility
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
Comment Utility
ping.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

762 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

12 Experts available now in Live!

Get 1:1 Help Now