NotInList Event

[Klegg72]

Hello,
I am trying to set up a combo box that will allow new data as well. I found some code* to do this, but it isn't working. The combo box will populate a field in a relationship. Also this is part of a subform.

Runtime error '3192'
Cannot find output table 'tblParts'
How do I use the NotInList event?

I can explain my entire situation and upload my database if it will help.

Thanks In Advance

*
Private Sub ComboPartID_NotInList(NewData As String, Response As Integer)

Dim strSQL As String
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "'is a new part!." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Part Number...")
If i = vbYes Then
strSQL = "Insert Into tblParts ([strParts]) " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

 

[John Big Booty]

Just been messing around in my Sand box DB, and the following codes works on a continuous Sub-form;

    Dim MsgText As String
    Dim lngcombo As Long
   
    
    MsgText = Me.Combo0.Text & " is not in the list. Would you like to add it to the list now?"
 
   
    If MsgBox(MsgText, vbYesNo) = vbYes Then
        
        DoCmd.RunSQL "INSERT INTO TBL_Ingredient ( Ingredient, ingtyp )  values (" & Chr(34) & Me.Combo0.Text & Chr(34) & ", 1 ) ;"
        Else
            MsgBox "You can double click this field to add this item latter"
    End If
    
    If IsNull(Me![Combo0]) Then
        Me![Combo0].Text = ""
    Else
        lngcombo = Me![Combo0]
        Me![Combo0] = Null
    End If
    Me![Combo0].Requery
    
    Response = acDataErrContinue

 

[Klegg72]

It still gives me "Runtime error 3192. Cannot find Output table 'TBL_Parts' I've tried 'Parts_PartID' 'TBLPartID' 'Parts' 'PartID'
Parts is definitely the table name.
PartID is the field where I want the data to go.
I guess my form isn't a 'true' subform, but it does have combo boxes and text boxes that look at two different tables... is that the problem?
I even tried this in a normal form (not a subform)

 

[John Big Booty]

Any chance of posting a copy of your DB? Pre '07 version for preference

 

[Klegg72]

Sure... here it is. I tried to save as a 2002-2003 database, but apparently I'm using '07 features not in older versions.

I will use any method I need to get the results I need. i.e. If you can think of another approach I will gladly look into it.

What I need:
For every order there will be one or more jobs (a job is basically a line item from an order). For every job there will be only one part, but eventually one part will be assigned to more than one job (i.e. subsequent orders) That is why I need a table for Jobs and Parts.

I have a working database now, it's just not as powerful as I want, so I have time to tweak this one until it's perfect.

Thanks

 

[DCrake]

Your Syntax = is TBL_Parts when your table is called Parts

Also it is bad pratic to use this as a primary key. And you do no and cannot use the .text syntax in this instance. You can only use .Text when the control has the focus.

 

[Klegg72]

I think I'll keep the parts data in the jobs table and use a query to analyze/manipulate the data.

 

[John Big Booty]

I've had a look at your DB. Frankly your Parts table is too complicated to simply add a part the way you are trying to do. The approach you are currently attempting is better suited to a situation where you only need to add one piece of information to your table.

A better approach for your situation would be to add the part via a pop up form that is partially populated with the information that you have typed into the combo. See the Not In List event for the ingredients combo in the attached DB.

 

[Thales750]

Hey Guys,
Years ago someone on this forum had this posted.

Private Sub cboYourCombo_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim strMsg As String
Dim ctl As Control
Set ctl = Screen.ActiveControl
 
strMsg = "Type " & NewData & " Is not listed!" & vbCrLf & "Do you want to add it?"
    If MsgBox(strMsg, vbYesNo, "Not listed") = vbYes Then
        strSQL = "INSERT INTO tblYourTable (YourField) "
        strSQL = strSQL & "VALUES('" & NewData & "');"
        CurrentDb.Execute strSQL
        Response = acDataErrAdded
    Else
        ctl.Undo
        Response = acDataErrContinue
    End If
End Sub

The only modification you need to make is to the “INSERT INTO table (field)” line.
If you see this and recognize it as yours take credit here.

 

[Thales750]

I use this to open a form and retain the text, the user has added.

Private Sub Combo194_NotInList(NewData As String, Response As Integer)
 
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "frmCoreCompaniesAddNew"
 
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
    Forms![frmCoreCompaniesAddNew]![CompanyName] = NewData
        Response = acDataErrContinue
 
End Sub

On the Click Event of the Close Button on the frmAddNew Form I use this to populate the original Combo

Private Sub Command2_Click()
 
    On Error GoTo Err_Command2_Click
 
 If Me.Dirty Then Me.Dirty = False
 
 
      If IsLoaded("frmCoreVendorsAggreementNew") Then 
Forms![frmCoreVendorsAggreementNew]![Combo194] = Null
Forms![frmCoreVendorsAggreementNew]![Combo194].Requery
Forms![frmCoreVendorsAggreementNew]![Combo194] = Me.CompanyID
End if
 
    DoCmd.Close
 
 
 
Exit_Command2_Click:
    Exit Sub
 
Err_Command2_Click:
    MsgBox Err.Description
    Resume Exit_Command2_Click
 
End Sub

This seems to work pretty well. Notice on the close button it refers to the ID field of the frmAddNew. The Combo box is actually storing the ID (primary Key) data of the tblCompanies, from the bound column (0). Combo boxes start their column count at “0”.

If IsLoaded( “Form or Report”) refers to the Microsoft Public Function

Function IsLoaded(ByVal strFormName As String) As Integer
 ' Returns True if the specified form is open in Form view or Datasheet view.
 
    Const conObjStateClosed = 0
    Const conDesignView = 0
 
    If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
        If Forms(strFormName).CurrentView <> conDesignView Then
            IsLoaded = True
        End If
    End If
 
End Function

 

[Thales750]

Hello All,
Can anyone tell me why the dates insert in this one.

Private Sub Combo2_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim strMsg As String
 
Dim ctl As Control
Set ctl = Screen.ActiveControl
strMsg = "Type " & NewData & " Is not listed!" & vbCrLf & "Do you want to add it?"
    If MsgBox(strMsg, vbYesNo, "Not listed") = vbYes Then
        strSQL = "INSERT INTO tblCompanies ( CompanyName, CompanyLogDate, CompanyLogTime, CompanyDateLastChanged) "
        strSQL = strSQL & "VALUES('" & NewData & "',#2/5/2011#,#2/5/2011 5:26:27 PM#,#2/5/2011#);"
        CurrentDb.Execute strSQL
        Response = acDataErrAdded
    Else
        ctl.Undo
        Response = acDataErrContinue
    End If
End Sub

And why this one wont?

Private Sub Combo2_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim strMsg As String
Dim logdate As Date
Dim LogTime As Date

logdate = Date
LogTime = Now()
 
Dim ctl As Control
Set ctl = Screen.ActiveControl
strMsg = "Type " & NewData & " Is not listed!" & vbCrLf & "Do you want to add it?"
    If MsgBox(strMsg, vbYesNo, "Not listed") = vbYes Then
        strSQL = "INSERT INTO tblCompanies ( CompanyName, CompanyLogDate, CompanyLogTime, CompanyDateLastChanged) "
        strSQL = strSQL & "VALUES('" & NewData & "',#logdate#,#LogTime#,#logdate#);"
        CurrentDb.Execute strSQL
        Response = acDataErrAdded
    Else
        ctl.Undo
        Response = acDataErrContinue
    End If
End Sub

I recon it's something to do with

Run-Time error '3075'
Syntax error in date in expression '#logdate'

Syntax.

 

[DCrake]

As you have already declared the variables as dates you do not need to wrap them in #'s. Remove them and it should be ok.

 

[Thales750]

As you have already declared the variables as dates you do not need to wrap them in #'s. Remove them and it should be ok.

Thanks David,

I was playing around with it and found this to work

Dim logdate As String
Dim LogTime As String
 
logdate = Date
LogTime = Now()
 
 
  strSQL = strSQL & "VALUES('" & NewData & "','" & logdate & "','" & LogTime & "','" & logdate & "');"

Which seems weird to me. Access apearently converted the text to "Date"

 

[DCrake]

As an alternative you could switch to this method

Dim Rs As DAO.Recordset
Set Rs = CurrentDb.Openrecordset("TblCompanies")
      Rs.AddNew
      Rs("CompanyName") = NewData
      Rs("CompanyLogDate") = Date()
      Rs("CompanyLogTime") = Now()
      Rs("CompanyDateLastChanged") = Date()
      Rs.Update
      Rs.Close
Set Rs. = Nothing

 

[Thales750]

Which is better?

Plus you were right.

This works:

Dim logdate As Date
Dim LogTime As Date
 
logdate = Date
LogTime = Now()
 
   strSQL = "INSERT INTO tblCompanies ( CompanyName, CompanyLogDate, CompanyLogTime, CompanyDateLastChanged) "
        strSQL = strSQL & "VALUES('" & NewData & "',' & logdate & ',' & LogTime & ',' & logdate & ');"
        CurrentDb.Execute strSQL

 

[DCrake]

It not a case of which is better it is a case of which method you prefer. Me I prefer the latter. Reason being I can code it quicker and it is more readable and easier to debug. Whatever floats your boat. so to speak.

 

[Thales750]

It looks to be easier to debug. plus less formatting.

This system is going to connect to a My SQL server. Is there any performance advantage to either of them?

If not I, think I'm going to use more DAO in the future.

Thanks again David.

 

[Thales750]

for some reason this line

Set Rs. = Nothing

Is causing an error.
I found it, it should not have a "." between the "RS" and the "=".

Bob Larson wrote on another thread from 2007 that it's a good isea to make RS = Nothing to empty the varible from memory space. Should that apply to all varibles?

 

[smig]

your strSQL is a string

when you write it like this:
strSQL = strSQL & "VALUES('" & NewData & "',#logdate#,#LogTime#,#logdate#);"
it read this part "',#logdate#,#LogTime#,#logdate#);" as simple string. it will never know logdate, logtime and logdate are variables

when you write it this way it read the first string part, add the value of the variable, add the next string part, add the next value of varaible ...and make it all as long string
strSQL = strSQL & "VALUES('" & NewData & "','" & logdate & "','" & LogTime & "','" & logdate & "');"

use debug.print strSQL after each one of them and see the difference


as for your question, which is better. there is not much of a difference. it's your preference. using the recordsets is easier to read and mostly to debug.

 

[Thales750]

As an alternative you could switch to this method

Dim Rs As DAO.Recordset
Set Rs = CurrentDb.Openrecordset("TblCompanies")
      Rs.AddNew
      Rs("CompanyName") = NewData
      Rs("CompanyLogDate") = Date()
      Rs("CompanyLogTime") = Now()
      Rs("CompanyDateLastChanged") = Date()
      Rs.Update
      Rs.Close
Set Rs. = Nothing

I've been using this, and I will not be going back to writing SQL style queries if I don't have to.

Thanks again David.