Combo box selection

  • Thread starter Thread starter Walter
  • Start date Start date
W

Walter

I am working on a database to keep planting records for my
farm.
I have a table "Farms" for the individual farms.
I also have a table "Fields" for the field numbers for
each individual farm. This table is related to
the "Farms" table by the "FarmID"(autonumber) field.
On my form to input the planting data I have combo boxes
to simplify input. What I need to accomplish is to have
the Field combo box only display the appropriate field #'s
for the particular farm. I think I need to draw this
information from a query with the data from the "Farm"
combo box as the criteria for the record selection. My
problem is how to apply this query and have it draw the
criteria from the "Farm" combo box data. Everything I
have tried ends up with the parameter input box showing up
when the query runs.
Any help in this matter is greatly appreciated
Thanks,
Walter
 
Walter said:
I am working on a database to keep planting records for my
farm.
I have a table "Farms" for the individual farms.
I also have a table "Fields" for the field numbers for
each individual farm. This table is related to
the "Farms" table by the "FarmID"(autonumber) field.
On my form to input the planting data I have combo boxes
to simplify input. What I need to accomplish is to have
the Field combo box only display the appropriate field #'s
for the particular farm. I think I need to draw this
information from a query with the data from the "Farm"
combo box as the criteria for the record selection. My
problem is how to apply this query and have it draw the
criteria from the "Farm" combo box data. Everything I
have tried ends up with the parameter input box showing up
when the query runs.
Any help in this matter is greatly appreciated
Thanks,
Walter
Click to expand...

The article at

http://www.mvps.org/access/forms/frm0028.htm

shows two methods of doing this. The second of the two corresponds to
what you're attempting. Does that article give you enough information
to solve your problem?
 
-----Original Message-----


The article at

http://www.mvps.org/access/forms/frm0028.htm

shows two methods of doing this. The second of the two corresponds to
what you're attempting. Does that article give you enough information
to solve your problem?

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

(please reply to the newsgroup)


.
I printed the article and substituted my names, as I
Click to expand...
enterpreted it, for the stored query and typed this in the
row source line for the second combo box. What I typed
was this;
SELECT FieldID,FieldNumber FROM tblFields WHERE FarmID=
[Forms]![Plantings]!cbxFarmID; .

My tables are this;
tblFarms:FarmID, FarmName (first combo box)
tblFields:FieldID, FarmID, FieldNumber (second combo box)

I also did the requery macro on the After Update Event on
the first combo box. I closed and saved the changes to
the form and reopened it in form view. When the form
opens, the Enter Parameter Box comes up with FieldID
prompt when the first combo box loads on screen. I am
using Access 2000 if this makes a difference.
Thanks again for your help.
Walter
 
Walter said:
-----Original Message-----


The article at

http://www.mvps.org/access/forms/frm0028.htm

shows two methods of doing this. The second of the two corresponds
to what you're attempting. Does that article give you enough
information to solve your problem?

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

(please reply to the newsgroup)


.
I printed the article and substituted my names, as I
Click to expand...
enterpreted it, for the stored query and typed this in the
row source line for the second combo box. What I typed
was this;
SELECT FieldID,FieldNumber FROM tblFields WHERE FarmID=
[Forms]![Plantings]!cbxFarmID; .

My tables are this;
tblFarms:FarmID, FarmName (first combo box)
tblFields:FieldID, FarmID, FieldNumber (second combo box)

I also did the requery macro on the After Update Event on
the first combo box. I closed and saved the changes to
the form and reopened it in form view. When the form
opens, the Enter Parameter Box comes up with FieldID
prompt when the first combo box loads on screen. I am
using Access 2000 if this makes a difference.
Thanks again for your help.
Walter
Click to expand...

It's probably some minor error. For each combo box, please post the
following properties:

Name
Row Source (if a stored query, please post the SQL)
Column Count
Bound Column
ControlSource

Also please post the Record Source of the form.
 
-----Original Message-----
-----Original Message-----
I am working on a database to keep planting records for my
farm.
I have a table "Farms" for the individual farms.
I also have a table "Fields" for the field numbers for
each individual farm. This table is related to
the "Farms" table by the "FarmID"(autonumber) field.
On my form to input the planting data I have combo boxes
to simplify input. What I need to accomplish is to have
the Field combo box only display the appropriate field #'s
for the particular farm. I think I need to draw this
information from a query with the data from the "Farm"
combo box as the criteria for the record selection. My
problem is how to apply this query and have it draw the
criteria from the "Farm" combo box data. Everything I
have tried ends up with the parameter input box showing up
when the query runs.
Any help in this matter is greatly appreciated
Thanks,
Walter

The article at

http://www.mvps.org/access/forms/frm0028.htm

shows two methods of doing this. The second of the two corresponds
to what you're attempting. Does that article give you enough
information to solve your problem?

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

(please reply to the newsgroup)


.
I printed the article and substituted my names, as I
Click to expand...
enterpreted it, for the stored query and typed this in the
row source line for the second combo box. What I typed
was this;
SELECT FieldID,FieldNumber FROM tblFields WHERE FarmID=
[Forms]![Plantings]!cbxFarmID; .

My tables are this;
tblFarms:FarmID, FarmName (first combo box)
tblFields:FieldID, FarmID, FieldNumber (second combo box)

