How to build form for outer join

  • Thread starter Thread starter Jonathan Blitz
  • Start date Start date
J

Jonathan Blitz

I have the following data:

Cust:
CustomerID
CustomerName
...
....

ErrorCodes:
ErrorId
Description

ErrorsPerCustomer:
CustomerId
ErrorId

Each customer may have any number of errors from 0 up to the number of
errors in the ErroCodes table (the same error can not appear more than once
for the same customer).

My problem is how do I design a form for this?
I select the data using an outer join.

What I need is at least one line per customer even if they have no errors.
In addition, it must be possible to add or delete error entries per
customer.

I know I could do it using a subform but since most customers will not have
anyu error entries it would be a bit wasteful.

--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."
 
Each customer may have any number of errors from 0 up to the number of
errors in the ErroCodes table (the same error can not appear more than once
for the same customer).

My problem is how do I design a form for this?
I select the data using an outer join.

What I need is at least one line per customer even if they have no errors.
In addition, it must be possible to add or delete error entries per
customer.

I know I could do it using a subform but since most customers will not have
anyu error entries it would be a bit wasteful.
Click to expand...

Use a Subform anyway. It's not wasteful at all; it's the standard and
simplest technique. Base the Form on the Customer table, and a
continuous Subform on the ErrorsPerCustomer table (or use queries on
these tables sorting the records however you like). The Subform will
show all the existing errors for that customer, with a blank line at
the end (even if there are no errors you'll have a blank line ready to
enter the first error record). It's convenient to use a Combo Box
control on the Subform, bound to the ErrorID field but displaying the
error description.
 
Problem is that, at most, a customer may have 2 or 3 messages.

If I am correct a form containing a subform must be defined as continuous.
In this case there would be a terrible was of space.
It would also not be user-friendly.

--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."
 
Jonathon,

Just size the subform control large enough to display 2 or 3
and set if for vertical scroll bar only. If there are more,
the users can scroll through them as needed.
--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
Jonathan Blitz said:
Problem is that, at most, a customer may have 2 or 3 messages.

If I am correct a form containing a subform must be defined as continuous.
In this case there would be a terrible was of space.
It would also not be user-friendly.

--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."
appear more than
once they have no
errors. customers will not
have
Use a Subform anyway. It's not wasteful at all; it's the standard and
simplest technique. Base the Form on the Customer table, and a
continuous Subform on the ErrorsPerCustomer table (or use queries on
these tables sorting the records however you like). The Subform will
show all the existing errors for that customer, with a blank line at
the end (even if there are no errors you'll have a blank line ready to
enter the first error record). It's convenient to use a Combo Box
control on the Subform, bound to the ErrorID field but displaying the
error description.
Click to expand...
Click to expand...
 
Problem is that because it has a subform I cannot set it in continuous form
mode - only in single form mode.
So I will still only get one customer per page.

I could do it in datasheet mode but doesn't look good.

--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."
 
Hi Jonathan,

Thank you for using MSDN Newsgroup!

John and Gary has pointed out a lot ways to assist you on this issue. However, it seems that
the workaround may not look so good on this specific issue. In my view, it's flexible and even
graceful to use subform with a Combo Box. I'm unsure of why you should use continuous form,
but maybe you'd like to display ALL the needed information you've get from the Jet SQL.

If there is anything more I can still do to assist you, please don't hesitate to let me know.
Thanks again for providing detailed information and let us know your concern.

Best regards,

Billy Yao
Microsoft Online Support
 
Back
Top