Combo box error after using NotInList to add item

[Guest]

My form contains a combo box cboAirline whose control source is tblAirline,
which has three fields:
pk_strAirlineCode text 3
strAirline text 25
strAirlineShort text 15
Column 1 is bound, the column widths are 0,0,1.

The combo box has a NotInList event procedure, as follows:

Private Sub cboAirline_NotInList(NewData As String, Response As Integer)
If MsgBox("This airline is not in the list. Add it?", vbOKCancel,
"Flight Detail") = vbOK Then
DoCmd.OpenForm "fdlgAirline", , , , , acDialog
If CurrentProject.AllForms("fdlgAirline").IsLoaded Then
cboAirline = Forms("fdlgAirline")!txt_pk_strAirlineCode
DoCmd.Close acForm, "fdlgAirline"
End If
Response = acDataErrAdded
Else
Me!cboAirline.Undo
Response = acDataErrContinue
End If
End Sub

The procedure is supposed to return the airline code from the dialog box to
the main form, but it triggers the NotInList error again, even though the new
item has been added to the drop-down list. I tried changing the return to
Forms("fdlgAirline")!txt_strAirlineShort, but this triggers an error
(appropriately) that the data is too long for the field.

Where have I gone wrong?

 

[tina]

well, i'm not sure how the nested If statement even runs. when you open form
fdlgAirline as a dialog, the code in the calling procedure is suspended
until the dialog form closes, AFAIK. so
"CurrentProject.AllForms("fdlgAirline").IsLoaded" should always return
False.

at any rate, this shouldn't be that complicated. you open form fdlgAirline
as a dialog, in order to create a new record with the strAirline value as
well as the strAirlineShort value, correct? (btw, unless you're referencing
the strAirline column of your combo box, somewhere else in your form, you
don't need to include it in the combo box RowSource.) assuming that the user
types the exact same value for strAirlineShort in the dialog form, that s/he
typed in the combo box, the code should run correctly as

Private Sub cboAirline_NotInList(NewData As String, Response As
Integer)
If MsgBox("This airline is not in the list. Add it?", vbOKCancel,
"Flight Detail") = vbOK Then
DoCmd.OpenForm "fdlgAirline", , , , , acDialog
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me!cboAirline.Undo
End If
End Sub

also, with a few extra lines of code, you can "copy" the short code entered
by the user in the combo box, into the short code control in the dialog
form, so the user doesn't have to type it twice and possibly make a mistake.

hth

 

[Guest]

I am using the same methods as described in this thread and have a couple
questions...

In the OnNotInList event of my cbo:
Private Sub cboContractorName_NotInList(NewData As String, Response As
Integer)
If MsgBox("This Contract Company is not in the list. Add it?", vbOKCancel,
"Contractor Not Found") = vbOK Then
DoCmd.OpenForm "frmAddContractCompany", , , , , acDialog
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me!cboContractorName.Undo
End If
End Sub

#1...This works in that my form to add a contract company opens, I can add
the info., (and a record is created in the appropriate table). When I close
this dialog to "go back" to my first form with the combo, I am NOT brought
back to the form as I left it. it appears to be moving to a new record. ???
#2...what is the code for "copying" the user-entered text to the "pop up"
form?

Thanks

 

[tina]

#2...what is the code for "copying" the user-entered text to the "pop up"

form?

let's tackle this one first. in the NotInList event procedure, there's a
variable called NewData. it contains the value that the user entered in the
combo box. change the OpenForm action in your code to

DoCmd.OpenForm "frmAddContractCompany", , , , , acDialog, NewData

the above code assigns the value of NewData to the OpenArgs property of
frmAddContractCompany, as a String data type, when it opens the form. now
that value is available within the form.

now open frmAddContractCompany in design view. add the following code to the
form's Load event procedure, as

Private Sub Form_Load()

Me!ContractorName = Me.OpenArgs

End Sub

obviously i just guessed at the field name - ContractorName - so substitute
the correct name of the field that you want to assign the value to. note:
if this control is the first contorl in the form's Tab order, suggest you
set it's TabStop property to No. there's no point in setting the value for
the user, but then making the user tab out of the control to the next
control in the Tab order.

#1...When I close
this dialog to "go back" to my first form with the combo, I am NOT brought
back to the form as I left it. it appears to be moving to a new record.

???

well, that sounds really strange. is there any code running in the combo
box's AfterUpdate event? or any code running in the Enter or GotFocus events
of the next control in the Tab order? or any code running in
frmAddContractCompany that refers back to this form with a MoveNext or
RecordsGoToNew action, anything like that?

hth

 

[Guest]

Tina,

Thanks for replying. The "NewData", "Me.OpenArgs" instruction works nice.

Now for my other problem..
I don't have anything in the AfterUpdate of the cboContractor (although I
would like it to refresh as the cbo is linked to a subform which displays the
Contractors address, etc... I can do that later)
A little more info >
The cboContractor is actually on a form that allows the users to Add a new
person (or employee) to. It is also the last Tab stop on the form. So, I have
a form property set to enter new records only
(Data Entry=Yes). Could this be the culprit?? There is no code running in
the On Enter or Got Focus of any controls on the form. I figured when the
focus came back to the form with the cbo, it would be as I left it.
I hope I am giving you the needed info here.

 

[Guest]

What do you think about this???

A combination of two of the forms properties is
1. form only allows new records.
2. form propery>other tab>cycle set to ALL records.
So when the focus leaves the last tab stop (myCbo) it jumps to the next tab
stop which is in the next record as it cycles all records.
I switched the Cycle property to "current record" and it comes back from the
Add Contractor form "as I left it".

 

[tina]

well, you're correct that the focus moves to the next control in the Tab
order, and since the combo box is the last control on the form, the focus
automatically moves to the next record - unless the Cycle property to set to
CurrentRecord, as you found.

i'm wondering why you want to stop the "automatic move to next record"
action. what happens if the user selects an existing contractor in the combo
box, and hits Enter or Tab? since the contractor is in the combo box
droplist, the NotInList event does not fire, and the user is automatically
moved to the next record in the form. that's smooth and efficient, from a
data entry standpoint. why change that behavior just because the user added
a new entry to the combo box list? (i'm not saying there's anything
necessarily wrong with your changes, just asking questions to help you
analyze the motivation for them.)

hth

 

[Guest]

As this is a "Add a New Person" form, I want the user to be able to see all
the field entries they've made before saving (or moving to the next record).
If it automatically moved to the next record, and a form with blank fields
suddenly appears I think the user may wonder "What just happened? Did my
entry get saved?" So, I have a command button in the footer that reads "Save
and Add Another" . This may seem a little "clunky", but it works.

I really appreciate the insightful comments and observations. You have been
extremely helpful.

 

[tina]

from a data entry standpoint, that's not necessarily clunky, especially if
you can tab to the command button, or activate it with key strokes (such as
Alt+A, for instance) so the user isn't forced to take his/her hand off the
keyboard to use the mouse. sounds to me like you've thought it out and made
a reasonable decision.

and you're welcome for the help.