If then statement

[Guest]

Can someone help me with this IF then statement for this form that is built
on this query statement below:

What I am looking for is that IF the job title is RCT and their individual
score is <.75 then a popup box will appear and a notification will be in this
popup box; however, If this person is a Supervisor and their indidual score
is <.80 the popup box will appear for this one also.

SELECT [MAIN ROSTER].[Job Title], [Individual Drill Performance Data].Name,
[Individual Drill Performance Data].PERNR, [Individual Drill Performance
Data].DrillID, [Individual Drill Performance Data].IndividualScore,
[Individual Drill Performance Data].[Individual Sat?], [Individual Drill
Performance Data].ScenarioNumber, [Individual Drill Performance
Data].[Individual UnSat?], [Individual Drill Performance Data].[Is this a
106_12 Drill for this person?], [Individual Drill Performance
Data].NonParticipant FROM [MAIN ROSTER] INNER JOIN [Individual Drill
Performance Data] ON ([MAIN ROSTER].PERNR = [Individual Drill Performance
Data].PERNR) AND ([MAIN ROSTER].PERNR = [Individual Drill Performance
Data].PERNR);

Thank you.

 

[Guest]

I'm not exactly sure of where you need the IF THEN; however the sql below
should find the supervisors and RCT's that you need.

One serious tip on your naming convention: Do not use any special characters
including spaces in field, table, or other names. The only exception is the
underscore ( _ ) which is OK. "IndividualScore" is great as would be
"Individual_Score"; however, "Individual Sat?" has both a space and a
question mark that could cause problems in the future.

Also notice that I aliased the table names to clean up things some.

SELECT [MR].[Job Title],
[IDPD].[Name],
[IDPD].PERNR,
[IDPD].DrillID,
[IDPD].IndividualScore,
[IDPD].[Individual Sat?],
[IDPD].ScenarioNumber,
[IDPD].[Individual UnSat?],
[IDPD].[Is this a 106_12 Drill for this person?],
[IDPD].NonParticipant
FROM [MAIN ROSTER] AS MR,
[Individual Drill Performance Data] AS IDPD
WHERE [MR].PERNR = [IDPD].PERNR
AND [MR].[Job Title] = "Supervisor"
AND [IDPD].IndividualScore < .80 ;


SELECT [MR].[Job Title],
[IDPD].[Name],
[IDPD].PERNR,
[IDPD].DrillID,
[IDPD].IndividualScore,
[IDPD].[Individual Sat?],
[IDPD].ScenarioNumber,
[IDPD].[Individual UnSat?],
[IDPD].[Is this a 106_12 Drill for this person?],
[IDPD].NonParticipant
FROM [MAIN ROSTER] AS MR,
[Individual Drill Performance Data] AS IDPD
WHERE [MR].PERNR = [IDPD].PERNR
AND [MR].[Job Title] = " RCT"
AND [IDPD].IndividualScore < .75 ;

 

[Matthias Klaey]

Can someone help me with this IF then statement for this form that is built
on this query statement below:

What I am looking for is that IF the job title is RCT and their individual
score is <.75 then a popup box will appear and a notification will be in this
popup box; however, If this person is a Supervisor and their indidual score
is <.80 the popup box will appear for this one also.

SELECT [MAIN ROSTER].[Job Title], [Individual Drill Performance Data].Name,
[Individual Drill Performance Data].PERNR, [Individual Drill Performance
Data].DrillID, [Individual Drill Performance Data].IndividualScore,
[Individual Drill Performance Data].[Individual Sat?], [Individual Drill
Performance Data].ScenarioNumber, [Individual Drill Performance
Data].[Individual UnSat?], [Individual Drill Performance Data].[Is this a
106_12 Drill for this person?], [Individual Drill Performance
Data].NonParticipant
FROM [MAIN ROSTER] INNER JOIN [Individual Drill
Performance Data] ON ([MAIN ROSTER].PERNR = [Individual Drill Performance
Data].PERNR) AND ([MAIN ROSTER].PERNR = [Individual Drill Performance
Data].PERNR);

Thank you.

In the above SQL statement, the very last part

AND ([MAIN ROSTER].PERNR = [Individual Drill Performance

Data].PERNR)

seems to me to be superfluous (it is just a duplicate of the previous
condition).


Regarding your question, I would use code in the OnCurrent event of
the form like this:

