Combo box help

[Tony Williams]

I have a form based on Table A and a linked sub form based on Table B. On
the sub form is a drop down combobox based on Table C. The data in the
combobox, which is called cmbcompany, is based on a field in Table C called
txtcompany and the value is stored in the field txtcompany2 in Table B. I
created the combo box using the wizard. However when I delete a record from
my sub form it also deletes the value of txtcompany in Table C. What am I
doing wrong here?
Thanks
Tony

 

[Ken Snell [MVP]]

Is Table C related to Table B in the Relationships window? And do you have
Referential Integrity set for that relationship? And do you have Cascade
Delete set on that relationship? Assuming the answer to all of these is Yes,
then the record in Table C will be deleted when you delete its "parent"
record in Table B.

 

[Tony Williams]

Thanks Ken, the answer to all your questions is No, there is no relationship
set between any of these tables in the Relationship window. Odd?
Tony

 

[BruceM]

Do I understand correctly that the Row Source for cmbcompany is Table C, and
the record source is txtcompany2 in table B? Also, you say that there is no
relationship between "any of these tables". I assume there IS a relationship
between A and B.

 

[Ken Snell [MVP]]

Post the RowSource of the combobox. Post the RecordSource of the subform.
Post the RecordSource of the form.
--

Ken Snell
<MS ACCESS MVP>

 

[Tony Williams]

Hi Ken
Row source of Combo box
SELECT tblCompany.cmbCompany FROM tblCompany ORDER BY tblCompany.cmbCompany;
Record Source of combo box
tblmaintabs.txtCompany

Record source of subform is a query
qryFDAfrm

Record source of the main form is a table
tblMonth

I haven't any relationships set up apart from in the qryFDAfrm where
tblmaintabs is linked to tblcompany on txtcompany = cmbcompany

Is that any help?
Tony

 

[Ken Snell [MVP]]

Yes. You didn't post the SQL statement of the query that is the subform's
RecordSource, but you indicate that there is a relationship in that query
between tblmaintabs and tblcompany. When you delete a record from the
subform, you're deleting a record from that query; and as such, you delete a
record from at least one table because both are in the query. Do you really
need tblcompany to be in the query's SQL statement?

Tell us the SQL and the purpose of the subform.

Also note that a combo box has a control source but not a record source.
What you posted for the combo box is its control source, not record source.
--

Ken Snell
<MS ACCESS MVP>

 

[Tony Williams]

Thanks again Ken here is the SQL for the query on which the sub form is
based.

SELECT tblmaintabs.*, tblCompany.TxtCoNbr, tblmaintabs.txtCompany
FROM tblmaintabs LEFT JOIN tblCompany ON tblmaintabs.txtCompany =
tblCompany.cmbCompany
ORDER BY tblmaintabs.txtCompany;

As you can see I am using all the fields in the tblmaintabs and joining the
tables on the company name.
The combo box gives me a list of ALL the companies that provide data to us
from time to time. The tblcompany holds information about the companies such
as previous name, whether they are based in the Euro zone or in the UK etc.
The statistical data is provided on a quarterly basis and entered into the
subform. Not all companies provide the data every quarter. So the idea of
the combo box is for the user to select which company's data they are
inputting for a particular quarter and this data is held in the table
tblmaintabs. If I don't include the tblcompany in the query how do I get a
full list of compnaies for the user to choose from when inputting the data?

Sorry about the confusion on naming the control source as a record source.
Thanks again
Tony

 

[Tony Williams]

Ken
Incidentally if I don't join the two tables in the query I get hundreds of
blank records appearing in the form??
Tony

 

[Ken Snell [MVP]]

Use the combo box itself to "get" the company-specific data when the user
selects the company. See this article at The ACCESS Web for how to do it:
http://www.mvps.org/access/forms/frm0058.htm

Then just use this as the SQL of the subform's RecordSource:

SELECT tblmaintabs.*
FROM tblmaintabs
ORDER BY tblmaintabs.txtCompany;

--

Ken Snell
<MS ACCESS MVP>

 

[Tony Williams]

Thanks Ken that worked just fine
Tony

Use the combo box itself to "get" the company-specific data when the user
selects the company. See this article at The ACCESS Web for how to do it:
http://www.mvps.org/access/forms/frm0058.htm

Then just use this as the SQL of the subform's RecordSource:

SELECT tblmaintabs.*
FROM tblmaintabs
ORDER BY tblmaintabs.txtCompany;