Leave Forms open

  • Thread starter Thread starter Katrina
  • Start date Start date
K

Katrina

I have code to run through a couple forms. I would
like at the end to leave "3 summary form" and "0 P/N
Summary" open. I have pasted the end of my code below.
My major question is this. If I open "0 P/N Summary"
without the where statement, both forms stay open.
However, as soon as I include the where statement, Access
runs through the code (I can see the forms briefly open)
and then closes both forms. I don't see why it would
close forms because of a where statement.
How do I keep them open? Is there another way to only
show the specific records in "0 P/N Summary"?

DoCmd.OpenForm "0 P/N Summary", , , Forms![0 P/N Summary]!_
[Auto] = Forms![3 summary form]![Ref]
Forms![0 P/N Summary].Visible = True

DoCmd.SelectObject acForm, "3 summary form", no
Forms![3 summary form].AllowAdditions = False
Dim number As Variant
For number = 1 To 12
Dim cntrl
cntrl = Format$(number, """text""00")
DoCmd.Requery cntrl
Next number
DoCmd.Requery "sysdesc"


NewEst_Exit:
DoCmd.Close acForm, "1 Start Form"
DoCmd.Close acForm, "2 Sys ID Form"
Exit Function


NewEst_Err:
If Err.number = 2450 Then 'form is missing
DoCmd.Close acForm, "3 Summary Form"
Resume NewEst_Exit
Else
MsgBox Err.Description
DoCmd.Close acForm, "3 Summary Form"
Resume NewEst_Exit
End If

End Function
 
I also tried a apply filter (shown below)

DoCmd.ApplyFilter , Forms![0 P/N Summary]![Auto Number]
= "*" & Forms![3 summary form]![Ref] & "*"

However, this returns no results, although I know the
matching record is there.
Do I refer to the name of the field on the form is apply
filter or the name of the field in the table

the table is [0 Saved] and the field in the table is
[autonumber]
the form is [0 p/N summary] and the field is [auton]

how do I make the where clause work for this
 
Katrina said:
I have code to run through a couple forms. I would
like at the end to leave "3 summary form" and "0 P/N
Summary" open. I have pasted the end of my code below.
My major question is this. If I open "0 P/N Summary"
without the where statement, both forms stay open.
However, as soon as I include the where statement, Access
runs through the code (I can see the forms briefly open)
and then closes both forms. I don't see why it would
close forms because of a where statement.
How do I keep them open? Is there another way to only
show the specific records in "0 P/N Summary"?

DoCmd.OpenForm "0 P/N Summary", , , Forms![0 P/N Summary]!_
[Auto] = Forms![3 summary form]![Ref]
Forms![0 P/N Summary].Visible = True

DoCmd.SelectObject acForm, "3 summary form", no
Forms![3 summary form].AllowAdditions = False
Dim number As Variant
For number = 1 To 12
Dim cntrl
cntrl = Format$(number, """text""00")
DoCmd.Requery cntrl
Next number
DoCmd.Requery "sysdesc"


NewEst_Exit:
DoCmd.Close acForm, "1 Start Form"
DoCmd.Close acForm, "2 Sys ID Form"
Exit Function


NewEst_Err:
If Err.number = 2450 Then 'form is missing
DoCmd.Close acForm, "3 Summary Form"
Resume NewEst_Exit
Else
MsgBox Err.Description
DoCmd.Close acForm, "3 Summary Form"
Resume NewEst_Exit
End If

End Function
Click to expand...

This is not correct syntax:
DoCmd.OpenForm "0 P/N Summary", , , Forms![0 P/N Summary]!_
[Auto] = Forms![3 summary form]![Ref]
Click to expand...

Your where-condition argument must be a string, and it should refer to
the field in the form's recordsource, not to the control on the form.
If, as you say in your second message, the field is named
"autonumber" -- terrible choice, BTW -- then try this:

DoCmd.OpenForm "0 P/N Summary", _
WhereCondition:= "[autonumber]=" & _
Forms![3 summary form]![Ref]

That assumes that this field is actually numeric, as it would normally
be if its name corresponds to its data type.
 
Back
Top