Sub Form Update

R

rm

Well I am still having fun with this problem. Thanks to " Marsh MVP
[MS Access] " for your help.

I am trying to be a little more specific with names as I describe the
problem again.

I have a main form "frmDOMain", an imbeded form "frmDOstatus" (the
control is named "sfrmDOstatus"). When the user double clicks on a
record in "frmDOstatus" another form - "frmMngStatus" opens. The
purpose of "frmMngStatus" is to allow the user to add a child record
to the parent record presented in "frmDOMain". When the user selects
"Save" from "frmMngStatus" then a record is written to a table (via
ADO) that is used to supply data for "frmDOstatus" (the sub form on
"frmDOMain").

The goal is that after the user selects "save" from "frmMngStatus"
that the added record shows up in the subform ("frmDOstatus") on the
main form ("frmDOMain").

If all that didn't make sense the flow is - Open main form with
embeded form/child record. Double click on embeded form - pop-up form
is displayed. New child record/Information is added in the pop-up
form. Pop-up form is closed. What is expected next is new child record
is displayed in the embeded form.

The problem is that the child record is displayed *for the first
several iterations of the process only*. Then at some point the child
record no longer shows up in the subform "frmDOstatus" after the user
selects "save" from ""frmMngStatus".

When the record stops displaying as expected then I can execute a go
next, move prev routine (sometimes as many as 3 x for it to work) then
the child cecord is displayed. In addition to the fact that the same
exact, unchanged code produces inconsistent results the frustration is
that the child record is written to the table every single time.

Here is the code from "frmMngStatus":

Private Sub cmdUpdate_Click()

Dim lngStatusID As Long
Dim strNotes As String
Dim dteDate As Date

Dim objCon As ADODB.Connection
Dim objCmd As ADODB.Command
Dim objPrm As ADODB.Parameter

Set objCon = New ADODB.Connection
Set objCmd = New ADODB.Command
Set objPrm = New ADODB.Parameter

If IsNull(cboStatus.Value) Then

MsgBox "Please Select a Status Message", vbOKOnly, "Error"
Me.cboStatus.SetFocus
Else

lngStatusID = Me.cboStatus.Value
dteDate = CDate(Format(Now(), "dd mmm yyyy"))

If Not IsNull(txtNotes.Value) Then
strNotes = Me.txtNotes.Value
Else
strNotes = ""
End If

objCon.Open (CurrentProject.Connection)

'Setup Command Object for DB Call
With objCmd
.CommandText = "sp_insert_do_status"
.CommandType = adCmdStoredProc
.ActiveConnection = objCon

Set objPrm = .CreateParameter("do_id", adInteger,
adParamInput, , g_lngDOID)
.Parameters.Append objPrm

Set objPrm = .CreateParameter("s_id", adInteger,
adParamInput, , lngStatusID)
.Parameters.Append objPrm

Set objPrm = .CreateParameter("dte", adDate,
adParamInput, , dteDate)
.Parameters.Append objPrm

Set objPrm = .CreateParameter("StatusNotes", adVarChar,
adParamInput, 255, strNotes)
.Parameters.Append objPrm

.Execute '< Works every single time!

End With

objCon.Close

Set objCon = Nothing
Set objCmd = Nothing
Set objPrm = Nothing

Forms!frmDOMain!sfrmDOStatus.Form.Requery 'doesn't work
DoEvents
Forms!frmDOMain!sfrmDOStatus.Form.Refresh 'no good
DoEvents
Forms!frmDOMain!sfrmDOStatus.Form.Repaint 'doesn't do it
DoEvents

'tried...
'Dim frm as FORM_frmDOStatus
'Set frm = New FORM_frmDOStatus
'frm.Requery
'I think this is way off

DoCmd.Close acForm, "frmMngStatus"

End If

This project is turning out to be deadly. I am in the Army in Iraq.
When I walk outside people lob mortars at my head. When I walk inside
the COL is going to chew my head off because he wants his reports - he
may just put me out on the street. Microsoft is literally going to put
me in harms way! (OK it is only a joke [kind of - sort of] - I am in
the Army and am in Iraq though).