Private Sub Form_Current()

If ([MAIN ROSTER].[Job Title] = "RCT") And _
([Individual Drill Performance Data].IndividualScore < 0.75) Then
' Open popup box for RCT
ElseIf ([MAIN ROSTER].[Job Title] = "Supervisor") And _
([Individual Drill Performance Data].IndividualScore < 0.80) Then
' Open popup box for Supervisor
End If

End Sub

(the code is not tested since I do not completely understand your
table structure)

HTH
Matthias Kläy

 

[Guest]

Jerry,

If I understand your suggestion, I need to clean the fields up so that there
are no spaces or special characters in the fields. Great but I have many
queries, forms, reports based on this field, how do I change them all at once
and is there a way to do so?
Help.
Judy

I'm not exactly sure of where you need the IF THEN; however the sql below
should find the supervisors and RCT's that you need.

One serious tip on your naming convention: Do not use any special characters
including spaces in field, table, or other names. The only exception is the
underscore ( _ ) which is OK. "IndividualScore" is great as would be
"Individual_Score"; however, "Individual Sat?" has both a space and a
question mark that could cause problems in the future.

Also notice that I aliased the table names to clean up things some.

SELECT [MR].[Job Title],
[IDPD].[Name],
[IDPD].PERNR,
[IDPD].DrillID,
[IDPD].IndividualScore,
[IDPD].[Individual Sat?],
[IDPD].ScenarioNumber,
[IDPD].[Individual UnSat?],
[IDPD].[Is this a 106_12 Drill for this person?],
[IDPD].NonParticipant
FROM [MAIN ROSTER] AS MR,
[Individual Drill Performance Data] AS IDPD
WHERE [MR].PERNR = [IDPD].PERNR
AND [MR].[Job Title] = "Supervisor"
AND [IDPD].IndividualScore < .80 ;


SELECT [MR].[Job Title],
[IDPD].[Name],
[IDPD].PERNR,
[IDPD].DrillID,
[IDPD].IndividualScore,
[IDPD].[Individual Sat?],
[IDPD].ScenarioNumber,
[IDPD].[Individual UnSat?],
[IDPD].[Is this a 106_12 Drill for this person?],
[IDPD].NonParticipant
FROM [MAIN ROSTER] AS MR,
[Individual Drill Performance Data] AS IDPD
WHERE [MR].PERNR = [IDPD].PERNR
AND [MR].[Job Title] = " RCT"
AND [IDPD].IndividualScore < .75 ;

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Can someone help me with this IF then statement for this form that is built
on this query statement below:

What I am looking for is that IF the job title is RCT and their individual
score is <.75 then a popup box will appear and a notification will be in this
popup box; however, If this person is a Supervisor and their indidual score
is <.80 the popup box will appear for this one also.

SELECT [MAIN ROSTER].[Job Title], [Individual Drill Performance Data].Name,
[Individual Drill Performance Data].PERNR, [Individual Drill Performance
Data].DrillID, [Individual Drill Performance Data].IndividualScore,
[Individual Drill Performance Data].[Individual Sat?], [Individual Drill
Performance Data].ScenarioNumber, [Individual Drill Performance
Data].[Individual UnSat?], [Individual Drill Performance Data].[Is this a
106_12 Drill for this person?], [Individual Drill Performance
Data].NonParticipant FROM [MAIN ROSTER] INNER JOIN [Individual Drill
Performance Data] ON ([MAIN ROSTER].PERNR = [Individual Drill Performance
Data].PERNR) AND ([MAIN ROSTER].PERNR = [Individual Drill Performance
Data].PERNR);

Thank you.

 

[Guest]

Hey Matthias,
I redid the query for more accurate fields in the sql statement...can you
help me build the criteria with an IF Then Else statement, please!
I want if the Job Title is RCT from the Main Roster and the IndividualScore
is <.75 then a form by the name of pop up box for RCT will popup and if it is
a supervisor <.80 then a form byt he name of pop up box for supervisor will
popup with my note.
I do appreciate any help you can give me.
Judy

SELECT [MAIN ROSTER].PERNR, [MAIN ROSTER].Name, [Individual Drill
Performance Data].DrillID, [Individual Drill Performance
Data].IndividualScore, [Job code table].Code, [MAIN ROSTER].[Job Title]
FROM [Job code table] INNER JOIN ([MAIN ROSTER] LEFT JOIN [Individual Drill
Performance Data] ON [MAIN ROSTER].PERNR = [Individual Drill Performance
Data].PERNR) ON [Job code table].Code = [MAIN ROSTER].[Job Title];

