"save" button not requerying as coded

[Kathy R.]

Using Access 2007

I have a form (frmNewFamily) that is opened when a NotInList event is
triggered from a subform in a different form
(frmAttendance/sfrAttendee). I have a "Save" button on the frmNewFamily
that will save the record, close the frmNewFamily and requery the combo
boxes on sfrAttendee.

I have it working just great, but I want to delete one of the combo
boxes. But as soon as I comment out the requery line, the Save button
stops functioning properly. It will save the record to the table, but
not close the form or requery the combo box. The same thing happens if
I switch the two requery lines without commenting it out.

sfrAttendee has two combo boxes - CombinedName_cbo (which I want to
remove) and LastName_cbo, along with the control AtIndID, all of which
use the AtIndId as the Control Source. The combo boxes have different
Row Sources.

I've posted the code for the save button below. Can someone tell me why
how to delete the row in my code and the combo box without it causing my
save button to stop working? And, hopefully explain to me why it is
exhibiting this behavior. I'm trying to learn from my mistakes, but
this one has me stumped. :-(

Kathy R.


Private Sub SaveRec_cmd_Click()

On Error GoTo SaveRec_cmd_err

If IsNull(Me!sfrNewIndividual.Form.IndID) Then
MsgBox "Please place your cursor in the ""First Name"" field" & Chr$(13) & _
"of the individual you wish to add to the attendance form." _
, vbOKOnly, "CHOOSE SOMEONE!"
GoTo exit_sub:
End If

'saves new record
DoCmd.RunCommand acCmdSaveRecord

'commenting out the CombinedName_cbo.Requery line or switching
'the CombinedName line with the LastName line causes
'the Save button to save the record, but not requery the subform.
Forms!frmAttendance!sfrAttendee.Form.CombinedName_cbo.Requery
Forms!frmAttendance!sfrAttendee.Form.LastName_cbo.Requery
Forms!frmAttendance!MemberList_lst.Requery

'fills in the new info on sfrAttendee
Forms!frmAttendance!sfrAttendee.Form.AtIndID =
Forms!frmNewFamily.sfrNewIndividual.Form.IndID

'closes frmNewFamily
DoCmd.Close

SaveRec_cmd_err:
Dim Msg As String
Msg = "Error Number " & Err.Number & ": " & Err.Description


exit_sub:


End Sub

 

[Kathy R.]

Hi Ruralguy. Thank you for responding. Could you clarify your question
please? I'm a bit confused. It's not necessary to do which requery,
the one on the CombinedName_cbo or the one on the LastName_cbo? I
realize that it's not necessary to requery the CombinedName_cbo,
especially since I want to delete that control. But that's where my
problem lies. As soon as I delete (or comment out) that requery, the
"save" command button stops working as stated below.

Here's the code I use to open the frmNewFamily from the NotInList event
of the LastName_cbo control on the frmAttendance/sfrAttendee

Private Sub LastName_cbo_NotInList(newdata As String, Response As Integer)
'original code from
www.blueclaw-db.com/access_notinlist_advanced_example.htm

On Error GoTo LastName_cbo_NotInList_err

Response = acDataErrContinue
Dim ans As Variant

ans = MsgBox(newdata & " is not in the database." & Chr$(13) & _
"Would you like to add this PERSON?", _
vbYesNo, "Add New Individual")

If ans = vbNo Then
Me!sfrAttendee.Form.LastName_cbo = Null
DoCmd.GoToControl "LastName_cbo"
GoTo exit_sub
End If

' add ATTENDEE to NewFamily form
DoCmd.OpenForm ("frmNewFamily")
DoCmd.GoToRecord , , acNewRec
Form_frmNewFamily.FamLastName = newdata

'Me.LastName_cbo = Null ==> this line was in the original code from
Blue Claw, NotInList sample
'it gave me the error "You tried to assign a null value to a value that
is not a Variant type"
'I replaced it with the line "DoCmd.GoToRecord,,acNewRec" above

DoCmd.GoToControl "Street"

LastName_cbo_NotInList_err:
Dim Msg As String
Msg = "Error Number " & Err.Number & ": " & Err.Description


exit_sub:

End Sub

 

[Kathy R.]

Ruralguy,

I've changed the code as you suggested. When the NotInList event fires,
the message box pops up correctly, and the "No" button works correctly.
But when I click "Yes" the debug window pops up with the message "Field
‘tblFamily.FamLastName’ cannot be a zero-length string." and the line
"Form_frmNewFamily.FamLastName = NewData" is highlighted.

If I click "end" instead of "debug" the frmNewFamily window opens with
the new last name entered in the appropriate place. However, I cannot
navigate to the subform on that form, and if I close via the X in the
top right-hand corner, I am told that "You can't save this record at
this time."

On a side note - why would you not navigate to a different control with
this event? I'm just curious about your reasoning so that I may learn
better

Also, how do I reposition the frmNewFamily window when it opens? I have
my Windows start bar at the top of my window (Microsoft puts every other
menu bar at the top of the window...why is the Start bar at the bottom?)
so the top of the frmNewFamily is tucked under my start bar and is
inaccessible.

Kathy

Hi Kathy,

This is more what I use for a NotInList event:


Private Sub LastName_cbo_NotInList(NewData As String, Response As Integer)
'original code from
www.blueclaw-db.com/access_notinlist_advanced_example.htm

On Error GoTo LastName_cbo_NotInList_err

If MsgBox(NewData & " is not in the database." & Chr$(13) & _
"Would you like to add this PERSON?", _
vbYesNo, "Add New Individual") = vbNo Then

Me.LastName_cbo.Undo
Response = acDataErrContinue
'DoCmd.GoToControl "LastName_cbo" '-- I would not attempte to go
to another control in this event!
GoTo exit_sub
Else
' add ATTENDEE to NewFamily form
'-- By using the acDialog argument, this code stops until the next form
is closed or made invisible.
DoCmd.OpenForm "frmNewFamily", , , , acFormAdd, acDialog, NewData

'-- The following line of code will Requery this ComboBox
Response = acDataErrAdded

'-- This is best done in the OnLoad even of the "frmNewFamily" form
since we now pass the value in OpenArgs
' If Not IsNull(Me.OpenArgs) Then
' Me.FamLastName = Me.OpenArgs
' End If
' Form_frmNewFamily.FamLastName = NewData

'Me.LastName_cbo = Null ==> this line was in the original code from
Blue Claw, NotInList sample
'it gave me the error "You tried to assign a null value to a value that
is not a Variant type"
'I replaced it with the line "DoCmd.GoToRecord,,acNewRec" above

'DoCmd.GoToControl "Street" '-- I would not attempte to go to
another control in this event!
End If

LastName_cbo_NotInList_err:
' Dim Msg As String
MsgBox "Error Number " & Err.Number & ": " & Err.Description

exit_sub:

End Sub

Hi Ruralguy. Thank you for responding. Could you clarify your question
please? I'm a bit confused. It's not necessary to do which requery,
the one on the CombinedName_cbo or the one on the LastName_cbo? I
realize that it's not necessary to requery the CombinedName_cbo,
especially since I want to delete that control. But that's where my
problem lies. As soon as I delete (or comment out) that requery, the
"save" command button stops working as stated below.

Here's the code I use to open the frmNewFamily from the NotInList event
of the LastName_cbo control on the frmAttendance/sfrAttendee

Private Sub LastName_cbo_NotInList(newdata As String, Response As Integer)
'original code from
www.blueclaw-db.com/access_notinlist_advanced_example.htm

On Error GoTo LastName_cbo_NotInList_err

Response = acDataErrContinue
Dim ans As Variant

ans = MsgBox(newdata & " is not in the database." & Chr$(13) & _
"Would you like to add this PERSON?", _
vbYesNo, "Add New Individual")

If ans = vbNo Then
Me!sfrAttendee.Form.LastName_cbo = Null
DoCmd.GoToControl "LastName_cbo"
GoTo exit_sub
End If

' add ATTENDEE to NewFamily form
DoCmd.OpenForm ("frmNewFamily")
DoCmd.GoToRecord , , acNewRec
Form_frmNewFamily.FamLastName = newdata

'Me.LastName_cbo = Null ==> this line was in the original code from
Blue Claw, NotInList sample
'it gave me the error "You tried to assign a null value to a value that
is not a Variant type"
'I replaced it with the line "DoCmd.GoToRecord,,acNewRec" above

DoCmd.GoToControl "Street"

LastName_cbo_NotInList_err:
Dim Msg As String
Msg = "Error Number " & Err.Number & ": " & Err.Description

exit_sub:

End Sub

It is not necessary to do that Requery. Show us the code you are using to
open the 2nd form.

[quoted text clipped - 61 lines]

End Sub

 

[Kathy R.]

Okay, I removed that line, I see where I made that mistake. By the way
the record sources for both main forms are queries and both subforms are
SQL statements, none of them are based directly on the tables.

Unfortunately, I'm on to a new error, but I think I see what the problem
is. Once I add a new FamLastName to the frmNewFamily, and click close,
I get an error window with "Error Number 0:" Is this because I
shouldn't use

On Error GoTo LastName_cbo_NotInList_err
and
LastName_cbo_NotInList_err:
MsgBox "Error Number " & Err.Number & ": " & Err.Description

in conjunction with acDataErrContinue or acDataErrAdded?

I'm sorry if I'm being particularly dense (I'm certainly feeling that
way at the moment!), but Error 0 isn't giving me anything to go on. I
really appreciate your help and patience!

This line:
"Form_frmNewFamily.FamLastName = NewData" is highlighted
..s/b commented out not generate an error.

You need to base your forms on queries of tables rather than directly to
tables. That will avoid the locking problem you are getting.

Ruralguy,

I've changed the code as you suggested. When the NotInList event fires,
the message box pops up correctly, and the "No" button works correctly.
But when I click "Yes" the debug window pops up with the message "Field
‘tblFamily.FamLastName’ cannot be a zero-length string." and the line
"Form_frmNewFamily.FamLastName = NewData" is highlighted.

If I click "end" instead of "debug" the frmNewFamily window opens with
the new last name entered in the appropriate place. However, I cannot
navigate to the subform on that form, and if I close via the X in the
top right-hand corner, I am told that "You can't save this record at
this time."

On a side note - why would you not navigate to a different control with
this event? I'm just curious about your reasoning so that I may learn
better

Also, how do I reposition the frmNewFamily window when it opens? I have
my Windows start bar at the top of my window (Microsoft puts every other
menu bar at the top of the window...why is the Start bar at the bottom?)
so the top of the frmNewFamily is tucked under my start bar and is
inaccessible.

Kathy

Hi Kathy,

[quoted text clipped - 104 lines]

[quoted text clipped - 61 lines]
End Sub