Can somebody help a soldier out!?
 
R

rm

When the code below is executed the LOADED property for "frmDOMain"
and "frmDOstatus" = FALSE. The two forms are open. What am I missing?


Dim int1 As Integer

'Print the number of forms in the project
Debug.Print CurrentProject.AllForms.Count
Debug.Print

'Enumerate each form in the project
For int1 = 0 To CurrentProject.AllForms.Count - 1
Debug.Print CurrentProject.AllForms.Item(int1).Name
Debug.Print CurrentProject.AllForms.Item(int1).IsLoaded
Debug.Print
Next int1


Well I am still having fun with this problem. Thanks to " Marsh MVP
[MS Access] " for your help.

I am trying to be a little more specific with names as I describe the
problem again.

I have a main form "frmDOMain", an imbeded form "frmDOstatus" (the
control is named "sfrmDOstatus"). When the user double clicks on a
record in "frmDOstatus" another form - "frmMngStatus" opens. The
purpose of "frmMngStatus" is to allow the user to add a child record
to the parent record presented in "frmDOMain".  When the user selects
"Save" from "frmMngStatus" then a record is written to a table (via
ADO) that is used to supply data for "frmDOstatus" (the sub form on
"frmDOMain").

The goal is that after the user selects "save" from "frmMngStatus"
that the added record shows up in the subform ("frmDOstatus") on the
main form ("frmDOMain").

If all that didn't make sense the flow is - Open main form with
embeded form/child record. Double click on embeded form - pop-up form
is displayed. New child record/Information is added in the pop-up
form. Pop-up form is closed. What is expected next is new child record
is displayed in the embeded form.

The problem is that the child record is displayed *for the first
several iterations of the process only*. Then at some point the child
record no longer shows up in the subform "frmDOstatus" after the user
selects "save" from ""frmMngStatus".

When the record stops displaying as expected then I can execute a go
next, move prev routine (sometimes as many as 3 x for it to work) then
the child cecord is displayed. In addition to the fact that the same
exact, unchanged code produces inconsistent results the frustration is
that the child record is written to the table every single time.

Here is the code from "frmMngStatus":

Private Sub cmdUpdate_Click()

    Dim lngStatusID As Long
    Dim strNotes As String
    Dim dteDate As Date

    Dim objCon As ADODB.Connection
    Dim objCmd As ADODB.Command
    Dim objPrm As ADODB.Parameter

    Set objCon = New ADODB.Connection
    Set objCmd = New ADODB.Command
    Set objPrm = New ADODB.Parameter

    If IsNull(cboStatus.Value) Then

        MsgBox "Please Select a Status Message", vbOKOnly, "Error"
        Me.cboStatus.SetFocus
    Else

        lngStatusID = Me.cboStatus.Value
        dteDate = CDate(Format(Now(), "dd mmm yyyy"))

        If Not IsNull(txtNotes.Value) Then
            strNotes = Me.txtNotes.Value
        Else
            strNotes = ""
        End If

        objCon.Open (CurrentProject.Connection)

        'Setup Command Object for DB Call
        With objCmd
            .CommandText = "sp_insert_do_status"
            .CommandType = adCmdStoredProc
            .ActiveConnection = objCon

            Set objPrm = .CreateParameter("do_id", adInteger,
adParamInput, , g_lngDOID)
            .Parameters.Append objPrm

            Set objPrm = .CreateParameter("s_id", adInteger,
adParamInput, , lngStatusID)
            .Parameters.Append objPrm

            Set objPrm = .CreateParameter("dte", adDate,
adParamInput, , dteDate)
            .Parameters.Append objPrm

            Set objPrm = .CreateParameter("StatusNotes", adVarChar,
adParamInput, 255, strNotes)
            .Parameters.Append objPrm

            .Execute '< Works every single time!

        End With

        objCon.Close

        Set objCon = Nothing
        Set objCmd = Nothing
        Set objPrm = Nothing

        Forms!frmDOMain!sfrmDOStatus.Form.Requery 'doesn't work
        DoEvents
        Forms!frmDOMain!sfrmDOStatus.Form.Refresh 'no good
        DoEvents
        Forms!frmDOMain!sfrmDOStatus.Form.Repaint 'doesn't do it
        DoEvents

       'tried...
       'Dim frm as FORM_frmDOStatus
       'Set frm = New FORM_frmDOStatus
       'frm.Requery
       'I think this is way off

        DoCmd.Close acForm, "frmMngStatus"

    End If