Can someone help me with this IF then statement for this form that is built
on this query statement below:

What I am looking for is that IF the job title is RCT and their individual
score is <.75 then a popup box will appear and a notification will be in this
popup box; however, If this person is a Supervisor and their indidual score
is <.80 the popup box will appear for this one also.

SELECT [MAIN ROSTER].[Job Title], [Individual Drill Performance Data].Name,
[Individual Drill Performance Data].PERNR, [Individual Drill Performance
Data].DrillID, [Individual Drill Performance Data].IndividualScore,
[Individual Drill Performance Data].[Individual Sat?], [Individual Drill
Performance Data].ScenarioNumber, [Individual Drill Performance
Data].[Individual UnSat?], [Individual Drill Performance Data].[Is this a
106_12 Drill for this person?], [Individual Drill Performance
Data].NonParticipant
FROM [MAIN ROSTER] INNER JOIN [Individual Drill
Performance Data] ON ([MAIN ROSTER].PERNR = [Individual Drill Performance
Data].PERNR) AND ([MAIN ROSTER].PERNR = [Individual Drill Performance
Data].PERNR);

Thank you.

In the above SQL statement, the very last part

AND ([MAIN ROSTER].PERNR = [Individual Drill Performance

Data].PERNR)

seems to me to be superfluous (it is just a duplicate of the previous
condition).


Regarding your question, I would use code in the OnCurrent event of
the form like this:

Private Sub Form_Current()

If ([MAIN ROSTER].[Job Title] = "RCT") And _
([Individual Drill Performance Data].IndividualScore < 0.75) Then
' Open popup box for RCT
ElseIf ([MAIN ROSTER].[Job Title] = "Supervisor") And _
([Individual Drill Performance Data].IndividualScore < 0.80) Then
' Open popup box for Supervisor
End If

End Sub

(the code is not tested since I do not completely understand your
table structure)

HTH
Matthias Kläy

 

[Matthias Klaey]

Hey Matthias,
I redid the query for more accurate fields in the sql statement...can you
help me build the criteria with an IF Then Else statement, please!
I want if the Job Title is RCT from the Main Roster and the IndividualScore
is <.75 then a form by the name of pop up box for RCT will popup and if it is
a supervisor <.80 then a form byt he name of pop up box for supervisor will
popup with my note.
I do appreciate any help you can give me.
Judy

SELECT [MAIN ROSTER].PERNR, [MAIN ROSTER].Name, [Individual Drill
Performance Data].DrillID, [Individual Drill Performance
Data].IndividualScore, [Job code table].Code, [MAIN ROSTER].[Job Title]
FROM [Job code table] INNER JOIN ([MAIN ROSTER] LEFT JOIN [Individual Drill
Performance Data] ON [MAIN ROSTER].PERNR = [Individual Drill Performance
Data].PERNR) ON [Job code table].Code = [MAIN ROSTER].[Job Title];

Hello JudyT

First, where are you using this query? Is it the record source of a
form? or is it a standalone query? In the latter case, I cannot really
help you, since it is not possible to show a form just on a query
alone. What you really want (I guess), is that if the user selects a
certain row in the query, then the requested form should be shown.
However, to implement this, you need a form with your query as the
record source, and then follow the advice that I have given you
before; namely, create an OnCurrent event with the specified
conditions below.

I am repeating the code for the OnCurrent event:

Private Sub Form_Current()

If ([MAIN ROSTER].[Job Title] = "RCT") And _
([Individual Drill Performance Data].IndividualScore < 0.75) Then
DoCmd.OpenForm "pop up box for RCT"
ElseIf ([MAIN ROSTER].[Job Title] = "Supervisor") And _
([Individual Drill Performance Data].IndividualScore < 0.80) Then
DoCmd.OpenForm "pop up box for supervisor"
End If

End Sub

Have you ever used Visual Basic code in your application?

Greetings
Matthias Kläy

Can someone help me with this IF then statement for this form that is built
on this query statement below:

What I am looking for is that IF the job title is RCT and their individual
score is <.75 then a popup box will appear and a notification will be in this
popup box; however, If this person is a Supervisor and their indidual score
is <.80 the popup box will appear for this one also.

SELECT [MAIN ROSTER].[Job Title], [Individual Drill Performance Data].Name,
[Individual Drill Performance Data].PERNR, [Individual Drill Performance
Data].DrillID, [Individual Drill Performance Data].IndividualScore,
[Individual Drill Performance Data].[Individual Sat?], [Individual Drill
Performance Data].ScenarioNumber, [Individual Drill Performance
Data].[Individual UnSat?], [Individual Drill Performance Data].[Is this a
106_12 Drill for this person?], [Individual Drill Performance
Data].NonParticipant
FROM [MAIN ROSTER] INNER JOIN [Individual Drill
Performance Data] ON ([MAIN ROSTER].PERNR = [Individual Drill Performance
Data].PERNR) AND ([MAIN ROSTER].PERNR = [Individual Drill Performance
Data].PERNR);

Thank you.

In the above SQL statement, the very last part

AND ([MAIN ROSTER].PERNR = [Individual Drill Performance

Data].PERNR)

seems to me to be superfluous (it is just a duplicate of the previous
condition).


Regarding your question, I would use code in the OnCurrent event of
the form like this:

Private Sub Form_Current()

If ([MAIN ROSTER].[Job Title] = "RCT") And _
([Individual Drill Performance Data].IndividualScore < 0.75) Then
' Open popup box for RCT
ElseIf ([MAIN ROSTER].[Job Title] = "Supervisor") And _
([Individual Drill Performance Data].IndividualScore < 0.80) Then
' Open popup box for Supervisor
End If

End Sub

(the code is not tested since I do not completely understand your
table structure)

HTH
Matthias Kläy

 

[Guest]

Hey Matthias,

I have used code before but very little. I used the Event procedure that
you wrote for me in the current properties for the subform; however, it makes
somefields loose their link. In addition when I look at the form in the open
mode, I get an error of 2465 can't find "|". What is this, can youhelp me?

Hey Matthias,
I redid the query for more accurate fields in the sql statement...can you
help me build the criteria with an IF Then Else statement, please!
I want if the Job Title is RCT from the Main Roster and the IndividualScore
is <.75 then a form by the name of pop up box for RCT will popup and if it is
a supervisor <.80 then a form byt he name of pop up box for supervisor will
popup with my note.
I do appreciate any help you can give me.
Judy

SELECT [MAIN ROSTER].PERNR, [MAIN ROSTER].Name, [Individual Drill
Performance Data].DrillID, [Individual Drill Performance
Data].IndividualScore, [Job code table].Code, [MAIN ROSTER].[Job Title]
FROM [Job code table] INNER JOIN ([MAIN ROSTER] LEFT JOIN [Individual Drill
Performance Data] ON [MAIN ROSTER].PERNR = [Individual Drill Performance
Data].PERNR) ON [Job code table].Code = [MAIN ROSTER].[Job Title];

Hello JudyT

First, where are you using this query? Is it the record source of a
form? or is it a standalone query? In the latter case, I cannot really
help you, since it is not possible to show a form just on a query
alone. What you really want (I guess), is that if the user selects a
certain row in the query, then the requested form should be shown.
However, to implement this, you need a form with your query as the
record source, and then follow the advice that I have given you
before; namely, create an OnCurrent event with the specified
conditions below.

I am repeating the code for the OnCurrent event:

Private Sub Form_Current()

If ([MAIN ROSTER].[Job Title] = "RCT") And _
([Individual Drill Performance Data].IndividualScore < 0.75) Then
DoCmd.OpenForm "pop up box for RCT"
ElseIf ([MAIN ROSTER].[Job Title] = "Supervisor") And _
([Individual Drill Performance Data].IndividualScore < 0.80) Then
DoCmd.OpenForm "pop up box for supervisor"
End If

End Sub

Have you ever used Visual Basic code in your application?

Greetings
Matthias Kläy

Can someone help me with this IF then statement for this form that is built
on this query statement below:

What I am looking for is that IF the job title is RCT and their individual
score is <.75 then a popup box will appear and a notification will be in this
popup box; however, If this person is a Supervisor and their indidual score
is <.80 the popup box will appear for this one also.

