Re: editing fields on forms based on more than 1 table/query.
- From: Steve Schapel <schapel@xxxxxxxxxxx>
- Date: Tue, 17 Oct 2006 07:23:01 +1300
Rich,
There seems to be no reason for the DistinctRow in this query. In design view of the query, you can turn this off by right-clicking anywhere on the background of the upper panel of the query design window, select Properties from the popup menu, and set the Unique Records property to No.
There is also an odd thing in your SQL where the join between the tables is doubled up. I don't think I have seen this before, and can't think of an explanation. It should be like this...
SELECT [Daily Vehicle Log].[Record Number], [Daily Vehicle Log].Date, [Daily Vehicle Log].Name, [Daily Vehicle Log].[Registration Number], [Daily Vehicle Log].[Tacho Received], Vehicles.[Tacho Type]
FROM Vehicles INNER JOIN [Daily Vehicle Log] ON Vehicles.[Registration Number] = [Daily Vehicle Log].[Registration Number]
WHERE ((([Daily Vehicle Log].[Tacho Received])=No) AND ((Vehicles.[Tacho Type])<>"N/A"));
Even then, this query will only be updateable if the [Registration Number] field in your Vehicles table is unique. Is this the Primary Key field in the table? Otherwise, go to the design view of the table, and set the Indexed property to Yes (No Duplicates).
By the way, as an aside, 'name' and 'date' are Reserved Words (i.e. have a special meaning) in Access, and as such should not be used as the names of fields or controls. I would recommend you change this.
--
Steve Schapel, Microsoft Access MVP
rthur wrote:
I'm fairly new to access and I'm trying to build a database which is easy for.
other users. I've set up tables for Vehicles, where the field "Tacho Type" has a combo lookup to table Tacho
Type, which has 3 choices. I have another table called Daily Vehicle Log,
which has a field named "Name" and another field called "Registration Number",
both of which lookup values from the Driver table and Vehicles table
respectively:
Daily Vehicle Log - Table
Date
Name - combo box lookup from driver table
Registration Number - combo box lookup fom vehicle table
Tacho Received
Driver - Table
Name
Telephone Number
Vehicles - Table
Registration Number Tacho Type - combo box lookup fom tacho type table
Tacho Type - Table
Tacho Type - "analogue", "digital", "N/A"
I'm now trying to build a form based on a query, which displays all records
where the "tacho received" = no and the "tacho type" <> "N/A and is editable
so you can check the tacho received box.
The following sql works but returns all tacho type.
SELECT DISTINCTROW [Daily Vehicle Log].[Record Number], [Daily Vehicle Log].
Date, [Daily Vehicle Log].Name, [Daily Vehicle Log].[Registration Number],
[Daily Vehicle Log].[Tacho Received]
FROM [Daily Vehicle Log], [Tacho Type]
WHERE ((([Daily Vehicle Log].[Tacho Received])=No));
But when I add the Vehicles Table and query the tacho type, it displays
correctly but I'n not abble to edit it. i.e check the tacho received yes/no
box.
SELECT DISTINCTROW [Daily Vehicle Log].[Record Number], [Daily Vehicle Log].
Date, [Daily Vehicle Log].Name, [Daily Vehicle Log].[Registration Number],
[Daily Vehicle Log].[Tacho Received], Vehicles.[Tacho Type]
FROM Vehicles INNER JOIN [Daily Vehicle Log] ON (Vehicles.[Registration
Number] = [Daily Vehicle Log].[Registration Number]) AND (Vehicles.
[Registration Number] = [Daily Vehicle Log].[Registration Number])
WHERE ((([Daily Vehicle Log].[Tacho Received])=No) AND ((Vehicles.[Tacho Type]
)<>"N/A"));
Any help is much apprieciated. I've found so much useful information on
this site but i'm stumped on this one.
Cheers
Rich
- References:
- Prev by Date: Re: Form overlay
- Next by Date: Re: Control the value of one field based on date in another field
- Previous by thread: editing fields on forms based on more than 1 table/query.
- Next by thread: Re: editing fields on forms based on more than 1 table/query.
- Index(es):
Relevant Pages
|
|
