I'm confused!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RobUCSD - 30 May 2007 22:41 GMT

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

Reply to this Message

Ken Snell (MVP) - 31 May 2007 00:04 GMT

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?

Signature

Ken Snell
<MS ACCESS MVP>

>I have a form that has a lstbox control on it and a subForm on it that is
>set
[quoted text clipped - 18 lines]
>
> Rob

Reply to this Message

RobUCSD - 31 May 2007 00:34 GMT

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

> 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
[quoted text clipped - 24 lines]
> >
> > Rob

Reply to this Message

Ken Snell (MVP) - 31 May 2007 01:16 GMT

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?

Signature

Ken Snell
<MS ACCESS MVP>

> 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
[quoted text clipped - 55 lines]
>> >
>> > Rob

Reply to this Message

RobUCSD - 31 May 2007 01:33 GMT

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 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?
[quoted text clipped - 58 lines]
> >> >
> >> > Rob

Reply to this Message

Ken Snell (MVP) - 31 May 2007 01:55 GMT

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.

Signature

Ken Snell
<MS ACCESS MVP>

> 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
[quoted text clipped - 186 lines]
>> >> >
>> >> > Rob

Reply to this Message

RobUCSD - 31 May 2007 02:35 GMT

Ken, Thanks for your patience. Could you explain or give me an example of
the following, Thanks Rob

> 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
[quoted text clipped - 201 lines]
> >> >> >
> >> >> > Rob

Reply to this Message

RobUCSD - 31 May 2007 03:11 GMT

Here's the rest of the previous "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, Thanks for your patience. Could you explain or give me an example of
> the following, Thanks Rob
[quoted text clipped - 204 lines]
> > >> >> >
> > >> >> > Rob

Reply to this Message

Ken Snell (MVP) - 01 Jun 2007 05:15 GMT

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

Signature

Ken Snell
<MS ACCESS MVP>

> Ken, Thanks for your patience. Could you explain or give me an example of
> the following, Thanks Rob
[quoted text clipped - 225 lines]
>> >> >> >
>> >> >> > Rob

Reply to this Message