"if" program difficulties

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to set up an “if†statement to open a form depending on what is
specified on a subform. The main form is “customers†with the subform being
“PetTableâ€. I am trying to bring up one of either a canineshotrecord, or a
felineshotrecord form, depending on what is in the “species†field of the
subform “petTableâ€. Following is the button code that I’ve gotten so far;
however, I get the following error: “Microsoft Office Access can’t find the
field ‘|’ referred to in your expression.â€.

Any help would be appreciated.

Private Sub ShotRecords_Click()
On Error GoTo Err_ShotRecords_Click

Dim Canineshotrecords As String
Dim FelineShotRecords As String
Dim stLinkCriteria As String

stLinkCriteria = "[PetID]=" & "'" & Me![PetID] & "'"
If [subForms]![PetTable].[Species] = "Canine" Then
DoCmd.OpenForm Canineshotrecords, , , stLinkCriteria
Else: DoCmd.OpenForm FelineShotRecords, , , stLinkCriteria
End If

Exit_ShotRecords_Click:
Exit Sub

Err_ShotRecords_Click:
MsgBox Err.Description
Resume Exit_ShotRecords_Click

End Sub
 
There are several problems here. See notes imbedded below
--
Dave Hargis, Microsoft Access MVP


ahern79 said:
I am trying to set up an “if†statement to open a form depending on what is
specified on a subform. The main form is “customers†with the subform being
“PetTableâ€. I am trying to bring up one of either a canineshotrecord, or a
felineshotrecord form, depending on what is in the “species†field of the
subform “petTableâ€. Following is the button code that I’ve gotten so far;
however, I get the following error: “Microsoft Office Access can’t find the
field ‘|’ referred to in your expression.â€.

Any help would be appreciated.

Private Sub ShotRecords_Click()
On Error GoTo Err_ShotRecords_Click

Dim Canineshotrecords As String
Dim FelineShotRecords As String
Click to expand...
You dim the two variables, but never put anything in them. You are trying
to open a form named ""
Dim stLinkCriteria As String

stLinkCriteria = "[PetID]=" & "'" & Me![PetID] & "'"
If [subForms]![PetTable].[Species] = "Canine" Then
Click to expand...
This is not the correct way to address a subform. It should be:
Me.SubformControlName.Form.ControlName
DoCmd.OpenForm Canineshotrecords, , , stLinkCriteria
Else: DoCmd.OpenForm FelineShotRecords, , , stLinkCriteria
End If

Exit_ShotRecords_Click:
Exit Sub

Err_ShotRecords_Click:
MsgBox Err.Description
Resume Exit_ShotRecords_Click

End Sub
Click to expand...
If you can tell me the name of the two forms, the name of the subform
control on the form this code is in, and the name of the control containing
the species, I can show you how to clean this up.
 
ok, let me start over, I'm useing Access 2007, but the file is in 03 format
(has tot stay in 03 format for the actual owner of the database). I use the
standard MS VB that comes with Office for the code writing.

There are 4 forms (likewise 4 tables) involved in all this mess (and this
isnt even my biggest problem with this database).

Master Form:
CustomerTable

SubForm:
PetTable

I want to open one of two tables depending on the value in
[PetTable].[Species]. If the value is "Canine" then I want to open the
CanineShotRecords form. If the value in [PetTable].[Species] does not equal
"Canine" I want to open FelineShotRecords. These are the only two record
forms I need to access depending on the value of "Species" so, I figured a
simple "IF" command would be best.

Both Shot records are dependent on the Master Form "CustomerTable" as well
as the SubForm "PetTable". I have the PetID from the PetTable and the
CustomerID from the CustomerTable linking to both shot records. So I need
those two to show up, I suppose, as criteria in either record.

I think thats all I know about this issue at the moment. Thank you for all
your help.

Klatuu said:
There are several problems here. See notes imbedded below
--
Dave Hargis, Microsoft Access MVP


ahern79 said:
I am trying to set up an “if†statement to open a form depending on what is
specified on a subform. The main form is “customers†with the subform being
“PetTableâ€. I am trying to bring up one of either a canineshotrecord, or a
felineshotrecord form, depending on what is in the “species†field of the
subform “petTableâ€. Following is the button code that I’ve gotten so far;
however, I get the following error: “Microsoft Office Access can’t find the
field ‘|’ referred to in your expression.â€.

Any help would be appreciated.

Private Sub ShotRecords_Click()
On Error GoTo Err_ShotRecords_Click

Dim Canineshotrecords As String
Dim FelineShotRecords As String
Click to expand...
You dim the two variables, but never put anything in them. You are trying
to open a form named ""
Dim stLinkCriteria As String

