Your query here is matching records in the "client" table with the records
in "misc" and "finances." There are 16 records in the "client" table that
have values in the MasterID field that have no matched records in one -- or
both -- of the other two tables. That's why these 16 records aren't showing
up in the Recordset that is being used as the source of your Form.
You need to determine whether these 16 records should or shouldn't be
included in the Form's Recordset, but first you need to see *which* records
are in the "client" table, but not in the other two tables.
To do so, create a new query. Paste this entire SQL statement into the SQL
View pane:
SELECT Client.MasterID, Misc.MasterID, Finances.MasterID
FROM (Client LEFT JOIN Misc ON Client.MasterID = Misc.MasterID) LEFT JOIN
Finances ON Client.MasterID = Finances.MasterID
WHERE (IsNull(Misc.MasterID) OR IsNull(Finances.MasterID));
Run the query. In the Client.MasterID field you'll see which 16 unmatched
records aren't showing up on your Form when you open it. Wherever you see a
blank in the field named Misc.MasterID or Finances.MasterID is where there's
a record that *might* be missing from one of these other tables. I write
"might" because there may be a good reason that these records don't exist in
these tables, but only you can decide that after you've gathered a little
more information.
Open the "client" table and search for the values in each of the 16 records
in the Client.MasterID field of the query you just created. Look at these
records and investigate why you may or may not need matching records in the
"misc" or "finances" tables.
If there *should be* matching records in each of these 16 cases, then you
need to figure out why they aren't there. Perhaps someone forgot to add
these records in other data entry Forms. Perhaps these tables are results
of imports from a spreadsheet or another database that never needed any of
these 16 records. Or maybe there are other reasons.
So, you need to do some further investigation, but at least you know *why*
those records aren't showing up in your Form, and you know exactly which 16
records are in your "client" table, but aren't in either of the "misc" or
"finances" tables.
Good luck and post back if you have any further problems.
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips.
rleblanc said:
The complete SQL statement is as follows:
SELECT client.MasterID, client.CreationDate, client.l_name, client.f_name,
client.MI, client.SSN, client.DOB, client.Street, client.City, client.State,
client.ZipCode, client.PhoneNumber, client.MaritalStatus, client.AdultDeps,
client.Children, client.Seniors, finances.ClientWages,
finances.UnemployComp, finances.WorkersComp, finances.TANF,
finances.SSDisability, finances.SSI, finances.VeteranPension,
finances.SSRetirement, finances.ChildSupport, finances.SSSurvivor,
finances.RentalIncome, finances.FoodStamps, finances.OtherIncome,
finances.WIC, finances.RentAssistance, finances.FuelAssistance,
finances.CityWelfare, finances.TEFAPLetter, finances.IncomeMemo,
misc.[General Memo] FROM (client INNER JOIN misc ON
client.MasterID=misc.MasterID) INNER JOIN finances ON
client.MasterID=finances.MasterID;
'69 Camaro said:
Excellent. Open the Form in "Design View." Open the Properties
window,
and
select the "Data" tab. Place your cursor in the "Record Source" text area,
right in the part that reads "SELECT" and then press <SHIFT><F2> to
open
the
Zoom window. Copy and paste the entire SQL statement displayed in the Zoom
window into your next post so that we may see it and analyze it to determine
what's limiting your records.
By the way, when you make the "client" table the record source for your
Form, you're getting the "#Name?" showing in the text boxes on the
form
that
are bound to the fields in the one or more other tables in the query. Since
these fields don't exist in the "client" table, Access can't figure
out
what
field you want to bind each of these text boxes to.
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips.
Data Entry is set to no.
Record Source is set to SELECT. There are no VBA modules in this database
as
yet.
If I set the Record Source to "client" (which is the master table to which
everything else is related), some of the fields get filled with #name?
instead of the actual data. However, the data in the client table is
completely accessible.
Any more ideas?
Check the Form's Record Source Property (on the Form Properties "Data"
tab)
to see whether it is based upon a saved query whose criteria excludes
your
newest records or whether there's a SQL statement that also excludes
your
newest records.
If the Record Source Property isn't the culprit, look at the VBA
code
to
find a "Filter = ..." for this Form (the code doesn't actually
have
