adding form field result to query - urgent

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

Guest

I have a query that I'm using to identify audit questions as determined by a
combo I want this query to return audit questions based on a value in a combo
box on a form. I also want to add a field to the query that will populate
with PrimaryID field on that same form.

For example, Let's say I have the PreAudit form open to Audit1 (PrimaryID =
1), and it is an audit type 2 (TypeNum = 2). I want to return all questions
in the questions table where the TypeNum is 2 and I want to add a field to
the end that returns the value in PrimaryID on the PreAudit form.

I should see this:
TypeNum | QuestionText | Points | AuditID
2 | Phone | 3 | 1

Instead of getting 1 in the AuditID field, I get a square or a letter or
some random number, even though the form is open and the field displays 1.

Here is what I have in the QBE grid:
AuditID: [Forms]![PREAUDIT]![PRIMARYID]

This is from a database I created about a year ago and it worked just fine
then, but now it is failing. It was created in AccessXP (same as I'm using
now). I've tried running a database repair and even creating the database
from scratch. Why won't this work???

(e-mail address removed)

THX!
 
I have a query that I'm using to identify audit questions as determined by a
combo I want this query to return audit questions based on a value in a combo
box on a form. I also want to add a field to the query that will populate
with PrimaryID field on that same form.

For example, Let's say I have the PreAudit form open to Audit1 (PrimaryID =
1), and it is an audit type 2 (TypeNum = 2). I want to return all questions
in the questions table where the TypeNum is 2 and I want to add a field to
the end that returns the value in PrimaryID on the PreAudit form.

I should see this:
TypeNum | QuestionText | Points | AuditID
2 | Phone | 3 | 1

Instead of getting 1 in the AuditID field, I get a square or a letter or
some random number, even though the form is open and the field displays 1.

Here is what I have in the QBE grid:
AuditID: [Forms]![PREAUDIT]![PRIMARYID]

ADDED:
Here is the SQL:
SELECT Questions.TypeNum, Questions.GroupNum, Questions.SubGroupNum,
Questions.QuestionNum, Questions.Type, Questions.Group, Questions.SubGroup,
Questions.Question, Questions.Points, [Forms]![PREAUDIT]![PRIMARYID] AS
AuditID
FROM Questions
WHERE (((Questions.TypeNum)=[Forms]![PREAUDIT]![PRIMARYID]) AND
((Questions.StartDate)<Now()) AND ((Questions.EndDate)>Now()));

This is from a database I created about a year ago and it worked just fine
then, but now it is failing. It was created in AccessXP (same as I'm using
now). I've tried running a database repair and even creating the database
from scratch. Why won't this work???

(e-mail address removed)

THX!
 
Can you confirm the SQL as I would expect the where clause to reference the
combo and not the Primary ID

ie

WHERE (((Questions.TypeNum)=[Forms]![PREAUDIT]![ComboBoxShowingAuditType])
AND
((Questions.StartDate)<Now()) AND ((Questions.EndDate)>Now()));
 
I changed the name of the PrimaryID field to PriID (to see if it was
something to do with the PrimaryID name being reserved) and it still doesn't
work. Here is the SQL:
SELECT Questions.TypeNum, Questions.GroupNum, Questions.SubGroupNum,
Questions.QuestionNum, Questions.Type, Questions.Group, Questions.SubGroup,
Questions.Question, Questions.Points, Questions.StartDate, Questions.EndDate,
[Forms]![PREAUDIT]![PriID] AS Expr1
FROM Questions
WHERE (((Questions.TypeNum)=[Forms]![PREAUDIT]![AUDITTYPE]));

You can see it compares the TypeNum field from the Questions table to the
AuditType of the PreAudit form and returns all questions that match. It is
supposed to also place the PrimaryID of the record (PriID) from the source
form at the end of the query. Instead, it returns a square.

I'm trying to have the user enter data into an Audit table (User, Date, Type
of Audit, etc.). Once that is entered, I'm trying to have the database pull
the applicable questions from the Questions table and append them to an
AuditResult table. I will then open up a form based on the Audit table with
a subform based on the AuditResult table where the PrimaryID's match
(allowing the user to then update the results of the audit).

I need to get the PrimaryID (PriID) field from the 1st form to list as the
last field in the query. What am I doing wrong?

Keep in mind that I am using the QBE grid, as our company doesn't permit
coding directly in SQL.

THX!

JohnFol said:
Can you confirm the SQL as I would expect the where clause to reference the
combo and not the Primary ID

ie

WHERE (((Questions.TypeNum)=[Forms]![PREAUDIT]![ComboBoxShowingAuditType])
AND
((Questions.StartDate)<Now()) AND ((Questions.EndDate)>Now()));




Robert_L_Ross said:
I have a query that I'm using to identify audit questions as determined by
a
combo I want this query to return audit questions based on a value in a
combo
box on a form. I also want to add a field to the query that will populate
with PrimaryID field on that same form.

For example, Let's say I have the PreAudit form open to Audit1 (PrimaryID
=
1), and it is an audit type 2 (TypeNum = 2). I want to return all
questions
in the questions table where the TypeNum is 2 and I want to add a field to
the end that returns the value in PrimaryID on the PreAudit form.

