AboutScottgem Expertise I can answer some questions on a wide variety of business applications, including MS Office, Lotus Smartsuite, Visio, Notes and many others.
Experience I have over 16 years of experience as an IT professional, supporting a wide variety of business applications.
You told me the following yesterday following my question about workgroups:
"As for workgroup security, I tend not to use it. I find its too cumbersome".
So how do you share a database which allows multi-users to open and use at the same time.
And my final question (see the code following):
When I type in the new data to be added my message box comes up asking "Add Item To List?" - I click yes and then received another message box saying "The text you entered isn't an item in the list. Select an item from the list, or enter text that matches.........."
When I click "OK" I see the new item in the drop down list and I click on the new item and it remains in the box as selected.
How do I stop that last msgbox from coming up? Also, why does the new item added have a space before the first letter? The old items are all at the left margin
Thanks in advance for your help.
Private Sub Drawing_Document_Title1_NotInList(NewData As String, Response As Integer)
Dim Answer As Integer
Dim msg As String
Dim strSQL As String
ANSWER: First, Access is, by default and design, a multi-user application. Workgroups have nothing to do with that. Workgroups allow you to restrict access to certain parts of the application, but have nothing to do with the multi-user aspect.
A shared database MUST be split into front and back ends. If you don't the potential for corruption is great. For optimum perfomance the front end should be on the local hard drive and only the back end on a shared drive.
I use a slightly different code snippet for NotInList events. Following is what I use:
Private Sub cboAcctStatus_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim strMsg As String
Dim lngStatusID As Long
Dim ctl As Control
Set ctl = Screen.ActiveControl
strMsg = "The Status code, " & NewData & ", you entered is not listed!" & vbCrLf & "Do you want to add it?"
If MsgBox(strMsg, vbYesNo, "Not listed") = vbYes Then
strSQL = "INSERT INTO tluAcctStatus (Account_Status) "
strSQL = strSQL & "VALUES('" & NewData & "');"
CurrentDb.Execute strSQL
Response = acDataErrAdded
Else
ctl.Undo
Response = acDataErrContinue
End If
End Sub
Try that and see if it helps.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
---------- FOLLOW-UP ----------
QUESTION: Good morning Scott:
Thanks again.
Thanks for the code but I am getting "The text you entered isn't an item in the list. Select an item from the list, or enter text that matches.........." after I click Yes to add new item.
How do I get rid of that?
Regards,
Alicia
Answer Did you try my code? I don't get that when I use it. The reason for that message is Access isn't refreshing the RowSource. That's what this line is supposed to do:
Response = acDataErrAdded
So either that line isn't executing or something else is wrong.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
