Use VBA code to change from Edit mode to Add/New mode?

[Guest]

I want to use this in my Not in List Event of a combo box that is on a Edit
form, so that if a user attempts to enter a product that is not on the list,
they will be warned that it is not a product currently in the database and
then will be asked if they want to add it. I don't however, want them to
just be able to add items to the list. If they select "Yes" they want to add
product to the database, then I want to open a new form in Add New mode in
order to prompt them to enter all the specific details about the product
instead of just adding a name to a list. I've done this in the past by
actually having two identical forms with different names. One set to "Edit"
mode, and another set to "Add" mode. When on the edit form, the not in list
event would prompt a user to add a new item, and would then open the "ADD
New" form for data entry.

This time I was trying to get by with just using the same form and opening
it with the different arguments, which works fine when opening them directly
from command buttons, but how can I do this for the not in list event. I
can't "Open" the form I'm already on in a different mode can I ???. Does
anyone have any ideas how to do something like this?

Any help greatly appreciated,
thanks

 

[Marshall Barton]

I want to use this in my Not in List Event of a combo box that is on a Edit
form, so that if a user attempts to enter a product that is not on the list,
they will be warned that it is not a product currently in the database and
then will be asked if they want to add it. I don't however, want them to
just be able to add items to the list. If they select "Yes" they want to add
product to the database, then I want to open a new form in Add New mode in
order to prompt them to enter all the specific details about the product
instead of just adding a name to a list. I've done this in the past by
actually having two identical forms with different names. One set to "Edit"
mode, and another set to "Add" mode. When on the edit form, the not in list
event would prompt a user to add a new item, and would then open the "ADD
New" form for data entry.

This time I was trying to get by with just using the same form and opening
it with the different arguments, which works fine when opening them directly
from command buttons, but how can I do this for the not in list event. I
can't "Open" the form I'm already on in a different mode can I ???. Does
anyone have any ideas how to do something like this?

Check the OpenForm method in Help. I think the DataMode
argument will do what you want.

 

[Guest]

Yes, I know how to use the data mode when opening the form, that works fine.
What I'm wanting though is when a form is already open in "Edit" mode, then
for the Not In List Event of a combo box If the user selects yes they want to
add a new record, I want a "New" form (really the same form- just different
mode) to open in the "Add New" mode. Like going to a New Record, but I tried
that and it won't let me go to a new record when the form is in Edit- because
Allow Additions=No. I basically want 2 forms- one where the user is allowed
to view/edit existing records, but not allowed to Add new ones, and the other
one for when the user specifically choses to "Add a New Record" where that's
all they can do, not view or edit existing records.

So is there any way to change the DataMode argument in code while a form is
already open? I guess closing the current form and re-open with the
different arguments would be fine too, but how can I use OpenForm method when
the form I want to open is the same form I'm already in? Can you have two
copies of the same form open at the same time??? Does this make any sense?

 

[Marshall Barton]

Are you saying you don't want to open a form, you just want
to change it's AllowEdits and AllowAdditions properties??

If that's all you need, then just do it. The only trick is
to make sure the current record is saved:

If Me.Dirty Then Me.Dirty = False 'save any edits
Me.AllowAdditions = True
Me.AllowEdits = False

 

[Rick Brandt]

Yes, I know how to use the data mode when opening the form, that
works fine. What I'm wanting though is when a form is already open in
"Edit" mode, then for the Not In List Event of a combo box If the
user selects yes they want to add a new record, I want a "New" form
(really the same form- just different mode) to open in the "Add New"
mode. [snip]

I'm puzzled a bit here. Normally the RowSource for a ComboBox is a
different table than the one the form is bound to. In your NotInList event
you would want to open a form for adding a record to the Lookup table, not
the same table as the current form is bound to.

If I am half way through filling out a record in this form why would I want
the NotInList event of a ComboBox to suddenly take me to a form where I can
add a record to the same table I was already editing?

 

[Guest]

Rick,

The combo box is the first control on the form, it is used to "look up"
specific items by name, then once a selection is made, the details for the
selected item will display in the rest of the form and the user can then edit
any of the detail data. What I'm wanting is in case the user attempts to
type in an item which is not found in the list, then they will be given the
option of adding the new item to the database. If they chose to do so, then
I want to give them a blank form where they can enter the new item (in text
box instead of combo box) and the specified details for the new item. I
don't want them to be able to type in anything and have it automatically
added to the list because 1) I want them to actually look at the list, many
of these items have complicated spellings and I have previously found the
same item listed several times with only slightly different spellings, so
maybe if they have to go to a whole new form and fill in all the details,
they'll take a better look at the list to make sure the item they want isn't
already there; and 2) I figure if they are directed to a new blank form
specifically for adding a new item, where they can't see or edit the existing
records, they'll be more likely to go ahead and fill in the details for the
new item, rather than skipping over to look at the next item.

I hope this makes sense, I will try Marshalls suggestion.

Thanks

Yes, I know how to use the data mode when opening the form, that
works fine. What I'm wanting though is when a form is already open in
"Edit" mode, then for the Not In List Event of a combo box If the
user selects yes they want to add a new record, I want a "New" form
(really the same form- just different mode) to open in the "Add New"
mode. [snip]

I'm puzzled a bit here. Normally the RowSource for a ComboBox is a
different table than the one the form is bound to. In your NotInList event
you would want to open a form for adding a record to the Lookup table, not
the same table as the current form is bound to.

