Sorry, that's "CurrentProject.Connection", not "CurrentPorject.Connection"

=)

mmcdonal

Hi mmcdonal,

Thanks, it worked!

I did catch the misspelling, and wasn't sure about it it being a command word and needing to be spelled that way, or not. So I did a google scan and then made the spelling change. Not a biggie!

My PK is two words (eg. 'Customer Number'), and that was giving me grief, so changed that to 'Customer_Number' in both tables and in the code and that resolved that.

I'm still getting a little hiccup, but it seems to be in my field names, so not sure yet if I spelled one differently in either one of the tables or the code, but should be able to resolve that tonight.

In this code, can I have multiple word field names, or do I need to link them together with something (eg. 'Customer DOB' vs. 'Customer_DOB')? If I need to link them, than that would solve the problem, and I'm guessing that is the case. As when I comment out all the fields except for the first two (eg. Date, Customer_Number), then it writes the new record (with just those two fields) to the archive file (TableB) and then deletes the record from the active file (TableA), just like I need it to do.

Again, I can't thank you enough! I've been in the business for decades, and even do SQL for internet DB's (eg. MySQL) but just couldn't get past this! Guess I'm going to need to do some more studying up! This coding is all new to me, but sure seems powerful, once you get the right functions and syntacs!

Thank you much!

Glad to help.

SQL does not like spaces in field names, but the usual way around that if they have ended up in your design is to bracket them, so:

SELECT Customer Number FROM tblCustomer etc

becomes

SELECT [Customer Number] FROM tblCustomer etc.

Most Access developers use Pascal case on their field names, and Camel case on thier table and type names, so:

CustomerNumber for the field name
tblCustomer for the table
cboComboBox for the control name

SQL developers use caps and underscores mostly, so

CUSTOMER_NUMBER for a field name
CUSTOMER or Customer for a table name etc

HTH


mmcdonal

Yes, that helps tons! And, it's good to know some of the standards, there's nothing better than to follow the 'norm'. Sure helps in maintenance too!

Again, I can't thank you enough!