Problem with query row source on form

G

Guest

Hi, I did post this question but think I may have asked it wrong or posted it
to the wrong place.....

I have this query as the row source for Enforcement Subtype field.

SELECT Enforcement_Subtypes.Enf_Subtype_ID,
Enforcement_Subtypes.Enf_Type_ID, Enforcement_Subtypes.Enforcement_SubType
FROM Enforcement_Subtypes
WHERE
(((Enforcement_Subtypes.Enf_Type_ID)=[Forms]![Compl_MAIN_Form]![Enforcement
Type]));

When you select the appropriate Enforcement Type a drop down list of the
applicable subtypes appears and you can select one. It seems that if you
pick the first possible Enforcement Type it will save the correct subtype in
the record on the form. If you pick any other Enforcement Type, for some
reason it does not save it on the form but does save it to the table.

I do have a frontend and backend. The Control Source is Enforcement_Subtype
in my main table. Any ideas why this isn't working properly are greatly
appreciated!
 
B

Brian Bastl

Hi Carrie,

if I understand correctly, you have two combo boxes on your form:
[Enforcement Type] and [Enforcement SubType].

First, you can slightly modify the rowsource for [Enforcement Subtype] to:

SELECT Enforcement_Subtypes.Enf_Subtype_ID,
Enforcement_Subtypes.Enforcement_SubType
FROM Enforcement_Subtypes
WHERE
(((Enforcement_Subtypes.Enf_Type_ID)=[Forms]![Compl_MAIN_Form]![Enforcement
Type]));

Secondly, you'll need to adjust the column properties for the combobox on
its property sheet.

Column Count = 2
Column Widths 0"; Something greater that 0"
Bound Column = 1

You should be storing Subtype_ID in the table, as opposed to SubType.

Try it, and if it doesn't work, then post back with the forms recordsource.

HTH,
Brian
 
G

Guest

Hi Brian, Thanks for your help. I modified the query and am still having
the same problem. The correct value is definitely being stored in the table
and now if I click in the subtype field, the value will appear - it just
won't stay there. My form's record source is Compl_MAIN_Table and given what
you said about storing the Subtype_ID instead of SubType - I think that may
be my problem but I'm not sure how to correct it.

The field in the main table has the following as a row source:

SELECT Enforcement_Subtypes.Enf_Subtype_ID,
Enforcement_Subtypes.Enforcement_SubType FROM Enforcement_Subtypes;

Bound Column is 1 and Column Count is 2 - I tried to change this to 1 but
that didn't solve my problem either. Thanks again.

Brian Bastl said:
Hi Carrie,

if I understand correctly, you have two combo boxes on your form:
[Enforcement Type] and [Enforcement SubType].

First, you can slightly modify the rowsource for [Enforcement Subtype] to:

SELECT Enforcement_Subtypes.Enf_Subtype_ID,
Enforcement_Subtypes.Enforcement_SubType
FROM Enforcement_Subtypes
WHERE
(((Enforcement_Subtypes.Enf_Type_ID)=[Forms]![Compl_MAIN_Form]![Enforcement
Type]));

Secondly, you'll need to adjust the column properties for the combobox on
its property sheet.

Column Count = 2
Column Widths 0"; Something greater that 0"
Bound Column = 1

You should be storing Subtype_ID in the table, as opposed to SubType.

Try it, and if it doesn't work, then post back with the forms recordsource.

HTH,
Brian


Carrie said:
Hi, I did post this question but think I may have asked it wrong or posted it
to the wrong place.....

I have this query as the row source for Enforcement Subtype field.

SELECT Enforcement_Subtypes.Enf_Subtype_ID,
Enforcement_Subtypes.Enf_Type_ID, Enforcement_Subtypes.Enforcement_SubType
FROM Enforcement_Subtypes
WHERE
(((Enforcement_Subtypes.Enf_Type_ID)=[Forms]![Compl_MAIN_Form]![Enforcement
Type]));

When you select the appropriate Enforcement Type a drop down list of the
applicable subtypes appears and you can select one. It seems that if you
pick the first possible Enforcement Type it will save the correct subtype in
the record on the form. If you pick any other Enforcement Type, for some
reason it does not save it on the form but does save it to the table.

I do have a frontend and backend. The Control Source is Enforcement_Subtype
in my main table. Any ideas why this isn't working properly are greatly
appreciated!
Click to expand...
Click to expand...
 
G

Guest

Hello, Just wanted to let you know that I finally figured out the problem. I
needed to requery the field on the Current Event of the FORM. Works great
now.

Carrie said:
Hi Brian, Thanks for your help. I modified the query and am still having
the same problem. The correct value is definitely being stored in the table
and now if I click in the subtype field, the value will appear - it just
won't stay there. My form's record source is Compl_MAIN_Table and given what
you said about storing the Subtype_ID instead of SubType - I think that may
be my problem but I'm not sure how to correct it.

The field in the main table has the following as a row source:

SELECT Enforcement_Subtypes.Enf_Subtype_ID,
Enforcement_Subtypes.Enforcement_SubType FROM Enforcement_Subtypes;

