Results 1 to 15 of 15

Thread: Not In List

  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Not In List

    I am trying to write code that will deal with an item entered in a combo box that is not in the table. I have copied some code to do this but I get the new name into the new form but unless I make some other change to the form it does not add it to the record set. If I make a small alteration it saves it but I cannot work out how to requery the underlying table.

    The code in the table that calls the not in list event is

    Private Sub VendorID_NotInList(NewData As String, Response As Integer)
    Dim strVendor As String
    Dim intReturn As Integer
    strVendor = NewData
    message = "This vendor " & strVendor & " is not in the list do you want to add it"
    intReturn = MsgBox(message, vbQuestion + vbYesNo, "New Vendor")
    If intReturn = vbYes Then
    DoCmd.OpenForm FormName:="frmVendor", datamode:=acAdd, windowmode:=acDialog, OpenArgs:=strVendor
    End If
    If IsNull(DLookup("VendorID", "tblVendor", "[Name]" = """ & strVendor & """)) Then
    Response = acDataErrContinue
    Else
    Response = acDataErrAdded
    End If
    End Sub

    The code called as the form is opened is
    Private Sub Form_Load()
    Dim strVendor As String
    If IsNull(Me.OpenArgs) Then Exit Sub
    strVendor = Me.OpenArgs
    Me![Name].DefaultValue = """" & strVendor & """"
    End Sub

    The Vendor table has three fields VendorID, Name and Date entered.

    I would like to be able to transfer the new name, create a record, then requery the underlying table so the name would now be accepted, can anyone help me please.

    Mitch

  2. #2
    Star Lounger
    Join Date
    Feb 2001
    Location
    Wirral, Merseyside, Merseyside, England
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not In List

    This is the code I use to ask if the user would like to add the new item to a list. I then use some kind of requery on the combo box to ensure the item immediately gets added to the combo drop down list.

    Dim strMsg As String
    Dim MySQl As String
    Dim rst As Recordset
    Dim db As Database

    strMsg = "'" & NewData & "' is not in the list. "
    strMsg = strMsg & "Would you like to add it?"
    If vbNo = MsgBox(strMsg, vbYesNo + vbQuestion, "Colour") Then
    Response = acDataErrDisplay
    Else
    Set db = CurrentDb()
    MySQl = "Select * from [Colour Chart];"
    Set rst = db.OpenRecordset(MySQl)
    rst.AddNew
    rst![MyColour] = NewData
    rst.Update
    Response = acDataErrAdded
    rst.close
    End If

  3. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Re: Not In List

    Thanks for your reply but I get stuck at the Dim statement that sets db as a Database. I guess you have defined this somehow, but I have to admit I have not a clue as to how to do so.

    Mitch

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

    Re: Not In List

    What do you mean, you get stuck on it? Do you mean you get an error or that you don't understand it?

    If you happen to be using Access 2000 (you didn't specify, which makes all this guesswork), you won't have a reference to the DAO object library set by default, and the database object is only available through DAO. So you'll have to set the reference to DAO 3.6 yourself.

    If you are using Access 2000 and you have set the DAO reference, then make sure you declare the DAO object specifically, i.e., Dim db As DAO.Database. Both DAO and ADO have recordsets and properties and parameters, among other things, and the code will break if it finds a method for an unspecified DAO object that doesn't exist in ADO.
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Re: Not In List

    I apologize, I am using Access 2000. The error message I get tells me I have not defined the object. Since my first post I have solved the first have of my problem. My remaining problem is once the new form is opened in dialog mode the name inserted, without making any further alterations how do I save that record. When the form opens the Name has been inserted in the correct field but the Record ID is still showing autonumber. Now I am entering a space and then deleting it which creates the record but clearly that is not the way to go.

    Thank you

  6. #6
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not In List

    If I understand you correctly you are storing the vendor name and date the record is created plus an autonumber for the vendorID? If this is so you really don't need the form at all. You could open the vendor table through dao or ado and use the addnew method to create a new record setting the vendor name field to the NewData argument and the Date field to Date(). The atonumber ID field should update automatically when you use the update method. Then set the response argument to acDataErrAdded. This lets Access know a new item has been added to the combobox's underlying recordset and to requery the list. If this isn't happening double check the row source of your combobox. Good luck!!

  7. #7
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Re: Not In List

    I have to admit to being an amateur when it comes to access so using DAO or ADO is something totally new to me. I have solved my problem but I feel that I have done so in a very clumsy way. Certainly without reference to DAO or ADO.

    The code that now calls the notinlist event is

    Private Sub VendorID_NotInList(NewData As String, Response As Integer)
    Dim strVendor As String
    Dim intReturn As Integer
    strVendor = NewData
    message = "This vendor " & strVendor & " is not in the list do you want to add it"
    intReturn = MsgBox(message, vbQuestion + vbYesNo, "New Vendor")
    If intReturn = vbYes Then
    DoCmd.OpenForm FormName:="frmVendor", datamode:=acAdd, windowmode:=acDialog, OpenArgs:=strVendor
    End If
    If IsNull(DLookup("VendorID", "tblVendor", "[Name] = """ & strVendor & """")) Then
    Response = acDataErrContinue
    Else
    Response = acDataErrAdded 'this is a constant that requeries the undelying table
    End If
    End Sub

    The code that executes as the form loads is:-

    Private Sub Form_Load()
    Dim strVendor As String
    If IsNull(Me.OpenArgs) Then Exit Sub
    strVendor = Me.OpenArgs
    Me![Name].DefaultValue = """" & strVendor & """"
    Me![Name].Value = strVendor
    End Sub

    The main reason I think it is clumsy, is that if I make any alterations to the vendor form when it opens, when I close the form and I return to the original form it again tells me it is not in list.

    If anyone can help I would appreciate it.

  8. #8
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Re: Not In List

    Thanks Paul

    I am correct in assuming that I could use ADO just as well as DAO?

    Regards

    Peter

  9. #9
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not In List

    Though I'm not as familiar with ADO I believe it is just a matter of changing the type of objects (db,rst) from DAO to ADO. One thing I left out was to destroy the objects at the end of the procedure. I'll try and modify the post to reflect this oversight on my part.

  10. #10
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not In List

    The point I was trying to make was that after you prompt the user to confirm they want to add a member to the list it is not required that you open a form since the only variable data you are storing is the vendor name, which is contained in the NewData argument. The rest is an autonumber and the date which can be handled in the Default Value property of the field in table design. Below is a modified version of your original notinlist event. Please post back with any questions.


    Private Sub VendorID_NotInList(NewData As String, Response As Integer)
    Dim rst As DAO.Recordset
    Dim db As DAO.DATABASE
    Dim strMsg as String

    On Local Error GoTo ErrorHandler

    strMsg = "This vendor " & NewData & " is not in the list. Do you want to add it?"
    If MsgBox(strMsg, vbQuestion + vbYesNo, "New Vendor")= vbYes Then
    Set rst = db.OpenRecordset("YourTableName")
    With rst
    .AddNew
    !VendorName = NewData
    .Update
    End With
    Response = acDataErrAdded
    Else
    Response = acDataErrContinue
    End If

    ExitProc:
    rst.Close
    Set dbs = Nothing
    Set rst = Nothing
    Exit Sub

    ErrorHandler:
    Select Case Err
    Case Else
    strMsg = "Error Information..." & vbCrLf & vbCrLf
    strMsg = strMsg & "Sub: VendorID_NotInList" & vbCrLf
    strMsg = strMsg & "Description: " & Err.Description & vbCrLf
    strMsg = strMsg & "Error #: " & Format$(Err.Number) & vbCrLf
    MsgBox strMsg, vbInformation, "VendorID_NotInList"
    Response = acDataErrContinue
    Resume ExitProc
    End Select

    End Sub

    You will need to open the vendor table in design mode and set the Default Value of the date field to Date(). This will set this field to the system date of the pc when a new record is added.

    If you prefer to use the form make sure the form is bound to the vendor table or a query based on the vendor table.

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

    Re: Not In List

    No, DAO and ADO are two very different object models, and they aren't interchangeable, or even very intermixable. They contain some objects with the same name, but only some, and even those aren't necessarily exactly the same kind of object.

    Here's a generic notinlist event that I use behind simple comboboxes, along with an ADO routine to build the SQL to add a record. These routines assume that the combobox is based on a table or a simple select statement and that the name of the field you're adding data to is in the combobox's tag property.

    <pre>********************************************* ********
    Private Sub cbo_NotInList(NewData As String, _
    Response As Integer)

    ' not in list routine usable with either
    ' the DAO or ADO object models

    Dim ctl As Control 'holds the combobox control
    Dim strSQL As String 'holds the SQL being built
    Dim strTbl As String 'holds the table name the record
    'will be inserted into

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

    If InStr(ctl.RowSource, " tbl") > 0 Then
    ' this extracts the table name from the combobox
    ' rowsource but it won't work properly if you
    ' use a query rather than a table or select statement
    ' and it depends on a naming convention for tables

    strTbl = Trim(Mid(ctl.RowSource, InStr(ctl.RowSource, " tbl")))
    strTbl = Left(strTbl, InStr(strTbl, ".") - 1)
    End If 'InStr(ctl.RowSource, " tbl") > 0

    'create the SQL for the record insert
    strSQL = BuildInsertSQL(ctl, NewData)

    ' I use this method because it works with either
    ' the ADO or DAO versions of BuildInsertSQL

    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

    End Sub 'cbo_NotInList(NewData As String, _
    Response As Integer)
    </pre>


    <pre>********************************************* ********
    Private Function BuildInsertSQL(ByRef ctl As Control, _
    ByVal strTbl, _
    ByVal NewData As String) _
    As String
    Dim strSQL As String 'holds the SQL string being built
    Dim cat As ADOX.Catalog 'holds the ADO catalog object
    Dim tbl As ADOX.Table 'holds the ADO table object
    Dim fld As ADOX.Column 'holds the ADO field object

    'initialize the catalog and set its
    'active connection to the current ADO connection

    Set cat = New ADOX.Catalog
    cat.ActiveConnection = CurrentProject.Connection

    'start to build the SQL statement
    strSQL = "INSERT INTO " & strTbl _
    & " (" & ctl.Tag & ") SELECT "

    'set the tbl object to the passed tablename
    Set tbl = cat.Tables(strTbl)

    'set the fld object to the field whose name
    'was stored in the tag property of the control

    Set fld = tbl.Columns(ctl.Tag)

    'wrap the value in delimiters if necessary
    'and finish building the SQL statement

    Select Case fld.Type
    Case adChar, adVarChar, adVarWChar
    strSQL = strSQL & chr(34) & NewData _
    & chr(34) & " As Expr1;"

    Case adDBTimeStamp, adDBDate, adDate, adDBTime
    strSQL = strSQL & "#" & NewData & "# As Expr1;"

    Case Else
    strSQL = strSQL & NewData & " As Expr1;"
    End Select 'Case fld.Type

    'destroy object variables and exit
    On Error Resume Next
    Set fld = Nothing
    Set tbl = Nothing
    Set cat = Nothing

    BuildInsertSQL = strSQL
    End Function 'BuildInsertSQL(ByRef ctl As Control, _
    ByVal strTbl, _
    ByVal NewData As String) _
    As String
    </pre>

    Charlotte

  12. #12
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not In List

    Great! I had been mislead to believe they were interchangeable. Thanks for the correction.

    Your code was very enlightening as well. I particularly like your commenting style. It makes for very readable code.

    Should this line:

    strSQL = BuildInsertSQL(ctl, NewData)

    Read:

    strSQL = BuildInsertSQL(ctl, strTbl, NewData)

    or have I missed something again?

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

    Re: Not In List

    Nice catch. No, I'm the one who missed something. Probably pasted from two different versions of routines so there was a mismatch between them. <img src=/S/nuts.gif border=0 alt=nuts width=15 height=15>

    The extensive commenting is something that was originally forced on me by a VB professor, but I got addicted to it because it does make the code easier to understand and maintain. What seems so obvious at the time becomes more and more obscure as time passes, and it makes it easier for someone else to pick up and use.
    Charlotte

  14. #14
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not In List

    <hr>What seems so obvious at the time becomes more and more obscure as time passes, and it makes it easier for someone else to pick up and use.<hr>

    How many times have I written something only to come back a month later and scratch my head. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    I particularly like where you comment the end ifs, end selects and even end function with the head line. I've never seen that done before and while some may consider it overkill I can see where it would come in handy in some of the more complicated logical routines.

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

    Re: Not In List

    That, too, was the instructor's idea, but once I tried it I discovered how it simplifies figuring out which If this End If belongs to or which long routine the End Sub belongs to. It seems like overkill on short constructs, but if you standardize, it makes it all consistent and you don't ever overlook something by accident. It's much the same as using a naming convention, except this is a coding style convention.
    Charlotte

Posting Permissions

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