stLinkCriteria = "[PetID]=" & "'" & Me![PetID] & "'"
If [subForms]![PetTable].[Species] = "Canine" Then
Click to expand...
This is not the correct way to address a subform. It should be:
Me.SubformControlName.Form.ControlName
DoCmd.OpenForm Canineshotrecords, , , stLinkCriteria
Else: DoCmd.OpenForm FelineShotRecords, , , stLinkCriteria
End If

Exit_ShotRecords_Click:
Exit Sub

Err_ShotRecords_Click:
MsgBox Err.Description
Resume Exit_ShotRecords_Click

End Sub
Click to expand...
If you can tell me the name of the two forms, the name of the subform
control on the form this code is in, and the name of the control containing
the species, I can show you how to clean this up.
Click to expand...
 
I understand what it is you are trying to do. It would help if you would
answer my questions. Since I don't have the real names of things, I will
just make them up and you can put the real ones in. The reference to the
subform will not be at all clear, because referencing control values on sub
forms has to have the correct form or it will not work, so since you didn't
answer the questions, it may not work correctly.

Private Sub ShotRecords_Click()
Dim strFormName as String

On Error GoTo Err_ShotRecords_Click

If Me!subFormControlName.Form!SomeControl = "Canine" Then
strFormName = "CanineFormName"
Else
strFormName = "FelineFormName"
End If

DoCmd.OpenForm strFormName, , , "[PetID]= '" & Me![PetID] & "'"

Exit_ShotRecords_Click:
Exit Sub

Err_ShotRecords_Click:
MsgBox Err.Description
Resume Exit_ShotRecords_Click

End Sub

--
Dave Hargis, Microsoft Access MVP


ahern79 said:
ok, let me start over, I'm useing Access 2007, but the file is in 03 format
(has tot stay in 03 format for the actual owner of the database). I use the
standard MS VB that comes with Office for the code writing.

There are 4 forms (likewise 4 tables) involved in all this mess (and this
isnt even my biggest problem with this database).

Master Form:
CustomerTable

SubForm:
PetTable

I want to open one of two tables depending on the value in
[PetTable].[Species]. If the value is "Canine" then I want to open the
CanineShotRecords form. If the value in [PetTable].[Species] does not equal
"Canine" I want to open FelineShotRecords. These are the only two record
forms I need to access depending on the value of "Species" so, I figured a
simple "IF" command would be best.

Both Shot records are dependent on the Master Form "CustomerTable" as well
as the SubForm "PetTable". I have the PetID from the PetTable and the
CustomerID from the CustomerTable linking to both shot records. So I need
those two to show up, I suppose, as criteria in either record.

I think thats all I know about this issue at the moment. Thank you for all
your help.

Klatuu said:
There are several problems here. See notes imbedded below
--
Dave Hargis, Microsoft Access MVP


ahern79 said:
I am trying to set up an “if†statement to open a form depending on what is
specified on a subform. The main form is “customers†with the subform being
“PetTableâ€. I am trying to bring up one of either a canineshotrecord, or a
felineshotrecord form, depending on what is in the “species†field of the
subform “petTableâ€. Following is the button code that I’ve gotten so far;
however, I get the following error: “Microsoft Office Access can’t find the
field ‘|’ referred to in your expression.â€.

Any help would be appreciated.

Private Sub ShotRecords_Click()
On Error GoTo Err_ShotRecords_Click

Dim Canineshotrecords As String
Dim FelineShotRecords As String
Click to expand...
You dim the two variables, but never put anything in them. You are trying
to open a form named ""
Dim stLinkCriteria As String

stLinkCriteria = "[PetID]=" & "'" & Me![PetID] & "'"
If [subForms]![PetTable].[Species] = "Canine" Then
Click to expand...
This is not the correct way to address a subform. It should be:
Me.SubformControlName.Form.ControlName
DoCmd.OpenForm Canineshotrecords, , , stLinkCriteria
Else: DoCmd.OpenForm FelineShotRecords, , , stLinkCriteria
End If

Exit_ShotRecords_Click:
Exit Sub

Err_ShotRecords_Click:
MsgBox Err.Description
Resume Exit_ShotRecords_Click

End Sub
Click to expand...
If you can tell me the name of the two forms, the name of the subform
control on the form this code is in, and the name of the control containing
the species, I can show you how to clean this up.
Click to expand...
Click to expand...
 
Apperently I dont understand what you are asking....the forms and tables are
as follows:

CustomerTable:
*CustomerID
CustomerFirstName
CustomerLastName
CustomerAdderss1
CustomerAddress2
CustomerCity
CustomerState
CustomerZip
(and I have all the phonenumbers also)

