editing fields on forms based on more than 1 table/query.

[rthur]

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

 

[Steve Schapel]

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

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

 

[rthur via AccessMonster.com]

Steve

Thanks for your help, It now works - I'm not sure where the double join came
from - like I say, a bit of a novice!

Cheers

Rich