I also did the requery macro on the After Update Event on
the first combo box. I closed and saved the changes to
the form and reopened it in form view. When the form
opens, the Enter Parameter Box comes up with FieldID
prompt when the first combo box loads on screen. I am
using Access 2000 if this makes a difference.
Thanks again for your help.
Walter
Click to expand...

It's probably some minor error. For each combo box, please post the
following properties:

Name
Row Source (if a stored query, please post the SQL)
Column Count
Bound Column
ControlSource

Also please post the Record Source of the form.

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

(please reply to the newsgroup)


.
The first combo box is this:
Click to expand...
Name:cbxFarmID
Row Source:SELECT [tblFarms].[Farm ID], [tblFarms].[Farm
Name] FROM tblFarms;
Column Count:2
Bound Column:1
Control Source:FarmID
This one works as it should.

The second combo box is this:
Name:cbxFieldID
Row Source:SELECT FieldID, FieldNumber FROM tblFields
WHERE FarmID=[Forms]![Plantings]!cbxFarmID;
Column Count:2
Bound Column:1
Control Source:FieldID

I am not sure what you mean by the record source of the
form but it is based on a table named "tblPlantings" which
has these fields:
PlantingID (autonumber & primary key)
PlantDate
FarmID
FieldID
CropID
Acres
TillageID
Comments
Harvested

I noticed the column count of 2 in the cbxFieldsID box and
there are 3 columns in this table. That number came from
the original LookupWizard when I set it up to start with
as I did the cbxFarmID box. This was before I realized
needed to limit the choices.
Thanks,
Walter
 
Walter said:
-----Original Message-----
Walter said:
-----Original Message-----
I am working on a database to keep planting records for my
farm.
I have a table "Farms" for the individual farms.
I also have a table "Fields" for the field numbers for
each individual farm. This table is related to
the "Farms" table by the "FarmID"(autonumber) field.
On my form to input the planting data I have combo boxes
to simplify input. What I need to accomplish is to have
the Field combo box only display the appropriate field #'s
for the particular farm. I think I need to draw this
information from a query with the data from the "Farm"
combo box as the criteria for the record selection. My
problem is how to apply this query and have it draw the
criteria from the "Farm" combo box data. Everything I
have tried ends up with the parameter input box showing up
when the query runs.
Any help in this matter is greatly appreciated
Thanks,
Walter

The article at

http://www.mvps.org/access/forms/frm0028.htm

shows two methods of doing this. The second of the two corresponds
to what you're attempting. Does that article give you enough
information to solve your problem?


.
I printed the article and substituted my names, as I
enterpreted it, for the stored query and typed this in the
row source line for the second combo box. What I typed
was this;
SELECT FieldID,FieldNumber FROM tblFields WHERE FarmID=
[Forms]![Plantings]!cbxFarmID; .

My tables are this;
tblFarms:FarmID, FarmName (first combo box)
tblFields:FieldID, FarmID, FieldNumber (second combo box)

I also did the requery macro on the After Update Event on
the first combo box. I closed and saved the changes to
the form and reopened it in form view. When the form
opens, the Enter Parameter Box comes up with FieldID
prompt when the first combo box loads on screen. I am
using Access 2000 if this makes a difference.
Thanks again for your help.
Walter
Click to expand...

It's probably some minor error. For each combo box, please post the
following properties:

Name
Row Source (if a stored query, please post the SQL)
Column Count
Bound Column
ControlSource

Also please post the Record Source of the form.



.
The first combo box is this:
Click to expand...
Name:cbxFarmID
Row Source:SELECT [tblFarms].[Farm ID], [tblFarms].[Farm
Name] FROM tblFarms;
Column Count:2
Bound Column:1
Control Source:FarmID
This one works as it should.

The second combo box is this:
Name:cbxFieldID
Row Source:SELECT FieldID, FieldNumber FROM tblFields
WHERE FarmID=[Forms]![Plantings]!cbxFarmID;
Column Count:2
Bound Column:1
Control Source:FieldID

I am not sure what you mean by the record source of the
form but it is based on a table named "tblPlantings" which
has these fields:
PlantingID (autonumber & primary key)
PlantDate
FarmID
FieldID
CropID
Acres
TillageID
Comments
Harvested

I noticed the column count of 2 in the cbxFieldsID box and
there are 3 columns in this table. That number came from
the original LookupWizard when I set it up to start with
as I did the cbxFarmID box. This was before I realized
needed to limit the choices.
Click to expand...

The column count is correct, because although the table has three
columns your rowsource query is only selecting two of them.

I may be overlooking something, but I don't see anything wrong in the
rowsources or table definitions as you've posted them. That leads me to
suspect that you've misspelled a field name in the table definition. Is
it possible that the field defined in one of your tables (tblFields or
tblPlantings) is "FeildID" or some other misspelling, rather than
"FieldID"? Or have you actually corrected the rowsource SQL statement
in transcribing it to post here?

If you copy the SQL statement from the rowsource of cbxFieldID and paste
it into the SQL View of a new query, then switch to Datasheet View, do
you get a parameter prompt for FieldID as well as for
[Forms]![Plantings]![cbxFarmID] ?
 