I should see this:
TypeNum | QuestionText | Points | AuditID
2 | Phone | 3 | 1

Instead of getting 1 in the AuditID field, I get a square or a letter or
some random number, even though the form is open and the field displays 1.

Here is what I have in the QBE grid:
AuditID: [Forms]![PREAUDIT]![PRIMARYID]

ADDED:
Here is the SQL:
SELECT Questions.TypeNum, Questions.GroupNum, Questions.SubGroupNum,
Questions.QuestionNum, Questions.Type, Questions.Group,
Questions.SubGroup,
Questions.Question, Questions.Points, [Forms]![PREAUDIT]![PRIMARYID] AS
AuditID
FROM Questions
WHERE (((Questions.TypeNum)=[Forms]![PREAUDIT]![PRIMARYID]) AND
((Questions.StartDate)<Now()) AND ((Questions.EndDate)>Now()));

This is from a database I created about a year ago and it worked just fine
then, but now it is failing. It was created in AccessXP (same as I'm
using
now). I've tried running a database repair and even creating the database
from scratch. Why won't this work???

(e-mail address removed)

THX!
Click to expand...
Click to expand...
 
NEVERMIND! I found it. For some reason, I had to specify a parameter of
"[Forms]![PREAUDIT]![PriID]" as double.

I always thought you only needed that when the field you were returning was
entered by a user - not from the table. Anyway, I added that parameter and
it worked like a charm (so all you other newbies, here's a note to remember).

JohnFol, thx for the help, making me go back to the SQL gave me the idea to
try that, so I'm still giving u credit for the answer, hehe

Robert_L_Ross said:
I changed the name of the PrimaryID field to PriID (to see if it was
something to do with the PrimaryID name being reserved) and it still doesn't
work. Here is the SQL:
SELECT Questions.TypeNum, Questions.GroupNum, Questions.SubGroupNum,
Questions.QuestionNum, Questions.Type, Questions.Group, Questions.SubGroup,
Questions.Question, Questions.Points, Questions.StartDate, Questions.EndDate,
[Forms]![PREAUDIT]![PriID] AS Expr1
FROM Questions
WHERE (((Questions.TypeNum)=[Forms]![PREAUDIT]![AUDITTYPE]));

You can see it compares the TypeNum field from the Questions table to the
AuditType of the PreAudit form and returns all questions that match. It is
supposed to also place the PrimaryID of the record (PriID) from the source
form at the end of the query. Instead, it returns a square.

I'm trying to have the user enter data into an Audit table (User, Date, Type
of Audit, etc.). Once that is entered, I'm trying to have the database pull
the applicable questions from the Questions table and append them to an
AuditResult table. I will then open up a form based on the Audit table with
a subform based on the AuditResult table where the PrimaryID's match
(allowing the user to then update the results of the audit).

I need to get the PrimaryID (PriID) field from the 1st form to list as the
last field in the query. What am I doing wrong?

Keep in mind that I am using the QBE grid, as our company doesn't permit
coding directly in SQL.

THX!

JohnFol said:
Can you confirm the SQL as I would expect the where clause to reference the
combo and not the Primary ID

ie

WHERE (((Questions.TypeNum)=[Forms]![PREAUDIT]![ComboBoxShowingAuditType])
AND
((Questions.StartDate)<Now()) AND ((Questions.EndDate)>Now()));




Robert_L_Ross said:
I have a query that I'm using to identify audit questions as determined by
a
combo I want this query to return audit questions based on a value in a
combo
box on a form. I also want to add a field to the query that will populate
with PrimaryID field on that same form.

For example, Let's say I have the PreAudit form open to Audit1 (PrimaryID
=
1), and it is an audit type 2 (TypeNum = 2). I want to return all
questions
in the questions table where the TypeNum is 2 and I want to add a field to
the end that returns the value in PrimaryID on the PreAudit form.

I should see this:
TypeNum | QuestionText | Points | AuditID
2 | Phone | 3 | 1

Instead of getting 1 in the AuditID field, I get a square or a letter or
some random number, even though the form is open and the field displays 1.

Here is what I have in the QBE grid:
AuditID: [Forms]![PREAUDIT]![PRIMARYID]

ADDED:
Here is the SQL:
SELECT Questions.TypeNum, Questions.GroupNum, Questions.SubGroupNum,
Questions.QuestionNum, Questions.Type, Questions.Group,
Questions.SubGroup,
Questions.Question, Questions.Points, [Forms]![PREAUDIT]![PRIMARYID] AS
AuditID
FROM Questions
WHERE (((Questions.TypeNum)=[Forms]![PREAUDIT]![PRIMARYID]) AND
((Questions.StartDate)<Now()) AND ((Questions.EndDate)>Now()));

This is from a database I created about a year ago and it worked just fine
then, but now it is failing. It was created in AccessXP (same as I'm
using
now). I've tried running a database repair and even creating the database
from scratch. Why won't this work???

(e-mail address removed)

THX!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top