On NotInList

[kiwi44]

I have a combo box that is populated from a table. If an item is not in the table, I trigger the following code:

Private Sub frm1_Control_NotInList(NewData As String, Response As Integer)
Dim intReply As Integer

intReply = MsgBox("The item'" & NewData & "' is not in the list: Would you like to add?", vbYesNo)
If intReply = vbYes Then
' DoCmd.OpenTable "tbl1", acViewNormal, acAdd
DoCmd.OpenForm "frmAddItemTotbl1", , , , acFormAdd, , NewData
Response = acDataErrAdded
Else
MsgBox "Please select an item from the list"
Response = acDataErrContinue
End If

End Sub

Neither the 'active' DoCmd line or the commented out line work - the active line gives me a 'Not in List' message when the second form opens. The OpenTable line opens the table, but doesn't fill in the field (which I can see is 'correct' because there is nothing to pass NewData to the record).

Is it possible to get NewData to populate a field on the second form (or directly into the table) without having to rekey NewData, or getting an error message?

I am using Access 2000.

Thanks for any help.

Kiwi44

[charlotte]

Here's how I do it. This is a template, so replace the cbo in the sub declaration with the name of the particular combobox control. The combobox must have the name of its bound field in its tag property.

I haven't built an ADO version yet, so this requires a DAO reference in Access 2000.

<pre>Private Sub cbo_NotInList(NewData As String, _
Response As Integer)
Dim ctl As Control
Dim strSQL As String
Dim strTbl As String

' Return Control object that points to combo box.
Set ctl = Screen.ActiveControl
' Prompt user to verify they wish to add new value.
If MsgBox(NewData & " does not exist. Add it?", _
vbOKCancel) = vbOK Then
' Set Response argument to indicate that
'data is being added.
Response = acDataErrAdded
' Add string in NewData argument to row source.
' this assumes the combobox is based on a
' table or select statement
If InStr(ctl.RowSource, " tbl") > 0 Then
strTbl = Trim(Mid(ctl.RowSource, _
InStr(ctl.RowSource, " tbl")))
strTbl = Left(strTbl, InStr(strTbl, ".") - 1)
End If 'InStr(ctl.RowSource, " tbl") > 0
'Build the sql string for an
'insert query (this version uses DAO)
strSQL = BuildInsertSQL(ctl, NewData)
'this is the least efficient but
'most reliable way to run the query
'in if you use DAO
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Else
' If user chooses Cancel, suppress
' error message and undo changes.
Response = acDataErrContinue
ctl.Undo
End If 'MsgBox(NewData & " does not exist. Add it?", _
vbOKCancel) = vbOK
On Error Resume Next
End Sub

Private Function BuildInsertSQL(ByRef ctl As Control, _
ByVal NewData As String) _
As String
'Requires a reference to DAO 3.6 library
Dim strTbl As String
Dim strSQL As String
Dim dbs As dao.Database
Dim fld As dao.Field
If InStr(ctl.RowSource, " tbl") > 0 Then
strTbl = Trim(Mid(ctl.RowSource, _
InStr(ctl.RowSource, " tbl")))
strTbl = Left(strTbl, InStr(strTbl, ".") - 1)
End If 'InStr(ctl.RowSource, " tbl") > 0
strSQL = "INSERT INTO " & strTbl _
& " (" & ctl.Tag & ") SELECT "
Set dbs = CurrentDb
' use the field name stored in the
' tag property of the combobox
Set fld = dbs.TableDefs(strTbl).Fields(ctl.Tag)

'get the type of field
Select Case fld.Type
Case dbText
strSQL = strSQL & Chr(34) & NewData _
& Chr(34) & " As Expr1;"
Case dbDate
strSQL = strSQL & "#" & NewData & "# As Expr1;"
Case Else
strSQL = strSQL & NewData & " As Expr1;"
End Select
On Error Resume Next
Set fld = Nothing
dbs.Close
Set dbs = Nothing
BuildInsertSQL = strSQL
End Function</pre>

[kiwi44]

Many thanks - I was thinking about it today, and my thoughts went somewhat along these lines, but there are still a few new things for me to learn!

I will let you know when I have had an opportunity to put this into my program and got it to work (or not!).

Kiwi44

[kiwi44]

Dear Charlotte,

Thought I had this sorted out, but am having a problem with the Set fld line in the BuildInsertSQL function - the following is the contents of variables that I think are relevant:

? ctl.Rowsource
SELECT [tblComparatorDrug].[ComparatorDrug], [tblComparatorDrug].[ComparatorDrug_id] AS xyz_ID_xyz, [tblComparatorDrug].[ComparatorDrug] AS xyz_DispExpr_xyz FROM tblComparatorDrug ORDER BY [tblComparatorDrug].[ComparatorDrug];
?strTbl
tblComparatorDrug ORDER BY [tblComparatorDrug]
? strSQL
INSERT INTO tblComparatorDrug ORDER BY [tblComparatorDrug] (ComparatorDrug_id) SELECT

? Err.Number & ": " & Err.Description
3265: Item not found in this collection.

I have tried the various syntax options to get at this table, but still end up with the same error message.

? dbs.TableDefs.Count returns 40, which is the number of tables that show up in the database window, so I am assuming that some sort of connection is being made.

Your assistance with the debugging process will be much appreciated!

Kiwi44

[Bart]

Hi,

The problem is in this line:
INSERT INTO tblComparatorDrug ORDER BY [tblComparatorDrug] (ComparatorDrug_id) SELECT

It stops after the SELECT.
Debug the BuildSQL functions and try to find out why there is nothing aftere the SELECT statement and the ORDER BY should not be in it. It should look something like this:
INSERT into tblYourTable SELECT fld1, fld2 FROM tblAnotherTable;

[kiwi44]

Sorry if I have missed something, but isn't the "Select" bit handled by the Case Select section of the code?

Also, why should the SQL string have any effect on "Set fld = dbs.TableDefs(strTbl).Fields(ctl.Tag)"?

Thanks,

Kiwi44

[Bart]

No,
Charlottes code builds a SQL statement like this:
INSERT INTO tblYourTable YourFieldValue AS YourFieldName.

You should build a SQL statement like this:
INSERT INTO tblYourTable (fldYourFieldName) VALUES (YourValue);

So, you have to modify the code a little bit to get the result you want.

[kiwi44]

Thanks, I understand the need to modify the SQL query, but the code on my machine actually breaks at the 'set fld= ' line. I have tried using

? dbs.TableDefs(strTbl) 'strTable=table name

but each time I get an Item not found in this collection error message. Problem is, I do not know how to correct this error. Is it a reference problem? I have added a reference to DAO 3.60, and I can list all the tables in dbs.

TIA

Kiwi44

[charlotte]

If the code breaks at that line, it's because the object that fld is dependent on hasn't been set. This line ...

Set fld = dbs.TableDefs(strTbl).Fields(ctl.Tag)

is referencing the tabledef the field is in. If your strTbl variable contains anything but the name of a table, this will fail because the engine doesn't recognize that as a table name. Try declaring a DAO.TableDef variable at the start and assigning dbs.TableDefs(strTbl) to it before you try to set the fld variable. That will tell you whether you're coming up with a legitimate tabledef object.

[charlotte]

Bart,

There are alternate syntaxes for the Insert Into command, and I chose the other one for this routine.[img]/w3timages/icons/grin.gif[/img] The Access query engine converts the "Values" syntax on the fly.

[Bart]

Charlotte,

I know about the alternative syntaxes, I also use them frequently.
In the lounge I usually recommend this syntax INSERT INTO tblYourTable (fldYourFieldName) VALUES (YourValue);
because it contains both fieldnames and values. In that way it is clear which value gets into which field, so no misunderstanding about that can occur.

[kiwi44]

Thanks for the assistance with this problem - while I was trying to work it out, I happened to find alternative code on DejaNews (<A target="_blank" HREF=http://groups.google.com>http://groups.google.com</A> after searching on 'NotInList'.

This works perfectly, after I worked out how to name the function as something that could be called from multiple forms.

Thank you Michael Blake!

Kiwi44

[Bart]

Did you find how to call a function from multiple forms?
In the properties window:
=fnYourFunction(parm1; parm2; parm3)
Note the ; instead of ,!!!!!

[kiwi44]

I haven't had a problem with that, so now I am wondering if I should change the code throughout?

What I did have problems with was setting it up as a module that could be called from the form. I used the name (fIsInLookup) as the function name, but I got an error message saying that (I don't remember exactly) the code was looking for a sub or procedure, not a module. Changing the name to fIsInLookupFunction fixed that, then the only issue was changing the names of the tables/field names for each of the different forms.

I am sure that this is common knowledge for experienced programmers, but I do not consider myself in that category - yet!

Kiwi44