Combobox Question

[Edward]

I posted this before, but apparently the original post may have confused
some people. My apologies for that.

I have a form called Vendors that is bound to tbl_Vendors. tbl_Vendors is
normalized in that Cities, States, and
Postal Codes all have their own separate tables with a one to many
relationship--each has cascade update to prevent having to requery when
changes are made to an existing record.

My form Vendors has three combo boxes that are tied to the foreign keys in
tbl_Vendors, but the underlying recordset for the combo box display is off
of the parent. Meaning that the display shows the city names from
tbl_cities (etc) even though the actual field is the foreign key in
tbl_Vendors.

On the NOT IN LIST event, I want to trigger a response that forces the user
to say Yes/No about adding a new city/state/zip. On selecting Yes, it will
append the appropriate table (city, state zip) and hopefully move to the
next field.

My problem is not in the response or the execution of the append. It is the
fact that at the moment that it updates tbl_City (for example), the record
DOES append, but the underlying record in frmVendors moves to the next
record rather than to the next field. I have successfully used this same
scenario and code for unbound forms that were for data entry, and I know
they work. My troubleshooting points to the fact that the problem lies with
the fact that frmVendors is bound.

My questions are these:

1) Am I correct in the troubleshooting?

2) Is there a way around this? If so what/how?

3) If not, what are some other suggestions, considering that my data is
small right now, and I cannot guarantee that the move is always to the next
record.

4) Is another way around this possibly using the bookmark/recordest clone?
If so, I have not been able to get that to work successfully, so any help
here would be appreciated.

Many thanks in advance.

Edward

 

[Dirk Goldgar]

I posted this before, but apparently the original post may have confused
some people. My apologies for that.

I have a form called Vendors that is bound to tbl_Vendors. tbl_Vendors is
normalized in that Cities, States, and
Postal Codes all have their own separate tables with a one to many
relationship--each has cascade update to prevent having to requery when
changes are made to an existing record.

My form Vendors has three combo boxes that are tied to the foreign keys in
tbl_Vendors, but the underlying recordset for the combo box display is off
of the parent. Meaning that the display shows the city names from
tbl_cities (etc) even though the actual field is the foreign key in
tbl_Vendors.

On the NOT IN LIST event, I want to trigger a response that forces the
user to say Yes/No about adding a new city/state/zip. On selecting Yes, it
will append the appropriate table (city, state zip) and hopefully move to
the next field.

My problem is not in the response or the execution of the append. It is
the fact that at the moment that it updates tbl_City (for example), the
record DOES append, but the underlying record in frmVendors moves to the
next record rather than to the next field. I have successfully used this
same scenario and code for unbound forms that were for data entry, and I
know they work. My troubleshooting points to the fact that the problem
lies with the fact that frmVendors is bound.

My questions are these:

1) Am I correct in the troubleshooting?

I don't think so.

2) Is there a way around this? If so what/how?

Please post the code that you are executing in the NotInList event. I think
you must inadvertently be doing something to cause your form to move to the
next record.

 

[Edward]

Dirk, this is the same code that I use for the bound and unbound forms.
I've also used the .Edit of the recordset, and just the adErrDataAdd. These
all move to the next record.

NOTE: the code is commented to allow for changes later.

*****BEGIN CODE*****

'' Allows additions to cities if not found
'Private Sub City_ID_NotInList(strNewData As String, intResponse As Integer)
'On Error GoTo Err_NotInList
'
' Dim db As Database
' Dim intStyle As Integer
' Set db = CurrentDb
'
' intStyle = vbYesNo + vbDefaultButton2 + vbQuestion
'
' DoCmd.Beep
' intResponse = acDataErrContinue
' intResponse = MsgBox("The city " & Chr(34) & strNewData & Chr(34) & "
is not in the list." & Chr(13) & "Do you wish to add it?", intStyle, "Add
City?")
'
' If intResponse = vbNo Then
' Me.Undo
' Me.City_ID = Null
' Else
' db.Execute "Insert into tbl_Cities(City) Values (" & Chr(34) &
strNewData & Chr(34) & ")"
' intResponse = acDataErrAdded
' End If
'
'Exit_NotInList:
' Exit Sub
'Err_NotInList:
' mdl_Specialized_Routines.Error_Handler Err.Number, Me.Name
' Resume Exit_NotInList
'End Sub

*****END CODE*****

 

[Dirk Goldgar]

Dirk, this is the same code that I use for the bound and unbound forms.
I've also used the .Edit of the recordset, and just the adErrDataAdd.
These all move to the next record.

NOTE: the code is commented to allow for changes later.

*****BEGIN CODE*****

'' Allows additions to cities if not found
'Private Sub City_ID_NotInList(strNewData As String, intResponse As
Integer)
'On Error GoTo Err_NotInList
'
' Dim db As Database
' Dim intStyle As Integer
' Set db = CurrentDb
'
' intStyle = vbYesNo + vbDefaultButton2 + vbQuestion
'
' DoCmd.Beep
' intResponse = acDataErrContinue
' intResponse = MsgBox("The city " & Chr(34) & strNewData & Chr(34) &
" is not in the list." & Chr(13) & "Do you wish to add it?", intStyle,
"Add City?")
'
' If intResponse = vbNo Then
' Me.Undo
' Me.City_ID = Null
' Else
' db.Execute "Insert into tbl_Cities(City) Values (" & Chr(34) &
strNewData & Chr(34) & ")"
' intResponse = acDataErrAdded
' End If
'
'Exit_NotInList:
' Exit Sub
'Err_NotInList:
' mdl_Specialized_Routines.Error_Handler Err.Number, Me.Name
' Resume Exit_NotInList
'End Sub

