Not In List

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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!!

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Thanks Paul

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

Regards

Peter

Reply | Quote

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.

Reply | Quote

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.

*****************************************************
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)

*****************************************************
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

Reply | Quote

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?

Reply | Quote

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.

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.

Reply | Quote

[indent]

---

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.

---

[/indent]

How many times have I written something only to come back a month later and scratch my head.

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.

Reply | Quote

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.

Reply | Quote