|
|
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
|
|
"Edward" <edward[ at ]nospam.com> wrote in message
news:%23buNXZyTJHA.5024[ at ]TK2MSFTNGP03.phx.gbl...
[Quoted Text] >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.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
|
|
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" <dg[ at ]NOdataSPAMgnostics.com.invalid> wrote in message
news:eg6KxKzTJHA.5344[ at ]TK2MSFTNGP06.phx.gbl...
[Quoted Text] > "Edward" <edward[ at ]nospam.com> wrote in message
> news:%23buNXZyTJHA.5024[ at ]TK2MSFTNGP03.phx.gbl...
>>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.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
|
|
"Edward" <edward[ at ]nospam.com> wrote in message
news:%23GWqcszTJHA.5920[ at ]TK2MSFTNGP06.phx.gbl...
[Quoted Text] > 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.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
|
|
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" <dg[ at ]NOdataSPAMgnostics.com.invalid> wrote in message
news:eJpxi5zTJHA.1164[ at ]TK2MSFTNGP03.phx.gbl...
[Quoted Text] > "Edward" <edward[ at ]nospam.com> wrote in message
> news:%23GWqcszTJHA.5920[ at ]TK2MSFTNGP06.phx.gbl...
>> 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.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
|
|
"Edward" <edward[ at ]nospam.com> wrote in message
news:O6OLmj2TJHA.1184[ at ]TK2MSFTNGP03.phx.gbl...
[Quoted Text] > 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.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
|
|
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
"Dirk Goldgar" <dg[ at ]NOdataSPAMgnostics.com.invalid> wrote in message
news:uCLXAR9TJHA.5408[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] > "Edward" <edward[ at ]nospam.com> wrote in message
> news:O6OLmj2TJHA.1184[ at ]TK2MSFTNGP03.phx.gbl...
>> 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.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
|
|
|