SELECT [MAIN ROSTER].[Job Title], [Individual Drill Performance Data].Name,
[Individual Drill Performance Data].PERNR, [Individual Drill Performance
Data].DrillID, [Individual Drill Performance Data].IndividualScore,
[Individual Drill Performance Data].[Individual Sat?], [Individual Drill
Performance Data].ScenarioNumber, [Individual Drill Performance
Data].[Individual UnSat?], [Individual Drill Performance Data].[Is this a
106_12 Drill for this person?], [Individual Drill Performance
Data].NonParticipant
FROM [MAIN ROSTER] INNER JOIN [Individual Drill
Performance Data] ON ([MAIN ROSTER].PERNR = [Individual Drill Performance
Data].PERNR) AND ([MAIN ROSTER].PERNR = [Individual Drill Performance
Data].PERNR);

Thank you.

In the above SQL statement, the very last part

AND ([MAIN ROSTER].PERNR = [Individual Drill Performance
Data].PERNR)

seems to me to be superfluous (it is just a duplicate of the previous
condition).


Regarding your question, I would use code in the OnCurrent event of
the form like this:

Private Sub Form_Current()

If ([MAIN ROSTER].[Job Title] = "RCT") And _
([Individual Drill Performance Data].IndividualScore < 0.75) Then
' Open popup box for RCT
ElseIf ([MAIN ROSTER].[Job Title] = "Supervisor") And _
([Individual Drill Performance Data].IndividualScore < 0.80) Then
' Open popup box for Supervisor
End If

End Sub

(the code is not tested since I do not completely understand your
table structure)

HTH
Matthias Kläy

 

[Guest]

My apologies...in line answers

Hey Matthias,
I redid the query for more accurate fields in the sql statement...can you
help me build the criteria with an IF Then Else statement, please!
I want if the Job Title is RCT from the Main Roster and the IndividualScore
is <.75 then a form by the name of pop up box for RCT will popup and if it is
a supervisor <.80 then a form byt he name of pop up box for supervisor will
popup with my note.
I do appreciate any help you can give me.
Judy

SELECT [MAIN ROSTER].PERNR, [MAIN ROSTER].Name, [Individual Drill
Performance Data].DrillID, [Individual Drill Performance
Data].IndividualScore, [Job code table].Code, [MAIN ROSTER].[Job Title]
FROM [Job code table] INNER JOIN ([MAIN ROSTER] LEFT JOIN [Individual Drill
Performance Data] ON [MAIN ROSTER].PERNR = [Individual Drill Performance
Data].PERNR) ON [Job code table].Code = [MAIN ROSTER].[Job Title];

Hello JudyT

First, where are you using this query?

This query is the record source for the form
Is it the record source of a

form? or is it a standalone query? In the latter case, I cannot really
help you, since it is not possible to show a form just on a query
alone. What you really want (I guess), is that if the user selects a
certain row in the query, then the requested form should be shown.
However, to implement this, you need a form with your query as the
record source, and then follow the advice that I have given you
before; namely, create an OnCurrent event with the specified
conditions below.

I am repeating the code for the OnCurrent event:

Private Sub Form_Current()

If ([MAIN ROSTER].[Job Title] = "RCT") And _
([Individual Drill Performance Data].IndividualScore < 0.75) Then
DoCmd.OpenForm "pop up box for RCT"
ElseIf ([MAIN ROSTER].[Job Title] = "Supervisor") And _
([Individual Drill Performance Data].IndividualScore < 0.80) Then
DoCmd.OpenForm "pop up box for supervisor"
End If

End Sub

Have you ever used Visual Basic code in your application?

Greetings
Matthias Kläy

Can someone help me with this IF then statement for this form that is built
on this query statement below:

What I am looking for is that IF the job title is RCT and their individual
score is <.75 then a popup box will appear and a notification will be in this
popup box; however, If this person is a Supervisor and their indidual score
is <.80 the popup box will appear for this one also.

SELECT [MAIN ROSTER].[Job Title], [Individual Drill Performance Data].Name,
[Individual Drill Performance Data].PERNR, [Individual Drill Performance
Data].DrillID, [Individual Drill Performance Data].IndividualScore,
[Individual Drill Performance Data].[Individual Sat?], [Individual Drill
Performance Data].ScenarioNumber, [Individual Drill Performance
Data].[Individual UnSat?], [Individual Drill Performance Data].[Is this a
106_12 Drill for this person?], [Individual Drill Performance
Data].NonParticipant
FROM [MAIN ROSTER] INNER JOIN [Individual Drill
Performance Data] ON ([MAIN ROSTER].PERNR = [Individual Drill Performance
Data].PERNR) AND ([MAIN ROSTER].PERNR = [Individual Drill Performance
Data].PERNR);

