Needs help with expression

  • Thread starter Thread starter JaneM
  • Start date Start date
J

JaneM

I have an expression to enter data into drop-downs automatically, but
it did not work. Could you tell me what went wrong?

Private Sub Industry_Type_NotInList(NewData As String, Response As Integer)
Dim dbsProjects2 As DAO.Database
Dim rstIndustry_Type As DAO.Recordset
Dim intAnswer As Integer

On Error GoTo ErrorHandler

intAnswer = MsgBox("Add " & NewData & " to the list of industry types?", _
vbQuestion + vbYesNo)

If intAnswer = vbYes Then


Set dbsProjects2 = CurrentDb
Set rstIndustry_Type = dbsProjects2.OpenRecordset("Industry Type")
rstIndustry_Type.AddNew
rstIndustry_Type![Industry Type] = NewData
rstIndustry_Type.Update

Response = acDataErrAddedElse
Response = acDataErrDisplayEndIf

rstIndustry_Type.Close
dbsProjects2.Close

Exit Sub
Thanks in advance.
Jane Marlotty
 
Define "Did not work"
If you are getting an error, what error?
On which line of code is the error occuring?
What other things are happening or not happeing that you exepect?

I do see some issues with your code, but I expect that is just a problem
with cutting and pasting.

But, there area also a couple of things you are doing incorrectly. For
example, you are trying to close the recordset whether you use it or not and
you are trying to close the current database.
Also, an SQL statment is much more efficient in this case.
And, you do not navigate to the newly created record, so the user would not
even see it.

See if this works any better:

Private Sub Industry_Type_NotInList(NewData As String, Response As Integer)
Dim strSQL As String

Me.Industry_Type.Undo

If MsgBox("Add " & NewData & " to the list of industry types?", _
vbQuestion + vbYesNo) = vbYes Then
strSQL = "INSERT INTO [Industry Type] ([Industry Type]) SELECT """ & _
NewDAta & """ As Dummy;"
Currentdb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrDisplay
End If

Exit Sub
 
Back
Top