Records irretrievable from tables

[jman]

I have a quoation table that is linked to a quoation details table. I then
have a form with a quoation table main form and quation details subform. This
subform contains three fields, all of which are controlled by the RTF2
ActiveX control so that words can be individually formatted. The subform is
linked to the main form by the Quotation No.

Once I enter data into the form, subform, or both it essentially disappears.
If I click my print preview button, the report is blank. If I close the form
and reopen it, it goes to a new record pretending like the old record never
existed. After I enter that new record, I cannot advance to another new
record. Although all this occurs, the data is correctly entered in both
tables. I cannot view the records through either the form or the report. The
report is setup via a query to retrieve data from an employees table and the
two quotation tables. When I click on that query, it also retrieves no data
even though there is data in all three tables.

I am at a complete loss as to why this is occurring and can only guess that
it has something to do with the rtf control (which is necessary for the form
to function in the required manner).

 

[John Vinson]

Once I enter data into the form, subform, or both it essentially disappears.
If I click my print preview button, the report is blank. If I close the form
and reopen it, it goes to a new record pretending like the old record never
existed. After I enter that new record, I cannot advance to another new
record. Although all this occurs, the data is correctly entered in both
tables. I cannot view the records through either the form or the report. The
report is setup via a query to retrieve data from an employees table and the
two quotation tables. When I click on that query, it also retrieves no data
even though there is data in all three tables.

Perhaps you could post the SQL of the query. Are you CERTAIN that the
joins are correct, and that there is matching data in the joining
fields in all three tables? The symptom certainly sounds like the data
is in the tables, but the join (or the data in the join fields) is
incorrect!

John W. Vinson[MVP]

 

[jman]

SELECT [Quotation Sheet].[CC4:], [Quotation Sheet].[CC3:], [Quotation
Sheet].[CC2:], [Quotation Sheet].[CC1:], [Quotation Sheet].QuoteID,
[Quotation Sheet].Date, [Quotation Sheet].[Quotation No], [Quotation
Sheet].[Customer Inquiry No], [Quotation Sheet].[Customer Information],
[Quotation Details].Description, [Quotation Details].Quantity, [Quotation
Details].[Price Per], [Matrix Employees].[Full Name], [Matrix Employees].Title
FROM [Matrix Employees] INNER JOIN ([Quotation Sheet] INNER JOIN [Quotation
Details] ON [Quotation Sheet].[Quotation No] = [Quotation Details].[Quotation
Number]) ON [Matrix Employees].EmployeeID = [Quotation Sheet].CboName;


I don't know much about SQL, but it looks ok becuase Quotation No in
Quotation Details should be the same as Quotation Number in Quotation Details
and all others look correct. Maybe the join type is incorrect? I don't even
know what the join type is so any help would be greatly appreciated.

 

[jman]

Also, the problem has slightly changed to now there is no problem with
accessing or entering the records with the form. The information is still not
being transmitted to the query or report from that query though.

 

[John Vinson]

SELECT [Quotation Sheet].[CC4:], [Quotation Sheet].[CC3:], [Quotation
Sheet].[CC2:], [Quotation Sheet].[CC1:], [Quotation Sheet].QuoteID,
[Quotation Sheet].Date, [Quotation Sheet].[Quotation No], [Quotation
Sheet].[Customer Inquiry No], [Quotation Sheet].[Customer Information],
[Quotation Details].Description, [Quotation Details].Quantity, [Quotation
Details].[Price Per], [Matrix Employees].[Full Name], [Matrix Employees].Title
FROM [Matrix Employees] INNER JOIN ([Quotation Sheet] INNER JOIN [Quotation
Details] ON [Quotation Sheet].[Quotation No] = [Quotation Details].[Quotation
Number]) ON [Matrix Employees].EmployeeID = [Quotation Sheet].CboName;

This makes me very suspicious. Does your table named [Quotation Sheet]
actually have a field called CboName? This sounds like the name of a
Form Control, not a field stored in a table.

Your other question is:

Also, the problem has slightly changed to now there is no problem with
accessing or entering the records with the form. The information is still not
being transmitted to the query or report from that query though.

I'm sorry but this makes no sense. Information is not "transmitted to
the query from the query", or for that matter it's not "transmitted"
to the Report (at any rate I've never heard that term used). The Query
is the Recordsource for the report; if you open the query in datasheet
view, you will see the data which is available for formatting and
display on the Report, and no other data will be available on the
Report.

If you look in the three tables - Quotation Sheet, Quotation Details,
Matrix Employees - do you see the values you expect? Are any of these
fields Lookup fields (which will cause confusion because the value you
*see* is not what's stored in the table)?

John W. Vinson[MVP]

 

[jman]

I'm sorry for incorrect terminology use. I am very new at this. I do
understand what is being done between the queries, tables, reports, etc.

When I open the tables, I do see the values I expect in all of them. One
field (employee name) is a lookup value where it looks up the full name of
the employee in that table.

Again though, this query and its report all have been working fine until
recently. I cannot figure out what the problem is.

 

[mnature]

Again though, this query and its report all have been working fine until
recently. I cannot figure out what the problem is.

Have you tried just going back to a recent backup (from just before the
problem started) to see what the difference may be between that and the
current database?

 

[John Vinson]

When I open the tables, I do see the values I expect in all of them. One
field (employee name) is a lookup value where it looks up the full name of
the employee in that table.

THat may be the problem! If you have a Lookup field in the table, when
you look at the table datasheet you *see* an employee name. But what's
actually IN the table is a concealed ID number. It's that number which
must match in your Join expression; if you're trying to match the
Lookup Field to a text employee name in another table, it won't work.

John W. Vinson[MVP]