This project is turning out to be deadly. I am in the Army in Iraq.
When I walk outside people lob mortars at my head. When I walk inside
the COL is going to chew my head off because he wants his reports - he
may just put me out on the street. Microsoft is literally going to put
me in harms way! (OK it is only a joke [kind of - sort of] - I am in
the Army and am in Iraq though).

Can somebody help a soldier out!?
Click to expand...
 
R

rm

Here is a really bad, frustrating, stupid solution that makes the
interface look silly but if freaking works

Thank you M$!

Private Sub ThisSucks()

Dim frm1 As Form
Dim ctl1 As Control
Dim bolHaveHook As Boolean
Dim lngRecCount As Long
Dim lngRecsOnForm As Long

'Get number of records from table

Dim objCon As ADODB.Connection
Dim objRs As ADODB.Recordset

Set objCon = New ADODB.Connection
Set objRs = New ADODB.Recordset

Screen.MousePointer = 11

objCon.Open (CurrentProject.Connection)

strStatus = txtNewStatusMessage.Value

With objRs
.Source = "SELECT MAX(ID) AS [MAXID] FROM status;"
.Open "SELECT COUNT(id) AS [CNT] FROM DO_Status WHERE DOID = "
& g_lngDOID & ";", objCon, adOpenForwardOnly, adLockBatchOptimistic
If Not IsNull(.Fields("CNT").Value) Then
lngRecCount = CLng(.Fields("CNT").Value)
End If
.Close
End With

objCon.Close

Set objCon = Nothing
Set objRs = Nothing

bolHaveHook = False
bolRefreshed = False

'Get a hook into the form in the open forms collection

For Each frm1 In Forms
If bolHaveHook Then
Exit For
End If
For Each ctl1 In frm1.Controls
If ctl1.Name = "sfrmDOStatus" Then
bolHaveHook = True
Exit For
End If
Next
Next

'Get number of records on the form
lngRecsOnForm = ctl1.Form.Recordset.RecordCount

'Do a stinking loop until the records in the table and the records
on the screen match
' Makes the interface look really silly - but Access is too
unstable
Do Until lngRecsOnForm = lngRecCount
ctl1.Requery
DoEvents
[Forms]![frmDOMain]![sfrmDOStatus].Form.Refresh 'no good
DoEvents
lngRecsOnForm = ctl1.Form.Recordset.RecordCount
Loop

Screen.MousePointer = 1

End Sub




When the code below is executed the LOADED property for "frmDOMain"
and "frmDOstatus"  = FALSE. The two forms are open. What am I missing?

   Dim int1 As Integer

    'Print the number of forms in the project
    Debug.Print CurrentProject.AllForms.Count
    Debug.Print

    'Enumerate each form in the project
    For int1 = 0 To CurrentProject.AllForms.Count - 1
        Debug.Print CurrentProject.AllForms.Item(int1).Name
        Debug.Print CurrentProject.AllForms.Item(int1).IsLoaded
        Debug.Print
    Next int1