Thank you.

In the above SQL statement, the very last part

AND ([MAIN ROSTER].PERNR = [Individual Drill Performance
Data].PERNR)

seems to me to be superfluous (it is just a duplicate of the previous
condition).


Regarding your question, I would use code in the OnCurrent event of
the form like this:

Private Sub Form_Current()

If ([MAIN ROSTER].[Job Title] = "RCT") And _
([Individual Drill Performance Data].IndividualScore < 0.75) Then
' Open popup box for RCT
ElseIf ([MAIN ROSTER].[Job Title] = "Supervisor") And _
([Individual Drill Performance Data].IndividualScore < 0.80) Then
' Open popup box for Supervisor
End If

End Sub

(the code is not tested since I do not completely understand your
table structure)

HTH
Matthias Kläy

 

[Guest]

My apologies, I hit post before I was finished with my post.

When I view the form, it returns that pipe symbol and then I go to debug my
VB and the areas that are highlighted are saying that it can't find those
fields. What am i doing wrong? Please help! Desparate in Virginia!

 

[Matthias Klaey]

My apologies, I hit post before I was finished with my post.

When I view the form, it returns that pipe symbol and then I go to debug my
VB and the areas that are highlighted are saying that it can't find those
fields. What am i doing wrong? Please help! Desparate in Virginia!

Hi JudyT

no need to despair , this is just some typeo someplace. Please post
the code in your OnCurrent event, and indicate the line where the
error occurs.

Greetings, Matthias Kläy

[...]

 

[Guest]

Hey Matthias,

Here is my code for the form, which is based on a query...
Private Sub Form_Current()
If [MAIN ROSTER].[Job Title] = "RCT" And [Individual Drill Performance
Data].IndividualScore < 0.75 Then
DoCmd.OpenForm "popupboxforRCT"
ElseIf [MAIN ROSTER].[Job Title] = "Supervisor" And _
[Individual Drill Performance Data].IndividualScore < 0.8 Then
DoCmd.OpenForm "popupboxforsupervisor"
End If

End Sub

but I keep getting this Run-time error 2465
Drill data can't find the field '|' referred to in your expression. What is
this error, i have looked up and can't figure out what the error is. When i
do the debug it shows me the first line of my code as if to say the fields
are not there in this query. Help please!

My apologies, I hit post before I was finished with my post.

When I view the form, it returns that pipe symbol and then I go to debug my
VB and the areas that are highlighted are saying that it can't find those
fields. What am i doing wrong? Please help! Desparate in Virginia!

Hi JudyT

no need to despair , this is just some typeo someplace. Please post
the code in your OnCurrent event, and indicate the line where the
error occurs.

Greetings, Matthias Kläy

[...]

 

[Guest]

Okay, I think i may have figured out what is the problem. In the main roster
the job title shows a number 1 for rct but i inlcuded the job table and job
name to show rct now here is what query my form is based on:
SELECT [MAIN ROSTER].PERNR, [MAIN ROSTER].Name, [Job code table].[Job name],
[MAIN ROSTER].[Job Title], [Individual Drill Performance
Data].IndividualScore, [Individual Drill Performance Data].[Individual Sat?],
[Individual Drill Performance Data].DrillType, [Drill information].Date,
[Drill information].DrillID, [Individual Drill Performance
Data].ScenarioNumber, [Individual Drill Performance Data].[Is this a 106_12
Drill for this person?], [Individual Drill Performance
Data].[Is_this_a_REQUAL?], [Individual Drill Performance Data].[Individual
Unsat?]
FROM [Drill information] INNER JOIN ([Job code table] INNER JOIN ([MAIN
ROSTER] INNER JOIN [Individual Drill Performance Data] ON [MAIN ROSTER].PERNR
= [Individual Drill Performance Data].PERNR) ON [Job code table].Code = [MAIN
ROSTER].[Job Title]) ON [Drill information].DrillID = [Individual Drill
Performance Data].DrillID;

My apologies, I hit post before I was finished with my post.