-----Original Message-----
-----Original Message-----
I am working on a database to keep planting records for my
farm.
I have a table "Farms" for the individual farms.
I also have a table "Fields" for the field numbers for
each individual farm. This table is related to
the "Farms" table by the "FarmID"(autonumber) field.
On my form to input the planting data I have combo boxes
to simplify input. What I need to accomplish is to have
the Field combo box only display the appropriate field #'s
for the particular farm. I think I need to draw this
information from a query with the data from the "Farm"
combo box as the criteria for the record selection. My
problem is how to apply this query and have it draw the
criteria from the "Farm" combo box data. Everything I
have tried ends up with the parameter input box showing up
when the query runs.
Any help in this matter is greatly appreciated
Thanks,
Walter

The article at

http://www.mvps.org/access/forms/frm0028.htm

shows two methods of doing this. The second of the two corresponds
to what you're attempting. Does that article give you enough
information to solve your problem?

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

(please reply to the newsgroup)


.
I printed the article and substituted my names, as I
Click to expand...
enterpreted it, for the stored query and typed this in the
row source line for the second combo box. What I typed
was this;
SELECT FieldID,FieldNumber FROM tblFields WHERE FarmID=
[Forms]![Plantings]!cbxFarmID; .

My tables are this;
tblFarms:FarmID, FarmName (first combo box)
tblFields:FieldID, FarmID, FieldNumber (second combo box)

I also did the requery macro on the After Update Event on
the first combo box. I closed and saved the changes to
the form and reopened it in form view. When the form
opens, the Enter Parameter Box comes up with FieldID
prompt when the first combo box loads on screen. I am
using Access 2000 if this makes a difference.
Thanks again for your help.
Walter
Click to expand...

It's probably some minor error. For each combo box, please post the
following properties:

Name
Row Source (if a stored query, please post the SQL)
Column Count
Bound Column
ControlSource

Also please post the Record Source of the form.

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

(please reply to the newsgroup)


.
Click to expand...
First combo box:
Name:cbxFarmID
Row Source:SELECT [tblFarms].[Farm ID], [tblFarms].[Farm
Name] FROM tblFarms;
Column Count:2
Bound Column:1
Control Source:FarmID
This one works as it should.

Second Combo Box:
Name:cbxFieldID
Row Source:SELECT FieldID, FieldNumber FROM tblFields
WHERE FarmID=[Forms]![Plantings]!cbxFarmID;
Column Count:2
Bound Column:1
Control Source:FieldID

I am not sure what you mean by the forms record source but
it is based on a table named tblPlantings which consists
of the following fields:
PlantingID (autonumber & primary key)
PlantDate
FarmID
FieldID
CropID
Acres
TillageID
Comments
Harvested

I noticed the column count of two in the cbxFieldID box.
That table has three columns. This number, I suppose,
came from the LookupWizard when I originally set it up
before I realized I needed to limit the choices for this
box.
Thanks Again,
Walter
 
Walter said:
-----Original Message-----
Walter said:
-----Original Message-----
I am working on a database to keep planting records for my
farm.
I have a table "Farms" for the individual farms.
I also have a table "Fields" for the field numbers for
each individual farm. This table is related to
the "Farms" table by the "FarmID"(autonumber) field.
On my form to input the planting data I have combo boxes
to simplify input. What I need to accomplish is to have
the Field combo box only display the appropriate field #'s
for the particular farm. I think I need to draw this
information from a query with the data from the "Farm"
combo box as the criteria for the record selection. My
problem is how to apply this query and have it draw the
criteria from the "Farm" combo box data. Everything I
have tried ends up with the parameter input box showing up
when the query runs.
Any help in this matter is greatly appreciated
Thanks,
Walter

The article at

http://www.mvps.org/access/forms/frm0028.htm

shows two methods of doing this. The second of the two corresponds
to what you're attempting. Does that article give you enough
information to solve your problem?

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

(please reply to the newsgroup)


.
I printed the article and substituted my names, as I
enterpreted it, for the stored query and typed this in the
row source line for the second combo box. What I typed
was this;
SELECT FieldID,FieldNumber FROM tblFields WHERE FarmID=
[Forms]![Plantings]!cbxFarmID; .

My tables are this;
tblFarms:FarmID, FarmName (first combo box)
tblFields:FieldID, FarmID, FieldNumber (second combo box)

I also did the requery macro on the After Update Event on
the first combo box. I closed and saved the changes to
the form and reopened it in form view. When the form
opens, the Enter Parameter Box comes up with FieldID
prompt when the first combo box loads on screen. I am
using Access 2000 if this makes a difference.
Thanks again for your help.
Walter
Click to expand...

It's probably some minor error. For each combo box, please post the
following properties:

Name
Row Source (if a stored query, please post the SQL)
Column Count
Bound Column
ControlSource

Also please post the Record Source of the form.

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

(please reply to the newsgroup)


.
Click to expand...
First combo box:
Name:cbxFarmID
Row Source:SELECT [tblFarms].[Farm ID], [tblFarms].[Farm
Name] FROM tblFarms;
Column Count:2
Bound Column:1
Control Source:FarmID
This one works as it should.

Second Combo Box:
Name:cbxFieldID
Row Source:SELECT FieldID, FieldNumber FROM tblFields
WHERE FarmID=[Forms]![Plantings]!cbxFarmID;
Column Count:2
Bound Column:1
Control Source:FieldID

I am not sure what you mean by the forms record source but
it is based on a table named tblPlantings which consists
of the following fields:
PlantingID (autonumber & primary key)
PlantDate
FarmID
FieldID
CropID
Acres
TillageID
Comments
Harvested

