EJ, this is the code I am now using. Right now when it opens up the modal
form, it is not passing the "not in list" text that the user first tried to
add into the original form that triggered the not in list event.
Like, they enter "elevator", it's not in the list. The message box opens
asking if they want to add.. they say yes. The modal form opens, and the
value they first typed is not preloaded for them.
This could mean that they could possibly type in a value other than the
original "not in list" value.
Like they said "elevator", but when the modal form pops up, they could type
"elevatorS" and then all of the info, and close- basically creating two new
records- elevator and elevatorS in the underlying table.
did i explain that ok?
Private Sub Building_Equip_NotInList(NewData As String, Response As Integer)
Dim strsql As String, x As Integer
x = MsgBox("Do you want to add this Equipment to the list?", vbYesNo)
If x = vbYes Then
DoCmd.OpenForm "FrmEquipment", acNormal, , "[EQUIPMENT]='" & NewData & "'",
acFormEdit, acDialog
strsql = "Insert Into tblEquipment ([Equipment]) values ('" & NewData &
"')"
'MsgBox strsql
CurrentDb.Execute strsql, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
EJ Williams said:
Gina,
I must be missing something in your description of your project. What I
understand is that you have a combo box that lists equiment. If the user
enters equipment that is not in the list you propmpt for confirmation that
the user wants to add the record. If they select Yes then you add the record
by SQL. The code I gave you should open the equipment form to the newly added
record alllowing the user to complete the entry.
What additional information needs to be loaded into the Equipment form for
the user? Where can this information be taken from? If it appears on the
original form with the combo box then you can get it in the NotInList event
and add it to the record when you create the new record in SQL. It should the
be present when the form is subsequently opened.
If I am unclear or you can give me more information that would better define
the situation please let me know.
Thanks,
Eric
Gina said:
That worked really well, thank you. The only thing I could ask is how to get
the new value to automatically fill into the modal form. But if that's
complicated, I'll go ahead and use this as it is.
Many many thanks!
Gina
:
Gina,
It looks like the fundamentals are there. If there are additional fields
that are required in the equipment table you could launch the equipment form
as a modal form with a filter that limits the display to the newly added
record.
DoCmd.OpenForm "Frm_Equipment", acNormal, , "[EQUIPMENT]='" & NewData & "'",
acFormEdit, acDialog
This will suspend processing the NotInList event until the user dismisses
the equipment form. At that point code execution will resume at the "Response
= acDataErrAdded" line.
Hopefully this is what you were looking for.
Eric
:
Additional Information: This is how I currently have the NotInList event set
up. It does allow for the NotInList event to add the record tot he single
field- but it doesn't allow the user to open the form and complete the
underlying record's fields.
Private Sub Building_Equip_NotInList(NewData As String, Response As Integer)
Dim strsql As String, x As Integer
x = MsgBox("Do you want to add this Equipment to the list?", vbYesNo)
If x = vbYes Then
strsql = "Insert Into tblEquipment ([Equipment]) values ('" & NewData &
"')"
'MsgBox strsql
CurrentDb.Execute strsql, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
:
I have a field in the form "Frm_Tasklist" that has a field that looks up to a
field in another table - this field is the primary key and contains Equipment
ID. I have made a form based off of the equipment table and called it
"Frm_Equipment.
Because the Equipment field on the Tasklist form is limited, I'd like the
user to be able to add new equipment when it is not on the list by opening
the "Frm_Equipment" form and entering the new data.
Please help.