When I view the form, it returns that pipe symbol and then I go to debug my
VB and the areas that are highlighted are saying that it can't find those
fields. What am i doing wrong? Please help! Desparate in Virginia!

Hi JudyT

no need to despair , this is just some typeo someplace. Please post
the code in your OnCurrent event, and indicate the line where the
error occurs.

Greetings, Matthias Kläy

[...]

 

[Matthias Klaey]

Okay, I think i may have figured out what is the problem. In the main roster
the job title shows a number 1 for rct but i inlcuded the job table and job
name to show rct now here is what query my form is based on:
SELECT [MAIN ROSTER].PERNR, [MAIN ROSTER].Name, [Job code table].[Job name],
[MAIN ROSTER].[Job Title], [Individual Drill Performance
Data].IndividualScore, [Individual Drill Performance Data].[Individual Sat?],
[Individual Drill Performance Data].DrillType, [Drill information].Date,
[Drill information].DrillID, [Individual Drill Performance
Data].ScenarioNumber, [Individual Drill Performance Data].[Is this a 106_12
Drill for this person?], [Individual Drill Performance
Data].[Is_this_a_REQUAL?], [Individual Drill Performance Data].[Individual
Unsat?]
FROM [Drill information] INNER JOIN ([Job code table] INNER JOIN ([MAIN
ROSTER] INNER JOIN [Individual Drill Performance Data] ON [MAIN ROSTER].PERNR
= [Individual Drill Performance Data].PERNR) ON [Job code table].Code = [MAIN
ROSTER].[Job Title]) ON [Drill information].DrillID = [Individual Drill
Performance Data].DrillID;

Each textbox on the form has a property "Name" and a property "Control
Source". (In design view of the form, right click on the textbox and
select "Properties").
The "Control Source" property contains the field name of the query.
Often, the "Name" property contains the same text as the "Control
Source" property, but not always. Basically, you can use (almost) any
text for the "Name" property.

In the code, use the "Name" property instead of the field names. You
need to use the square brackets when you have spaces in the name. It
may look like this, but you have to check this out.

Private Sub Form_Current()
If [Job name] = "RCT" And _
IndividualScore < 0.75 Then
DoCmd.OpenForm "popupboxforRCT"
ElseIf [Job name] = "Supervisor" And _
IndividualScore < 0.8 Then
DoCmd.OpenForm "popupboxforsupervisor"
End If

End Sub

Greetings, Matthias Kläy

 

[Guest]

Okay Matthis,

Time for some real confusion. I apparently did not realize that it was based
on the table. But there are several fields that are based on the main
roster. This what is there. The form is based on Individual Drill
Performance Data, which has these fields Name PERNR DrillID IndividualScore
etc. and then in this form the Name field has a query that points back to the
PERNR so that it can understand that when someone puts in their PERNR
(employee number) it is associated to that name the query is as such:
SELECT [Main Roster].PERNR, [Main Roster].Name, [Main Roster].[Job Title]
FROM [Main Roster]
ORDER BY [Main Roster].Name;

Simple right but how do I make this work in source code...help.

Okay, I think i may have figured out what is the problem. In the main roster
the job title shows a number 1 for rct but i inlcuded the job table and job
name to show rct now here is what query my form is based on:
SELECT [MAIN ROSTER].PERNR, [MAIN ROSTER].Name, [Job code table].[Job name],
[MAIN ROSTER].[Job Title], [Individual Drill Performance
Data].IndividualScore, [Individual Drill Performance Data].[Individual Sat?],
[Individual Drill Performance Data].DrillType, [Drill information].Date,
[Drill information].DrillID, [Individual Drill Performance
Data].ScenarioNumber, [Individual Drill Performance Data].[Is this a 106_12
Drill for this person?], [Individual Drill Performance
Data].[Is_this_a_REQUAL?], [Individual Drill Performance Data].[Individual
Unsat?]
FROM [Drill information] INNER JOIN ([Job code table] INNER JOIN ([MAIN
ROSTER] INNER JOIN [Individual Drill Performance Data] ON [MAIN ROSTER].PERNR
= [Individual Drill Performance Data].PERNR) ON [Job code table].Code = [MAIN
ROSTER].[Job Title]) ON [Drill information].DrillID = [Individual Drill
Performance Data].DrillID;