I noticed the column count of two in the cbxFieldID box.
That table has three columns. This number, I suppose,
came from the LookupWizard when I originally set it up
before I realized I needed to limit the choices for this
box.
Thanks Again,
Walter
Click to expand...

I don't see any new content in this message, Walter. Am I overlooking
something? I'm still waiting for answers to my last questions.
 
-----Original Message-----
-----Original Message-----
-----Original Message-----
I am working on a database to keep planting records for my
farm.
I have a table "Farms" for the individual farms.
I also have a table "Fields" for the field numbers for
each individual farm. This table is related to
the "Farms" table by the "FarmID"(autonumber) field.
On my form to input the planting data I have combo boxes
to simplify input. What I need to accomplish is to have
the Field combo box only display the appropriate field #'s
for the particular farm. I think I need to draw this
information from a query with the data from the "Farm"
combo box as the criteria for the record selection. My
problem is how to apply this query and have it draw the
criteria from the "Farm" combo box data. Everything I
have tried ends up with the parameter input box showing up
when the query runs.
Any help in this matter is greatly appreciated
Thanks,
Walter

The article at

http://www.mvps.org/access/forms/frm0028.htm

shows two methods of doing this. The second of the two corresponds
to what you're attempting. Does that article give you enough
information to solve your problem?


.
I printed the article and substituted my names, as I
enterpreted it, for the stored query and typed this in the
row source line for the second combo box. What I typed
was this;
SELECT FieldID,FieldNumber FROM tblFields WHERE FarmID=
[Forms]![Plantings]!cbxFarmID; .

My tables are this;
tblFarms:FarmID, FarmName (first combo box)
tblFields:FieldID, FarmID, FieldNumber (second combo box)

I also did the requery macro on the After Update Event on
the first combo box. I closed and saved the changes to
the form and reopened it in form view. When the form
opens, the Enter Parameter Box comes up with FieldID
prompt when the first combo box loads on screen. I am
using Access 2000 if this makes a difference.
Thanks again for your help.
Walter

It's probably some minor error. For each combo box, please post the
following properties:

Name
Row Source (if a stored query, please post the SQL)
Column Count
Bound Column
ControlSource

Also please post the Record Source of the form.



.
The first combo box is this:
Click to expand...
Name:cbxFarmID
Row Source:SELECT [tblFarms].[Farm ID], [tblFarms].[Farm
Name] FROM tblFarms;
Column Count:2
Bound Column:1
Control Source:FarmID
This one works as it should.

The second combo box is this:
Name:cbxFieldID
Row Source:SELECT FieldID, FieldNumber FROM tblFields
WHERE FarmID=[Forms]![Plantings]!cbxFarmID;
Column Count:2
Bound Column:1
Control Source:FieldID

I am not sure what you mean by the record source of the
form but it is based on a table named "tblPlantings" which
has these fields:
PlantingID (autonumber & primary key)
PlantDate
FarmID
FieldID
CropID
Acres
TillageID
Comments
Harvested

I noticed the column count of 2 in the cbxFieldsID box and
there are 3 columns in this table. That number came from
the original LookupWizard when I set it up to start with
as I did the cbxFarmID box. This was before I realized
needed to limit the choices.
Click to expand...

The column count is correct, because although the table has three
columns your rowsource query is only selecting two of them.

I may be overlooking something, but I don't see anything wrong in the
rowsources or table definitions as you've posted them. That leads me to
suspect that you've misspelled a field name in the table definition. Is
it possible that the field defined in one of your tables (tblFields or
tblPlantings) is "FeildID" or some other misspelling, rather than
"FieldID"? Or have you actually corrected the rowsource SQL statement
in transcribing it to post here?

If you copy the SQL statement from the rowsource of cbxFieldID and paste
it into the SQL View of a new query, then switch to Datasheet View, do
you get a parameter prompt for FieldID as well as for
[Forms]![Plantings]![cbxFarmID] ?

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

(please reply to the newsgroup)


