I'm confused!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that has a lstbox control on it and a subForm on it that is set
up to look like a data sheet. In the subForm I have a field, bound to a
table, that has the default property set to =CurrentUser(). In that same
record is the field fldRNnotes.

When the list box is doubled clicked it requeries the sForms record source,
thereby adding a record to the form. The problem is is that when the field in
the form is populated with a canned nursing note, the CurrentUser is not
added to the record.

If I enter in a note myself (by typing in the field of a new record) then
the form is properly updated with the CurrentUser id.

So this baffles me. Why does it work properly when a note is hand entered
and not when a canned note is entered via the listbox?

I really need this to work so your help will be very much appreciated.

Rob
 
The Default Value will be put into the textbox (and later saved to the
table) when the record becomes "dirty" -- which happens when you manually
type into that new record. But, depending upon how you're adding the "canned
nursing note", the record may not become "dirty". Can you tell us more
details about the code that runs from the listbox's event?
 
The following is in the source listbox's dblClick event (multi select=no).
The forms record source is the same as the target list (a sfrm that looks
like a dataSheet). As you can see from the code, on the sources dblClick
event it requeries the targets record source and adds the record, only
without the currentUser name.

I hope this is clear. Thanks for your help.
**************************************************
Private Sub lstRNnotesLU_DblClick(Cancel As Integer)

On Error GoTo lstRNnotesLU_DblClick_Error

Me.Refresh

On Error GoTo 0
Exit Sub

lstRNnotesLU_DblClick_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
lstRNnotesLU_DblClick of VBA Document Form_frmRNnotes"

End Sub
 
I don't see a code step in your posted code that would add a new record to
the subform's data? I see the Refresh step, but that's it?
 
Here's the code for the entire module. the Main form is frmRNnotes, the
source list box is lstRNnotes, and the target form is fsubRNnotes. Funky I
know.

Option Compare Database
Option Explicit
**********************************************************
Private Sub cmdRNnotesEdit_Click()
On Error GoTo cmdRNnotesEdit_Click_Error

Me.Form.AllowEdits = True
Me.lstRNnotesLU.Locked = False
Me.fsubRNnotes.Locked = False
Me.fsubRNnotes.Form.AllowDeletions = True


On Error GoTo 0
Exit Sub

cmdRNnotesEdit_Click_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
cmdRNnotesEdit_Click of VBA Document Form_frmRNnotes"
End Sub
**********************************************
Private Sub Form_AfterInsert()
On Error GoTo Form_AfterInsert_Error

Me.Requery

On Error GoTo 0
Exit Sub
Form_AfterInsert_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
Form_AfterInsert of VBA Document Form_frmRNnotes"
End Sub
************************************************************

Private Sub Form_Current()

On Error GoTo Form_Current_Error

DoCmd.GoToRecord , , acNewRec

On Error GoTo 0
Exit Sub

Form_Current_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
Form_Current of VBA Document Form_frmRNnotes"
End Sub
************************************************************

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Form_Open_Error


Me.Form.AllowEdits = False
Me.lstRNnotesLU.Locked = True
Me.fsubRNnotes.Locked = True
Me.fsubRNnotes.Form.AllowDeletions = False
On Error GoTo 0
Exit Sub

Form_Open_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
Form_Open of VBA Document Form_frmRNnotes"
End Sub
************************************************************

Private Sub lstRNnotesLU_DblClick(Cancel As Integer)

On Error GoTo lstRNnotesLU_DblClick_Error


Me.Refresh

On Error GoTo 0
Exit Sub

lstRNnotesLU_DblClick_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
lstRNnotesLU_DblClick of VBA Document Form_frmRNnotes"

End Sub
************************************************************


Private Sub cmdRNnotesClose_Click()
On Error GoTo Err_cmdRNnotesClose_Click


DoCmd.Close

Exit_cmdRNnotesClose_Click:
Exit Sub

Err_cmdRNnotesClose_Click:
MsgBox Err.Description
Resume Exit_cmdRNnotesClose_Click

End Sub
Private Sub cmdRNnotesRptOpen_Click()
On Error GoTo Err_cmdRNnotesRptOpen_Click

Dim stDocName As String

stDocName = "rptCaseReport"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdRNnotesRptOpen_Click:
Exit Sub

Err_cmdRNnotesRptOpen_Click:
MsgBox Err.Description
Resume Exit_cmdRNnotesRptOpen_Click

End Sub
************************************************************
 
I personally think it's very dangerous to use a form's Current event to move
the form to a new record, which then triggers the Current event again, which
moves the form to a new record, which triggers the Current event again,
which moves the form to a new record, and so on. (This may be part of why
you're not seeing the record stored?) Plus, any time you make a record
current, the form will immediately move to a new record, which will prevent
you from editing data in that one record.

I'd be inclined to use the listbox's event procedure to move to the new
record, not the form's current event. And, if you want the record to
"stick", you might want to actually write a value into a bound control in
that record, which will cause the default value to be put into that
CurrentUser control. Or better, use the code to write the result of
CurrentUser() function into the textbox and forgo the use of the textbox's
Default Value property entirely.
--

Ken Snell
<MS ACCESS MVP>
 
Ken, Thanks for your patience. Could you explain or give me an example of
the following, Thanks Rob
 
I've made small change to your listbox's DoubleClick event procedure:

Private Sub lstRNnotesLU_DblClick(Cancel As Integer)

On Error GoTo lstRNnotesLU_DblClick_Error


Me.Refresh
' This adds new record
Me.Recordset.AddNew
' This dirties the record by writing the CurrentUser value into the
appropriate field
Me.NameOfUserField.Value = CurrentUser()

On Error GoTo 0
Exit Sub

lstRNnotesLU_DblClick_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
lstRNnotesLU_DblClick of VBA Document Form_frmRNnotes"

End Sub


--

Ken Snell
<MS ACCESS MVP>
 
Back
Top