Message box for no records found

[Tony Williams]

I have a form that has two combo control's based on different tables, table
A and table B which linked on a text field that holds a company name. The
user selects a company from the combo box based on table A and selects a
date from the other combo box which is based on table B and then clicks a
command form to open a form that is also based on table B. The company may
exist in Table A but not in table B. However the form opens even if there is
no record for the company in table B. How can I stop the form from opening
and pup up a message that says "There isn't a record for this company?"
Thanks
Tony
PS I'm new to VBA!!!!!

 

[Keith Wilby]

I have a form that has two combo control's based on different tables, table
A and table B which linked on a text field that holds a company name. The
user selects a company from the combo box based on table A and selects a
date from the other combo box which is based on table B and then clicks a
command form to open a form that is also based on table B. The company may
exist in Table A but not in table B. However the form opens even if there
is no record for the company in table B. How can I stop the form from
opening and pup up a message that says "There isn't a record for this
company?"
Thanks
Tony
PS I'm new to VBA!!!!!

Hi Tony.

It sounds like your basic table design may be a little suspect since it's
not recommended to store the same type of information in different tables.
Having said that, a solution to the question you asked would be to set up a
query for your "company" combo which is based upon the "company" field in
table A but excludes company names that are not in table B.

Post back if you need to know how to do that but do bear in mind that it
might be that your schema design might need an overhaul.

HTH - Keith.
www.keithwilby.com

 

[Tony Williams]

Thanks Keith I'll try that
Tony

Hi Tony.

It sounds like your basic table design may be a little suspect since it's
not recommended to store the same type of information in different tables.
Having said that, a solution to the question you asked would be to set up
a query for your "company" combo which is based upon the "company" field
in table A but excludes company names that are not in table B.

Post back if you need to know how to do that but do bear in mind that it
might be that your schema design might need an overhaul.

HTH - Keith.
www.keithwilby.com

 

[Tony Williams]

Yep that worked fine. Based the combo box on a query that matched the date.
Thanks
Tony