Can't find the field 'Forms' in your expression

  • Thread starter Thread starter Tina Hudson
  • Start date Start date
T

Tina Hudson

Good afternoon,

Sorry for the long post, but I want to provide as much info upfront as I can.

I'm having difficulty with opening a form that I have created for users to
add a new location for a child. This form is to be used when there are no
placements identified for a child, or when there are 1 or more placements
already identified. If there are no placements, the form I want to open
works as it should. If I have 1 or more placements, I get the error message
"Database can't find the field 'Forms' in your expression."

I know that somewhere I am not referencing a field correctly, but I can't
figure out where, although I have checked both forms thoroughly.

The cmd button to open the form is on the sub form "frmMainChild_Location"
(which is a continuous form), linked to the parent form "frmMainChild" by the
field Person_ID. If a child already has 1 or more placements, each placement
shows in the subform. Here is my code behind the on click event of the cmd
button to open the form "frmQuickAdd_Location":


Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmQuickAdd_Location"
stLinkCriteria = "[Person_ID]=" & Me.Parent!Person_ID


With Me
If Me.RecordsetClone.RecordCount = 0 Then
DoCmd.OpenForm stDocName, , , stLinkCriteria

ElseIf IsNull([DateLeft].Value) Then 'This will check to see if
user entered date left placement
Beep
MsgBox ("You must enter a date the child left current safety
resource placement and where the child went. Press OK to continue."),
vbOKOnly, "Missing date left safety resource"
DoCmd.GoToControl "DateLeft"
ElseIf IsNull([Outcome].Value) Then 'this will check outcome field
Beep
MsgBox ("You must identify the outcome of the child. Press OK
to continue."), vbOKOnly, "Missing Outcome Info"
DoCmd.GoToControl "Outcome"

Else
!Active = False 'this will make this placement no longer
current placement
DoCmd.OpenForm stDocName, , , stLinkCriteria

End If
End With


Okay, the form to be opened "frmQuickAdd_Location" has for the "on load"
event the following code:

Private Sub Form_Load()

Forms!frmQuickAdd_Location!Person_ID =
Forms!frmQuickAdd_Location!PersonIDFromForm
Forms!frmQuickAdd_Location!Placement_Nbr =
Forms!frmQuickAdd_Location!txtPlacement_NbrBox

End Sub


The expression in the "PersonIDFromForm" is:
=[Forms]![frmMainChild]![Person_ID]

And for txtPlacement_NbrBox is:
=IIf(DMax("[Placement_Nbr]","tblLocationHistory","[Person_ID]=[Forms]![frmQuickAdd_Location]![Person_ID]")>0,(1+DMax("[Placement_Nbr]","tblLocationHistory","[Person_ID]=[Forms]![frmQuickAdd_Location]![Person_ID]")),1)

I have no other expressions except some in the query, which look okay when I
run it.

Any and all help will be most appreciated.
 
Okay, I think the problem is in the calculated expression that automatically
calculates the placement number. If there are no records, then the
expression couldn't be calculated the way I have it. I think it needs a "IIF
IsNot Null" but I don't know how to do nulls very well.

Can anyone help here?
--
Thanks,
Tina Hudson


Tina Hudson said:
Good afternoon,

Sorry for the long post, but I want to provide as much info upfront as I can.

I'm having difficulty with opening a form that I have created for users to
add a new location for a child. This form is to be used when there are no
placements identified for a child, or when there are 1 or more placements
already identified. If there are no placements, the form I want to open
works as it should. If I have 1 or more placements, I get the error message
"Database can't find the field 'Forms' in your expression."

I know that somewhere I am not referencing a field correctly, but I can't
figure out where, although I have checked both forms thoroughly.

The cmd button to open the form is on the sub form "frmMainChild_Location"
(which is a continuous form), linked to the parent form "frmMainChild" by the
field Person_ID. If a child already has 1 or more placements, each placement
shows in the subform. Here is my code behind the on click event of the cmd
button to open the form "frmQuickAdd_Location":


Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmQuickAdd_Location"
stLinkCriteria = "[Person_ID]=" & Me.Parent!Person_ID


With Me
If Me.RecordsetClone.RecordCount = 0 Then
DoCmd.OpenForm stDocName, , , stLinkCriteria

ElseIf IsNull([DateLeft].Value) Then 'This will check to see if
user entered date left placement
Beep
MsgBox ("You must enter a date the child left current safety
resource placement and where the child went. Press OK to continue."),
vbOKOnly, "Missing date left safety resource"
DoCmd.GoToControl "DateLeft"
ElseIf IsNull([Outcome].Value) Then 'this will check outcome field
Beep
MsgBox ("You must identify the outcome of the child. Press OK
to continue."), vbOKOnly, "Missing Outcome Info"
DoCmd.GoToControl "Outcome"

Else
!Active = False 'this will make this placement no longer
current placement
DoCmd.OpenForm stDocName, , , stLinkCriteria

End If
End With


Okay, the form to be opened "frmQuickAdd_Location" has for the "on load"
event the following code:

Private Sub Form_Load()

Forms!frmQuickAdd_Location!Person_ID =
Forms!frmQuickAdd_Location!PersonIDFromForm
Forms!frmQuickAdd_Location!Placement_Nbr =
Forms!frmQuickAdd_Location!txtPlacement_NbrBox

End Sub


The expression in the "PersonIDFromForm" is:
=[Forms]![frmMainChild]![Person_ID]

And for txtPlacement_NbrBox is:
=IIf(DMax("[Placement_Nbr]","tblLocationHistory","[Person_ID]=[Forms]![frmQuickAdd_Location]![Person_ID]")>0,(1+DMax("[Placement_Nbr]","tblLocationHistory","[Person_ID]=[Forms]![frmQuickAdd_Location]![Person_ID]")),1)

I have no other expressions except some in the query, which look okay when I
run it.

Any and all help will be most appreciated.
Click to expand...
 
This is the problem:
The expression in the "PersonIDFromForm" is:
=[Forms]![frmMainChild]![Person_ID]

When you try to assign it that way, it gets confused because you are trying
to assign an object reference to a string variable:

Forms!frmQuickAdd_Location!Person_ID =
Forms!frmQuickAdd_Location!PersonIDFromForm


Let's see if we can clean the code up a bit:

Dim stDocName As String
Dim stLinkCriteria As String

With Me
stDocName = "frmQuickAdd_Location"
stLinkCriteria = "[Person_ID]=" & .Parent!Person_ID
If .Recordset.RecordCount = 0 Then
DoCmd.OpenForm stDocName, , , stLinkCriteria, , ,
..Parent!Person_ID
ElseIf IsNull(.DateLeft) Then 'This will check to see if
user entered date left placement
Beep
MsgBox ("You must enter a date the child left current safety
resource placement and where the child went. Press OK to continue."),
vbOKOnly, "Missing date left safety resource"
.DateLeft.SetFocus
ElseIf IsNull(.Outcome) Then 'this will check outcome field
Beep
MsgBox ("You must identify the outcome of the child. Press OK
to continue."), vbOKOnly, "Missing Outcome Info"
.Outcome.SetFocus
Else
.Active = False 'this will make this placement no longer
current placement
DoCmd.OpenForm stDocName, , , stLinkCriteria, , ,
..Parent!Person_ID
End If
End With

Now for the other part. In this case, it is better to just assign the
values directly

Private Sub Form_Load()

With Me
.Person_ID = .OpenArgs
.Placement_Nbr =
Nz(DMax("[Placement_Nbr]","tblLocationHistory","[Person_ID] = " &
..OpenArgs),0) + 1
End With

End Sub

I did change some of your code to improve the style. Also, notice the use
of the OpenArgs arguement of the OpenForm method. It is a handy way of
passing a value directly to a form you are opening.
If you have any questions or problems, post back.
--
Dave Hargis, Microsoft Access MVP


Tina Hudson said:
Good afternoon,

Sorry for the long post, but I want to provide as much info upfront as I can.

I'm having difficulty with opening a form that I have created for users to
add a new location for a child. This form is to be used when there are no
placements identified for a child, or when there are 1 or more placements
already identified. If there are no placements, the form I want to open
works as it should. If I have 1 or more placements, I get the error message
"Database can't find the field 'Forms' in your expression."

I know that somewhere I am not referencing a field correctly, but I can't
figure out where, although I have checked both forms thoroughly.

The cmd button to open the form is on the sub form "frmMainChild_Location"
(which is a continuous form), linked to the parent form "frmMainChild" by the
field Person_ID. If a child already has 1 or more placements, each placement
shows in the subform. Here is my code behind the on click event of the cmd
button to open the form "frmQuickAdd_Location":


Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmQuickAdd_Location"
stLinkCriteria = "[Person_ID]=" & Me.Parent!Person_ID


With Me
If Me.RecordsetClone.RecordCount = 0 Then
DoCmd.OpenForm stDocName, , , stLinkCriteria

ElseIf IsNull([DateLeft].Value) Then 'This will check to see if
user entered date left placement
Beep
MsgBox ("You must enter a date the child left current safety
resource placement and where the child went. Press OK to continue."),
vbOKOnly, "Missing date left safety resource"
DoCmd.GoToControl "DateLeft"
ElseIf IsNull([Outcome].Value) Then 'this will check outcome field
Beep
MsgBox ("You must identify the outcome of the child. Press OK
to continue."), vbOKOnly, "Missing Outcome Info"
DoCmd.GoToControl "Outcome"

Else
!Active = False 'this will make this placement no longer
current placement
DoCmd.OpenForm stDocName, , , stLinkCriteria

End If
End With


Okay, the form to be opened "frmQuickAdd_Location" has for the "on load"
event the following code:

Private Sub Form_Load()

Forms!frmQuickAdd_Location!Person_ID =
Forms!frmQuickAdd_Location!PersonIDFromForm
Forms!frmQuickAdd_Location!Placement_Nbr =
Forms!frmQuickAdd_Location!txtPlacement_NbrBox

End Sub


The expression in the "PersonIDFromForm" is:
=[Forms]![frmMainChild]![Person_ID]

And for txtPlacement_NbrBox is:
=IIf(DMax("[Placement_Nbr]","tblLocationHistory","[Person_ID]=[Forms]![frmQuickAdd_Location]![Person_ID]")>0,(1+DMax("[Placement_Nbr]","tblLocationHistory","[Person_ID]=[Forms]![frmQuickAdd_Location]![Person_ID]")),1)

I have no other expressions except some in the query, which look okay when I
run it.

Any and all help will be most appreciated.
Click to expand...
 
Wow!!! Works beautifully! Thanks for giving me the explanation for what I
did wrong, too, as now I'll need to go back and check my other forms.

I owe you big time!!! If you're ever in Raleigh, NC, look me up and I'll
buy you dinner!
--
Thanks,
Tina Hudson


Klatuu said:
This is the problem:
The expression in the "PersonIDFromForm" is:
=[Forms]![frmMainChild]![Person_ID]

When you try to assign it that way, it gets confused because you are trying
to assign an object reference to a string variable:

Forms!frmQuickAdd_Location!Person_ID =
Forms!frmQuickAdd_Location!PersonIDFromForm


Let's see if we can clean the code up a bit:

Dim stDocName As String
Dim stLinkCriteria As String

With Me
stDocName = "frmQuickAdd_Location"
stLinkCriteria = "[Person_ID]=" & .Parent!Person_ID
If .Recordset.RecordCount = 0 Then
DoCmd.OpenForm stDocName, , , stLinkCriteria, , ,
.Parent!Person_ID
ElseIf IsNull(.DateLeft) Then 'This will check to see if
user entered date left placement
Beep
MsgBox ("You must enter a date the child left current safety
resource placement and where the child went. Press OK to continue."),
vbOKOnly, "Missing date left safety resource"
.DateLeft.SetFocus
ElseIf IsNull(.Outcome) Then 'this will check outcome field
Beep
MsgBox ("You must identify the outcome of the child. Press OK
to continue."), vbOKOnly, "Missing Outcome Info"
.Outcome.SetFocus
Else
.Active = False 'this will make this placement no longer
current placement
DoCmd.OpenForm stDocName, , , stLinkCriteria, , ,
.Parent!Person_ID
End If
End With

Now for the other part. In this case, it is better to just assign the
values directly

Private Sub Form_Load()

With Me
.Person_ID = .OpenArgs
.Placement_Nbr =
Nz(DMax("[Placement_Nbr]","tblLocationHistory","[Person_ID] = " &
.OpenArgs),0) + 1
End With

End Sub

I did change some of your code to improve the style. Also, notice the use
of the OpenArgs arguement of the OpenForm method. It is a handy way of
passing a value directly to a form you are opening.
If you have any questions or problems, post back.
--
Dave Hargis, Microsoft Access MVP


Tina Hudson said:
Good afternoon,

Sorry for the long post, but I want to provide as much info upfront as I can.

I'm having difficulty with opening a form that I have created for users to
add a new location for a child. This form is to be used when there are no
placements identified for a child, or when there are 1 or more placements
already identified. If there are no placements, the form I want to open
works as it should. If I have 1 or more placements, I get the error message
"Database can't find the field 'Forms' in your expression."

I know that somewhere I am not referencing a field correctly, but I can't
figure out where, although I have checked both forms thoroughly.

The cmd button to open the form is on the sub form "frmMainChild_Location"
(which is a continuous form), linked to the parent form "frmMainChild" by the
field Person_ID. If a child already has 1 or more placements, each placement
shows in the subform. Here is my code behind the on click event of the cmd
button to open the form "frmQuickAdd_Location":


Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmQuickAdd_Location"
stLinkCriteria = "[Person_ID]=" & Me.Parent!Person_ID


With Me
If Me.RecordsetClone.RecordCount = 0 Then
DoCmd.OpenForm stDocName, , , stLinkCriteria

ElseIf IsNull([DateLeft].Value) Then 'This will check to see if
user entered date left placement
Beep
MsgBox ("You must enter a date the child left current safety
resource placement and where the child went. Press OK to continue."),
vbOKOnly, "Missing date left safety resource"
DoCmd.GoToControl "DateLeft"
ElseIf IsNull([Outcome].Value) Then 'this will check outcome field
Beep
MsgBox ("You must identify the outcome of the child. Press OK
to continue."), vbOKOnly, "Missing Outcome Info"
DoCmd.GoToControl "Outcome"

Else
!Active = False 'this will make this placement no longer
current placement
DoCmd.OpenForm stDocName, , , stLinkCriteria

End If
End With


Okay, the form to be opened "frmQuickAdd_Location" has for the "on load"
event the following code:

Private Sub Form_Load()

Forms!frmQuickAdd_Location!Person_ID =
Forms!frmQuickAdd_Location!PersonIDFromForm
Forms!frmQuickAdd_Location!Placement_Nbr =
Forms!frmQuickAdd_Location!txtPlacement_NbrBox

End Sub


The expression in the "PersonIDFromForm" is:
=[Forms]![frmMainChild]![Person_ID]

And for txtPlacement_NbrBox is:
=IIf(DMax("[Placement_Nbr]","tblLocationHistory","[Person_ID]=[Forms]![frmQuickAdd_Location]![Person_ID]")>0,(1+DMax("[Placement_Nbr]","tblLocationHistory","[Person_ID]=[Forms]![frmQuickAdd_Location]![Person_ID]")),1)

I have no other expressions except some in the query, which look okay when I
run it.

Any and all help will be most appreciated.
Click to expand...
Click to expand...
 
Glad I could help.
Or, if you are in the Dallas/Fort Worth area <g>
--
Dave Hargis, Microsoft Access MVP


Tina Hudson said:
Wow!!! Works beautifully! Thanks for giving me the explanation for what I
did wrong, too, as now I'll need to go back and check my other forms.

I owe you big time!!! If you're ever in Raleigh, NC, look me up and I'll
buy you dinner!
--
Thanks,
Tina Hudson


Klatuu said:
This is the problem:
The expression in the "PersonIDFromForm" is:
=[Forms]![frmMainChild]![Person_ID]

When you try to assign it that way, it gets confused because you are trying
to assign an object reference to a string variable:

Forms!frmQuickAdd_Location!Person_ID =
Forms!frmQuickAdd_Location!PersonIDFromForm


Let's see if we can clean the code up a bit:

Dim stDocName As String
Dim stLinkCriteria As String

With Me
stDocName = "frmQuickAdd_Location"
stLinkCriteria = "[Person_ID]=" & .Parent!Person_ID
If .Recordset.RecordCount = 0 Then
DoCmd.OpenForm stDocName, , , stLinkCriteria, , ,
.Parent!Person_ID
ElseIf IsNull(.DateLeft) Then 'This will check to see if
user entered date left placement
Beep
MsgBox ("You must enter a date the child left current safety
resource placement and where the child went. Press OK to continue."),
vbOKOnly, "Missing date left safety resource"
.DateLeft.SetFocus
ElseIf IsNull(.Outcome) Then 'this will check outcome field
Beep
MsgBox ("You must identify the outcome of the child. Press OK
to continue."), vbOKOnly, "Missing Outcome Info"
.Outcome.SetFocus
Else
.Active = False 'this will make this placement no longer
current placement
DoCmd.OpenForm stDocName, , , stLinkCriteria, , ,
.Parent!Person_ID
End If
End With

Now for the other part. In this case, it is better to just assign the
values directly

Private Sub Form_Load()

With Me
.Person_ID = .OpenArgs
.Placement_Nbr =
Nz(DMax("[Placement_Nbr]","tblLocationHistory","[Person_ID] = " &
.OpenArgs),0) + 1
End With

End Sub

I did change some of your code to improve the style. Also, notice the use
of the OpenArgs arguement of the OpenForm method. It is a handy way of
passing a value directly to a form you are opening.
If you have any questions or problems, post back.
--
Dave Hargis, Microsoft Access MVP


Tina Hudson said:
Good afternoon,

Sorry for the long post, but I want to provide as much info upfront as I can.

I'm having difficulty with opening a form that I have created for users to
add a new location for a child. This form is to be used when there are no
placements identified for a child, or when there are 1 or more placements
already identified. If there are no placements, the form I want to open
works as it should. If I have 1 or more placements, I get the error message
"Database can't find the field 'Forms' in your expression."

I know that somewhere I am not referencing a field correctly, but I can't
figure out where, although I have checked both forms thoroughly.

The cmd button to open the form is on the sub form "frmMainChild_Location"
(which is a continuous form), linked to the parent form "frmMainChild" by the
field Person_ID. If a child already has 1 or more placements, each placement
shows in the subform. Here is my code behind the on click event of the cmd
button to open the form "frmQuickAdd_Location":


Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmQuickAdd_Location"
stLinkCriteria = "[Person_ID]=" & Me.Parent!Person_ID


With Me
If Me.RecordsetClone.RecordCount = 0 Then
DoCmd.OpenForm stDocName, , , stLinkCriteria

ElseIf IsNull([DateLeft].Value) Then 'This will check to see if
user entered date left placement
Beep
MsgBox ("You must enter a date the child left current safety
resource placement and where the child went. Press OK to continue."),
vbOKOnly, "Missing date left safety resource"
DoCmd.GoToControl "DateLeft"
ElseIf IsNull([Outcome].Value) Then 'this will check outcome field
Beep
MsgBox ("You must identify the outcome of the child. Press OK
to continue."), vbOKOnly, "Missing Outcome Info"
DoCmd.GoToControl "Outcome"

Else
!Active = False 'this will make this placement no longer
current placement
DoCmd.OpenForm stDocName, , , stLinkCriteria

End If
End With


Okay, the form to be opened "frmQuickAdd_Location" has for the "on load"
event the following code:

Private Sub Form_Load()

Forms!frmQuickAdd_Location!Person_ID =
Forms!frmQuickAdd_Location!PersonIDFromForm
Forms!frmQuickAdd_Location!Placement_Nbr =
Forms!frmQuickAdd_Location!txtPlacement_NbrBox

End Sub


The expression in the "PersonIDFromForm" is:
=[Forms]![frmMainChild]![Person_ID]

And for txtPlacement_NbrBox is:
=IIf(DMax("[Placement_Nbr]","tblLocationHistory","[Person_ID]=[Forms]![frmQuickAdd_Location]![Person_ID]")>0,(1+DMax("[Placement_Nbr]","tblLocationHistory","[Person_ID]=[Forms]![frmQuickAdd_Location]![Person_ID]")),1)

I have no other expressions except some in the query, which look okay when I
run it.

Any and all help will be most appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top