RowSource to limit values in a field

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

Guest

I am using Access 2003 and am trying to figure out the RowSource for a field
in one of my tables. I am trying to limit the values that can be entered in
said field.

I have created a query with the following SQL:

SELECT P.pkSampleID, c.pkSampleID, c.SampleName
FROM tblSamples AS P INNER JOIN tblSamples AS c ON P.pkSampleID =
c.RepSampleID
WHERE (((c.pkSampleID)=6969) AND ((c.SampleName)="AW010") AND
((c.fkpkSiteID)=[P].[fkpkSiteID]) AND
((Int([c].[StartDate]))=Int([P].[StartDate])));

This query captures the exact data I want except, two of the criteria are
"hard coded" (c.pkSampleID=6969 and c.SampleName="AW010"). I would like to
use whatever the pkSampleID and SampleName are in the current record. Also,
once I figure out the correct syntax, should use a saved query or the
SQLstring as the RowSource. Can anyone offer a suggestion?

Thanks,
Wendy
 
Hi Wendy

It sounds like you are setting up a combo-box directly in the table design.

Personally, I would avoid this like the plague. Also, I don't believe you
can refer to values of other fields already entered for that row.

Take a look at http://www.mvps.org/access/tencommandments.htm especially
rule 2 re: lookups.

Generally, you should not allow data entry directly into a table - only
through forms and it is there that you can use your SQL as a row source for a
combo-box and you will be able to refer to values in other fields of the same
row by using parameters like...

Me.pkSampleID & Me.SampleName

Regards

Andy Hull
 
Andy,

Yes, I agree with not using a lookup and have seen the rules in the link you
provided. Also, users won't ever be entering data directly into the tables.
However, I would like to implement the business rule at the table level but
am not sure it is even possible.

Wendy

Andy Hull said:
Hi Wendy

It sounds like you are setting up a combo-box directly in the table design.

Personally, I would avoid this like the plague. Also, I don't believe you
can refer to values of other fields already entered for that row.

Take a look at http://www.mvps.org/access/tencommandments.htm especially
rule 2 re: lookups.

Generally, you should not allow data entry directly into a table - only
through forms and it is there that you can use your SQL as a row source for a
combo-box and you will be able to refer to values in other fields of the same
row by using parameters like...

Me.pkSampleID & Me.SampleName

Regards

Andy Hull


Wendy V said:
I am using Access 2003 and am trying to figure out the RowSource for a field
in one of my tables. I am trying to limit the values that can be entered in
said field.

I have created a query with the following SQL:

SELECT P.pkSampleID, c.pkSampleID, c.SampleName
FROM tblSamples AS P INNER JOIN tblSamples AS c ON P.pkSampleID =
c.RepSampleID
WHERE (((c.pkSampleID)=6969) AND ((c.SampleName)="AW010") AND
((c.fkpkSiteID)=[P].[fkpkSiteID]) AND
((Int([c].[StartDate]))=Int([P].[StartDate])));

This query captures the exact data I want except, two of the criteria are
"hard coded" (c.pkSampleID=6969 and c.SampleName="AW010"). I would like to
use whatever the pkSampleID and SampleName are in the current record. Also,
once I figure out the correct syntax, should use a saved query or the
SQLstring as the RowSource. Can anyone offer a suggestion?

Thanks,
Wendy
Click to expand...
Click to expand...
 
Hi Wendy

I see (I think!)

I could get it to work based on other already existing rows (as you probably
could too) but can see no way to refer to the current row because while it's
being entered it doesn't exist yet in order to be queried in any way.

I am not totally up to speed with why you need to validate at the table
level unless the data is being added via a query or code but in that case it
will bypass any lookup restrictions.

Post back if you find a way!

Regards

Andy Hull


Wendy V said:
Andy,

Yes, I agree with not using a lookup and have seen the rules in the link you
provided. Also, users won't ever be entering data directly into the tables.
However, I would like to implement the business rule at the table level but
am not sure it is even possible.

Wendy

Andy Hull said:
Hi Wendy

It sounds like you are setting up a combo-box directly in the table design.

Personally, I would avoid this like the plague. Also, I don't believe you
can refer to values of other fields already entered for that row.

Take a look at http://www.mvps.org/access/tencommandments.htm especially
rule 2 re: lookups.

Generally, you should not allow data entry directly into a table - only
through forms and it is there that you can use your SQL as a row source for a
combo-box and you will be able to refer to values in other fields of the same
row by using parameters like...

Me.pkSampleID & Me.SampleName

Regards

Andy Hull


Wendy V said:
I am using Access 2003 and am trying to figure out the RowSource for a field
in one of my tables. I am trying to limit the values that can be entered in
said field.

I have created a query with the following SQL:

SELECT P.pkSampleID, c.pkSampleID, c.SampleName
FROM tblSamples AS P INNER JOIN tblSamples AS c ON P.pkSampleID =
c.RepSampleID
WHERE (((c.pkSampleID)=6969) AND ((c.SampleName)="AW010") AND
((c.fkpkSiteID)=[P].[fkpkSiteID]) AND
((Int([c].[StartDate]))=Int([P].[StartDate])));

This query captures the exact data I want except, two of the criteria are
"hard coded" (c.pkSampleID=6969 and c.SampleName="AW010"). I would like to
use whatever the pkSampleID and SampleName are in the current record. Also,
once I figure out the correct syntax, should use a saved query or the
SQLstring as the RowSource. Can anyone offer a suggestion?

Thanks,
Wendy
Click to expand...
Click to expand...
Click to expand...
 
I can't figure out what you are trying to do. Are you trying to define a
self referencing relationship? An example of this would be SupervisorID in
the tblEmployee. You would want to limit the value in SupervisorID to the
set of valid values in EmployeeID. This is done by adding the employee
table to the relationships window twice and drawing join lines between the
supervisorID in instance 1 and the EmployeeID in instance 2. You then
select the enforce RI (Referential Integrity) box to make the engine do this
for you.

I prefer to use querydefs so I can reuse queries and I have a documenter to
get fields out of them. I haven't gotten around to picking up SQL strings
in the RecordSource or RowSource properties. People who prefer SQL strings
in the rowsource say it reduces database clutter. In any event, they are
effectively the same since Access generates hidden querydefs in which to
store the SQL strings. So, take your choice.
 
Back
Top