Does the Conditional Formatting resume working after the import if you enter a new record manually again?

In other words the only records that are not working are the imported ones?
Or...  All of the records after an import no matter what quit working?

You need to look for what is different at the table level on an import vs typed.

Hey Scott!  Thanks for the reply I really appreciate it.  I am trying to use the check register to make taxes easier.  lol

Yes - The Conditional Formatting resumes working after I enter a new record manually.
Yes - Only imported records appear to be affected and don't work

OK I will try comparing the 2 tables.  Thanks

I just don't get it.  I have tried to import / append directly to Rick's table, in case i was doing something wrong when added the import data as a new table, and then create the query to add it to Rick's table.  I tried to save data to a brand new excel spreadsheet as values.

No matter what i try, nothing seems to work.  Imported transactions loose the conditional formatting and the checkbox functions.  When I click on the CalcGST checkbox of an imported transaction, the CalcGST puts a checkmark, but then it deletes the zeros that were there, and there is blanks in the subtotal and GST fields.

Could my problem have something to do with the LockUnlockFields?  Is there a setting in Excel or Access I have to change?  Is anyone else having the same problem i am with importing transactions?

OK so the GST doesn't work because there is no amount in the Amount Field created in CheckRegQ - So that makes sense.  But why would the amount stop calculating for the imported fields only?  The imported data all have ID's, so it's not because the transactions didn't save to the table.

When you perform the import you are bypassing all of the code that is in the form that does all of you checking.

So as a part of the import you need to do the came calculations and checking that you do when you are typing things in manually.

Ah Ok.  How do I do that when I am importing?  I'm not sure what you mean.  Sorry can you explain a bit further what steps or something i would have to take.

Before making any assumptions lets make sure that is in fact what is happening.

So basically the import would be like opening up the table and directly entering the data in without the benefit of a form.  Try manually entering one of the import records "in the back" just as you are importing it.  In other words leaving out any calculations, lookups, etc that you are doing in the forms.

Check out this post toward the bottom where I helped out Michelle Maughan.  Same basic thing was happening to her.  When she did the import, the import "skipped" the part covered in the form of creating the EntityCode.  The fix was to go update the whole table using a Recordset.

My thinking here is that you would need to do that same type of operation for importing your records.  Since I don't know how your forms are working with regard to data entry - what events are happening as you enter data in the form - I don't know how to advise you what you need.

What ever code is in your form we'll need to duplicate via your VBA when bulk entering records as in the import.  The difference is the computer is entering a record vs a person entering a record into a form.

Hope that makes sense.  (sorry for the long response)

Hey Scott!  Thanks for replying, and I love long responses.  The more information the better.  :)
I read your post to Michelle, Incorporated the MakeEntityCode and DoEntityCode in my Check Register Database, to see if it would work to add a Transaction Code to my Tables, and it did it perfectly.

CheckRegF Record Source is CheckRegQ  (I added SubTotal and GST to the Query)
I chose not to lock my added fields (CalcGST Button)
Amount was Ricks   Amount: [Credit]-[Debit]
ClearedAmount was Ricks   ClearedAmount: IIf([IsCleared],[Amount],0)
Running Balance was Ricks   RunningBalance: DSum("Credit","CheckRegT","AccountID=" & [AccountID] & " AND ID<=" & [ID])-DSum("Debit","CheckRegT","AccountID=" & [AccountID] & " AND ID<=" & [ID])

I added Subtotal in Query    SubTotal: Round(IIf([CalcGST],[Amount]/1.05,0),2)
I added GST in Query    GST: Round(IIf([CalcGST],[SubTotal]*0.05,0),2)

In the meantime I am going to study the scenario and coding (Michelle's Solution) a bit more.

Glad that helped.  The best way would be to incorporate that into your import, working with one record at a time, as you are pulling it in.

Richard has teachings in the Developer courses just how to do something like this.
I see that you have the LC membership but have started skipping around a bit in the Developer.  I really encourage you to start back with Dev 1 and go through the things in order. (I totally understand needing to get stuff done.)
You now have the exciting things ahead of you but really get the base down solid just as you have done with the Beginner and Expert courses.

Hey Scott!  Unfortunately it didn't solve my problem, I was only able to add a a Transaction Code to my table. (Michelle's Solution)
Do you know which developer course that Richard teaches, shows how to do something like this?  I'm on a bit of a time crunch.

You will have to bring in every field that you are importing via the RS.  That goes in between the
      rs.Edit and the rs.Update

      rs.Edit
Field1 = rs!field1
Field2 = rs!field2
Field3 = rs!field3
---
Fieldxxx = rs!fieldxxx

      rs.Update
      rs.MoveNext    'to the next record

Richard started in with Record Sets beginning in Developer 16 on.  I'll look this evening but I don't recall which Dev lesson has the best example right off hand.

BTW -
The rs.Edit is assuming  ypu are editing an existing record.
If this is going to be ongoing you would use the .AddNew property instead of .Edit and read the record in from the  table of file you are working from.  See Developer 18

Scott - Thank you for the information, and it will be used ongoing.  I will get on it tonight.

All of a sudden i am getting a syntax error (missing operator in query expression ...) in my RunningBalance Field on the CheckRegQ.

RunningBalance: DSum("Credit","CheckRegT","AccountID=" & [AccountID] & " AND ID<=" & [ID])-DSum("Debit","CheckRegT","AccountID=" & [AccountID] & " AND ID<=" & [ID])

What am I missing?

Try splitting them into 2 fields then do the subtraction.