the Form for the customers is the same:
CustomerTable:

PetTable:
*PetID
CustomerID (from CustomerTable) Indexed, dup. OK
PetName
Species (selection of Canine or Feline only)
BreedID (linked to another table)
PetSex
PetAge
PetNotes

The Form for that is of course the same:
PetTable:

The PetTable form is linked to the CustomerTable form by CustomerID

I have two "ALMOST' identical shot forms (because I have to track different
shots for each species.

Each Shot record has

*CanineShotID/FelineShotID
PetID (=Forms!CustomerTable!PetTable.Form!PetName)
CustomerID (=Forms!CustomerTable!CustomerLastName)
and then a list of shots and dates as needed.

Likewise the forms for these two tables are:
CanineShotRecords or FelineShotRecords

So now I have this:

Private Sub ShotRecords_Click()
Dim strFormName As String

On Error GoTo Err_ShotRecords_Click

If Me!PetTable.Form!Species = "Canine" Then
strFormName = "CanineShotRecords"
Else
strFormName = "FelineShotRecords"
End If

DoCmd.OpenForm strFormName, , , "[PetID]= '" & Me![PetID] & "'"

Exit_ShotRecords_Click:
Exit Sub

Err_ShotRecords_Click:
MsgBox Err.Description
Resume Exit_ShotRecords_Click

End Sub

So I hope that helps, its all the info I know.

Thank you
 
Is the code working? If not, what is the error and on which line are you
getting the error?
--
Dave Hargis, Microsoft Access MVP


ahern79 said:
Apperently I dont understand what you are asking....the forms and tables are
as follows:

CustomerTable:
*CustomerID
CustomerFirstName
CustomerLastName
CustomerAdderss1
CustomerAddress2
CustomerCity
CustomerState
CustomerZip
(and I have all the phonenumbers also)

the Form for the customers is the same:
CustomerTable:

PetTable:
*PetID
CustomerID (from CustomerTable) Indexed, dup. OK
PetName
Species (selection of Canine or Feline only)
BreedID (linked to another table)
PetSex
PetAge
PetNotes

The Form for that is of course the same:
PetTable:

The PetTable form is linked to the CustomerTable form by CustomerID

I have two "ALMOST' identical shot forms (because I have to track different
shots for each species.

Each Shot record has

*CanineShotID/FelineShotID
PetID (=Forms!CustomerTable!PetTable.Form!PetName)
CustomerID (=Forms!CustomerTable!CustomerLastName)
and then a list of shots and dates as needed.

Likewise the forms for these two tables are:
CanineShotRecords or FelineShotRecords

So now I have this:

Private Sub ShotRecords_Click()
Dim strFormName As String

On Error GoTo Err_ShotRecords_Click

If Me!PetTable.Form!Species = "Canine" Then
strFormName = "CanineShotRecords"
Else
strFormName = "FelineShotRecords"
End If

DoCmd.OpenForm strFormName, , , "[PetID]= '" & Me![PetID] & "'"

Exit_ShotRecords_Click:
Exit Sub

Err_ShotRecords_Click:
MsgBox Err.Description
Resume Exit_ShotRecords_Click

End Sub

So I hope that helps, its all the info I know.

Thank you


Klatuu said:
I understand what it is you are trying to do. It would help if you would
answer my questions. Since I don't have the real names of things, I will
just make them up and you can put the real ones in. The reference to the
subform will not be at all clear, because referencing control values on sub
forms has to have the correct form or it will not work, so since you didn't
answer the questions, it may not work correctly.

Private Sub ShotRecords_Click()
Dim strFormName as String

On Error GoTo Err_ShotRecords_Click

If Me!subFormControlName.Form!SomeControl = "Canine" Then
strFormName = "CanineFormName"
Else
strFormName = "FelineFormName"
End If

DoCmd.OpenForm strFormName, , , "[PetID]= '" & Me![PetID] & "'"

Exit_ShotRecords_Click:
Exit Sub

Err_ShotRecords_Click:
MsgBox Err.Description
Resume Exit_ShotRecords_Click

End Sub
Click to expand...
Click to expand...
 
I now get an error message of "Microsoft Office Access can't find the field
'PetTable' referred to in your expression."

so I can only assume that the expresion should read:

If Me!CustomerTable.From!PetTable.Species ="Canine" Then
....

but I'm not sure...does any of this make sence??

Sorry to be such a pest; I have other issues if you really want a challenge,
but it will take DAYS to type out all the tables and forms and what not for
these issues.

Thanks again for all your help.

Klatuu said:
Is the code working? If not, what is the error and on which line are you
getting the error?
--
Dave Hargis, Microsoft Access MVP


