UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Access Corrupting Records, Access 2010    
 
   
GavinX
post Apr 15 2018, 08:06 AM
Post#1



Posts: 28
Joined: 10-September 14



My recently developed little access db is corrupting records. Let me explain

The purpose of my database is to characterise transactions in the bank account for tax purposes.
My workflow is;

1. the bank creates a CSV export. I do this for each account.
2. I have a linked access table to read this data. I refresh this table for each new CSV file.
3. An append query that reads this linked table, attaching an account number (that has been specified in a loaded form) into a table and reformating one of the fields. This is done for each account - reading into the same table. Don't know if this is important but this table has no index and is not related to any other. It would only contain say 100 records.
4. Opening a form and subform based on this table I tick the latest transactions (using a checkbox) until they total the desired amount.
5. Then import the ticked transactions into another table that lists the finalised bank transactions.

The corruption happens at point 4. First time I open the form (which is a query based on the table created by the append query listed in point 3) all of the transactions are marked with the correct account number. if I open this form again (after previously closing it) I notice that one, maybe two transactions have the wrong account number. This is usually the first or last record. I don't understand why this is happening.
I have imported the database into a blank database in case there was a corruption. Still displays the same behaviour. I have checked the queries and nothing should be doing this. Prior to importing the database into a blank one I was having the problem of when opening the form in point 4 a new blank record was created. After importing this behaviour has disappeared.

What could cause these few fields to change?

Thanks for any help
Go to the top of the page
 
GroverParkGeorge
post Apr 15 2018, 08:40 AM
Post#2


UA Admin
Posts: 32,372
Joined: 20-June 02
From: Newcastle, WA


" if I open this form again (after previously closing it) I notice that one, maybe two transactions have the wrong account number."

This is probably NOT the result of corruption. You'd see random changes if that were the case. Probably, this is the result of logic not implemented correctly in the VBA that performs the step.

So that's where we need additional detail about HOW the VBA accomplishes the intended task.

Thanks.

--------------------
Go to the top of the page
 
GavinX
post Apr 15 2018, 09:26 AM
Post#3



Posts: 28
Joined: 10-September 14



At point 4 I open a form that is based on a query of the data. This form contains a combo box based on part of that query whose purpose is to list the unique account numbers that are contained in the data. No VBA here.
This form in turn has a sub form (continuous form) based on the same data, the Child link field is the account number as is the Master Link field. This data is listed in date order, newest first.

After further investigation I have realised;
I select the combo box in the main form and select the account number resulting in the sub form only listing transactions assigned to the account number. The cursor drops to the first record in the sub form. It also creates a new blank record - but this is at the bottom of the records listed which is below the page which I have only just noticed.
I tick the checkbox for the various records in the sub form and when they total the correct number I return to the main form and select the next account number from the combo box - and here is the problem. Because the sub form remains in edit mode whatever record is current is automatically amended with the new account number I have selected in the main form combo box.

So here is the problem. I do not want to amend the subform except for being able to tick the checkboxes. And I certainly do not want it to change the account number OR add records.
I have confirmed the table of this data does not have any fields set to Required so I don't know why new a new blank record is being created. How to fix?

thanks for your help.
Go to the top of the page
 
GroverParkGeorge
post Apr 15 2018, 10:08 AM
Post#4


UA Admin
Posts: 32,372
Joined: 20-June 02
From: Newcastle, WA


I apologize. It'll take a bit to digest all of that. Hang on, please.

--------------------
Go to the top of the page
 
projecttoday
post Apr 15 2018, 11:06 AM
Post#5


UtterAccess VIP
Posts: 9,381
Joined: 10-February 04
From: South Charleston, WV


Can't you just set Allow Additions of the subform to No?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
GroverParkGeorge
post Apr 15 2018, 03:03 PM
Post#6


UA Admin
Posts: 32,372
Joined: 20-June 02
From: Newcastle, WA


I think Robert's suggestion is probably the simplest, most effective.

--------------------
Go to the top of the page
 
GavinX
post Apr 15 2018, 06:52 PM
Post#7



Posts: 28
Joined: 10-September 14



QUOTE
Can't you just set Allow Additions of the subform to No?


That seems to have stopped the behavior of adding records.

Now I need to solve the other issue; the combo box in the main form changes whatever record had focus in the main form.
Just to recap - the main and subform are based on a query of a transaction table. The combo box on the main form is also based on a query of this data - it lists the unique account numbers in the data. The child and master link field is the account number. The sub form display the filtered transactions whose whose account number matches that in the combo box of the main form. Note the transactions listed on the sub form include the account numbers.

So the workflow is to select an account in the combo box and then check the check box fields of various records in the sub form whose account number matches that in the combo box. I then select the next account number in the combo box. However on this action the focus moves to the top (or newest) record in the subform and changes the account number field to that which matches the combo box

I have changed the account number field on the sub form to Locked but this is ineffective.
Is there are way to fix this?

thanks
This post has been edited by GavinX: Apr 15 2018, 07:08 PM
Go to the top of the page
 
projecttoday
post Apr 15 2018, 08:30 PM
Post#8


UtterAccess VIP
Posts: 9,381
Joined: 10-February 04
From: South Charleston, WV


Are you saying the wrong record is selected in the subform after a selection in the main form?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
GavinX
post Apr 15 2018, 10:00 PM
Post#9



Posts: 28
Joined: 10-September 14



The sub form is continuous...the correct records are shown. But when I select another account in the combobox on the main form this process changes the first record of the subform field's account number to that which matches the combobox.
Go to the top of the page
 
zaxbat
post Apr 15 2018, 10:19 PM
Post#10



Posts: 652
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


This does not make sense to me.....in all of my child-master linked subforms.....as soon as I exit the subform it writes back to the table any of my changes.
Now, as soon as I go up into the main control (you have a combobox, i usually use a datasheet....same no matter)....but as soon as you select a different header/master record the subform should refill itself with the associated detail records for the newly chosen header record. For my purposes I put a hidden textbox on the main form....then in the oncurrent event of the datasheet...I stuff the primarykey of the header into the hidden textbox. This textbox is the master for my subforms linkage master-child. This works really well. I am just wondering what linkage you are using. It seems to have a lag where something odd is happening to your subform.

--------------------
A picture is worth a thousand words and a zipped DB is worth a thousand pictures.

Cheers! Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
projecttoday
post Apr 16 2018, 04:07 AM
Post#11


UtterAccess VIP
Posts: 9,381
Joined: 10-February 04
From: South Charleston, WV


But the data in the table are not changed? Can you post it?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
GavinX
post Apr 16 2018, 07:32 AM
Post#12



Posts: 28
Joined: 10-September 14



All fixed - I replaced the combobox with a text box and step through the records manually and no longer have those problems.

Thanks for your help.
Go to the top of the page
 
projecttoday
post Apr 16 2018, 07:42 AM
Post#13


UtterAccess VIP
Posts: 9,381
Joined: 10-February 04
From: South Charleston, WV


Glad you got it working.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
John Vinson
post Apr 16 2018, 04:02 PM
Post#14


UtterAccess VIP
Posts: 4,213
Joined: 6-January 07
From: Parma, Idaho, US


QUOTE
Now I need to solve the other issue; the combo box in the main form changes whatever record had focus in the main form.


I see you got it solved but for the lurkers... this implies that the combo box was BOUND to the field in the form's table. A combo box that is intended to be used for updating a record should be bound; but if (as in this case, or for a combo used to select which record to bring up) the combo is NOT intended to update the current record, it must be UNBOUND - have nothing in its Control Source.

--------------------
John W. Vinson
Wysard of Information
Go to the top of the page
 
GavinX
post Apr 16 2018, 07:55 PM
Post#15



Posts: 28
Joined: 10-September 14



QUOTE
A combo box that is intended to be used for updating a record should be bound; but if (as in this case, or for a combo used to select which record to bring up) the combo is NOT intended to update the current record, it must be UNBOUND - have nothing in its Control Source.


Nailed it.

Thanks for the lesson - I should know that already. hat_tip.gif
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd April 2018 - 05:48 AM