No problem
'Present a message box to ask the user if they want to add the new entry to
the table
If MsgBox(NewData & " Is Not In The Attribute Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
'If the user answers Yes, the insert a record with the primary key populated
with the value in the NewData argument. The Execute method runs SQL Action
queries. See additional notes below
CurrentDb.Execute ("INSERT INTO CISAttributeTable (ACTIVITY) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
'Requery the form so the new record added to the table is included in the
form's recordset.
Me.Requery
'Establish a recordset based on the form's recordsetclone
Set rst = Me.RecordsetClone
'Move the current record pointer for the recorset to the newly created record
rst.FindFirst "[Activity] = '" & NewData & "'"
'Make the form's current record the same as the current record for the
recordset
Me.Bookmark = rst.Bookmark
'Destroy the reference to the rst object
Set rst = Nothing
'Moves the cursor to the descripion control on the form (purely cosmetic and
for user convenience)
Me.txtDescription.SetFocus
'This is the response constant you use when you add a new record.
Response = acDataErrAdded
Else
'The user answered No
'Remove the entry from the combo box and revert the the value it was before
the user attempted the update
Me.cboActivity.Undo
'This is the response constant you use when you did not add the new record,
but don't want to produce an error
Response = acDataErrContinue
End If
The Execute method of the CurrentDatabase object is used to run Action
queries. It basically does the same thing as the Docmd.RunSQL, but it is
much faster because it does not go through the Acces UI. It is passed
directly to Jet for processing. Since it does not go through the Access UI,
it will not throw an error if it fails. That is why I always use the
dbFailOnError contstant that says if an error occurs, raise an Access error.
Change the value of NewData....hhmmmm..... I never even considered that. I
don't know if you can or what would happen. Now I have a new "Sience
Project".
Since I don't normally use popup dialogs like you are doing (nothing wrong
with it, a lot do). If there were no required fields in the table, I would
not bother to take the time to load a form. All it really needs, in this
case, is a primary key, so you can just add the record as I have done in the
combo. That record then becomes the current record and they can then enter
the data for the record. If there are fields that are required before a
record can be created, I would just add a new record in the form and populate
the primary key field with the NewData value. So if a user determines they
have mistyped an entry, the can delete the record if the record has already
been created or undo the record if it has not. Now, that leads us to getting
the new record's entry into the combo. If you add a record, not a problem,
it works as is. If you only go to a new record and populate the key, then I
would requery the combo in the form's After Insert event.
The .Text property can only be used when a control has the focus.
=Me.SomeControl and =Me.SomeControl.Text will return the same thing. I
never use the .Text property. It is mostly a hold over from VB.
A Requery does not use any data in the form. It is at the recordsource
level. It would be the same as closing and reopening a form. Requerying a
combo does the same thing except it only requeries its own row source query.
The name is from the old SciFi movie "The Day the Earth Stood Still". I had
to misspell it to get to use it (Klaatu is the correct way).
And, finally, I dont' knew anybad tYppstis
--
Dave Hargis, Microsoft Access MVP
Gilbert 2097 said:
Thanx for the reply Klatuu (where did you get that name anyway??)
I pretty much followed your example; i've never used the .execute to add the
item. I usually use a pop-up form to collect the data. I'll try to give a
'simple' example - purely hypothetical. Say you were choosing store names
from a combo box (like Joe's Pizza, Fred's jewelry, etc.) and you entered
'Gene's Csr Repair' and when you press enter the pop-up opens with this
'newdata' in a textbox (thru .openargs). Then you notice you hit an s
instead of an a in Car so you fix it on the pop-up, then click OK. The
combo requeries but the newdata does not match the record that was just added
to your table so it gives the not in list msgbox. What exactly is it that is
compared when it requeries?? I've tried changing the .text of the combo so
they match to no avail. Can you change the NewData variable to the altered
value before it requeries? Is the example you gave here your usual way of
dealing with not in list?? Could you possibly give it a few comments to
explain the purpose of each line.?. I hope you understand better what I'm
trying to do..... any help along these lines. I'm really not trying to be a
pain in the 'access' but I'm very curious about how it works, I know there
are a lot of bad typists out there who would appreciate a second chance to
get it right without having to type in the whole thing over again!!
--
Gil
Klatuu said:
I am not sure what you want to happen, but in the example below, what happens
is the record is not created and the user is returned to the combo where it
was when the user typed in the incorrect value. It shows the value of the
current record.
Private Sub cboActivity_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset
If MsgBox(NewData & " Is Not In The Attribute Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
CurrentDb.Execute ("INSERT INTO CISAttributeTable (ACTIVITY) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & NewData & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Me.txtDescription.SetFocus
Response = acDataErrAdded
Else
Me.cboActivity.Undo
Response = acDataErrContinue
End If
End Sub
--
Dave Hargis, Microsoft Access MVP
:
I have used a Combobox many times and know how they work (generally), but
I've always wanted to be able to allow the user to change their entry in case
he misspelled a word or realized they put in something wrong. When the
notinlist event fires I have a form pop up with a text box showing the
'newdata' passed by the openargs. Usually at this point the user clicks OK
and it is added to the recordsource table and the combobox is satisfied.
But what happens when the user wants to edit his entry on this form; when the
combobox requeries, it shows the not in list msgbox. It's like telling the
user 'Sorry you made a mistake; now you have to live with it". I realize
they could go back and edit the entry later in the underlying table but I'd
like to allow them to do it ' on the fly' so to speak. I have tried many
different options to try to get the combobox to recognize the new, editted
entry but haven't hit on the correct procedure. I tried setting the text
portion of the combobox to the new data when I click OK, I've tried setting
the value to the new ID once the item is added to the table.... I keep
getting error messages. Can anyone help me with the procedure I need to
follow so this can be done. Or if it is impossible I guess what I have to do
is disable the textbox that displays the newdata on the pop-up form so it
cannot be changed.?.
Thanx for any help...I am very familiar with the MSDN Library articles on
'notinlist' but maybe a third party article about htis might be helpful.?.
Using Access2003