ahern79 said:
Apperently I dont understand what you are asking....the forms and tables are
as follows:

CustomerTable:
*CustomerID
CustomerFirstName
CustomerLastName
CustomerAdderss1
CustomerAddress2
CustomerCity
CustomerState
CustomerZip
(and I have all the phonenumbers also)

the Form for the customers is the same:
CustomerTable:

PetTable:
*PetID
CustomerID (from CustomerTable) Indexed, dup. OK
PetName
Species (selection of Canine or Feline only)
BreedID (linked to another table)
PetSex
PetAge
PetNotes

The Form for that is of course the same:
PetTable:

The PetTable form is linked to the CustomerTable form by CustomerID

I have two "ALMOST' identical shot forms (because I have to track different
shots for each species.

Each Shot record has

*CanineShotID/FelineShotID
PetID (=Forms!CustomerTable!PetTable.Form!PetName)
CustomerID (=Forms!CustomerTable!CustomerLastName)
and then a list of shots and dates as needed.

Likewise the forms for these two tables are:
CanineShotRecords or FelineShotRecords

So now I have this:

Private Sub ShotRecords_Click()
Dim strFormName As String

On Error GoTo Err_ShotRecords_Click

If Me!PetTable.Form!Species = "Canine" Then
strFormName = "CanineShotRecords"
Else
strFormName = "FelineShotRecords"
End If

DoCmd.OpenForm strFormName, , , "[PetID]= '" & Me![PetID] & "'"

Exit_ShotRecords_Click:
Exit Sub

Err_ShotRecords_Click:
MsgBox Err.Description
Resume Exit_ShotRecords_Click

End Sub

So I hope that helps, its all the info I know.

Thank you


Klatuu said:
I understand what it is you are trying to do. It would help if you would
answer my questions. Since I don't have the real names of things, I will
just make them up and you can put the real ones in. The reference to the
subform will not be at all clear, because referencing control values on sub
forms has to have the correct form or it will not work, so since you didn't
answer the questions, it may not work correctly.

Private Sub ShotRecords_Click()
Dim strFormName as String

On Error GoTo Err_ShotRecords_Click

If Me!subFormControlName.Form!SomeControl = "Canine" Then
strFormName = "CanineFormName"
Else
strFormName = "FelineFormName"
End If

DoCmd.OpenForm strFormName, , , "[PetID]= '" & Me![PetID] & "'"

Exit_ShotRecords_Click:
Exit Sub

Err_ShotRecords_Click:
MsgBox Err.Description
Resume Exit_ShotRecords_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
OMG....I GOT IT...

that line should read...
If Me!Species ="Canine" Then
....

Sorry to be such a pest. Like I said, I have more issues with resorvations
if you'd like to help...

ahern79 said:
I now get an error message of "Microsoft Office Access can't find the field
'PetTable' referred to in your expression."

so I can only assume that the expresion should read:

If Me!CustomerTable.From!PetTable.Species ="Canine" Then
...

but I'm not sure...does any of this make sence??

Sorry to be such a pest; I have other issues if you really want a challenge,
but it will take DAYS to type out all the tables and forms and what not for
these issues.

Thanks again for all your help.

Klatuu said:
Is the code working? If not, what is the error and on which line are you
getting the error?
--
Dave Hargis, Microsoft Access MVP


ahern79 said:
Apperently I dont understand what you are asking....the forms and tables are
as follows:

CustomerTable:
*CustomerID
CustomerFirstName
CustomerLastName
CustomerAdderss1
CustomerAddress2
CustomerCity
CustomerState
CustomerZip
(and I have all the phonenumbers also)

the Form for the customers is the same:
CustomerTable:

PetTable:
*PetID
CustomerID (from CustomerTable) Indexed, dup. OK
PetName
Species (selection of Canine or Feline only)
BreedID (linked to another table)
PetSex
PetAge
PetNotes

The Form for that is of course the same:
PetTable:

The PetTable form is linked to the CustomerTable form by CustomerID

I have two "ALMOST' identical shot forms (because I have to track different
shots for each species.

Each Shot record has

*CanineShotID/FelineShotID
PetID (=Forms!CustomerTable!PetTable.Form!PetName)
CustomerID (=Forms!CustomerTable!CustomerLastName)
and then a list of shots and dates as needed.

Likewise the forms for these two tables are:
CanineShotRecords or FelineShotRecords

So now I have this:

Private Sub ShotRecords_Click()
Dim strFormName As String

On Error GoTo Err_ShotRecords_Click

