Subforms

[Guest]

Hi

I am creating a database to track drivers of vehicles. The drivers will be
identified by the vehicle they are seen driving.

I am using three tables:
vehicle details (VEHICLE ID is the key)
driver names and information (DRIVER ID is the key)
Known characteristics of each driver. There is a fixed list of 19
characteristics, any or all of which may be assigned to each driver (CHAR ID
is the key)

Each vehicle can have multiple drivers, and each driver can drive multiple
vehicles, so I have set up a many to many relationship using a junction table.
Each driver can have one or multiple characteristics and each characteristic
can belong to many drivers, so I have set up another many to many
relationship using another junction table.

I am displaying vehicle information on a single form, within which there is
a continous subform to show drivers plus their information for that
particular vehicle. I need to be able to show the known characteristics for
each driver of the vehicle on the same screen display. Access does not allow
me to display "driver characteristics" as a continous subform within the
driver information subform as you cannot have a subform within a continuous
form. In terms of presentation of the form, I need to keep the "driver
characteristics" listed exactly next to each driver. Once there are lots of
drivers associated with each vehicle, if scroll bars appear, when you scroll
down the list I need the driver characteristics to scroll too to show
relevant information. I therefore assume the driver info and characteristics
need to be on the same form.

How can I get around this? Can I use some sort of query? Or do I need to
somehow list the driver characteristics within the driver information table?
(I'm not sure how to do this as I thought that a separate many to many
relationship was needed because up to 19 characteristics may be associated
woth each driver, and these may change over time.)

Looking forward to your advice and expertise.
Thanks
Rich

 

[Dirk Goldgar]

Hi

I am creating a database to track drivers of vehicles. The drivers
will be identified by the vehicle they are seen driving.

I am using three tables:
vehicle details (VEHICLE ID is the key)
driver names and information (DRIVER ID is the key)
Known characteristics of each driver. There is a fixed list of 19
characteristics, any or all of which may be assigned to each driver
(CHAR ID is the key)

Each vehicle can have multiple drivers, and each driver can drive
multiple vehicles, so I have set up a many to many relationship using
a junction table. Each driver can have one or multiple
characteristics and each characteristic can belong to many drivers,
so I have set up another many to many relationship using another
junction table.

I am displaying vehicle information on a single form, within which
there is
a continous subform to show drivers plus their information for that
particular vehicle. I need to be able to show the known
characteristics for each driver of the vehicle on the same screen
display. Access does not allow me to display "driver
characteristics" as a continous subform within the driver information
subform as you cannot have a subform within a continuous form. In
terms of presentation of the form, I need to keep the "driver
characteristics" listed exactly next to each driver. Once there are
lots of drivers associated with each vehicle, if scroll bars appear,
when you scroll down the list I need the driver characteristics to
scroll too to show relevant information. I therefore assume the
driver info and characteristics need to be on the same form.

How can I get around this? Can I use some sort of query? Or do I
need to somehow list the driver characteristics within the driver
information table? (I'm not sure how to do this as I thought that a
separate many to many relationship was needed because up to 19
characteristics may be associated woth each driver, and these may
change over time.)

Looking forward to your advice and expertise.
Thanks
Rich

This may be a case where you need to present a denormalized view of your
data. What sort of information is in the Characteristics table? You
may need to use a function like the fConcatChild function posted at

http://www.mvps.org/access/modules/mdl0004.htm

to return all the characteristics for each driver, concatenated together
into a string that you can show in a text box on the subform. That
won't be fast, but it will probably be acceptable.

 

[Guest]

Hi Dirk

Thanks for your message. The information in the Characteristics table is
simply one field with 19 short entries (3 words max.)
This is slightly different from the Northwind example mentioned on the link
you gave because I need to get information from the Characteristics table
(for each driver), which is related to the driver/characteristics junction
table.

The main form is vehicles and the subform is drivers (related to vehicles
via a many to many relationship.) So which table do I need to use as the
"parent" in place of the Orders table in the example (Drivers, presumably.)
And then, which table do I specify as the "child" table -
driver/characteristics junction table, presumably (containing driver ID as a
number and categories ID as a number)?... but the data I need to retrieve is
the characteristics themselves which are of course in the characteristics
table...

I am a relative newcomer to Access and am confused! I copy the code into a
module - right? And then I amend the SQL code to suit the tables and fields
relevant to this case and put them in a text box on the drivers subform..
right?

Help!

Thank you

Richard

 

[Dirk Goldgar]

Hi Dirk

