Subforms

"Rich1234" wrote in message

Thanks Dirk. I took your advice and IT WORKS!

Excellent!

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?

I'm not sure, but it seems likely to me that you could begin getting
slower response with a higher record volume. I don't know where the
threshold would be, though. Access tries very hard not call functions
used in queries until the return value is actually needed -- for
display, filtering, or sorting. So it may well be that you won't notice
any significant difference no matter how many records there are in the
table.

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.)

I imagine you could use conditional formatting for this, as you suggest,
using the "Expression Is" form of the Conditional Formatting entry, and
specifying an expression like

Len(Characteristics) 0

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

You're welcome. I'll drink to that!

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

(please reply to the newsgroup)

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" wrote:

"Rich1234" wrote in message

Thanks Dirk. I took your advice and IT WORKS!

Excellent!

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?

I'm not sure, but it seems likely to me that you could begin getting
slower response with a higher record volume. I don't know where the
threshold would be, though. Access tries very hard not call functions
used in queries until the return value is actually needed -- for
display, filtering, or sorting. So it may well be that you won't notice
any significant difference no matter how many records there are in the
table.

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.)

I imagine you could use conditional formatting for this, as you suggest,
using the "Expression Is" form of the Conditional Formatting entry, and
specifying an expression like

Len(Characteristics) 0

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

You're welcome. I'll drink to that!

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

(please reply to the newsgroup)

"Rich1234" wrote in message

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)

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

"Dirk Goldgar" wrote:

"Rich1234" wrote in message

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)

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

"Dirk Goldgar" wrote:

"Rich1234" wrote in message

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 a

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)

"Rich1234" wrote in message

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?

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

(please reply to the newsgroup)

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

"Dirk Goldgar" wrote:

"Rich1234" wrote in message

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?

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

(please reply to the newsgroup)