*****END CODE*****

I only see one oddity there. You are overloading the argument intResponse
to be used also for the user's reply to the MsgBox. If the user replies No,
then intResponse will be set to the value of vbNo, which has a value of 7,
and that is the value it will have when the procedure exits. 7 is not any
of the values of acDataErrAdded, acDataErrContinue, or acDataErrDisplay, so
I'm not sure what Access thinks it's supposed to do when it gets it. If I
were you, I would use a separate variable for the return code from the
MsgBox function.

However, you said that the form moves to a new record when the user replies
Yes to the combo box, right? In which case, intResponse is set to
acDataErrAdded, and all should be well. That being so, I think we need to
look elsewhere for the problem.

Question: is this control last in the tab order on the form? If it is, and
the form's Cycle property is set to "All Records", then tabbing out of
City_ID will automatically cause the form to move to the next record. If
that's what's going on, all you have to do is set the form's Cycle property
(on the Other tab of the form's property sheet in design view) to "Current
Record", and that will restrict navigation to the current form unless you
explicitly tell it to go to a new record.

If that's not the problem, then I suspect there is some other code operating
on the form, possibly in the AfterUpdate or Click event of the City_ID combo
box, that is causing the form to change records. So if it's not the Cycle
property, please post (if possible) all code behind the form.

 

[Edward]

Dirk,

To respond to the statement about vbNo, it seems to be escaping correctly,
even on this form.

The cities combo box is not the last tab order on the form. It's somewhere
in the middle along with the states and postal code. The cycle is set to
current record. The reaction to the form is identical for city, states, or
zip. I am beginning to wonder whether this particular module is corrupted
and needs to be rebuilt from scratch. Or possibly a corrupted link between
the tables? I know it's rare, but it does happen sometimes. At any rate,
I'm including the entire code behind the scenes, but there is little more
than what I posted before. Nothing on the after update events on any of the
combo. FYI, I've changed the recordset to inconsistent updates to see if
there is any difference. I'm not expecting any, though.

Thanks again for your help.

Edward

*****BEGIN CODE*****
Option Compare Database
Option Explicit

Private Sub cmdMainScreen_Click()
DoCmd.Close acForm, "frm_Vendor_Maintenance"
DoCmd.OpenForm "frm_Maintenance"

Exit_Continue:
Exit Sub
End Sub

Private Sub Form_Load()

Dim strMsg As String, strTitle As String
Dim intStyle As Integer

strMsg = "You cannot add new cities, states, or postal codes from this
screen." & Chr(13) & Chr(13) & _
"Please use the Maintenance Screen to add these BEFORE
making new vendor entries on this screen."
strTitle = "Reminder"
intStyle = vbExclamation + vbOKOnly

DoCmd.Beep
MsgBox strMsg, intStyle, strTitle

End Sub

'' Allows additions to cities if not found
'Private Sub City_ID_NotInList(strNewData As String, intResponse As Integer)
'On Error GoTo Err_NotInList
'
' Dim db As Database
' Dim intStyle As Integer
' Set db = CurrentDb
'
' mlngRecord = Me.Vendor_ID
' intStyle = vbYesNo + vbDefaultButton2 + vbQuestion
'
' DoCmd.Beep
' intResponse = acDataErrContinue
' intResponse = MsgBox("The city " & Chr(34) & strNewData & Chr(34) & "
is not in the list." & Chr(13) & "Do you wish to add it?", intStyle, "Add
City?")
'
' If intResponse = vbNo Then
' Me.Undo
' Me.City_ID = Null
' Else
' db.Execute "Insert into tbl_Cities(City) Values (" & Chr(34) &
strNewData & Chr(34) & ")"
' intResponse = acDataErrAdded
' End If
'
'Exit_NotInList:
' Exit Sub
'Err_NotInList:
' mdl_Specialized_Routines.Error_Handler Err.Number, Me.Name
' Resume Exit_NotInList
'End Sub
*****END CODE*****

 

[Dirk Goldgar]

Dirk,

To respond to the statement about vbNo, it seems to be escaping correctly,
even on this form.

The cities combo box is not the last tab order on the form. It's
somewhere in the middle along with the states and postal code. The cycle
is set to current record. The reaction to the form is identical for city,
states, or zip. I am beginning to wonder whether this particular module
is corrupted and needs to be rebuilt from scratch. Or possibly a
corrupted link between the tables? I know it's rare, but it does happen
sometimes. At any rate, I'm including the entire code behind the scenes,
but there is little more than what I posted before. Nothing on the after
update events on any of the combo. FYI, I've changed the recordset to
inconsistent updates to see if there is any difference. I'm not expecting
any, though.

I don't see what's going on. Would you be willing to send me a copy of your
database to look at? If so, please make it a cut-down copy, containing only
the elements necessary to demonstrate the problem, compacted and then zipped
to less than 1MB in size (preferably much smaller). I'll have a look at it,
time permitting. You can send it to the address derived by removing NO SPAM
and ".invalid" from the reply address of this message. If that address
isn't visible to you, you can get my address from my web site, which is
listed in my sig. Do *not* post my real address in the newsgroup -- I don't
want to be buried in spam and viruses.

 

[Edward]

It's on its way to you. It's coming from my private account.

BTW: I would never post confidential info here. I'm the CIO and lead
programmer of my company, so I understand and respect the need for
confidentiality. I don't want SPAM or viruses either!

Thanks for the help.

Edward