Thanks for your message. The information in the Characteristics
table is simply one field with 19 short entries (3 words max.)
This is slightly different from the Northwind example mentioned on
the link you gave because I need to get information from the
Characteristics table (for each driver), which is related to the
driver/characteristics junction table.

Yes, it is slightly different because of the additional table in the
middle.

The main form is vehicles and the subform is drivers (related to
vehicles via a many to many relationship.) So which table do I need
to use as the "parent" in place of the Orders table in the example
(Drivers, presumably.) And then, which table do I specify as the
"child" table - driver/characteristics junction table, presumably
(containing driver ID as a number and categories ID as a number)?...
but the data I need to retrieve is the characteristics themselves
which are of course in the characteristics table...

I am a relative newcomer to Access and am confused!

Sorry. Let's see if I can get you unconfused.

I copy the code into a module - right?

Right, but I think we'll want to adapt it to the circumstances. I'll
put a special version of the code below, designed to suit your
situation.

And then I amend the SQL code to suit the
tables and fields relevant to this case and put them in a text box on
the drivers subform.. right?
Right.

Help!

On its way.

For the revised function code below, I'm going to make certain
assumptions about the names and fields of your tables. You'll have to
change them in my code and SQL examples to match those you actually are
using. My assumptions are:

Table name: Vehicles
Field name: VehicleID (primary key, numeric)

Table name: Drivers
Field name: DriverID (primary key, numeric)

Table name: Characteristics
Field name: CharID (primary key, numeric)
Field name: Characteristic (text)

Table name: VehiclesDrivers
Field name: VehicleID (foreign key to Vehicles)
Field name: DriverID (foreign key to Drivers)

Table name: DriversCharacteristics
Field name: DriverID (foreign key to Drivers)
Field name: CharID (foreign key to Characteristics)

Note that the table and field names I've assumed don't contain any
embedded spaces. If yours do, I recommend that you get rid of them
(changing forms and queries as necessary). If your names contain spaces
(or other nonstandard characters), you'll always have to surround such
names with the square brackets ([]) in SQL or code, and that leaves lots
of opportunities for error.

The Function:
Copy the function below into a standard module. It can be a new module
or an existing one, and you can name it anything you like, so long as
the name of the module isn't the same as that of any other public
name -- specifically, don't make the mistake of naming the module the
same as the function. I'd suggest something like "basUtilities" or
"modUtilities", but there's nothing really wrong with "Module1", as
Access proposes.

'----- start of code -----
Function fncDriverCharacteristics(varDriverID As Variant) _
As String

' Returns a string listing the characteristics of the
' given driver, in a comma-separated format.
'
' Written by: Dirk Goldgar, 6-Sep-2005
' Published for free use.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strCharacteristics As String
Dim strSQL As String

On Error GoTo Err_Handler

If IsNull(varDriverID) Then
Exit Function
End If

strSQL = _
"SELECT Characteristic " & _
"FROM DriversCharacteristics INNER JOIN Characteristics " & _
"ON DriversCharacteristics.CharID = Characteristics.CharID " & _
"WHERE DriversCharacteristics.DriverID=" & varDriverID

Set db = Application.DBEngine.Workspaces(0).Databases(0)
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

With rs
Do Until .EOF
strCharacteristics = strCharacteristics & ", " & .Fields(0)
.MoveNext
Loop
.Close
End With

' If the string isn't empty, trim off the leading comma and space.
If Len(strCharacteristics) > 0 Then
fncDriverCharacteristics = Mid(strCharacteristics, 3)
End If

Exit_Point:
Set rs = Nothing
Set db = Nothing
Exit Function

Err_Handler:
Debug.Print Err.Number, Err.Description
Resume Exit_Point

End Function
'----- end of code -----

Now set the recordsource query for your subform to something like

SELECT *,
fncDriverCharacteristics(VehiclesDrivers.DriverID)
As Characteristics
FROM VehiclesDrivers;

Note that you may possibly have a more complex query for the subform's
recordsource, joining the Drivers table to DriversCharacters so as to
pick up more info about the driver, but the significant point above is
the addition of the calculated field "Characteristics". Once you have
that field defined in the query, you can add a text box to your subform
to show this field, listing the driver's characteristics.

That calculated field won't be updatable, so you can't use it as a means
to update the driver's characteristics. For that you have to build a
different mechanism, possibly a form that you would pop up when the user
double-clicks on the text box.

 

[Guest]

Hi Dirk

Thank you for your post and code. I have been away for 2 days and have
tried your suggestions today.
I have put your code in a module. Here is the existing query (before
Characteristics are added to the query)in the subform which displays driver
information in the continuous subform for the vehicle displayed in the main
form:

SELECT Drivers.Surname, Drivers.[First Names], Drivers.DOB, Drivers.Sex, *
FROM Drivers INNER JOIN VehiclesDrivers ON
Drivers.DriverID=VehiclesDrivers.DriverID;

The SQL Statement Query Builder previously generated this SQL before I
thought about how to include driver characteristics.

You suggest the following query:
SELECT *,
fncDriverCharacteristics(VehiclesDrivers.DriverID)
As Characteristics
FROM VehiclesDrivers;

How do I combine these two together into one query? I'm having difficulty
because I can't seem to do this using the query builder. When I even just
show the DriverCharacteristics table together with the existing query in the
top part of the builder screen, the subform ends up showing each driver many
times (once for every characteristic they have.) This is before I try to add
the function into the query.

In the SQL statement builder, the Function does not appear (unless I have
typed the SQL you suggest directly into the recordsource. But doing it this
way, I can't work out how to incoroporate the existing SQL (which brings up
the driver info for the vehicle.) I think this is the final obstacle because
all of the data is laid out correctly in tables and when I look at
subdatasheets for related tables they all contain the correct data, and there
are entries for specific driver characteristics.

I don't think it makes any difference, but on the SQL Statement Builder, the
Drivers Table's Primary key, DriverID, has a one to many relationship with
two separate junction tables: DriverCharacteristics and VehiclesDriver. This
is what is confusing me.

There are no spaces in the field names. Vehicles Table Primary key is a
unique text field (license plate) but all of your other assumptions are as
you expected.

Thank you Dirk. You are a lifeline and fountain of knowledge!

Rich

Hi Dirk

Thanks for your message. The information in the Characteristics
table is simply one field with 19 short entries (3 words max.)
This is slightly different from the Northwind example mentioned on
the link you gave because I need to get information from the
Characteristics table (for each driver), which is related to the
driver/characteristics junction table.

Yes, it is slightly different because of the additional table in the
middle.

The main form is vehicles and the subform is drivers (related to
vehicles via a many to many relationship.) So which table do I need
to use as the "parent" in place of the Orders table in the example
(Drivers, presumably.) And then, which table do I specify as the
"child" table - driver/characteristics junction table, presumably
(containing driver ID as a number and categories ID as a number)?...
but the data I need to retrieve is the characteristics themselves
which are of course in the characteristics table...

I am a relative newcomer to Access and am confused!

Sorry. Let's see if I can get you unconfused.

I copy the code into a module - right?

Right, but I think we'll want to adapt it to the circumstances. I'll
put a special version of the code below, designed to suit your
situation.

And then I amend the SQL code to suit the
tables and fields relevant to this case and put them in a text box on
the drivers subform.. right?
Right.

Help!

On its way.

For the revised function code below, I'm going to make certain
assumptions about the names and fields of your tables. You'll have to
change them in my code and SQL examples to match those you actually are
using. My assumptions are:

Table name: Vehicles
Field name: VehicleID (primary key, numeric)

Table name: Drivers
Field name: DriverID (primary key, numeric)

Table name: Characteristics
Field name: CharID (primary key, numeric)
Field name: Characteristic (text)

Table name: VehiclesDrivers
Field name: VehicleID (foreign key to Vehicles)
Field name: DriverID (foreign key to Drivers)

Table name: DriversCharacteristics
Field name: DriverID (foreign key to Drivers)
Field name: CharID (foreign key to Characteristics)

Note that the table and field names I've assumed don't contain any
embedded spaces. If yours do, I recommend that you get rid of them
(changing forms and queries as necessary). If your names contain spaces
(or other nonstandard characters), you'll always have to surround such
names with the square brackets ([]) in SQL or code, and that leaves lots
of opportunities for error.

The Function:
Copy the function below into a standard module. It can be a new module
or an existing one, and you can name it anything you like, so long as
the name of the module isn't the same as that of any other public
name -- specifically, don't make the mistake of naming the module the
same as the function. I'd suggest something like "basUtilities" or
"modUtilities", but there's nothing really wrong with "Module1", as
Access proposes.

'----- start of code -----
Function fncDriverCharacteristics(varDriverID As Variant) _
As String

' Returns a string listing the characteristics of the
' given driver, in a comma-separated format.
'
' Written by: Dirk Goldgar, 6-Sep-2005
' Published for free use.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strCharacteristics As String
Dim strSQL As String

On Error GoTo Err_Handler

If IsNull(varDriverID) Then
Exit Function
End If

strSQL = _
"SELECT Characteristic " & _
"FROM DriversCharacteristics INNER JOIN Characteristics " & _
"ON DriversCharacteristics.CharID = Characteristics.CharID " & _
"WHERE DriversCharacteristics.DriverID=" & varDriverID

Set db = Application.DBEngine.Workspaces(0).Databases(0)
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

With rs
Do Until .EOF
strCharacteristics = strCharacteristics & ", " & .Fields(0)
.MoveNext
Loop
.Close
End With

' If the string isn't empty, trim off the leading comma and space.
If Len(strCharacteristics) > 0 Then
fncDriverCharacteristics = Mid(strCharacteristics, 3)
End If

Exit_Point:
Set rs = Nothing
Set db = Nothing
Exit Function

Err_Handler:
Debug.Print Err.Number, Err.Description
Resume Exit_Point

End Function
'----- end of code -----

Now set the recordsource query for your subform to something like

SELECT *,
fncDriverCharacteristics(VehiclesDrivers.DriverID)
As Characteristics
FROM VehiclesDrivers;

Note that you may possibly have a more complex query for the subform's
recordsource, joining the Drivers table to DriversCharacters so as to
pick up more info about the driver, but the significant point above is
the addition of the calculated field "Characteristics". Once you have
that field defined in the query, you can add a text box to your subform
to show this field, listing the driver's characteristics.

That calculated field won't be updatable, so you can't use it as a means
to update the driver's characteristics. For that you have to build a
different mechanism, possibly a form that you would pop up when the user
double-clicks on the text box.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Hi Dirk

Thank you for your post and code. I have been away for 2 days and
have tried your suggestions today.
I have put your code in a module. Here is the existing query (before
Characteristics are added to the query)in the subform which displays
driver information in the continuous subform for the vehicle
displayed in the main form:

SELECT Drivers.Surname, Drivers.[First Names], Drivers.DOB,
Drivers.Sex, * FROM Drivers INNER JOIN VehiclesDrivers ON
Drivers.DriverID=VehiclesDrivers.DriverID;

Okay, I see that you are joining the Drivers table to VehiclesDrivers so
that you can pick up some info about the driver. That's fine, though
you are including "*" (all fields) on top of those, which is excessive.

The SQL Statement Query Builder previously generated this SQL before I
thought about how to include driver characteristics.

You suggest the following query:
SELECT *,
fncDriverCharacteristics(VehiclesDrivers.DriverID)
As Characteristics
FROM VehiclesDrivers;

How do I combine these two together into one query? I'm having
difficulty because I can't seem to do this using the query builder.
When I even just show the DriverCharacteristics table together with
the existing query in the top part of the builder screen, the subform
ends up showing each driver many times (once for every characteristic
they have.) This is before I try to add the function into the query.

You don't want to include the DriverCharacteristics table in the query,
because the function will be rolling up all the related characteristics
into a single field. Try this:

SELECT
VehiclesDrivers.*,
Drivers.Surname,
Drivers.[First Names],
Drivers.DOB,
Drivers.Sex
fncDriverCharacteristics(VehiclesDrivers.DriverID)
As Characteristics
FROM
VehiclesDrivers
INNER JOIN
Drivers
ON
VehiclesDrivers.DriverID=Drivers.DriverID;

I don't think it makes any difference, but on the SQL Statement
Builder, the Drivers Table's Primary key, DriverID, has a one to many
relationship with two separate junction tables: DriverCharacteristics
and VehiclesDriver. This is what is confusing me.

It would, but that's not important because we're not going to include
DriverCharacteristics in the query.

There are no spaces in the field names.

Oh yeah? What about Drivers.[First Names]?

Vehicles Table Primary key
is a unique text field (license plate) but all of your other
assumptions are as you expected.

I don't think that difference is going to matter.

Thank you Dirk. You are a lifeline and fountain of knowledge!

LOL Let's see how this works out before you start getting flowery.

 

[Guest]

I tried typing your suggested query in the control source of the subform.
First I got this error when I put the form in form view:
Syntax error (missing operator) in query expression 'Drivers.Sex
fncDriverCharacteristics (VehicleDrivers.DriverID)

I tried putting a comma after Drivers.Sex in the first part of the query
which stopped this error appearing... but then I get the following error when
I put the form in form view:
Syntax error in JOIN operation

Just to clarify, here is the query as it appears in the subform control
source:
SELECT VehiclesDrivers.*, Drivers.Surname, Drivers.[First Names],
Drivers.DOB, Drivers.Sex, fncDriverCharacteristics (VehiclesDrivers.DriverID)
As Characteristics FROM VehiclesDrivers INNER JOIN Driver ON
VehiclesDrivers.DriverID=Drivers.DriverID;

So I am unable to run the query....

Hi Dirk

Thank you for your post and code. I have been away for 2 days and
have tried your suggestions today.
I have put your code in a module. Here is the existing query (before
Characteristics are added to the query)in the subform which displays
driver information in the continuous subform for the vehicle
displayed in the main form:

SELECT Drivers.Surname, Drivers.[First Names], Drivers.DOB,
Drivers.Sex, * FROM Drivers INNER JOIN VehiclesDrivers ON
Drivers.DriverID=VehiclesDrivers.DriverID;

Okay, I see that you are joining the Drivers table to VehiclesDrivers so
that you can pick up some info about the driver. That's fine, though
you are including "*" (all fields) on top of those, which is excessive.

The SQL Statement Query Builder previously generated this SQL before I
thought about how to include driver characteristics.

You suggest the following query:
SELECT *,
fncDriverCharacteristics(VehiclesDrivers.DriverID)
As Characteristics
FROM VehiclesDrivers;

How do I combine these two together into one query? I'm having
difficulty because I can't seem to do this using the query builder.
When I even just show the DriverCharacteristics table together with
the existing query in the top part of the builder screen, the subform
ends up showing each driver many times (once for every characteristic
they have.) This is before I try to add the function into the query.

You don't want to include the DriverCharacteristics table in the query,
because the function will be rolling up all the related characteristics
into a single field. Try this:

SELECT
VehiclesDrivers.*,
Drivers.Surname,
Drivers.[First Names],
Drivers.DOB,
Drivers.Sex
fncDriverCharacteristics(VehiclesDrivers.DriverID)
As Characteristics
FROM
VehiclesDrivers
INNER JOIN
Drivers
ON
VehiclesDrivers.DriverID=Drivers.DriverID;

I don't think it makes any difference, but on the SQL Statement
Builder, the Drivers Table's Primary key, DriverID, has a one to many
relationship with two separate junction tables: DriverCharacteristics
and VehiclesDriver. This is what is confusing me.

It would, but that's not important because we're not going to include
DriverCharacteristics in the query.

There are no spaces in the field names.

Oh yeah? What about Drivers.[First Names]?

Vehicles Table Primary key
is a unique text field (license plate) but all of your other
assumptions are as you expected.

I don't think that difference is going to matter.

Thank you Dirk. You are a lifeline and fountain of knowledge!

LOL Let's see how this works out before you start getting flowery.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Guest]

In my earlier post I said that I typed the query into the "control source" -
I meant to say the record source for the subform, not a control source. I
have still not been able to get it to work.

I tried typing your suggested query in the control source of the subform.
First I got this error when I put the form in form view:
Syntax error (missing operator) in query expression 'Drivers.Sex
fncDriverCharacteristics (VehicleDrivers.DriverID)

I tried putting a comma after Drivers.Sex in the first part of the query
which stopped this error appearing... but then I get the following error when
I put the form in form view:
Syntax error in JOIN operation

Just to clarify, here is the query as it appears in the subform control
source:
SELECT VehiclesDrivers.*, Drivers.Surname, Drivers.[First Names],
Drivers.DOB, Drivers.Sex, fncDriverCharacteristics (VehiclesDrivers.DriverID)
As Characteristics FROM VehiclesDrivers INNER JOIN Driver ON
VehiclesDrivers.DriverID=Drivers.DriverID;

So I am unable to run the query....

Hi Dirk

Thank you for your post and code. I have been away for 2 days and
have tried your suggestions today.
I have put your code in a module. Here is the existing query (before
Characteristics are added to the query)in the subform which displays
driver information in the continuous subform for the vehicle
displayed in the main form:

SELECT Drivers.Surname, Drivers.[First Names], Drivers.DOB,
Drivers.Sex, * FROM Drivers INNER JOIN VehiclesDrivers ON
Drivers.DriverID=VehiclesDrivers.DriverID;

Okay, I see that you are joining the Drivers table to VehiclesDrivers so
that you can pick up some info about the driver. That's fine, though
you are including "*" (all fields) on top of those, which is excessive.

The SQL Statement Query Builder previously generated this SQL before I
thought about how to include driver characteristics.

You suggest the following query:
SELECT *,
fncDriverCharacteristics(VehiclesDrivers.DriverID)
As Characteristics
FROM VehiclesDrivers;

How do I combine these two together into one query? I'm having
difficulty because I can't seem to do this using the query builder.
When I even just show the DriverCharacteristics table together with
the existing query in the top part of the builder screen, the subform
ends up showing each driver many times (once for every characteristic
they have.) This is before I try to add the function into the query.