If I am half way through filling out a record in this form why would I want
the NotInList event of a ComboBox to suddenly take me to a form where I can
add a record to the same table I was already editing?

 

[Guest]

Marshall,

I put this code in the Not In List Event for if the user selected Yes from
the message box asking if they want to add a new record. I also added

DoCmd.GoToRecord acActiveDataObject, , acNewRec

to go to a new record. I got stuck with my message box asking about adding
a new record, but when I clicked yes, it did nothing- just kept displaying
the message box. After selecting yes and not getting rid of the message box
if I click "No", then an error: Can't go to specified record will display.
However, if I just select NO from the beginning from the message box, it
works correctly in returning to the form, "undoing" whatever was typed in the
combo box, and displaying the drop down list again.

Here is the complete code:

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

If MsgBox("""" & NewData & """ does not match an Item currently in the
database." & _
"Would you like to add this Item to the database?", vbYesNo, "New
Item?") = vbNo Then
cboItemName.Undo
Response = acDataErrContinue
Else
Me.AllowAdditions = True
Me.AllowEdits = False

DoCmd.GoToRecord acActiveDataObject, , acNewRec


Any ideas as to what's wrong?

Are you saying you don't want to open a form, you just want
to change it's AllowEdits and AllowAdditions properties??

If that's all you need, then just do it. The only trick is
to make sure the current record is saved:

If Me.Dirty Then Me.Dirty = False 'save any edits
Me.AllowAdditions = True
Me.AllowEdits = False
--
Marsh
MVP [MS Access]

Yes, I know how to use the data mode when opening the form, that works fine.
What I'm wanting though is when a form is already open in "Edit" mode, then
for the Not In List Event of a combo box If the user selects yes they want to
add a new record, I want a "New" form (really the same form- just different
mode) to open in the "Add New" mode. Like going to a New Record, but I tried
that and it won't let me go to a new record when the form is in Edit- because
Allow Additions=No. I basically want 2 forms- one where the user is allowed
to view/edit existing records, but not allowed to Add new ones, and the other
one for when the user specifically choses to "Add a New Record" where that's
all they can do, not view or edit existing records.

So is there any way to change the DataMode argument in code while a form is
already open? I guess closing the current form and re-open with the
different arguments would be fine too, but how can I use OpenForm method when
the form I want to open is the same form I'm already in? Can you have two
copies of the same form open at the same time??? Does this make any sense?

 

[Marshall Barton]

Three points:
One, the combo box must be unbound.

Two, If the current record has been edited, you must save it
before trying to go to another record (or all sorts of
events are queued up before the NotInList event can finish).

Three, I am beginning to think the NotInList event has too
many things going on to do this. That should not be an
issue because you don't really need the event for this. Use
the combo box's AfterUpdate event:

If Me.cboItemName.ListIndex = -1 Then
If MsgBox("""" & Me.cboItemName & """ does not match an
Item currently in the database." & _
"Would you like to add this Item to the
database?", vbYesNo, "New Item?") = vbNo Then
cboItemName.Undo
Else
If Me.Dirty Then Me.Dirty = False 'save any edits
Me.AllowAdditions = True
Me.AllowEdits = False
DoCmd.GoToRecord acActiveDataObject, , acNewRec
End If

Be sure to set Limit to List to No.

 

[Guest]

Marshall,

Thank you very much, this works great- exactly like I wanted. I was
starting to have other problems with the Not In List event anyway- even when
I'd get it to work correctly, it would continue to display my message box
twice no matter what response I used. This works great, I never even thought
of doing it this way in the After Update, glad to get rid of the Not In List
event.

Thanks

Three points:
One, the combo box must be unbound.

Two, If the current record has been edited, you must save it
before trying to go to another record (or all sorts of
events are queued up before the NotInList event can finish).

Three, I am beginning to think the NotInList event has too
many things going on to do this. That should not be an
issue because you don't really need the event for this. Use
the combo box's AfterUpdate event:

If Me.cboItemName.ListIndex = -1 Then
If MsgBox("""" & Me.cboItemName & """ does not match an
Item currently in the database." & _
"Would you like to add this Item to the
database?", vbYesNo, "New Item?") = vbNo Then
cboItemName.Undo
Else
If Me.Dirty Then Me.Dirty = False 'save any edits
Me.AllowAdditions = True
Me.AllowEdits = False
DoCmd.GoToRecord acActiveDataObject, , acNewRec
End If

Be sure to set Limit to List to No.
--
Marsh
MVP [MS Access]

I put this code in the Not In List Event for if the user selected Yes from
the message box asking if they want to add a new record. I also added

DoCmd.GoToRecord acActiveDataObject, , acNewRec

to go to a new record. I got stuck with my message box asking about adding
a new record, but when I clicked yes, it did nothing- just kept displaying
the message box. After selecting yes and not getting rid of the message box
if I click "No", then an error: Can't go to specified record will display.
However, if I just select NO from the beginning from the message box, it
works correctly in returning to the form, "undoing" whatever was typed in the
combo box, and displaying the drop down list again.

Here is the complete code:

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

If MsgBox("""" & NewData & """ does not match an Item currently in the
database." & _
"Would you like to add this Item to the database?", vbYesNo, "New
Item?") = vbNo Then
cboItemName.Undo
Response = acDataErrContinue
Else
Me.AllowAdditions = True
Me.AllowEdits = False

DoCmd.GoToRecord acActiveDataObject, , acNewRec