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
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
BTW, the Combo Box wizard can easily assist you in placing a cbo on a form.
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?
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.
I am the programmer, dev and DBA ....etc lol.
Thank you for the advice I will deploy this weekend and report back.
You're certainly very welcome. I'll await your report.
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)....
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.
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?
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].
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?
@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.
What functions do you have in the table? I didn't catch that.
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
Last edited by cdscivic; 09-28-2016 at 01:22 PM. Reason: added a quotation #grammar
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.