You don't want to include the DriverCharacteristics table in the query,
because the function will be rolling up all the related characteristics
into a single field. Try this:

SELECT
VehiclesDrivers.*,
Drivers.Surname,
Drivers.[First Names],
Drivers.DOB,
Drivers.Sex
fncDriverCharacteristics(VehiclesDrivers.DriverID)
As Characteristics
FROM
VehiclesDrivers
INNER JOIN
Drivers
ON
VehiclesDrivers.DriverID=Drivers.DriverID;

I don't think it makes any difference, but on the SQL Statement
Builder, the Drivers Table's Primary key, DriverID, has a one to many
relationship with two separate junction tables: DriverCharacteristics
and VehiclesDriver. This is what is confusing me.

It would, but that's not important because we're not going to include
DriverCharacteristics in the query.

There are no spaces in the field names.

Oh yeah? What about Drivers.[First Names]?

Vehicles Table Primary key
is a unique text field (license plate) but all of your other
assumptions are as you expected.

I don't think that difference is going to matter.

Thank you Dirk. You are a lifeline and fountain of knowledge!

LOL Let's see how this works out before you start getting flowery.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

I tried typing your suggested query in the control source of the
subform. First I got this error when I put the form in form view:
Syntax error (missing operator) in query expression 'Drivers.Sex
fncDriverCharacteristics (VehicleDrivers.DriverID)

Oops, I left out a comma after Sex. There's a lesson to be learned

I tried putting a comma after Drivers.Sex in the first part of the
query which stopped this error appearing... but then I get the
following error when I put the form in form view:
Syntax error in JOIN operation

Just to clarify, here is the query as it appears in the subform
control source:
SELECT VehiclesDrivers.*, Drivers.Surname, Drivers.[First Names],
Drivers.DOB, Drivers.Sex, fncDriverCharacteristics
(VehiclesDrivers.DriverID) As Characteristics FROM VehiclesDrivers
INNER JOIN Driver ON VehiclesDrivers.DriverID=Drivers.DriverID;

There are two errors there that I didn't make. You have a space between
the function name and its arguments, and you joined to a (probably
nonexistent) table named "Driver" instead of "Drivers". Try this:

SELECT
VehiclesDrivers.*,
Drivers.Surname,
Drivers.[First Names],
Drivers.DOB,
Drivers.Sex,
fncDriverCharacteristics(VehiclesDrivers.DriverID)
As Characteristics
FROM
VehiclesDrivers
INNER JOIN
Drivers
ON
VehiclesDrivers.DriverID=Drivers.DriverID;

There could still be a mistake in there, but something like that should
work.

 

[Guest]

Thanks Dirk. I took your advice and IT WORKS! marvellous!
At the moment, there are only a few test entries in the database. You
mentioned that this "might be a bit slow..." If works fine at the moment but
do you think there might be a time lag when there are perhaps a few thousand
records?

Also (the icing on the cake!) is there any way to display the driver's name
on the subform name in red instead of the default black (using conditional
formatting? If so, how?) if the characteristics entries for that driver are
anything other than "none" (this will be entry no.20 in the characteristics
field; the default if no others are selected.)

Thank you! You made my day (actually, several days as this has been
haunting me for some time!) Here's to taming Access!

Rich

I tried typing your suggested query in the control source of the
subform. First I got this error when I put the form in form view:
Syntax error (missing operator) in query expression 'Drivers.Sex
fncDriverCharacteristics (VehicleDrivers.DriverID)

Oops, I left out a comma after Sex. There's a lesson to be learned

I tried putting a comma after Drivers.Sex in the first part of the
query which stopped this error appearing... but then I get the
following error when I put the form in form view:
Syntax error in JOIN operation

Just to clarify, here is the query as it appears in the subform
control source:
SELECT VehiclesDrivers.*, Drivers.Surname, Drivers.[First Names],
Drivers.DOB, Drivers.Sex, fncDriverCharacteristics
(VehiclesDrivers.DriverID) As Characteristics FROM VehiclesDrivers
INNER JOIN Driver ON VehiclesDrivers.DriverID=Drivers.DriverID;

There are two errors there that I didn't make. You have a space between
the function name and its arguments, and you joined to a (probably
nonexistent) table named "Driver" instead of "Drivers". Try this:

SELECT
VehiclesDrivers.*,
Drivers.Surname,
Drivers.[First Names],
Drivers.DOB,
Drivers.Sex,
fncDriverCharacteristics(VehiclesDrivers.DriverID)
As Characteristics
FROM
VehiclesDrivers
INNER JOIN
Drivers
ON
VehiclesDrivers.DriverID=Drivers.DriverID;

