Expert: Scottgem - 3/12/2009

Question
QUESTION: Hi again Scott:

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   

On Error GoTo ErrHandler   

Answer = MsgBox("Add Item to List?", vbYesNo + vbQuestion, "Transmittal Form")   

If Answer = vbYes Then strSQL = "INSERT INTO Drawings(Title)" & "VALUES (' " & NewData & " ');"   

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True   

Response = acDataErrAdded   

ElseIf Answer = vbNo Then   

Response = acDataErrContinue   

End If   

Exit Sub   

ErrHandler: MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Error"

End Sub

Regards Alicia

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

---------- FOLLOW-UP ----------

QUESTION: Hi Scott:

Yes I used your code as follows:

Dim strSQL As String
Dim strMsg As String
Dim IngStatusID As Long
Dim ctl As Control
Set ctl = Screen.ActiveControl

strMsg = "The Drawing Title, " & NewData & ", you entered is not listed!" & vbCrLf & "Do you want to add it?"
   
If MsgBox(strMsg, vbYesNo, "Drawing Title") = vbYes Then
 strSQL = "INSERT INTO Drawings (Title)"
 strSQL = strSQL & "VALUES (' " & NewData & " ');"
 CurrentDb.Execute strSQL
 Response = acDataErrAdded

Else
ctl.Undo
Response = acDataErrContinue

End If

End Sub

I made sure I entered your code as exact as I could get it.  I tried it a few times so i don't know what is wrong with my end.

The table that the inforamtion is going into has 2 columns: "Drawing Number" & "Title"  my other problem is how do I get to enter the drawing number that correspondences with the title -

I would like a code that allows me to enter the drawing number and the title in there relevant columns

I have been googling forever it seems to get an answer to that one(lol)

I still do have a few strands of hair left to pull out (lol)

regards,
Alicia

Answer
Ahh, I assumed you were only entering Title. I'm pretty sure I can come up with a solution, but I need to know two things. First the RowSource of the combo and, second, where you get DrawingNumber from.

Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA