Report overrides data in table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am doing a db to record Account Transactions. In the db, there are a few
types of report, eg. View Transactions per month, View per year, View by
supplier. I have almost completed the db except one problem - when I run the
report, it overrides my data in the table!

For example, Record 01 has amount $100 payable to Company A, and Record 02
has amount $200 payable to Company B. When the report is run for the first
time, it shows the correct data. But when I run it again, Company A turns out
in Record 02 and vice versa. If I keep running the report, the data is
switched again and again.

Did I do anything wrong? Please help!!
 
We're not there, so it's going to be tough guessing what might be happening.

When you say "the data is switched", do you mean the underlying table rows
are modified? Or are you saying that the report shows the data in different
places?

Do you use a query as a source for the report? If so, does the query change
as well? Could you post the SQL statement of your query?

Do you have any code-behind-report for this report? Can you post that code?
 
The data is switched in the sense that the rows are modified, yes. Eg. Bank
Name in row one shows Bank A, and in row two Bank B. When the report is
opened, the data is switched, row one becomes Bank B and row two becomes Bank
A.

Each time I open the report, the data is switched again and again. I tried
with more data, ie. 6 rows, and this still happens, affecting all the rows
with data.

I created a query and base my report on the query, below the code:

SELECT Transaction_Records.TransactionDate, Transaction_Records.BankID,
Transaction_Records.DebtorID, Transaction_Records.MiscID,
Transaction_Records.SubConID, Transaction_Records.SupplierID,
Transaction_Records.Amount_Received, Transaction_Records.Amount_Paid,
Transaction_Records.PaymentNumber_Received,
Transaction_Records.PaymentNumber_Paid
FROM Transaction_Records
WHERE (((Transaction_Records.BankID)=[Forms]![Dialog_Test]![BankID]));

My report only has this code:

Private Sub Report_NoData(Cancel As Integer)
MsgBox "There is no data for this report. Canceling report..."
Cancel = -1
End Sub

Thanks for helping.
 
I'm still not convinced that the data is changed IN THE TABLE. Are you
saying that you have a report that somehow modifies the TABLE rows? If so,
my only guess is that your application is haunted! <g>

Or are you describing what you see in the report, i.e., the "row" being on
which line the data is displayed in the report? This seems more likely to
me, and could be caused if you are not giving the report any particular sort
order to follow.

--
Good luck

Jeff Boyce
<Access MVP>

P.S., it helps to keep your replies "tacked onto" the original thread

spacerocket said:
The data is switched in the sense that the rows are modified, yes. Eg. Bank
Name in row one shows Bank A, and in row two Bank B. When the report is
opened, the data is switched, row one becomes Bank B and row two becomes Bank
A.

Each time I open the report, the data is switched again and again. I tried
with more data, ie. 6 rows, and this still happens, affecting all the rows
with data.

I created a query and base my report on the query, below the code:

SELECT Transaction_Records.TransactionDate, Transaction_Records.BankID,
Transaction_Records.DebtorID, Transaction_Records.MiscID,
Transaction_Records.SubConID, Transaction_Records.SupplierID,
Transaction_Records.Amount_Received, Transaction_Records.Amount_Paid,
Transaction_Records.PaymentNumber_Received,
Transaction_Records.PaymentNumber_Paid
FROM Transaction_Records
WHERE (((Transaction_Records.BankID)=[Forms]![Dialog_Test]![BankID]));

My report only has this code:

Private Sub Report_NoData(Cancel As Integer)
MsgBox "There is no data for this report. Canceling report..."
Cancel = -1
End Sub

Thanks for helping.

Jeff Boyce said:
We're not there, so it's going to be tough guessing what might be happening.

When you say "the data is switched", do you mean the underlying table rows
are modified? Or are you saying that the report shows the data in different
places?

Do you use a query as a source for the report? If so, does the query change
as well? Could you post the SQL statement of your query?

Do you have any code-behind-report for this report? Can you post that code?

--
More info, please ...

Jeff Boyce
<Access MVP>

run
the turns
out
Click to expand...
Click to expand...
 
Hi Jeff,

I am not sure myself. I just re-opened the report and the data has 'run'
again. Then I checked the table, indeed the data in the table has another
value. In the SQL query, I took out the last line:

WHERE (((Transaction_Records.BankID)=[Forms]![Dialog_Test]![BankID]));

And my last few tries shows that the report is running normally. Perhaps
this code is causing it....?

Please explain what is meant by "giving the report a particular sort order
to follow".

Also, please teach me how do I assign a default value to a combo box?

Thanks in advance!

Jeff Boyce said:
I'm still not convinced that the data is changed IN THE TABLE. Are you
saying that you have a report that somehow modifies the TABLE rows? If so,
my only guess is that your application is haunted! <g>

Or are you describing what you see in the report, i.e., the "row" being on
which line the data is displayed in the report? This seems more likely to
me, and could be caused if you are not giving the report any particular sort
order to follow.

--
Good luck

Jeff Boyce
<Access MVP>

P.S., it helps to keep your replies "tacked onto" the original thread

spacerocket said:
The data is switched in the sense that the rows are modified, yes. Eg. Bank
Name in row one shows Bank A, and in row two Bank B. When the report is
opened, the data is switched, row one becomes Bank B and row two becomes Bank
A.

Each time I open the report, the data is switched again and again. I tried
with more data, ie. 6 rows, and this still happens, affecting all the rows
with data.

I created a query and base my report on the query, below the code:

SELECT Transaction_Records.TransactionDate, Transaction_Records.BankID,
Transaction_Records.DebtorID, Transaction_Records.MiscID,
Transaction_Records.SubConID, Transaction_Records.SupplierID,
Transaction_Records.Amount_Received, Transaction_Records.Amount_Paid,
Transaction_Records.PaymentNumber_Received,
Transaction_Records.PaymentNumber_Paid
FROM Transaction_Records
WHERE (((Transaction_Records.BankID)=[Forms]![Dialog_Test]![BankID]));

My report only has this code:

Private Sub Report_NoData(Cancel As Integer)
MsgBox "There is no data for this report. Canceling report..."
Cancel = -1
End Sub

Thanks for helping.

Jeff Boyce said:
We're not there, so it's going to be tough guessing what might be happening.

When you say "the data is switched", do you mean the underlying table rows
are modified? Or are you saying that the report shows the data in different
places?

Do you use a query as a source for the report? If so, does the query change
as well? Could you post the SQL statement of your query?

Do you have any code-behind-report for this report? Can you post that code?

--
More info, please ...

Jeff Boyce
<Access MVP>

I am doing a db to record Account Transactions. In the db, there are a few
types of report, eg. View Transactions per month, View per year, View by
supplier. I have almost completed the db except one problem - when I run
the
report, it overrides my data in the table!

For example, Record 01 has amount $100 payable to Company A, and Record 02
has amount $200 payable to Company B. When the report is run for the first
time, it shows the correct data. But when I run it again, Company A turns
out
in Record 02 and vice versa. If I keep running the report, the data is
switched again and again.

Did I do anything wrong? Please help!!
Click to expand...
Click to expand...
Click to expand...
 
As far as I know, the WHERE clause of a SQL statement that isn't an "action"
query has no way to modify the underlying data. Re-post your full SQL
statement, please.

When you are in design mode for a report, you can use the Grouping/Sorting
feature to specify the order your data will be displayed -- it doesn't
matter what order you set in your query, the report needs its own.

Default values for controls are set via the Default property of that
control.

--
Good luck

Jeff Boyce
<Access MVP>

spacerocket said:
Hi Jeff,

I am not sure myself. I just re-opened the report and the data has 'run'
again. Then I checked the table, indeed the data in the table has another
value. In the SQL query, I took out the last line:

WHERE (((Transaction_Records.BankID)=[Forms]![Dialog_Test]![BankID]));

And my last few tries shows that the report is running normally. Perhaps
this code is causing it....?

Please explain what is meant by "giving the report a particular sort order
to follow".

Also, please teach me how do I assign a default value to a combo box?

Thanks in advance!

Jeff Boyce said:
I'm still not convinced that the data is changed IN THE TABLE. Are you
saying that you have a report that somehow modifies the TABLE rows? If so,
my only guess is that your application is haunted! <g>

Or are you describing what you see in the report, i.e., the "row" being on
which line the data is displayed in the report? This seems more likely to
me, and could be caused if you are not giving the report any particular sort
order to follow.

--
Good luck

Jeff Boyce
<Access MVP>

P.S., it helps to keep your replies "tacked onto" the original thread

spacerocket said:
The data is switched in the sense that the rows are modified, yes. Eg. Bank
Name in row one shows Bank A, and in row two Bank B. When the report is
opened, the data is switched, row one becomes Bank B and row two
Click to expand...
becomes
Bank
A.

Each time I open the report, the data is switched again and again. I tried
with more data, ie. 6 rows, and this still happens, affecting all the rows
with data.

I created a query and base my report on the query, below the code:

SELECT Transaction_Records.TransactionDate, Transaction_Records.BankID,
Transaction_Records.DebtorID, Transaction_Records.MiscID,
Transaction_Records.SubConID, Transaction_Records.SupplierID,
Transaction_Records.Amount_Received, Transaction_Records.Amount_Paid,
Transaction_Records.PaymentNumber_Received,
Transaction_Records.PaymentNumber_Paid
FROM Transaction_Records
WHERE (((Transaction_Records.BankID)=[Forms]![Dialog_Test]![BankID]));

My report only has this code:

Private Sub Report_NoData(Cancel As Integer)
MsgBox "There is no data for this report. Canceling report..."
Cancel = -1
End Sub

Thanks for helping.

:

We're not there, so it's going to be tough guessing what might be happening.

When you say "the data is switched", do you mean the underlying
Click to expand...
table
rows
are modified? Or are you saying that the report shows the data in different
places?

Do you use a query as a source for the report? If so, does the
Click to expand...
query
change
as well? Could you post the SQL statement of your query?

Do you have any code-behind-report for this report? Can you post
Click to expand...
that
code?
--
More info, please ...

Jeff Boyce
<Access MVP>

I am doing a db to record Account Transactions. In the db, there
Click to expand...
are a
few
types of report, eg. View Transactions per month, View per year,
Click to expand...
View
by
supplier. I have almost completed the db except one problem - when
Click to expand...
I
run
the
report, it overrides my data in the table!

For example, Record 01 has amount $100 payable to Company A, and Record 02
has amount $200 payable to Company B. When the report is run for
Click to expand...
the
first
time, it shows the correct data. But when I run it again, Company
Click to expand...
A
turns
out
in Record 02 and vice versa. If I keep running the report, the data is
switched again and again.

Did I do anything wrong? Please help!!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top