Results 1 to 14 of 14

Thread: On NotInList

  1. #1
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    On NotInList

    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

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: On NotInList

    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>

    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: On NotInList

    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

  4. #4
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: On NotInList

    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

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: On NotInList

    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;

  6. #6
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: On NotInList

    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

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: On NotInList

    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.

  8. #8
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: On NotInList

    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

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: On NotInList

    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

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: On NotInList

    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.
    Charlotte

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: On NotInList

    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.

  12. #12
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: On NotInList

    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

  13. #13
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: On NotInList

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

  14. #14
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: On NotInList

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •