Problems getting subform to show new data.

[plh]

I have a form "frmParts" within which is a subform "frmToolAssems".

Within frmToolAssems are two combo boxes which can be filled either by picking
from the drop-down list in the standard way *OR* by double-clicking, which opens
one of two forms: "frmTools" or "frmHolders", depending on which combo box the
user is using.

If the user selects an existing record from frmTools or frmHolders, then selects
the command button which passes the value along (using OpenArgs, see below), the
new entry does not show up on the subform within the parent form. I can make the
new entry "show up" with a requery. This is not ideal, but OTOH not a disaster.

BUT if the user creates a new record in frmTools or frmHolders, and passes the
value (yes, the record is committed, as indicated by the little pencil turning
into a triangle) then a requery does not do the trick. I have to close the
parent form (frmParts) and re-open it in order for the new entry to show up.
This IS a real problem. I might add that I don't programmatically close the
parent form when starting this process. In the end I might have to do that,
figuring out a way to make it go to the correct record upon re-opening. However,
I WOULD like to understand what is going on.
Thank you all in advance!
-plh
PS:
Here is the relevant code.

frmTools passes the value to the receiving form using this code:

Private Sub cmdCloseAndPassValue_Click()
On Error GoTo Err_cmdCloseAndPassValue_Click
lngTabelRef = Me.txtCurrTool.Value
DoCmd.OpenForm "frmToolAssems", acNormal, , , , , OpenArgs & ";" & lngTabelRef
DoCmd.Close acForm, "frmTools"
....
(error handling)
....
End Sub

frmHolders passes the value to the recieving form using this code:

Private Sub cmdPassHolder_Click()
lngHolder = Me.txtCurHldr.Value
DoCmd.OpenForm "frmToolAssems", acNormal, , , , , OpenArgs & ";" & lngHolder *
-1
'multiply by negative one to distinguish it from a change of tool
DoCmd.Close acForm, "frmHolders"
End Sub

frmToolAssems recieves this information with this code:

Private Sub Form_Open(Cancel As Integer)
'MsgBox (OpenArgs)

Dim rst As Recordset

Dim lngRec As Long
Dim lngTool As Long
Dim lngHldr As Long
Dim varRec
Dim varTool
Dim bolOK As Boolean

bolOK = False

Set rst = Me.RecordsetClone

If Not IsNull(OpenArgs) Then
varRec = Left(OpenArgs, InStr(OpenArgs, ";") - 1)
varTool = Right(OpenArgs, Len(OpenArgs) - InStr(OpenArgs, ";"))
If IsNull(varRec) Or varRec = "" Then
lngRec = 0 'enabels triggering of addition of new tool
lngTool = Right(OpenArgs, Len(OpenArgs) - InStr(OpenArgs, ";"))
Else
lngRec = Left(OpenArgs, InStr(OpenArgs, ";") - 1)
lngTool = Right(OpenArgs, Len(OpenArgs) - InStr(OpenArgs, ";"))
End If
If lngRec > 0 And lngTool > 0 Then
'case if we want to change the *TOOL* on the current tool assembly recor
'in frmParts
With rst
.MoveFirst
While Not .EOF
If !IDToolAssem = lngRec Then
.Edit
!ToolDescription = lngTool
.Update
bolOK = True
End If
.MoveNext
Wend
End With
ElseIf lngRec > 0 And lngTool < 0 Then
'case if we want to change the *HOLDER* on the current tool assembly recor
'in frmParts -- note that we are using the variable lngTool to carry the holder
value
With rst
.MoveFirst
While Not .EOF
If !IDToolAssem = lngRec Then
.Edit
!ToolHolder = lngTool * -1
.Update
bolOK = True
End If
.MoveNext
Wend
End With
ElseIf lngRec = 0 And lngTool > 0 Then
'case if we are creating a new entry in the tool assembly list
'in frmParts
'if we are adding new we will have to have a tool holder
With rst
.AddNew
!ToolDescription = lngTool
!ToolHolder = GetHolder()
!IDPart = lngCurPart
.Update
bolOK = True
End With
End If 'If lngRec > 0 And lngTool > 0 Then -- ElseIf lngRec = 0 And lngTool > 0
Then
Me.Requery
DoCmd.Close acForm, "frmToolAssems"
End If 'If Not IsNull(OpenArgs) Then
End Sub

 

[Dirk Goldgar]

I have a form "frmParts" within which is a subform "frmToolAssems".

Within frmToolAssems are two combo boxes which can be filled either
by picking from the drop-down list in the standard way *OR* by
double-clicking, which opens one of two forms: "frmTools" or
"frmHolders", depending on which combo box the user is using.

If the user selects an existing record from frmTools or frmHolders,
then selects the command button which passes the value along (using
OpenArgs, see below), the new entry does not show up on the subform
within the parent form. I can make the new entry "show up" with a
requery. This is not ideal, but OTOH not a disaster.

BUT if the user creates a new record in frmTools or frmHolders, and
passes the value (yes, the record is committed, as indicated by the
little pencil turning into a triangle) then a requery does not do the
trick. I have to close the parent form (frmParts) and re-open it in
order for the new entry to show up. This IS a real problem. I might
add that I don't programmatically close the parent form when starting
this process. In the end I might have to do that, figuring out a way
to make it go to the correct record upon re-opening. However, I WOULD
like to understand what is going on.
Thank you all in advance!
-plh
PS:
Here is the relevant code.

frmTools passes the value to the receiving form using this code:

Private Sub cmdCloseAndPassValue_Click()
On Error GoTo Err_cmdCloseAndPassValue_Click
lngTabelRef = Me.txtCurrTool.Value
DoCmd.OpenForm "frmToolAssems", acNormal, , , , , OpenArgs & ";" &
lngTabelRef DoCmd.Close acForm, "frmTools"
...
(error handling)
...
End Sub

frmHolders passes the value to the recieving form using this code:

Private Sub cmdPassHolder_Click()
lngHolder = Me.txtCurHldr.Value
DoCmd.OpenForm "frmToolAssems", acNormal, , , , , OpenArgs & ";" &
lngHolder * -1
'multiply by negative one to distinguish it from a change of tool
DoCmd.Close acForm, "frmHolders"
End Sub

frmToolAssems recieves this information with this code:

Private Sub Form_Open(Cancel As Integer)
'MsgBox (OpenArgs)

Dim rst As Recordset

Dim lngRec As Long
Dim lngTool As Long
Dim lngHldr As Long
Dim varRec
Dim varTool
Dim bolOK As Boolean

bolOK = False

Set rst = Me.RecordsetClone

If Not IsNull(OpenArgs) Then
varRec = Left(OpenArgs, InStr(OpenArgs, ";") - 1)
varTool = Right(OpenArgs, Len(OpenArgs) - InStr(OpenArgs,
";")) If IsNull(varRec) Or varRec = "" Then
lngRec = 0 'enabels triggering of addition of new tool
lngTool = Right(OpenArgs, Len(OpenArgs) - InStr(OpenArgs,
";")) Else
lngRec = Left(OpenArgs, InStr(OpenArgs, ";") - 1)
lngTool = Right(OpenArgs, Len(OpenArgs) - InStr(OpenArgs,
";")) End If
If lngRec > 0 And lngTool > 0 Then
'case if we want to change the *TOOL* on the current tool
assembly recor 'in frmParts
With rst
.MoveFirst
While Not .EOF
If !IDToolAssem = lngRec Then
.Edit
!ToolDescription = lngTool
.Update
bolOK = True
End If
.MoveNext
Wend
End With
ElseIf lngRec > 0 And lngTool < 0 Then
'case if we want to change the *HOLDER* on the current tool assembly
recor 'in frmParts -- note that we are using the variable lngTool to
carry the holder value
With rst
.MoveFirst
While Not .EOF
If !IDToolAssem = lngRec Then
.Edit
!ToolHolder = lngTool * -1
.Update
bolOK = True
End If
.MoveNext
Wend
End With
ElseIf lngRec = 0 And lngTool > 0 Then
'case if we are creating a new entry in the tool assembly list
'in frmParts
'if we are adding new we will have to have a tool holder
With rst
.AddNew
!ToolDescription = lngTool
!ToolHolder = GetHolder()
!IDPart = lngCurPart
.Update
bolOK = True
End With
End If 'If lngRec > 0 And lngTool > 0 Then -- ElseIf lngRec = 0 And
lngTool > 0 Then
Me.Requery
DoCmd.Close acForm, "frmToolAssems"
End If 'If Not IsNull(OpenArgs) Then
End Sub

I'm afraid this doesn't make much sense to me. There is no connection
between the instance of frmToolAssems that you have being displayed as a
subform on frmParts, and any standalone instance you create by executing
DoCmd.OpenForm "frmToolAssems". So it doesn't matter that you open a
separate instance of the subform's form object, add or update a record
with it, and requery that instance. That will have no effect on the
subform.

I think you're going about this the wrong way. Why not add or update a
record directly in the appropriate table, by using DAO to execute an
appropriate SQL statement, and then refresh or requery the subform by
executing a line like

Forms!frmParts!frmToolAssems.Form.Requery

or

Forms!frmParts!frmToolAssems.Form.Refresh

depending on whether you added a new record or just updated an existing
one?

 

[plh]

I'm afraid this doesn't make much sense to me. There is no connection
between the instance of frmToolAssems that you have being displayed as a
subform on frmParts, and any standalone instance you create by executing
DoCmd.OpenForm "frmToolAssems". So it doesn't matter that you open a
separate instance of the subform's form object, add or update a record
with it, and requery that instance. That will have no effect on the
subform.

I think you're going about this the wrong way. Why not add or update a
record directly in the appropriate table, by using DAO to execute an
appropriate SQL statement, and then refresh or requery the subform by
executing a line like

Forms!frmParts!frmToolAssems.Form.Requery

or

Forms!frmParts!frmToolAssems.Form.Refresh

depending on whether you added a new record or just updated an existing
one?

Thanks for your reply. I had the feeling I was going about it the wrong way too!
So do I understand you correctly that I should put a control in the main form
that will trigger the SQL statements needed to place the new record in the table
referenced by the sub form? Are there any on-line or other references that have
good examples of how to do this?
One problem I foresee is if the user wants to change an item in one of the
existing sub form entries to an item they create on the fly.
Thanks Again,
-plh

 

[Dirk Goldgar]

[...]

I think you're going about this the wrong way. Why not add or
update a record directly in the appropriate table, by using DAO to
execute an appropriate SQL statement, and then refresh or requery
the subform by executing a line like

Forms!frmParts!frmToolAssems.Form.Requery

or

Forms!frmParts!frmToolAssems.Form.Refresh

depending on whether you added a new record or just updated an
existing one?

Thanks for your reply. I had the feeling I was going about it the
wrong way too! So do I understand you correctly that I should put a
control in the main form that will trigger the SQL statements needed
to place the new record in the table referenced by the sub form?

I think so; something like that, anyway. But my picture of what you're
actually doing and how you have it set up is too fuzzy to give you
specific instructions. It's not clear to me whether you are adding
items to the rowsource(s) of the combo boxes, or to the recordsource of
the subform.

Are
there any on-line or other references that have good examples of how
to do this?

I maybe able to give you an example if I can come to an understanding of
exactly what you're doing. Of course, it's been a while since you
wrote, so you may have solved your problem already.

One problem I foresee is if the user wants to change an item in one
of the existing sub form entries to an item they create on the fly.

I don't see the problem, which is another reason I'm not sure I
understand exactly what you're doing.