Problem with Form that pulls data from multiple tables

[Stewart Saathoff]

Hello Everyone,

I have been converting a front-end application from VB to Access so that
some of the users have more control over how fields are added and removed
from the forms.

One of the main things that I am having problems with has to do with
databound controls on the form either not appearing or appearing with no
values with some of the tables have no data.

For example, if I have a form that displays data from three tables,
Customers, Jobs, JobInfo and there is no record for the Jobnumber in
JobInfo, none of the fields will populate. Does anyone know how to correct
this? I cannot use subforms because the fields from the various tables are
strategically placed in different positions on the form.

Any suggestions would be greatly appreciated.

Stewart

 

[Larry Linson]

One of the main things that I am having
problems with has to do with databound
controls on the form either not appearing
or appearing with no values with some
of the tables have no data.

Sounds to me as if you are using a query with "equijoins", that is, return
only those records in all tables. If so, then the lack of that record will
cause the query to return nothing, and, disconcerting as it may be, the
display of a "blank form" when there is no data _and additions are not
allowed_ is Access "working as designed". It has worked that way since V 1,
just for the record.

Your choices are to allow additons or to ensure that there is at least one
record (could be a dummy and you could prevent the user actually doing
anything with it by code, if you wanted).

Larry Linson
Microsoft Access MVP

 

[avolcko]

seems wrong

I have had the same issue for years that I have had to do work-arounds. Why is there no event procedure that would be "on no record" as there is on reports( no data). Then you could make a simple error trap.

I have also found that if I use 2 related tables in a query and type a non-existant record entry that the query asks for, I can easily error trap this and close the form with a message box "try again".

As soon as I add a third table to the query, and then type in a parameter that doesn't exist, the same old blank form comes up.

It just seems wrong to me. Maybe there is a reason that this cannot be done at Microsoft.

Andy