Well I am still having fun with this problem. Thanks to " Marsh MVP
[MS Access] " for your help.
Click to expand...
I am trying to be a little more specific with names as I describe the
problem again.
Click to expand...
I have a main form "frmDOMain", an imbeded form "frmDOstatus" (the
control is named "sfrmDOstatus"). When the user double clicks on a
record in "frmDOstatus" another form - "frmMngStatus" opens. The
purpose of "frmMngStatus" is to allow the user to add a child record
to the parent record presented in "frmDOMain".  When the user selects
"Save" from "frmMngStatus" then a record is written to a table (via
ADO) that is used to supply data for "frmDOstatus" (the sub form on
"frmDOMain").
Click to expand...
The goal is that after the user selects "save" from "frmMngStatus"
that the added record shows up in the subform ("frmDOstatus") on the
main form ("frmDOMain").
Click to expand...
If all that didn't make sense the flow is - Open main form with
embeded form/child record. Double click on embeded form - pop-up form
is displayed. New child record/Information is added in the pop-up
form. Pop-up form is closed. What is expected next is new child record
is displayed in the embeded form.
Click to expand...
The problem is that the child record is displayed *for the first
several iterations of the process only*. Then at some point the child
record no longer shows up in the subform "frmDOstatus" after the user
selects "save" from ""frmMngStatus".
Click to expand...
When the record stops displaying as expected then I can execute a go
next, move prev routine (sometimes as many as 3 x for it to work) then
the child cecord is displayed. In addition to the fact that the same
exact, unchanged code produces inconsistent results the frustration is
that the child record is written to the table every single time.
Click to expand...
Here is the code from "frmMngStatus":
Click to expand...
Private Sub cmdUpdate_Click()
Click to expand...
    Dim lngStatusID As Long
    Dim strNotes As String
    Dim dteDate As Date
Click to expand...
    Dim objCon As ADODB.Connection
    Dim objCmd As ADODB.Command
    Dim objPrm As ADODB.Parameter
Click to expand...
    Set objCon = New ADODB.Connection
    Set objCmd = New ADODB.Command
    Set objPrm = New ADODB.Parameter
Click to expand...
    If IsNull(cboStatus.Value) Then
Click to expand...
        MsgBox "Please Select a Status Message", vbOKOnly, "Error"
        Me.cboStatus.SetFocus
    Else
Click to expand...
        lngStatusID = Me.cboStatus.Value
        dteDate = CDate(Format(Now(), "dd mmm yyyy"))
Click to expand...
        If Not IsNull(txtNotes.Value) Then
            strNotes = Me.txtNotes.Value
        Else
            strNotes = ""
        End If
Click to expand...
        objCon.Open (CurrentProject.Connection)
Click to expand...
        'Setup Command Object for DB Call
        With objCmd
            .CommandText = "sp_insert_do_status"
            .CommandType = adCmdStoredProc
            .ActiveConnection = objCon
Click to expand...
            Set objPrm = .CreateParameter("do_id", adInteger,
adParamInput, , g_lngDOID)
            .Parameters.Append objPrm
Click to expand...
            Set objPrm = .CreateParameter("s_id", adInteger,
adParamInput, , lngStatusID)
            .Parameters.Append objPrm
Click to expand...
            Set objPrm = .CreateParameter("dte", adDate,
adParamInput, , dteDate)
            .Parameters.Append objPrm
Click to expand...
            Set objPrm = .CreateParameter("StatusNotes", adVarChar,
adParamInput, 255, strNotes)
            .Parameters.Append objPrm
Click to expand...
            .Execute '< Works every single time!
Click to expand...
        End With
Click to expand...
        objCon.Close
Click to expand...
        Set objCon = Nothing
        Set objCmd = Nothing
        Set objPrm = Nothing
Click to expand...
        Forms!frmDOMain!sfrmDOStatus.Form.Requery 'doesn't work
        DoEvents
        Forms!frmDOMain!sfrmDOStatus.Form.Refresh 'no good
        DoEvents
        Forms!frmDOMain!sfrmDOStatus.Form.Repaint 'doesn't do it
        DoEvents
Click to expand...
       'tried...
       'Dim frm as FORM_frmDOStatus
       'Set frm = New FORM_frmDOStatus
       'frm.Requery
       'I think this is way off
Click to expand...
        DoCmd.Close acForm, "frmMngStatus"
Click to expand...
    End If
Click to expand...
This project is turning out to be deadly. I am in the Army in Iraq.
When I walk outside people lob mortars at my head. When I walk inside
the COL is going to chew my head off because he wants his reports - he
may just put me out on the street. Microsoft is literally going to put
me in harms way! (OK it is only a joke [kind of - sort of] - I am in
the Army and am in Iraq though).
Click to expand...
Can somebody help a soldier out!?- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
R

rm

Crap. This didn't end up working either. I changed a record. The code
started iterating. I left at the end of the day. I came back 12 hours
later. Still running in the freaking loop. Any chance that somebody
from Microsoft will chime in and tell me how to get around this Access
bug?

Here is a really bad, frustrating, stupid solution that makes the
interface look silly but if freaking works

Thank you M$!

Private Sub ThisSucks()

    Dim frm1 As Form
    Dim ctl1 As Control
    Dim bolHaveHook As Boolean
    Dim lngRecCount As Long
    Dim lngRecsOnForm As Long

    'Get number of records from table

    Dim objCon As ADODB.Connection
    Dim objRs As ADODB.Recordset

    Set objCon = New ADODB.Connection
    Set objRs = New ADODB.Recordset

    Screen.MousePointer = 11

    objCon.Open (CurrentProject.Connection)

    strStatus = txtNewStatusMessage.Value

    With objRs
        .Source = "SELECT MAX(ID) AS [MAXID] FROM status;"
        .Open "SELECT COUNT(id) AS [CNT] FROM DO_Status WHERE DOID= "
& g_lngDOID & ";", objCon, adOpenForwardOnly, adLockBatchOptimistic
        If Not IsNull(.Fields("CNT").Value) Then
            lngRecCount = CLng(.Fields("CNT").Value)
        End If
        .Close
    End With

    objCon.Close

    Set objCon = Nothing
    Set objRs = Nothing

    bolHaveHook = False
    bolRefreshed = False

    'Get a hook into the form in the open forms collection

    For Each frm1 In Forms
        If bolHaveHook Then
            Exit For
        End If
        For Each ctl1 In frm1.Controls
            If ctl1.Name = "sfrmDOStatus" Then
                bolHaveHook = True
                Exit For
            End If
        Next
    Next

    'Get number of records on the form
    lngRecsOnForm = ctl1.Form.Recordset.RecordCount

    'Do a stinking loop until the records in the table and the records
on the screen match
    ' Makes the interface look really silly - but Access is too
unstable
    Do Until lngRecsOnForm = lngRecCount
        ctl1.Requery
        DoEvents
        [Forms]![frmDOMain]![sfrmDOStatus].Form.Refresh 'no good
        DoEvents
        lngRecsOnForm = ctl1.Form.Recordset.RecordCount
    Loop

    Screen.MousePointer = 1

End Sub

When the code below is executed the LOADED property for "frmDOMain"
and "frmDOstatus"  = FALSE. The two forms are open. What am I missing?
Click to expand...
   Dim int1 As Integer
Click to expand...
    'Print the number of forms in the project
    Debug.Print CurrentProject.AllForms.Count
    Debug.Print
Click to expand...
    'Enumerate each form in the project
    For int1 = 0 To CurrentProject.AllForms.Count - 1
        Debug.Print CurrentProject.AllForms.Item(int1).Name
        Debug.Print CurrentProject.AllForms.Item(int1).IsLoaded
        Debug.Print
    Next int1
