Subform Combo Box problem

[Guest]

Firstly, my apologies for the lengthy post but I wanted to get as much
helpful information provided as possible.

I have had great help from this forum in the past and hope that someone
might be able to point out where my design/programming error lies. I am
using Access 2003 on a Windows XP.

I am attempting to modify the Asset Database 2003 Template downloaded from
the Microsoft site, specifically the Maintenance Subform of the Maintenance
Form.

Tbl_Maintenance in the original template contains the following fields;
MaintenanceID (AutoNumber, PK)
AssetID (Number, FK)
MaintenanceDate (Date/Time)
MaintenanceDescription (Text)
MaintenancePerformedBy (Text)
MaintenanceCost (Currency)

In order to ensure consistency of criteria for maintenance descriptions I
have created Tbl_Maintenance_Description containing the fields
MaintDescrID (AutoNumber, PK)
MaintenanceDescr (Text)

Tbl_Maintenance_Description has 2 records currently entered into the table.

I have deleted the field MaintenanceDescription from Tbl_Maintenance and
added the field MaintDescrID (Number, FK)

Tables Maintenance and Maintenance_Description have a one-many relationship
set.

In the Maintenance Subform I changed the MaintenanceDescription Field to a
Combo box (cbo_MaintenanceDescription) and changed the Control Source in the
properties to MaintDescrID. Column Count is 2 with column widths 0cm; 3cm
and bound column set at 2; List rows set at 10.

The row source has the following SQL statement…

SELECT Maintenance.*, [Maintenance Description].MaintDescrID
FROM [Maintenance Description] RIGHT JOIN Maintenance ON [Maintenance
Description].MaintDescrID = Maintenance.MaintDescrID;

On going to the datasheet view of Maintenance Subform I get the number 1
showing in the combo list in two rows. This remains the same if I make the
bound column 1.

I have been trying to work out what I am doing wrong for the past two days
but it is eluding me. Any help would be greatly appreciated.

 

[Rob Parker]

Hi Joe,

I assume you are doing this so that you can have a lookup table of possible
maintenance descriptions to choose from in the maintenance subform.