There could still be a mistake in there, but something like that should
work.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Guest]

Hi Dirk

Thanks for your message. Once more I have been away for a few days, hence
my lack of reply.
I tried your suggested expression. I had to put the brackets [ ] around the
Characteristics to get it to work, as follows:
Len([Characteristics]) > 0

without this, Access adds automatically adds quotes like this:
Len("Characteristics") > 0
and then appears to use "Characteristics" as a string and tests its length
within the expression and bases the conditional formatting on that!

Anyway, I have got it working. Thank you

 

[Dirk Goldgar]

Hi Dirk

Thanks for your message. Once more I have been away for a few days,
hence my lack of reply.
I tried your suggested expression. I had to put the brackets [ ]
around the Characteristics to get it to work, as follows:
Len([Characteristics]) > 0

without this, Access adds automatically adds quotes like this:
Len("Characteristics") > 0
and then appears to use "Characteristics" as a string and tests its
length within the expression and bases the conditional formatting on
that!

I didn't anticipate that. Access does guess wrong sometimes, when
you're entering criteria expressions, and I guess this is a very similar
case. In fact, I'll bet it calls the same function, exposed for our use
as BuildCriteria.

Anyway, I have got it working. Thank you

Very good. you're welcome.
\

 

[Guest]

Hi Dirk

I have now got the relationships, table and "display" elements of the
database sorted, with your help - the one form now shows all the information
I need it to.

thanks!

What is foxing me now is how to allow users to input (edit/delete/add new)
records to the main form and subforms. I have been able to accomplish this
with the main form and one subform but not the other two! And how I am going
to allow users to add a new driver and then assign characteristics to him/her
is making me think....hard! Is it possible to do all this from the one form?

In brief, I have the main form (vehicles info) with 3 subforms:
1) OtherT (one to many with many at OtherT end.) I am able to update
(edit/add new)records related to the main form within this subform

2) PreviousStops (one to many with vehicles with many at PreviousStops end.)
I am using a query to display this (so I can sort it by date) rather than a
table. I'm not sure how I can update/delete/add new records using this query
(can I?)

3) Subform to show Drivers associated with vehicle (many to many) -
displayed on the subform using a query to show fields from the
VehiclesDrivers junction and Drivers tables. (The characteristics are also
shown thanks to your code.) How can I add new drivers not yet in the
database if I need to for a new or existing vehicle? And how can I assign
new characteristics to a new or existing driver?

I know how to do all of this by manually inputting data into the underlying
tables but I'm trying to create a form to do it - or some kind of user
friendly interface not involving tables.

I have just submitted a post detailing this under "HELP! Add Records in
Form/Subform" in Access Database Forms...

if you feel able to help, I would be very grateful. Either way, thank you
for your vast assistance so far which has done so much to preserve my sanity!

Rich

Hi Dirk

Thanks for your message. Once more I have been away for a few days,
hence my lack of reply.
I tried your suggested expression. I had to put the brackets [ ]
around the Characteristics to get it to work, as follows:
Len([Characteristics]) > 0

without this, Access adds automatically adds quotes like this:
Len("Characteristics") > 0
and then appears to use "Characteristics" as a string and tests its
length within the expression and bases the conditional formatting on
that!

I didn't anticipate that. Access does guess wrong sometimes, when
you're entering criteria expressions, and I guess this is a very similar
case. In fact, I'll bet it calls the same function, exposed for our use
as BuildCriteria.

Anyway, I have got it working. Thank you

Very good. you're welcome.
\
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Guest]

Hi Dirk

The function you posted a while ago (to return Characteristics) works
beautifully.
Is there any way that the function can sort the characteristics
alphabetically?
I've tried putting in "ORDER BY Characteristic" in every place I can think
of in the SQL statement contained in your function but can't get it to work.
Am I going about this the wrong way? Have you got any advice? Is it
possible to do this?

Thanks again
Rich

Hi Dirk

Thanks for your message. The information in the Characteristics
table is simply one field with 19 short entries (3 words max.)
This is slightly different from the Northwind example mentioned on
the link you gave because I need to get information from the
Characteristics table (for each driver), which is related to the
driver/characteristics junction table.

Yes, it is slightly different because of the additional table in the
middle.

The main form is vehicles and the subform is drivers (related to
vehicles via a many to many relationship.) So which table do I need
to use as the "parent" in place of the Orders table in the example
(Drivers, presumably.) And then, which table do I specify as the
"child" table - driver/characteristics junction table, presumably
(containing driver ID as a number and categories ID as a number)?...
but the data I need to retrieve is the characteristics themselves
which are of course in the characteristics table...