Bound Column is 1 and Column Count is 2 - I tried to change this to 1 but
that didn't solve my problem either. Thanks again.

Brian Bastl said:
Hi Carrie,

if I understand correctly, you have two combo boxes on your form:
[Enforcement Type] and [Enforcement SubType].

First, you can slightly modify the rowsource for [Enforcement Subtype] to:

SELECT Enforcement_Subtypes.Enf_Subtype_ID,
Enforcement_Subtypes.Enforcement_SubType
FROM Enforcement_Subtypes
WHERE
(((Enforcement_Subtypes.Enf_Type_ID)=[Forms]![Compl_MAIN_Form]![Enforcement
Type]));

Secondly, you'll need to adjust the column properties for the combobox on
its property sheet.

Column Count = 2
Column Widths 0"; Something greater that 0"
Bound Column = 1

You should be storing Subtype_ID in the table, as opposed to SubType.

Try it, and if it doesn't work, then post back with the forms recordsource.

HTH,
Brian


Carrie said:
Hi, I did post this question but think I may have asked it wrong or posted it
to the wrong place.....

I have this query as the row source for Enforcement Subtype field.

SELECT Enforcement_Subtypes.Enf_Subtype_ID,
Enforcement_Subtypes.Enf_Type_ID, Enforcement_Subtypes.Enforcement_SubType
FROM Enforcement_Subtypes
WHERE
(((Enforcement_Subtypes.Enf_Type_ID)=[Forms]![Compl_MAIN_Form]![Enforcement
Type]));

When you select the appropriate Enforcement Type a drop down list of the
applicable subtypes appears and you can select one. It seems that if you
pick the first possible Enforcement Type it will save the correct subtype in
the record on the form. If you pick any other Enforcement Type, for some
reason it does not save it on the form but does save it to the table.

I do have a frontend and backend. The Control Source is Enforcement_Subtype
in my main table. Any ideas why this isn't working properly are greatly
appreciated!
Click to expand...
Click to expand...
Click to expand...
 
B

Brian Bastl

Hi Carrie,

glad to hear you worked it out.

Brian


Carrie said:
Hello, Just wanted to let you know that I finally figured out the problem. I
needed to requery the field on the Current Event of the FORM. Works great
now.

Carrie said:
Hi Brian, Thanks for your help. I modified the query and am still having
the same problem. The correct value is definitely being stored in the table
and now if I click in the subtype field, the value will appear - it just
won't stay there. My form's record source is Compl_MAIN_Table and given what
you said about storing the Subtype_ID instead of SubType - I think that may
be my problem but I'm not sure how to correct it.

The field in the main table has the following as a row source:

SELECT Enforcement_Subtypes.Enf_Subtype_ID,
Enforcement_Subtypes.Enforcement_SubType FROM Enforcement_Subtypes;

Bound Column is 1 and Column Count is 2 - I tried to change this to 1 but
that didn't solve my problem either. Thanks again.

Brian Bastl said:
Hi Carrie,

if I understand correctly, you have two combo boxes on your form:
[Enforcement Type] and [Enforcement SubType].

First, you can slightly modify the rowsource for [Enforcement Subtype] to:

SELECT Enforcement_Subtypes.Enf_Subtype_ID,
Enforcement_Subtypes.Enforcement_SubType
FROM Enforcement_Subtypes
WHERE
(((Enforcement_Subtypes.Enf_Type_ID)=[Forms]![Compl_MAIN_Form]![Enforcement
Type]));

Secondly, you'll need to adjust the column properties for the combobox on
its property sheet.

Column Count = 2
Column Widths 0"; Something greater that 0"
Bound Column = 1

You should be storing Subtype_ID in the table, as opposed to SubType.

Try it, and if it doesn't work, then post back with the forms recordsource.

HTH,
Brian


Hi, I did post this question but think I may have asked it wrong or posted
it
to the wrong place.....

I have this query as the row source for Enforcement Subtype field.

SELECT Enforcement_Subtypes.Enf_Subtype_ID,
Enforcement_Subtypes.Enf_Type_ID, Enforcement_Subtypes.Enforcement_SubType
FROM Enforcement_Subtypes
WHERE

(((Enforcement_Subtypes.Enf_Type_ID)=[Forms]![Compl_MAIN_Form]![Enforcement
Type]));

When you select the appropriate Enforcement Type a drop down list of the
applicable subtypes appears and you can select one. It seems that if you
pick the first possible Enforcement Type it will save the correct subtype
in
the record on the form. If you pick any other Enforcement Type, for some
reason it does not save it on the form but does save it to the table.

I do have a frontend and backend. The Control Source is
Enforcement_Subtype
in my main table. Any ideas why this isn't working properly are greatly
appreciated!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

relating list boxes to each other 1
Adding Calcs to a Crosstab? Or Form? 4
Updating combo box values 3
Report - Query Criteria problem <> 4
Problem with using function in a drop down query of a row source 4
can't get this cbx suggestion to work 2
List Box - Query As Row Source 1
Access Control on subform / combo box requesting parameter 0

Top