I've just tried duplicating what you've posted, and found that I get a
"Syntax error in Join operation" from the SQL statement you posted as therow
source for your combo-box. And I'm rather puzzled as to why you have any
sort of join in this query - all you need is a simple select statement; the
combo-box itself provides the join between the MaintDescID entry in the
Maintenance table (because that's what the combo-box is bound to) and the
text entry for the description from the Maintenance Description table. All
you need for the Row Source is:
SELECT MaintDescrID, MaintenanceDescr
FROM [Maintenance Description];

A couple of other points:

In your post, you use three separate names for your new table
("Tbl_Maintenance_Description", "Maintenance_Description", and "Maintenance
Description"). All are different, and I suspect that the last one is the
correct one - that's what I've assumed in the SQL statement above, and the
square brackets are therefore necessary to delimit the table name which
contains a space. Note that, if you had used an underscore in the table
name, the square brackets would not be necessary.

I strongly suggest that you read up on a naming convention (the Leszynski
one is the most common), and apply it when you are working with building a
database application. The template databases fom Microsoft are wonderful
examples of how confusing things can be when you don't have a naming
convention: eg. in the Asset database, there are both table and a form
named "Maintenance"; also, every control has the same name as its
datasource.

There's a rather nice add-in written by Helen Feddema which will do LNC
renaming of objects within your database. Check out code sample 10 at
http://www.helenfeddema.com/CodeSamples.htm; the .zip download includes an
excellent explanatory article.

And also, there is an alternative method of retrieving existing descriptions
for selection in a combo-box, which does not involve using a separate lookup
table. The row source for the combo-box (with the initial table structure
in the database) would be:
SELECT DISTINCT MaintenanceDescription FROM Maintenance;
This would be used in conjunction with setting the Limit To List property of
the combo-box to No, with a little VBA code to allow new entries to be
added. Search the access.formscoding group for examples.

HTH,

Rob

Firstly, my apologies for the lengthy post but I wanted to get as much
helpful information provided as possible.

I have had great help from this forum in the past and hope that someone
might be able to point out where my design/programming error lies. I am
using Access 2003 on a Windows XP.

I am attempting to modify the Asset Database 2003 Template downloaded from
the Microsoft site, specifically the Maintenance Subform of the
Maintenance
Form.

Tbl_Maintenance in the original template contains the following fields;
MaintenanceID (AutoNumber, PK)
AssetID (Number, FK)
MaintenanceDate (Date/Time)
MaintenanceDescription (Text)
MaintenancePerformedBy (Text)
MaintenanceCost (Currency)

In order to ensure consistency of criteria for maintenance descriptions I
have created Tbl_Maintenance_Description containing the fields
MaintDescrID (AutoNumber, PK)
MaintenanceDescr (Text)

Tbl_Maintenance_Description has 2 records currently entered into the
table.

I have deleted the field MaintenanceDescription from Tbl_Maintenance and
added the field MaintDescrID (Number, FK)

Tables Maintenance and Maintenance_Description have a one-many
relationship
set.

In the Maintenance Subform I changed the MaintenanceDescription Field to a
Combo box (cbo_MaintenanceDescription) and changed the Control Source in
the
properties to MaintDescrID. Column Count is 2 with column widths 0cm; 3cm
and bound column set at 2; List rows set at 10.

The row source has the following SQL statement.

SELECT Maintenance.*, [Maintenance Description].MaintDescrID
FROM [Maintenance Description] RIGHT JOIN Maintenance ON [Maintenance
Description].MaintDescrID = Maintenance.MaintDescrID;

On going to the datasheet view of Maintenance Subform I get the number 1
showing in the combo list in two rows. This remains the same if I make
the
bound column 1.

I have been trying to work out what I am doing wrong for the past two days
but it is eluding me. Any help would be greatly appreciated.

 

[Guest]

Thanks Rob for your quick response to my question. I have resolved the combo
box problem using the modified SELECT statement and it's working great -
Thanks! I was just beginning the process of renaming tables, forms etc so
your suggestion for the add-in is very timely and will save me heaps of time.

I have one other query if you are able to assist. How can I create a new
form using the same colour scheme and layout of the existing db forms? I've
tried making a copy but it seems such a complex process changing all the
fields manually rather than starting off with a blank template. Your help is
really appreciated.

Joe

Hi Joe,

I assume you are doing this so that you can have a lookup table of possible
maintenance descriptions to choose from in the maintenance subform.

I've just tried duplicating what you've posted, and found that I get a
"Syntax error in Join operation" from the SQL statement you posted as therow
source for your combo-box. And I'm rather puzzled as to why you have any
sort of join in this query - all you need is a simple select statement; the
combo-box itself provides the join between the MaintDescID entry in the
Maintenance table (because that's what the combo-box is bound to) and the
text entry for the description from the Maintenance Description table. All
you need for the Row Source is:
SELECT MaintDescrID, MaintenanceDescr
FROM [Maintenance Description];

A couple of other points:

In your post, you use three separate names for your new table
("Tbl_Maintenance_Description", "Maintenance_Description", and "Maintenance
Description"). All are different, and I suspect that the last one is the
correct one - that's what I've assumed in the SQL statement above, and the
square brackets are therefore necessary to delimit the table name which
contains a space. Note that, if you had used an underscore in the table
name, the square brackets would not be necessary.

I strongly suggest that you read up on a naming convention (the Leszynski
one is the most common), and apply it when you are working with building a
database application. The template databases fom Microsoft are wonderful
examples of how confusing things can be when you don't have a naming
convention: eg. in the Asset database, there are both table and a form
named "Maintenance"; also, every control has the same name as its
datasource.

There's a rather nice add-in written by Helen Feddema which will do LNC
renaming of objects within your database. Check out code sample 10 at
http://www.helenfeddema.com/CodeSamples.htm; the .zip download includes an
excellent explanatory article.

And also, there is an alternative method of retrieving existing descriptions
for selection in a combo-box, which does not involve using a separate lookup
table. The row source for the combo-box (with the initial table structure
in the database) would be:
SELECT DISTINCT MaintenanceDescription FROM Maintenance;
This would be used in conjunction with setting the Limit To List property of
the combo-box to No, with a little VBA code to allow new entries to be
added. Search the access.formscoding group for examples.

HTH,

Rob

Firstly, my apologies for the lengthy post but I wanted to get as much
helpful information provided as possible.

I have had great help from this forum in the past and hope that someone
might be able to point out where my design/programming error lies. I am
using Access 2003 on a Windows XP.

I am attempting to modify the Asset Database 2003 Template downloaded from
the Microsoft site, specifically the Maintenance Subform of the
Maintenance
Form.

Tbl_Maintenance in the original template contains the following fields;
MaintenanceID (AutoNumber, PK)
AssetID (Number, FK)
MaintenanceDate (Date/Time)
MaintenanceDescription (Text)
MaintenancePerformedBy (Text)
MaintenanceCost (Currency)

In order to ensure consistency of criteria for maintenance descriptions I
have created Tbl_Maintenance_Description containing the fields
MaintDescrID (AutoNumber, PK)
MaintenanceDescr (Text)

Tbl_Maintenance_Description has 2 records currently entered into the
table.

I have deleted the field MaintenanceDescription from Tbl_Maintenance and
added the field MaintDescrID (Number, FK)

Tables Maintenance and Maintenance_Description have a one-many
relationship
set.

In the Maintenance Subform I changed the MaintenanceDescription Field to a
Combo box (cbo_MaintenanceDescription) and changed the Control Source in
the
properties to MaintDescrID. Column Count is 2 with column widths 0cm; 3cm
and bound column set at 2; List rows set at 10.

The row source has the following SQL statement.

SELECT Maintenance.*, [Maintenance Description].MaintDescrID
FROM [Maintenance Description] RIGHT JOIN Maintenance ON [Maintenance
Description].MaintDescrID = Maintenance.MaintDescrID;

On going to the datasheet view of Maintenance Subform I get the number 1
showing in the combo list in two rows. This remains the same if I make
the
bound column 1.

I have been trying to work out what I am doing wrong for the past two days
but it is eluding me. Any help would be greatly appreciated.

 

[Rob Parker]

Hi again Joe,

Responses in-line:

Thanks Rob for your quick response to my question. I have resolved the
combo
box problem using the modified SELECT statement and it's working great -
Thanks! I was just beginning the process of renaming tables, forms etc so
your suggestion for the add-in is very timely and will save me heaps of
time.

You're welcome. Nice to see that you've already noticed this issue.

I have one other query if you are able to assist. How can I create a new
form using the same colour scheme and layout of the existing db forms?
I've
tried making a copy but it seems such a complex process changing all the
fields manually rather than starting off with a blank template. Your help
is
really appreciated.

The simplest way is to set an existing an existing form as the template for
new forms. From the Tools menu, select Options, then on the Forms/Reports
tab enter the name of an existing form in the Form Template field. This
will only affect new forms; if you need to change an existing form you'll
have to do it manually. If you are starting a new db from scratch, you can
set up custom design features in a dummy template form at the start of your
project, and then base everything on that. If you set the default
properties for each type of control in the template form, you'll also
inherit all those custom design settings in your new forms.

HTH,

Rob

<snip>

 

[Guest]

Hi Rob,

Yep, that's great - many thanks once again. You have been extremely helpful
and it is much appreciated.

Joe