Each textbox on the form has a property "Name" and a property "Control
Source". (In design view of the form, right click on the textbox and
select "Properties").
The "Control Source" property contains the field name of the query.
Often, the "Name" property contains the same text as the "Control
Source" property, but not always. Basically, you can use (almost) any
text for the "Name" property.

In the code, use the "Name" property instead of the field names. You
need to use the square brackets when you have spaces in the name. It
may look like this, but you have to check this out.

Private Sub Form_Current()
If [Job name] = "RCT" And _
IndividualScore < 0.75 Then
DoCmd.OpenForm "popupboxforRCT"
ElseIf [Job name] = "Supervisor" And _
IndividualScore < 0.8 Then
DoCmd.OpenForm "popupboxforsupervisor"
End If

End Sub

Greetings, Matthias Kläy

 

[Guest]

Matthias,

I used the same form and used your query with the job name in it and it is
not giving me an error; however, when I click on the * new record, the form
area comes up blank...did i do something wrong? Or do Ineed to change some
properties somewhere?
Help!Judy

Okay, I think i may have figured out what is the problem. In the main roster
the job title shows a number 1 for rct but i inlcuded the job table and job
name to show rct now here is what query my form is based on:
SELECT [MAIN ROSTER].PERNR, [MAIN ROSTER].Name, [Job code table].[Job name],
[MAIN ROSTER].[Job Title], [Individual Drill Performance
Data].IndividualScore, [Individual Drill Performance Data].[Individual Sat?],
[Individual Drill Performance Data].DrillType, [Drill information].Date,
[Drill information].DrillID, [Individual Drill Performance
Data].ScenarioNumber, [Individual Drill Performance Data].[Is this a 106_12
Drill for this person?], [Individual Drill Performance
Data].[Is_this_a_REQUAL?], [Individual Drill Performance Data].[Individual
Unsat?]
FROM [Drill information] INNER JOIN ([Job code table] INNER JOIN ([MAIN
ROSTER] INNER JOIN [Individual Drill Performance Data] ON [MAIN ROSTER].PERNR
= [Individual Drill Performance Data].PERNR) ON [Job code table].Code = [MAIN
ROSTER].[Job Title]) ON [Drill information].DrillID = [Individual Drill
Performance Data].DrillID;

Each textbox on the form has a property "Name" and a property "Control
Source". (In design view of the form, right click on the textbox and
select "Properties").
The "Control Source" property contains the field name of the query.
Often, the "Name" property contains the same text as the "Control
Source" property, but not always. Basically, you can use (almost) any
text for the "Name" property.

In the code, use the "Name" property instead of the field names. You
need to use the square brackets when you have spaces in the name. It
may look like this, but you have to check this out.

Private Sub Form_Current()
If [Job name] = "RCT" And _
IndividualScore < 0.75 Then
DoCmd.OpenForm "popupboxforRCT"
ElseIf [Job name] = "Supervisor" And _
IndividualScore < 0.8 Then
DoCmd.OpenForm "popupboxforsupervisor"
End If

End Sub

Greetings, Matthias Kläy

 

[Matthias Klaey]

Matthias,

I used the same form and used your query with the job name in it and it is
not giving me an error; however, when I click on the * new record, the form
area comes up blank...did i do something wrong? Or do Ineed to change some
properties somewhere?
Help!Judy

Judy

It is really hard to tell what exactly is going on in your form. Would
it be possible to send me a copy of your mdb, so that I can have a
closer look? I don't need the data, so

- make a copy of the .mdb
- empty all tables
- On the menu, Tools/Database Utilities/Compact and Repair Database
- Quit Access and Zip the mdb file and send it to me.

Greetings, Matthias Kläy

[...]

 

[Guest]

Hey,

I sent it to your hotmail account.
Thank you
Judy

Matthias,

I used the same form and used your query with the job name in it and it is
not giving me an error; however, when I click on the * new record, the form
area comes up blank...did i do something wrong? Or do Ineed to change some
properties somewhere?
Help!Judy

Judy

It is really hard to tell what exactly is going on in your form. Would
it be possible to send me a copy of your mdb, so that I can have a
closer look? I don't need the data, so

- make a copy of the .mdb
- empty all tables
- On the menu, Tools/Database Utilities/Compact and Repair Database
- Quit Access and Zip the mdb file and send it to me.

Greetings, Matthias Kläy

[...]