Business Software/Access VBA Code
Expert: Scottgem - 3/12/2009
QuestionQUESTION: 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
AnswerAhh, 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