.
I checked the field names and found some that had spaces
Click to expand...
in them. I corrected this and then the boxes on the form
worked. I entered some records checking it out and then
opened the table to check them out in datasheet view. I
got a "Me!FarmName" prompt when it opened and OK or Cancel
will close the box and display the records. I understand
why this is but I noticed the field numbers were different
from what I entered. The first farm in the list had 1
added to each field number (Form field# = 3, Datasheet
field# = 4 on the same record). The second farm in the
list had 21 or 22 added to the field number. The third
farm had 9 added to the field number. The reason I am not
sure about the second farm is when I went back to form
view to check the field numbers there, the first farm
showed the correct field number but the second and third
farms field numbers were blank.
Thanks,
Walter
 
I checked the field names and found some that had spaces
in them. I corrected this and then the boxes on the form
worked.
Hooray!

I entered some records checking it out and then
opened the table to check them out in datasheet view. I
got a "Me!FarmName" prompt when it opened and OK or Cancel
will close the box and display the records.
Click to expand...

When opening the table? Do you have these defined as lookup fields,
using a combo box as the display control, and with a rowsource that you
wrote yourself, erroneously specifying Me!FarmName as a criterion?
That's the only way I can think of for you to get a prompt like that.
I understand
why this is but I noticed the field numbers were different
from what I entered. The first farm in the list had 1
added to each field number (Form field# = 3, Datasheet
field# = 4 on the same record). The second farm in the
list had 21 or 22 added to the field number. The third
farm had 9 added to the field number. The reason I am not
sure about the second farm is when I went back to form
view to check the field numbers there, the first farm
showed the correct field number but the second and third
farms field numbers were blank.
Click to expand...

This is odd, but I suspect it has to do with your using combo boxes as
the Display Control for these fields (in the table design) and having
something wrong with the rowsource, bound column, or other properties of
these controls. Please check the Lookup tab of the property sheet for
each of these fields in the table design, and post the properties you
find there.
 
-----Original Message-----


When opening the table? Do you have these defined as lookup fields,
using a combo box as the display control, and with a rowsource that you
wrote yourself, erroneously specifying Me!FarmName as a criterion?
That's the only way I can think of for you to get a prompt like that.


This is odd, but I suspect it has to do with your using combo boxes as
the Display Control for these fields (in the table design) and having
something wrong with the rowsource, bound column, or other properties of
these controls. Please check the Lookup tab of the property sheet for
each of these fields in the table design, and post the properties you
find there.

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

(please reply to the newsgroup)


.
Click to expand...
tblPlantings (design view):

FarmID field Lookup tab:
Display Control: Combo Box
Row Source Type: Table/Query
Row Source: SELECT [tblFarms].[FarmID], [tblFarms].
[FarmName] FROM tblFarms;(copy & paste for accuracy)
Bound Column: 1
Column Count: 2
Column Heads: No
Column widths: 0";1"
List Rows:8
List Width: 1"
Limit To List: Yes

FieldID fleld Lookup tab:
Display Control: Combo Box
Row Source Type: Table/Query
Row Source:SELECT [tblFarms].[FarmName], [tblFields].
[FieldID], [tblFields].[FieldNumber]FROM tblFarms INNER
JOIN tblFields ON [tblFarms].[FarmID]=[tblFields].[FarmID]
WHERE ((([tblFarms}.[FarmName])=Me!FarmName));
Bound Column: 1
Column Count: 3
Column Heads: No
Column widths: 0";1";1"
List Rows:8
List Width: 2"
Limit To List: Yes

I defined these fields as lookup fields when I created the
table because I thought I needed to to have the combo
boxes on the form. There are some others that are done
the same way also (CropID & TillageID because they are
combo boxes also) but I can change them if I need to.
Thanks,
Walter
 
Walter said:
-----Original Message-----


When opening the table? Do you have these defined as lookup fields,
using a combo box as the display control, and with a rowsource that
you wrote yourself, erroneously specifying Me!FarmName as a
criterion? That's the only way I can think of for you to get a
prompt like that.


This is odd, but I suspect it has to do with your using combo boxes
as the Display Control for these fields (in the table design) and
having something wrong with the rowsource, bound column, or other
properties of these controls. Please check the Lookup tab of the
property sheet for each of these fields in the table design, and
post the properties you find there.
Click to expand...

tblPlantings (design view):

FarmID field Lookup tab:
Display Control: Combo Box
Row Source Type: Table/Query
Row Source: SELECT [tblFarms].[FarmID], [tblFarms].
[FarmName] FROM tblFarms;(copy & paste for accuracy)
Bound Column: 1
Column Count: 2
Column Heads: No
Column widths: 0";1"
List Rows:8
List Width: 1"
Limit To List: Yes

FieldID fleld Lookup tab:
Display Control: Combo Box
Row Source Type: Table/Query
Row Source:SELECT [tblFarms].[FarmName], [tblFields].
[FieldID], [tblFields].[FieldNumber]FROM tblFarms INNER
JOIN tblFields ON [tblFarms].[FarmID]=[tblFields].[FarmID]
WHERE ((([tblFarms}.[FarmName])=Me!FarmName));
Bound Column: 1
Column Count: 3
Column Heads: No
Column widths: 0";1";1"
List Rows:8
List Width: 2"
Limit To List: Yes

I defined these fields as lookup fields when I created the
table because I thought I needed to to have the combo
boxes on the form. There are some others that are done
the same way also (CropID & TillageID because they are
combo boxes also) but I can change them if I need to.
Click to expand...

You didn't need these fields to be lookup fields just in order to have
combo boxes on the form. You can create combo boxes on the form while
leaving the fields in the table represented by text boxes. I'd
recommend doing it that way, even there were not a problem with that
second field's display control (which there is). Lookup fields are
useful if you want to work with table datasheets, but they have several
drawbacks, of which one is the fact that they obscure what's really in
your tables. And since a wise developer won't let users work with table
datasheets, most of the people I know think lookup fields are more
trouble than they're worth.

The properties of the display control for that second lookup field,
FieldID, are screwy. The Row Source is not valid -- the reference to
Me!FarmName is meaningless in this context -- and the Bound Column
specified corresponds to the field [tblFarms].[FarmName] from the row
source, which makes no sense since this is the value that's supposed to
be stored in tblPlantings.FieldID. If you really want to keep this as a
lookup field, change the properties of the display control to

Row Source: SELECT FieldID, FieldNumber
FROM tblFields ORDER BY FieldNumber;
Bound Column: 1
Column Count: 2
Column widths: 0";1"

or something along those lines. However, if I were you I'd just change
both those fields to simple Number/Long fields and set their Display
Controls to "Text Box".
 
-----Original Message-----
-----Original Message-----
I checked the field names and found some that had spaces
in them. I corrected this and then the boxes on the form
worked.

Hooray!

I entered some records checking it out and then
opened the table to check them out in datasheet view. I
got a "Me!FarmName" prompt when it opened and OK or Cancel
will close the box and display the records.

When opening the table? Do you have these defined as lookup fields,
using a combo box as the display control, and with a rowsource that
you wrote yourself, erroneously specifying Me!FarmName as a
criterion? That's the only way I can think of for you to get a
prompt like that.

I understand
why this is but I noticed the field numbers were different
from what I entered. The first farm in the list had 1
added to each field number (Form field# = 3, Datasheet
field# = 4 on the same record). The second farm in the
list had 21 or 22 added to the field number. The third
farm had 9 added to the field number. The reason I am not
sure about the second farm is when I went back to form
view to check the field numbers there, the first farm
showed the correct field number but the second and third
farms field numbers were blank.

This is odd, but I suspect it has to do with your using combo boxes
as the Display Control for these fields (in the table design) and
having something wrong with the rowsource, bound column, or other
properties of these controls. Please check the Lookup tab of the
property sheet for each of these fields in the table design, and
post the properties you find there.
Click to expand...

tblPlantings (design view):

FarmID field Lookup tab:
Display Control: Combo Box
Row Source Type: Table/Query
Row Source: SELECT [tblFarms].[FarmID], [tblFarms].
[FarmName] FROM tblFarms;(copy & paste for accuracy)
Bound Column: 1
Column Count: 2
Column Heads: No
Column widths: 0";1"
List Rows:8
List Width: 1"
Limit To List: Yes

FieldID fleld Lookup tab:
Display Control: Combo Box
Row Source Type: Table/Query
Row Source:SELECT [tblFarms].[FarmName], [tblFields].
[FieldID], [tblFields].[FieldNumber]FROM tblFarms INNER
JOIN tblFields ON [tblFarms].[FarmID]=[tblFields]. [FarmID]
WHERE ((([tblFarms}.[FarmName])=Me!FarmName));
Bound Column: 1
Column Count: 3
Column Heads: No
Column widths: 0";1";1"
List Rows:8
List Width: 2"
Limit To List: Yes

I defined these fields as lookup fields when I created the
table because I thought I needed to to have the combo
boxes on the form. There are some others that are done
the same way also (CropID & TillageID because they are
combo boxes also) but I can change them if I need to.
Click to expand...

You didn't need these fields to be lookup fields just in order to have
combo boxes on the form. You can create combo boxes on the form while
leaving the fields in the table represented by text boxes. I'd
recommend doing it that way, even there were not a problem with that
second field's display control (which there is). Lookup fields are
useful if you want to work with table datasheets, but they have several
drawbacks, of which one is the fact that they obscure what's really in
your tables. And since a wise developer won't let users work with table
datasheets, most of the people I know think lookup fields are more
trouble than they're worth.

The properties of the display control for that second lookup field,
FieldID, are screwy. The Row Source is not valid -- the reference to
Me!FarmName is meaningless in this context -- and the Bound Column
specified corresponds to the field [tblFarms].[FarmName] from the row
source, which makes no sense since this is the value that's supposed to
be stored in tblPlantings.FieldID. If you really want to keep this as a
lookup field, change the properties of the display control to

Row Source: SELECT FieldID, FieldNumber
FROM tblFields ORDER BY FieldNumber;
Bound Column: 1
Column Count: 2
Column widths: 0";1"

or something along those lines. However, if I were you I'd just change
both those fields to simple Number/Long fields and set their Display
Controls to "Text Box".

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

(please reply to the newsgroup)


.
I changed the two fields in the table and it is working
Click to expand...
fine. However on the form, when I go back to records I
have entered, the field number box is blank for records
for the 2nd and 3rd farms in the farms list. The field
numbers show for the first farm in the list. It doesn't
make any difference what order the farms are used when
entering records. The information is correct in the
underlying table in datasheet view but it doesn't show up
in form view. If I highlight the farm and select it
again, the correct field number is displayed. I don't
understand why it works fine on the one and not on the
others.
Thanks,
Walter
 
Walter said:
-----Original Message-----
Walter said:
-----Original Message-----
I checked the field names and found some that had spaces
in them. I corrected this and then the boxes on the form
worked.

Hooray!

I entered some records checking it out and then
opened the table to check them out in datasheet view. I
got a "Me!FarmName" prompt when it opened and OK or Cancel
will close the box and display the records.

When opening the table? Do you have these defined as lookup
fields, using a combo box as the display control, and with a
rowsource that you wrote yourself, erroneously specifying
Me!FarmName as a criterion? That's the only way I can think of for
you to get a prompt like that.

I understand
why this is but I noticed the field numbers were different
from what I entered. The first farm in the list had 1
added to each field number (Form field# = 3, Datasheet
field# = 4 on the same record). The second farm in the
list had 21 or 22 added to the field number. The third
farm had 9 added to the field number. The reason I am not
sure about the second farm is when I went back to form
view to check the field numbers there, the first farm
showed the correct field number but the second and third
farms field numbers were blank.

This is odd, but I suspect it has to do with your using combo boxes
as the Display Control for these fields (in the table design) and
having something wrong with the rowsource, bound column, or other
properties of these controls. Please check the Lookup tab of the
property sheet for each of these fields in the table design, and
post the properties you find there.

tblPlantings (design view):

FarmID field Lookup tab:
Display Control: Combo Box
Row Source Type: Table/Query
Row Source: SELECT [tblFarms].[FarmID], [tblFarms].
[FarmName] FROM tblFarms;(copy & paste for accuracy)
Bound Column: 1
Column Count: 2
Column Heads: No
Column widths: 0";1"
List Rows:8
List Width: 1"
Limit To List: Yes

FieldID fleld Lookup tab:
Display Control: Combo Box
Row Source Type: Table/Query
Row Source:SELECT [tblFarms].[FarmName], [tblFields].
[FieldID], [tblFields].[FieldNumber]FROM tblFarms INNER
JOIN tblFields ON [tblFarms].[FarmID]=[tblFields]. [FarmID]
WHERE ((([tblFarms}.[FarmName])=Me!FarmName));
Bound Column: 1
Column Count: 3
Column Heads: No
Column widths: 0";1";1"
List Rows:8
List Width: 2"
Limit To List: Yes

I defined these fields as lookup fields when I created the
table because I thought I needed to to have the combo
boxes on the form. There are some others that are done
the same way also (CropID & TillageID because they are
combo boxes also) but I can change them if I need to.
Click to expand...

You didn't need these fields to be lookup fields just in order to
have combo boxes on the form. You can create combo boxes on the
form while leaving the fields in the table represented by text
boxes. I'd recommend doing it that way, even there were not a
problem with that second field's display control (which there is).
Lookup fields are useful if you want to work with table datasheets,
but they have several drawbacks, of which one is the fact that they
obscure what's really in your tables. And since a wise developer
won't let users work with table datasheets, most of the people I
know think lookup fields are more trouble than they're worth.

The properties of the display control for that second lookup field,
FieldID, are screwy. The Row Source is not valid -- the reference to
Me!FarmName is meaningless in this context -- and the Bound Column
specified corresponds to the field [tblFarms].[FarmName] from the row
source, which makes no sense since this is the value that's supposed
to be stored in tblPlantings.FieldID. If you really want to keep
this as a lookup field, change the properties of the display control
to

Row Source: SELECT FieldID, FieldNumber
FROM tblFields ORDER BY FieldNumber;
Bound Column: 1
Column Count: 2
Column widths: 0";1"

or something along those lines. However, if I were you I'd just
change both those fields to simple Number/Long fields and set their
Display Controls to "Text Box".

.
Click to expand...
I changed the two fields in the table and it is working
fine.
Great!

However on the form, when I go back to records I
have entered, the field number box is blank for records
for the 2nd and 3rd farms in the farms list. The field
numbers show for the first farm in the list. It doesn't
make any difference what order the farms are used when
entering records. The information is correct in the
underlying table in datasheet view but it doesn't show up
in form view. If I highlight the farm and select it
again, the correct field number is displayed. I don't
understand why it works fine on the one and not on the
others.
Click to expand...

Here's a copy of a post by Sandra Daigle that explains this problem and
explains a workaround, with a link to an example. I wanted to save time
writing it up for myself, so I went to http://groups.google.com and
searched for

combo "continuous form" group:*.*access.*

This was the second article (of many on the subject) that was returned:

---------------- start of quote ------------------
From: Sandra Daigle ([email protected])
Subject: Re: Combo Box on Continuous form
Newsgroups: microsoft.public.access.modulesdaovba
Date: 2003-02-28 04:26:48 PST


Hi Dan,

The problem you are having is a common one and it is due to the fact
that in
a continuous form there is really only the one combo, it is just
displayed
multiple times. Hence it can't have multiple rowsources (one for each
displayed record). There is also a common workaround that you can use -
you
can see an example of it along with several other strategies for dealing
with this type of data if you look at the SyncComboContinuous example on
my
website: http://www.daiglenet.com/msaccess.htm

Here's a description of the method: to create the effect of a
synchronized
combo in a continuous form create another textbox control, include the
display column of the combo in the Recordsource query of the form (join
the
foreign table and drag in the column). Position and size the new textbox
so
that you can place it
directly over the combo box allowing only the down-arrow portion of the
combo to show.

Create a GotFocus event for the textbox that only does a SetFocus to the
combo.

This will create the effect that you are wanting - the correct display
value
will always show even though the value itself might not be fit the
criteria
that is in effect based on the current record.

Here's a KB Article that explains the problem
ACC2000: Combo Box in Continuous Form Shows Incorrect Data
http://support.microsoft.com/default.aspx?scid=kb;en-us;208866

---------------- end of quote ------------------
 
-----Original Message-----
-----Original Message-----
-----Original Message-----
I checked the field names and found some that had spaces
in them. I corrected this and then the boxes on the form
worked.

Hooray!

I entered some records checking it out and then
opened the table to check them out in datasheet view. I
got a "Me!FarmName" prompt when it opened and OK or Cancel
will close the box and display the records.

When opening the table? Do you have these defined as lookup
fields, using a combo box as the display control, and with a
rowsource that you wrote yourself, erroneously specifying
Me!FarmName as a criterion? That's the only way I can think of for
you to get a prompt like that.

I understand
why this is but I noticed the field numbers were different
from what I entered. The first farm in the list had 1
added to each field number (Form field# = 3, Datasheet
field# = 4 on the same record). The second farm in the
list had 21 or 22 added to the field number. The third
farm had 9 added to the field number. The reason I am not
sure about the second farm is when I went back to form
view to check the field numbers there, the first farm
showed the correct field number but the second and third
farms field numbers were blank.

This is odd, but I suspect it has to do with your using combo boxes
as the Display Control for these fields (in the table design) and
having something wrong with the rowsource, bound column, or other
properties of these controls. Please check the Lookup tab of the
property sheet for each of these fields in the table design, and
post the properties you find there.

tblPlantings (design view):

FarmID field Lookup tab:
Display Control: Combo Box
Row Source Type: Table/Query
Row Source: SELECT [tblFarms].[FarmID], [tblFarms].
[FarmName] FROM tblFarms;(copy & paste for accuracy)
Bound Column: 1
Column Count: 2
Column Heads: No
Column widths: 0";1"
List Rows:8
List Width: 1"
Limit To List: Yes

FieldID fleld Lookup tab:
Display Control: Combo Box
Row Source Type: Table/Query
Row Source:SELECT [tblFarms].[FarmName], [tblFields].
[FieldID], [tblFields].[FieldNumber]FROM tblFarms INNER
JOIN tblFields ON [tblFarms].[FarmID]=[tblFields]. [FarmID]
WHERE ((([tblFarms}.[FarmName])=Me!FarmName));
Bound Column: 1
Column Count: 3
Column Heads: No
Column widths: 0";1";1"
List Rows:8
List Width: 2"
Limit To List: Yes

I defined these fields as lookup fields when I created the
table because I thought I needed to to have the combo
boxes on the form. There are some others that are done
the same way also (CropID & TillageID because they are
combo boxes also) but I can change them if I need to.

You didn't need these fields to be lookup fields just in order to
have combo boxes on the form. You can create combo boxes on the
form while leaving the fields in the table represented by text
boxes. I'd recommend doing it that way, even there were not a
problem with that second field's display control (which there is).
Lookup fields are useful if you want to work with table datasheets,
but they have several drawbacks, of which one is the fact that they
obscure what's really in your tables. And since a wise developer
won't let users work with table datasheets, most of the people I
know think lookup fields are more trouble than they're worth.

The properties of the display control for that second lookup field,
FieldID, are screwy. The Row Source is not valid -- the reference to
Me!FarmName is meaningless in this context -- and the Bound Column
specified corresponds to the field [tblFarms]. [FarmName] from the row
source, which makes no sense since this is the value that's supposed
to be stored in tblPlantings.FieldID. If you really want to keep
this as a lookup field, change the properties of the display control
to

Row Source: SELECT FieldID, FieldNumber
FROM tblFields ORDER BY FieldNumber;
Bound Column: 1
Column Count: 2
Column widths: 0";1"

or something along those lines. However, if I were you I'd just
change both those fields to simple Number/Long fields and set their
Display Controls to "Text Box".

.
Click to expand...
I changed the two fields in the table and it is working
fine.
Great!

However on the form, when I go back to records I
have entered, the field number box is blank for records
for the 2nd and 3rd farms in the farms list. The field
numbers show for the first farm in the list. It doesn't
make any difference what order the farms are used when
entering records. The information is correct in the
underlying table in datasheet view but it doesn't show up
in form view. If I highlight the farm and select it
again, the correct field number is displayed. I don't
understand why it works fine on the one and not on the
others.
Click to expand...

Here's a copy of a post by Sandra Daigle that explains this problem and
explains a workaround, with a link to an example. I wanted to save time
writing it up for myself, so I went to
Click to expand...
http://groups.google.com and
searched for

combo "continuous form" group:*.*access.*

This was the second article (of many on the subject) that was returned:

---------------- start of quote ------------------
From: Sandra Daigle ([email protected])
Subject: Re: Combo Box on Continuous form
Newsgroups: microsoft.public.access.modulesdaovba
Date: 2003-02-28 04:26:48 PST


Hi Dan,

The problem you are having is a common one and it is due to the fact
that in
a continuous form there is really only the one combo, it is just
displayed
multiple times. Hence it can't have multiple rowsources (one for each
displayed record). There is also a common workaround that you can use -
you
can see an example of it along with several other strategies for dealing
with this type of data if you look at the SyncComboContinuous example on
my
website: http://www.daiglenet.com/msaccess.htm

Here's a description of the method: to create the effect of a
synchronized
combo in a continuous form create another textbox control, include the
display column of the combo in the Recordsource query of the form (join
the
foreign table and drag in the column). Position and size the new textbox
so
that you can place it
directly over the combo box allowing only the down-arrow portion of the
combo to show.

Create a GotFocus event for the textbox that only does a SetFocus to the
combo.

This will create the effect that you are wanting - the correct display
value
will always show even though the value itself might not be fit the
criteria
that is in effect based on the current record.

Here's a KB Article that explains the problem
ACC2000: Combo Box in Continuous Form Shows Incorrect Data
http://support.microsoft.com/default.aspx?scid=kb;en- us;208866

---------------- end of quote ------------------


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

(please reply to the newsgroup)


.
I just wanted to thank you for all your help. I think
Click to expand...
I've got things worked out. Also thanks for the info as
to where to look for answers. Your help has been GREATLY
appreciated.
Thanks again,
Walter
 
Back
Top