I am a relative newcomer to Access and am confused!

Sorry. Let's see if I can get you unconfused.

I copy the code into a module - right?

Right, but I think we'll want to adapt it to the circumstances. I'll
put a special version of the code below, designed to suit your
situation.

And then I amend the SQL code to suit the
tables and fields relevant to this case and put them in a text box on
the drivers subform.. right?
Right.

Help!

On its way.

For the revised function code below, I'm going to make certain
assumptions about the names and fields of your tables. You'll have to
change them in my code and SQL examples to match those you actually are
using. My assumptions are:

Table name: Vehicles
Field name: VehicleID (primary key, numeric)

Table name: Drivers
Field name: DriverID (primary key, numeric)

Table name: Characteristics
Field name: CharID (primary key, numeric)
Field name: Characteristic (text)

Table name: VehiclesDrivers
Field name: VehicleID (foreign key to Vehicles)
Field name: DriverID (foreign key to Drivers)

Table name: DriversCharacteristics
Field name: DriverID (foreign key to Drivers)
Field name: CharID (foreign key to Characteristics)

Note that the table and field names I've assumed don't contain any
embedded spaces. If yours do, I recommend that you get rid of them
(changing forms and queries as necessary). If your names contain spaces
(or other nonstandard characters), you'll always have to surround such
names with the square brackets ([]) in SQL or code, and that leaves lots
of opportunities for error.

The Function:
Copy the function below into a standard module. It can be a new module
or an existing one, and you can name it anything you like, so long as
the name of the module isn't the same as that of any other public
name -- specifically, don't make the mistake of naming the module the
same as the function. I'd suggest something like "basUtilities" or
"modUtilities", but there's nothing really wrong with "Module1", as
Access proposes.

'----- start of code -----
Function fncDriverCharacteristics(varDriverID As Variant) _
As String

' Returns a string listing the characteristics of the
' given driver, in a comma-separated format.
'
' Written by: Dirk Goldgar, 6-Sep-2005
' Published for free use.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strCharacteristics As String
Dim strSQL As String

On Error GoTo Err_Handler

If IsNull(varDriverID) Then
Exit Function
End If

strSQL = _
"SELECT Characteristic " & _
"FROM DriversCharacteristics INNER JOIN Characteristics " & _
"ON DriversCharacteristics.CharID = Characteristics.CharID " & _
"WHERE DriversCharacteristics.DriverID=" & varDriverID

Set db = Application.DBEngine.Workspaces(0).Databases(0)
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

With rs
Do Until .EOF
strCharacteristics = strCharacteristics & ", " & .Fields(0)
.MoveNext
Loop
.Close
End With

' If the string isn't empty, trim off the leading comma and space.
If Len(strCharacteristics) > 0 Then
fncDriverCharacteristics = Mid(strCharacteristics, 3)
End If

Exit_Point:
Set rs = Nothing
Set db = Nothing
Exit Function

Err_Handler:
Debug.Print Err.Number, Err.Description
Resume Exit_Point

End Function
'----- end of code -----

Now set the recordsource query for your subform to something like

SELECT *,
fncDriverCharacteristics(VehiclesDrivers.DriverID)
As Characteristics
FROM VehiclesDrivers;

Note that you may possibly have a more complex query for the subform's
recordsource, joining the Drivers table to DriversCharacters so as to
pick up more info about the driver, but the significant point above is
the addition of the calculated field "Characteristics". Once you have
that field defined in the query, you can add a text box to your subform
to show this field, listing the driver's characteristics.

That calculated field won't be updatable, so you can't use it as a means
to update the driver's characteristics. For that you have to build a
different mechanism, possibly a form that you would pop up when the user
double-clicks on the text box.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Hi Dirk

The function you posted a while ago (to return Characteristics) works
beautifully.
Is there any way that the function can sort the characteristics
alphabetically?
I've tried putting in "ORDER BY Characteristic" in every place I can
think of in the SQL statement contained in your function but can't
get it to work. Am I going about this the wrong way? Have you got
any advice? Is it possible to do this?

If Characteristic is itself a text field, then this modified SQL ought
to work:

strSQL = _
"SELECT Characteristic " & _
"FROM DriversCharacteristics INNER JOIN Characteristics " & _
"ON DriversCharacteristics.CharID = Characteristics.CharID " & _
"WHERE DriversCharacteristics.DriverID=" & varDriverID & _
" ORDER BY Characteristic"

Did you happen to try that one?

 

[Guest]

I thought I tried this yesterday.. maybe I mistyped or did something wrong,
because it works fine now.
Thank you Dirk.