Access Form: No content with JOIN source

[peterfarge]

Hello NG,

I have a form in Access 2003 with some Combobox and Textbox controls.
The form has a SQL Stmt as Datasource (click on the dark grey in the
Form and choose Properties and Data).
If I choose a simple SQL stmt like "Select * From X Where ID=AAAA",
where AAAA is a foreign variable, my form works. All data is
displayed. If I change the SQL to a JOIN stmnt I get nothing. The form
is complete blank. No Textboxes or Comboboxes are displayed. I mean:
Not only the content is missing, the controls itself are missing.

Do you have an idea how I could change this behavior?


Thanks

Peter

 

[Dirk Goldgar]

in message

Hello NG,

I have a form in Access 2003 with some Combobox and Textbox controls.
The form has a SQL Stmt as Datasource (click on the dark grey in the
Form and choose Properties and Data).
If I choose a simple SQL stmt like "Select * From X Where ID=AAAA",
where AAAA is a foreign variable, my form works. All data is
displayed. If I change the SQL to a JOIN stmnt I get nothing. The form
is complete blank. No Textboxes or Comboboxes are displayed. I mean:
Not only the content is missing, the controls itself are missing.

Do you have an idea how I could change this behavior?

That generally means that two things are true: (1) your recordsource query
doesn't return any records, and (2) no records can be added.

Condition 1 is probably because you are using an INNER JOIN, and there is no
record in the second table that matches any record in the first table on the
join key. If that's what you expect, then change your join to an OUTER JOIN
(LEFT JOIN or RIGHT JOIN).

Condition 2 may be because the form's AllowAdditions property is no, or the
database is read-only, or the recordsource query is not updatable. There
can be a variety of reason that a query may not be updatable, including the
use of the GROUP BY or DISTINCT keywords (a totals query or a unique-values
query), as well as specific situations with complex queries. You can check
whether the query itself is updatable by opening the query (not the form)
directly as a datasheet, and seeing if you can edit or add records. There's
a help topic, "When can I update data from a query?" that discusses this
problem in detail and suggests possible solutions.

 

[XPS350]

Hello NG,

I have a form in Access 2003 with some Combobox and Textbox controls.
The form has a SQL Stmt as Datasource (click on the dark grey in the
Form and choose Properties and Data).
If I choose a simple SQL stmt like "Select * From X Where ID=AAAA",
where AAAA is a foreign variable, my form works. All data is
displayed. If I change the SQL to a JOIN stmnt I get nothing. The form
is complete blank. No Textboxes or Comboboxes are displayed. I mean:
Not only the content is missing, the controls itself are missing.

Do you have an idea how I could change this behavior?

Thanks

Peter

This is 'normal' access behaviour. It has nothing to do with then
join. Try the simple SQL in such a way there is no data and you wil
see the same.

To avoid it check the number of records in your record source before
opening a form. If zero do not open the form and display a message
box.

Groeten,

Peter
http://access.xps350.com

 

[peterfarge]

Thanks Peter and Dirk I'll try it