Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Demerit's Avatar
    Demerit is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Nov 2013
    Posts
    125

    Control source Updated when user has not clicked OK

    I have a form with three controls : Order Id, Customer Name, Invoice Total.
    Order ID is a combo box
    This form is linked to the table "TblFinance"
    The form is used when Customers pay for their orders.


    I am using a DLookUp to get the Invoice Total and Customer Name based on the Order Id I select from the combo box.

    The problem i have is that, immediately the items appear on the form, the Table "TblFinance" is automatically updated when the user has not Validated the entry.

    What can i do to make sure that the control items stay in the form until the user clicks the OK button?

    Thanks in advance for your help.

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Can you explain, in plain english, what users are doing with this form.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Demerit's Avatar
    Demerit is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Nov 2013
    Posts
    125
    The from is meant to be used by cashier at the time he/she is receiving money from a customer.
    What he/she does is this, he/she selects the Order Id from the combo, the amount and the customers name appear (Thats for verification purpose) then after collecting the money form the customer, he then validates the amount and it goes to the Finance table.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Data entry/edit on bound forms feeds directly to the associated table. Commitment to table happens when form closes, move to another record, or run code. Cancelling the input requires code.

    Suggest you disable the form X close button.

    Try something like:

    Option Compare Database
    Option Explicit

    Dim strSave As String

    Sub OK_Click()
    strSave = "Save"
    End Sub

    Sub Form_BeforeUpdate(Cancel As Integer)
    If strSave = "" Then
    Cancel = True
    Me.Undo
    Else
    strSave = ""
    End If
    End Sub
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Demerit's Avatar
    Demerit is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Nov 2013
    Posts
    125
    I have entered the code but when i click OK to save the record, nothing occurs. the form just does not update the control source.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Have to select [Event Procedure] in the event properties.

    Nothing in that code has anything to do with updating a control source.

    You probably want to also disable the intrinsic navigation bar at bottom of form.

    And I forgot a line. What do you want to happen when user clicks the OK button - close form or move to new record row or commit the record and stay?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Demerit's Avatar
    Demerit is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Nov 2013
    Posts
    125
    When the user clicks the OK Button, the entry should be stored in the Table and then move to a new record.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Try this code behind the form:

    Option Compare Database
    Option Explicit
    Dim strSave

    Private Sub OK_Click()
    strSave = "Save"
    DoCmd.GoToRecord , , acNewRec
    End Sub

    Private Sub Cancel_Click()
    Me.Undo 'or DoCmd.Close
    End Sub

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    If strSave = "" Then
    Cancel = True
    Me.Undo
    Else
    strSave = ""
    End If
    End Sub
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Demerit's Avatar
    Demerit is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Nov 2013
    Posts
    125
    Thnks June7 it is working the way i want. On this same form, normally the user selects the Order ID and the other items appear. On the after update event of the Order ID control, i have the following code.

    Private Sub Order_ID_AfterUpdate()
    If Not IsNull(Me![Order ID]) Then
    Me![CustomerID] = GetCustomerID(Me![Order ID])
    Me![Amount] = GetOrderTotal(Me![Order ID])
    Me![CustomerName] = GetCustomerName(Me![Order ID])
    Me![SecurityID] = User.SecurityID
    End If
    End Sub

    How can i add a code so that it refreshes after Update?

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Those values don't immediately show in the textboxes?

    Try Me.Refresh - however, that might cause the form to move to the first record.

    Or try naming the textboxes different from the fields and then populate the texboxes instead of the underlying fields.

    Me.tbxCustID = GetCustomerID(Me!OrderID)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    Demerit's Avatar
    Demerit is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Nov 2013
    Posts
    125
    The form is actually functioning well. Since its going to be a multiuser db, what i want is that the control "Order ID" be refreshed ( maybe on the On click event or the after update event) so that if there are two users working on this form, and their respective forms have to be continuously open, then if UserA has already used Order ID #12 for example, at the time UserB clicks on the control "Order ID", it should refresh so that Order ID #12 should not show up.

    Actually, the control Order ID is a combo box.

    I had added "Me.Refresh" on the after update event of "order ID" but it produced an error.
    Hope am some kind of explicit

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You want to refresh the form or the combobox list?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    Demerit's Avatar
    Demerit is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Nov 2013
    Posts
    125
    I want to refresh the combobox.

  14. #14
    Demerit's Avatar
    Demerit is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Nov 2013
    Posts
    125
    the other controls are from a DlookUp and all depend on the value i select in the ComboBox. I need the values of the combobox refreshed anytime a user clicks to open up the list of values in the combobox.

  15. #15
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    If I recall correctly refresh does not look at the table, only the form's class module and properties. Me.Requery will navigate to the first record in the form's recordset.

    You probably want Me.OrderID.Requery. This will cause you combo to take a trip to the data. The trick is going to be finding an event to place it in.

    small print: I did not read the whole thread though, only the last couple posts....

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 09-18-2013, 09:15 PM
  2. Replies: 4
    Last Post: 02-14-2013, 10:06 AM
  3. Replies: 1
    Last Post: 10-08-2012, 12:04 PM
  4. Replies: 5
    Last Post: 10-13-2011, 03:36 PM
  5. control source
    By nashr1928 in forum Forms
    Replies: 5
    Last Post: 03-12-2011, 09:31 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums