Problem with NotInList code 1

[SuePee]

Attached is my code (Copied from the internet and modified) for an Access 2000 database. I want to add a new "Analyst" to a table called "Financial_Analyst". I am using a combo box that lists the current Analysts, but if one is not there I would like the user to be able to add an Analyst. The combo box is on another form where all the rest of the data for the project is entered. So I put the code in the Event Procedure for "NotInList". THe problem is .. I keep getting this message [Run Time Error 13, Type Mismatch] What does this mean? THe line it craps out on is: Set rstAnalyst=db.OpenRecordset(sqlFinancial_Analyst, dbOpenDynaset)
Thanks for your help.

Private Sub Financial_Analyst_NotInList(NewData As String, Response As Integer)
'Suppress the default error message
Response = acDataErrContinue
'Prompt user to verify if they wish to add new value
If MsgBox("The Financial Analyst " & NewData & " is not in list. Add it?", vbYesNo) = vbYes Then
'Set Response arguement to indicate that data is being added
'Open Recordset of the Financial_Analyst Table
Dim db As Database
Dim rstAnalyst As Recordset
Dim Financial_Analyst As String
Set db = CurrentDb()
sqlFinancial_Analyst = "Select * From Financial_Analyst"
Set rstAnalyst = db.OpenRecordset(sqlFinancial_Analyst, dbOpenDynaset)
'Add new Analyst with the variable that is stored in the variable NewData
rstAnalyst.AddNew
rstAnalyst![Analyst] = NewData
rstAnalyst.Update
'Inform the combo box that the desired item has been added to the list
Response = acDataErrAdded
rstAnalyst.Close 'Close the recordset
End If
End Sub


Same Circus, Different Clowns

 

[TheAceMan1]

How are ya SuePee . . .

There's no tablename in the SQL . . .

[blue]Dim [red]Financial_Analyst[/red] As String
Set db = CurrentDb()
sqlFinancial_Analyst = "Select * From [red][b]Financial_Analyst[/b][/red]"[/blue]

. . . and Financial_Analyst is not concatenated properly.

Try this:

[blue]Private Sub Financial_Analyst_NotInList(NewData As String, Response As Integer)
   Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
   Dim Msg As String, Style As Integer, Title As String
   Dim Financial_Analyst As String
   
   Msg = "The Financial Analyst " & NewData & " is not in list. Add it?"
   Style = vbQuestion + vbYesNo
   Title = "User Response Required! . . ."
   
   If MsgBox(Msg, Style, Title) = vbYes Then
      Set db = CurrentDb()
      Financial_Analyst = "[purple][b][i]Your tableName Here[/i][/b][/purple]"
      SQL = [b]"Select * From " & Financial_Analyst & ";"[/b]
      Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
      
      rst.AddNew
      rst!Analyst = NewData
      rst.Update
      
      Set rst = Nothing
      Set db = Nothing
      Response = acDataErrAdded
   Else
      Response = acDataErrContinue
   End If

End Sub[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[PHV]

Replace this:
Dim rstAnalyst As Recordset
with this:
Dim rstAnalyst As DAO.Recordset

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SuePee]

Hi all,Me again.

I am still having a problem with a "Type Mismatch" error.

Maybe this will help, I don't know but here goes. The table that the data entry form completes is called "Work Directives" and the table the info cames from is called "Financial Analyst". The item on the Financial Analyst table is called [Analyst] and on the Work Directives table it is called [Financial Analyst]. Originally the name for the combo box was [Combo Box 86] and I changed it to [Analyst]

Working with all these tables has me so confused. Where could the type mismatch come from? Which table should be mentioned in the code? This is driving me crazy because it won't work and I KNOW it must be something so simple I will feel real stupid when it's fixed.




Same Circus, Different Clowns

 

[PHV]

Which line of code is highlighted at the time the error raises ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SuePee]

The line that is highlighted is:
Set rstAnalyst = db.OpenRecordset(sqlFinancial_Analyst,dbOpenDynaset)

Thanks for any help you can give.



Same Circus, Different Clowns

 

[PHV]

Have you tried my suggestion stamped 17 Mar 08 13:52 ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SuePee]

PHV,

Yes I tried this, but it didn't work. I think I am having a problem knowing what goes where. Going back to the original question:
I want to add a new "Analyst" to a table called "Financial_Analyst". I am using a combo box that lists the current Analysts, but if one is not there I would like the user to be able to add an Analyst to the Financial Analyst table. Then when the Engineering Data Entry form data is saved, it would also be saved to the Work Directives table in the Analyst field.
I think my problem lies in knowing which form/table to put in the code.

Did I confuse you enough?

Thanks again


Same Circus, Different Clowns

 

[maxhugen]

Looking at your original code snippet, it seems you are declaring a variable:

Dim Financial_Analyst As String

... but using an undeclared variable instead:

sqlFinancial_Analyst = "Select * From Financial_Analyst"

First, make sure that you have the following at the top of the module this code is in:

[tt]Option Compare Database
Option Explicit[/tt]

The 'Option Explicit' will catch any variables you have not explicitly declared - excellent way to catch typos!

Also, to help make the code more readable, I'd suggest you use a variable named something like 'strSQL'

Another thing... in one post you call the table "Financial_Analyst", but in another you call it "Financial Analyst" (with a space in the table name). Which is correct? If it has a space in the name, you'll need to enclose it in square brackets, eg:

[tt]"Select * From [red][[/red]Financial Analyst[red]][/red]"[/tt]

Max Hugen
Australia

 

[TheAceMan1]

Howdy maxhugen . . .

Thats already been mentioned. See my post [blue]17 Mar 08 13:06[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886