Page 2 of 4 FirstFirst 1234 LastLast
Results 16 to 30 of 51
  1. #16
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922

    Most developers prefer not to use Lookup Fields in tables. I've never used them. Here's a link you may not have seen: http://access.mvps.org/access/lookupfields.htm

  2. #17
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    BTW, the Combo Box wizard can easily assist you in placing a cbo on a form.

  3. #18
    cdscivic is offline Resident Troll
    Windows XP Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    52
    I can add the cbo to the form, that's fine. Is it best to create it natively there instead of what I need to do here? Best practice to use a table as the source for the choices?

  4. #19
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I *always* use a query of a table rather than the table directly. Using a Table/Query as the Row Source gives you the flexibility to modify the choices without calling the programmer. You can simply change the table. Who knows, you may come up with another "Status" in the future.

  5. #20
    cdscivic is offline Resident Troll
    Windows XP Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    52
    I am the programmer, dev and DBA ....etc lol.

    Thank you for the advice I will deploy this weekend and report back.

  6. #21
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You're certainly very welcome. I'll await your report.

  7. #22
    cdscivic is offline Resident Troll
    Windows XP Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    52
    Update, this is still occurring. I validated today that if a user gets the error on one form (liked to just one table), they are able to make changes on the other forms (different tables) still....this is killing me.

    There are only ~ 100 users on this (each with own DB copy)....

  8. #23
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    I never use table macros, so this is a bit of a guess. Try temporarily deleting the table Before Update macro, and see if the problem goes away. I'm wondering if perhaps the Form and the table macro are both trying to update the same table record at the "same time", causing a lock conflict.

  9. #24
    cdscivic is offline Resident Troll
    Windows XP Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    52
    interesting perspective...ill need to figure out a solution for calling that function from the form as it is a critical one. I am sure its likely pretty easy...any suggestions off the top to get me started?

  10. #25
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    I did a quick test - I think you can put the code in the Before Update event of the form. The table fields you update don't have to be on the form, but they do have to be in the form's record source. You can reference a table field using its name, and I use square brackets for clarity: [tablefieldname].

  11. #26
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Are you using simple queries of the tables as record sources of your forms, or are you using the tables directly as the record sources?

  12. #27
    cdscivic is offline Resident Troll
    Windows XP Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    52
    @John_G thank you I will try this method on the 2 forms with the most traffic (this seems a plausible reason and would remove ALL functions from the table)

    @RuralGuy I am using the table directly.

  13. #28
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    What functions do you have in the table? I didn't catch that.

  14. #29
    cdscivic is offline Resident Troll
    Windows XP Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    52
    Sorry, I only have one function and 1 "formula" that run via the "Before Change Data Macro" option. I should disclose that when the form is opened, a sort is also applied via VBA. See the attached screenshot for the macro and code below for the function.


    Code:
    Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
        "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
    
    
    Function fOSUserName() As String
    ' Returns the network login name
    Dim lngLen As Long, lngX As Long
    Dim strUserName As String
        strUserName = String$(254, 0)
        lngLen = 255
        lngX = apiGetUserName(strUserName, lngLen)
        If (lngX > 0) Then
            fOSUserName = Left$(strUserName, lngLen - 1)
        Else
            fOSUserName = vbNullString
        End If
    End Function
    Click image for larger version. 

Name:	DataMacro.PNG 
Views:	24 
Size:	5.0 KB 
ID:	25962
    Last edited by cdscivic; 09-28-2016 at 01:22 PM. Reason: added a quotation #grammar

  15. #30
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    The fOSUsername function should be in a standard module and it should be Public! What is the name of the Standard Module? Is that Macro in a table somewhere? I've never used Data Macros so I'm a neophyte in that area.

Page 2 of 4 FirstFirst 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 10-08-2015, 01:13 PM
  2. Can't update record, currently locked
    By clancy76 in forum Access
    Replies: 5
    Last Post: 04-16-2015, 10:20 AM
  3. BeforeUpdate: Could not update; currently locked.
    By v7davisa in forum Programming
    Replies: 3
    Last Post: 09-26-2014, 04:18 PM
  4. Could Not Update, Currently Locked
    By jlclark4 in forum Forms
    Replies: 5
    Last Post: 11-17-2011, 01:21 PM
  5. Err# 3218: Could not update; currently locked.
    By reachvali in forum Access
    Replies: 3
    Last Post: 06-30-2011, 05:13 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