Responding to the NOT ON LIST property

[Guest]

I am try to create code to respond to the Not on list event.

the following code is from Evan Callahan's book Access2000 Visual Basic for
Applications.

Private Sub Fabric_NotInList(NewData As String, Response As Integer)
'Ask user if want to add new fabric to list

Dim strMessage As String
Dim dbsSoftGoodsOrders As Database
Dim rstTypes As DAO.Recordset

strMessage = "Are you sure you want to add '" & NewData & "'to the list of
fabrics?"

If Confirm(strMessage) Then

'Open the Fabrics Table and add the NewData Value.
Set dbsSoftGoodsOrders = CurrentDb
Set rstTypes = dbsSoftGoodsOrders.OpenRecordset("Fabrics")
rstTypes.AddNew
rstTypes!FabricID = NewData
rstTypes.Update
Response = acDataErrAdded 'Requery the list
Else
Response = acDataErrDisplay 'Display the error
End If

End Sub


My problem is that before I can enter a new Fabric I must first enter its
(new) pattern name. I seem to recall there was another version of the above
code that would open a data entry form so I could fill in all the required
info.

Any suggestions are greatly appreciated.

 

[tina]

here's the code i use to add employee records on the fly, in a database i
use at work:


Private Sub td_empID_NotInList(NewData As String, Response As Integer)

If MsgBox("Do you want to add this employee to the droplist?", _
vbYesNo + vbDefaultButton2, "INITIALS NOT IN LIST") = vbYes Then
DoCmd.OpenForm "frm01Employees_pop", , , , acFormAdd, acDialog, _
UCase(NewData)
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

it's an example, so of course you'll have to tweak it to fit your form and
control names, etc.

hth

 

[Guest]

Lele:

The trick is to open the other form in dialog mode, which pauses code
execution in the procedure until the form is closed. The value of the NewData
argument is passed to the form as its OpenArgs property, which is then used
in the form's Open event procedure to set the DefaultValue property of the
relevant control. When code execution resumes in the NotInList event
procedure it first confirms that a new record was added in the other form and
sets the return value of the Response argument accordingly. Here's an
example for adding a City, which then needs other data such as County adding
in the other form. This is similar in principle to Tina's code, but extended
a little to cater for the possibility of the user changing their mind and not
adding the new city:

Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData " to list?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmCities", _
datamode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure city has been added
If Not IsNull(DLookup("CityID", "Cities", "City = """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Cities table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If

The above code merely passes the value to the frmCities form. In its Open
event procedure the value is assigned to the DefaultValue property of the
City control:

If Not IsNull(Me.OpenArgs) Then
Me.City.DefaultValue = """" & Me.OpenArgs & """"
End If

Assigning the value to the DeafaultValue property rather than to the Value
property of the control does not initiate a new record, so the user can back
out simply by closing the form. Once they add any other data however a new
record is initiated. Note that the code wraps the value assigned to the
DefaultValue property in literal quotes. This is because the DefaultValue
property is always a string expression regardless of the field's data type.
This can be crucial in circumstances where you might not expect it to be so;
dates are a case in question, where an incorrect value could be set depending
on the local date format being used, so its always prudent to wrap the value
in quote to ensure that it is correctly assigned to the property.

Ken Sheridan
Stafford, England

 

[Guest]

Thanks for the suggestion Tina,
It works pretty well, but I still have a problem. When I tell it I want to
add to the list, the data entry form opens up just fine, and takes the new
data. I then save the new data, and close the form. When I return to the
original drop down list, I continue to get the message ...Not on list,
although The new entry appears in the list. If I then scroll to the item,
the form accepts it and I can continue.

Obviously, this is a bit clumsy.

Do you know how I can add the new item on the popup data entry form, close
it, and have my cursor be returned to the dropdown list with the new data
already selected and ready to go to the next field?

Thanks

 

[tina]

well, that's how it works in my application. the key point is that the newly
created record in the droplist table must *exactly* match the value you
originally typed in the combo box. for instance, refering to the code i
posted: if i type initials JJB in the combo box and say Yes to the "want to
add?" msgbox, and then enter the new employee record and *change* the
initials to JB, then the NotInList error will continue to be generated.

hth

 

[Guest]

Thank you so much for your help on this. I have been pondering over this
situation, and have decided perhaps I need to step back and re-think

I have a table called Patterns which is the one side and is connected to a
Fabrics table (the many side) which lists the colors a pattern is available
in. Both tables have autocounters as their primary keys.

When I am entering a new line item on an order form, it is possible that
pattern is already in my database but the fabric (color) is not. It is also
possible that I don't have either the pattern or the color add would need to
add both "on the fly" by using the not on list property.

If the pattern is not in the list I would need to open a form to add various
other bits of info that I need. If the pattern is in the database but the
fabric color is not then I could add that info with out opening a form.

While working in MS excess, I have worked with a feature that lets me create
2 drop down boxes. The user's selection in the first box, influences the
options presented in the second box. Would something like that work here?
Of course, I would still need to be able to add to the list on by opening a
form for the pattern, but just and the data for the fabric.

Thanks so much for your help. I am feeling a bit overwhelmed.

Lele