Combo box help

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

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
 
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.
 
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
 
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.
 
Post the RowSource of the combobox. Post the RecordSource of the subform.
Post the RecordSource of the form.
--

Ken Snell
<MS ACCESS MVP>
 
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
 
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>
 
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
 
Ken
Incidentally if I don't join the two tables in the query I get hundreds of
blank records appearing in the form??
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;

--

Ken Snell
<MS ACCESS MVP>
 
Thanks Ken that worked just fine
Tony
Ken Snell said:
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;
Click to expand...
 
Back
Top