Click to expand...
Well I am still having fun with this problem. Thanks to " Marsh MVP
[MS Access] " for your help.
I am trying to be a little more specific with names as I describe the
problem again.
I have a main form "frmDOMain", an imbeded form "frmDOstatus" (the
control is named "sfrmDOstatus"). When the user double clicks on a
record in "frmDOstatus" another form - "frmMngStatus" opens. The
purpose of "frmMngStatus" is to allow the user to add a child record
to the parent record presented in "frmDOMain".  When the user selects
"Save" from "frmMngStatus" then a record is written to a table (via
ADO) that is used to supply data for "frmDOstatus" (the sub form on
"frmDOMain").
The goal is that after the user selects "save" from "frmMngStatus"
that the added record shows up in the subform ("frmDOstatus") on the
main form ("frmDOMain").
If all that didn't make sense the flow is - Open main form with
embeded form/child record. Double click on embeded form - pop-up form
is displayed. New child record/Information is added in the pop-up
form. Pop-up form is closed. What is expected next is new child record
is displayed in the embeded form.
The problem is that the child record is displayed *for the first
several iterations of the process only*. Then at some point the child
record no longer shows up in the subform "frmDOstatus" after the user
selects "save" from ""frmMngStatus".
When the record stops displaying as expected then I can execute a go
next, move prev routine (sometimes as many as 3 x for it to work) then
the child cecord is displayed. In addition to the fact that the same
exact, unchanged code produces inconsistent results the frustration is
that the child record is written to the table every single time.
Here is the code from "frmMngStatus":
Private Sub cmdUpdate_Click()
    Dim lngStatusID As Long
    Dim strNotes As String
    Dim dteDate As Date
    Dim objCon As ADODB.Connection
    Dim objCmd As ADODB.Command
    Dim objPrm As ADODB.Parameter
    Set objCon = New ADODB.Connection
    Set objCmd = New ADODB.Command
    Set objPrm = New ADODB.Parameter
    If IsNull(cboStatus.Value) Then
        MsgBox "Please Select a Status Message", vbOKOnly, "Error"
        Me.cboStatus.SetFocus
    Else
        lngStatusID = Me.cboStatus.Value
        dteDate = CDate(Format(Now(), "dd mmm yyyy"))
        If Not IsNull(txtNotes.Value) Then
            strNotes = Me.txtNotes.Value
        Else
            strNotes = ""
        End If
        objCon.Open (CurrentProject.Connection)
        'Setup Command Object for DB Call
        With objCmd
            .CommandText = "sp_insert_do_status"
            .CommandType = adCmdStoredProc
            .ActiveConnection = objCon
            Set objPrm = .CreateParameter("do_id", adInteger,
adParamInput, , g_lngDOID)
            .Parameters.Append objPrm
            Set objPrm = .CreateParameter("s_id", adInteger,
adParamInput, , lngStatusID)
            .Parameters.Append objPrm
            Set objPrm = .CreateParameter("dte", adDate,
adParamInput, , dteDate)
            .Parameters.Append objPrm
            Set objPrm = .CreateParameter("StatusNotes",adVarChar,
adParamInput, 255, strNotes)
            .Parameters.Append objPrm
            .Execute '< Works every single time!
        End With
        objCon.Close
        Set objCon = Nothing
        Set objCmd = Nothing
        Set objPrm = Nothing
        Forms!frmDOMain!sfrmDOStatus.Form.Requery 'doesn't work
        DoEvents
        Forms!frmDOMain!sfrmDOStatus.Form.Refresh 'no good
        DoEvents
        Forms!frmDOMain!sfrmDOStatus.Form.Repaint 'doesn't do it
        DoEvents
       'tried...
       'Dim frm as FORM_frmDOStatus
       'Set frm = New FORM_frmDOStatus
       'frm.Requery
       'I think this is way off
        DoCmd.Close acForm, "frmMngStatus"
    End If
This project is turning out to be deadly. I am in the Army in Iraq.
When I walk outside people lob mortars at my head. When I walk inside
the COL is going to chew my head off because he wants his reports - he
may just put me out on the street. Microsoft is literally going to put
me in harms way! (OK it is only a joke [kind of - sort of] - I am in
the Army and am in Iraq though).
Can somebody help a soldier out!?- Hide quoted text -
Click to expand...
Click to expand...
- Show quoted text -- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL server Insert 2
How do you call a stored procedure that returns a value? 3
ADO: how to execute SQL Server stored procedure and get return val 1
How do you pass parameters to a stored procedure 2
Numeric Data type 3
Getting a Return Value from a Stored Procedure in VBA 2
Passing dates to Sql Server 2005 stored procedure 3
Seeking Suggestions 3

Top