If Me!PetTable.Form!Species = "Canine" Then
strFormName = "CanineShotRecords"
Else
strFormName = "FelineShotRecords"
End If

DoCmd.OpenForm strFormName, , , "[PetID]= '" & Me![PetID] & "'"

Exit_ShotRecords_Click:
Exit Sub

Err_ShotRecords_Click:
MsgBox Err.Description
Resume Exit_ShotRecords_Click

End Sub

So I hope that helps, its all the info I know.

Thank you


:

I understand what it is you are trying to do. It would help if you would
answer my questions. Since I don't have the real names of things, I will
just make them up and you can put the real ones in. The reference to the
subform will not be at all clear, because referencing control values on sub
forms has to have the correct form or it will not work, so since you didn't
answer the questions, it may not work correctly.

Private Sub ShotRecords_Click()
Dim strFormName as String

On Error GoTo Err_ShotRecords_Click

If Me!subFormControlName.Form!SomeControl = "Canine" Then
strFormName = "CanineFormName"
Else
strFormName = "FelineFormName"
End If

DoCmd.OpenForm strFormName, , , "[PetID]= '" & Me![PetID] & "'"

Exit_ShotRecords_Click:
Exit Sub

Err_ShotRecords_Click:
MsgBox Err.Description
Resume Exit_ShotRecords_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Glad you got it.
If you have additional questions, feel free to post them.
--
Dave Hargis, Microsoft Access MVP


ahern79 said:
OMG....I GOT IT...

that line should read...
If Me!Species ="Canine" Then
...

Sorry to be such a pest. Like I said, I have more issues with resorvations
if you'd like to help...

ahern79 said:
I now get an error message of "Microsoft Office Access can't find the field
'PetTable' referred to in your expression."

so I can only assume that the expresion should read:

If Me!CustomerTable.From!PetTable.Species ="Canine" Then
...

but I'm not sure...does any of this make sence??

Sorry to be such a pest; I have other issues if you really want a challenge,
but it will take DAYS to type out all the tables and forms and what not for
these issues.

Thanks again for all your help.

Klatuu said:
Is the code working? If not, what is the error and on which line are you
getting the error?
--
Dave Hargis, Microsoft Access MVP


:

Apperently I dont understand what you are asking....the forms and tables are
as follows:

CustomerTable:
*CustomerID
CustomerFirstName
CustomerLastName
CustomerAdderss1
CustomerAddress2
CustomerCity
CustomerState
CustomerZip
(and I have all the phonenumbers also)

the Form for the customers is the same:
CustomerTable:

PetTable:
*PetID
CustomerID (from CustomerTable) Indexed, dup. OK
PetName
Species (selection of Canine or Feline only)
BreedID (linked to another table)
PetSex
PetAge
PetNotes

The Form for that is of course the same:
PetTable:

The PetTable form is linked to the CustomerTable form by CustomerID

I have two "ALMOST' identical shot forms (because I have to track different
shots for each species.

Each Shot record has

*CanineShotID/FelineShotID
PetID (=Forms!CustomerTable!PetTable.Form!PetName)
CustomerID (=Forms!CustomerTable!CustomerLastName)
and then a list of shots and dates as needed.

Likewise the forms for these two tables are:
CanineShotRecords or FelineShotRecords

So now I have this:

Private Sub ShotRecords_Click()
Dim strFormName As String

On Error GoTo Err_ShotRecords_Click

If Me!PetTable.Form!Species = "Canine" Then
strFormName = "CanineShotRecords"
Else
strFormName = "FelineShotRecords"
End If

DoCmd.OpenForm strFormName, , , "[PetID]= '" & Me![PetID] & "'"

Exit_ShotRecords_Click:
Exit Sub

Err_ShotRecords_Click:
MsgBox Err.Description
Resume Exit_ShotRecords_Click

End Sub

So I hope that helps, its all the info I know.

Thank you


:

I understand what it is you are trying to do. It would help if you would
answer my questions. Since I don't have the real names of things, I will
just make them up and you can put the real ones in. The reference to the
subform will not be at all clear, because referencing control values on sub
forms has to have the correct form or it will not work, so since you didn't
answer the questions, it may not work correctly.

Private Sub ShotRecords_Click()
Dim strFormName as String

On Error GoTo Err_ShotRecords_Click

If Me!subFormControlName.Form!SomeControl = "Canine" Then
strFormName = "CanineFormName"
Else
strFormName = "FelineFormName"
End If

DoCmd.OpenForm strFormName, , , "[PetID]= '" & Me![PetID] & "'"

Exit_ShotRecords_Click:
Exit Sub

Err_ShotRecords_Click:
MsgBox Err.Description
Resume Exit_ShotRecords_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top