SQL INSERT INTO via VBA; hates me.

[LarryE]

Eh, you are very close. They sell CCTV installations and have recently wanted to keep some transactions in-house. I started to slap together a simple Access project, and like i always do, mutated it into a monster. I kind of like that though as when i finally get done i've learned something

I hate to tell you this, but "slapping together" an ACCESS project will lead to nothing but trouble. Map out the work processes on paper first and then create the tables and relationships that are needed. For example, as Pat Hartman said, it makes logical sense that:

1. Each customer may have one or more agreements
2. Each agreement may have one or more orders AND one or more guarantors
3. Each order may have one or more pieces of equipment AND one or more technicians

Try to think of it as a hierarchy of data with tables and "sub-tables" that are connected with Primary Keys and Foreign Keys. In addition to what Pat said, I noticed the customer table and Orders table had no PK and FK relationship. They were connected only with a Customer Number. The Agreement table should be connected to the Customer table with a foreign key and the Agreement table should be connected to the Order table with a foreign key.

You also have a CXEmployee table and a CXEmpNames table which contain the same data. And you probably don't need both an OrderEquipment table and an Equipment table. The UnitCount field can be included in the Equipment table.

 

[brucemc777]

@ LarryE: Thank you! Yes, i know we don't "slap together" these things; just a way of describing my hybrid attempts. You would be rather amazed at the number of sheets of paper i have gone through with diagrams, table lists, ideas, more diagrams, areas that need to be reworked, priorities, revisions - and this is not how i make money! Oh, and the hours - more like days, i was watching YouTube videos...

 

[brucemc777]

@ LarryE: Thank you! Yes, i know we don't "slap together" these things; just a way of describing my hybrid attempts. You would be rather amazed at the number of sheets of paper i have gone through with diagrams, table lists, ideas, more diagrams, areas that need to be reworked, priorities, revisions - and this is not how i make money! Add to that the days of watching YouTube videos on the weekend... Anyway, now to make me happy since i have yet to completely grasp INSERT INTO, i am going to figure out how to make what i was doing work, then scrap it and go to the subform answer-

 

[LarryE]

@ LarryE: Thank you! Yes, i know we don't "slap together" these things; just a way of describing my hybrid attempts. You would be rather amazed at the number of sheets of paper i have gone through with diagrams, table lists, ideas, more diagrams, areas that need to be reworked, priorities, revisions - and this is not how i make money! Add to that the days of watching YouTube videos on the weekend... Anyway, now to make me happy since i have yet to completely grasp INSERT INTO, i am going to figure out how to make what i was doing work, then scrap it and go to the subform answer-

Do not create a query or a form or a report or anything else until you get the design and relationships properly defined. It's all a waste of time and effort until you do. Do not worry about how you are going to enter data until the design is completed. You are building a house. You know what happens if the foundation is not properly constructed. Same here. Every single advisor in this forum will tell you the same thing.

 

[brucemc777]

Frankly i owe everyone a huge apology here - you see, my memory plain old sucks and i really am subject to tunnel-vision, and i am not kidding about noticing some autistic tendencies.

To put it al together, i got so fixed in running down different paths to solve this problem and trying to learn from everyone's responses that i completely forgot what the primary motivation was for me taking the path - INSERT INTO - i had chosen, and it actually was a planned path on paper prior to implementing it. It is because at this stage the program is establishing a 60 month set of standard payments (and yes, sometimes they write 36 or 48 month, but for the sake of modeling, 99% are at 60). For this i am placing the INSERT INTO in a FOR-NEXT loop with a DateAdd adding an additional month to the date field for each pass of 1 to 60. Yes, i do want to establish the entire 60 month base set of records for the specific agreement at the onset, to which payments will be noted and any charges such as late charges, personal property tax charges, non-insurance coverage, etc. will be added if need be during the course of the agreement.

Normally i would come up with some stupid excuse to deal with what i have and not publicly declare my personal memory issues to the world; a situation for which i finally am seeking professional help, but after everyone put in so much effort to help me i basically can't be an @$$ and just shrug it off. It's why after working in Excel vba for since the late 1980's or early 1990's i still have to look up syntax and simple functions - frankly, a real PITA, but i know many have much worse issues to deal with, it is just rather embarrassing to both have, and have to admit to.

When i get to the entry of payments received on the stream of future amounts due, or charges that need be assessed, i presently can only imagine a method with a subform. I envision a subform that shows a record for every item that has the specific agreement number on it. I had thought of just the unpaid records, but there are times one wishes to review the entire agreement history.

They are happy i am providing this kind of additional support because i have been in the lending industry one way or another since 1980. I can pull out my soapbox and tell you why people relying on FICO are idiots and why so many institutions brought that pile of garbage in, but then i have worked that area a lot longer than this, and writing code is not my source of income - i just perversely enjoy it like my addiction to Sudoku.

And with that, i will go back to chasing down the proper structuring of that dang INSERT INTO! I hope that all makes sense and now you really do know why i appreciate the help folks like you provide to me.

Many thanks,
-Bruce

 

[ebs17]

And with that, i will go back to chasing down the proper structuring of that dang INSERT INTO!

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

If you don't just want to append one record, but two or many records, then an append query with a SELECT is the first choice. You have to try to put the amount of data you want to append into one table/query and then you can append everything in one go. Loops with individual appends are usually only a second choice.

The task determines the method to be chosen.

 

[LarryE]

I've been fighting to crack this nut for several days now but i am losing what's left of my mind. The cold medicine is not helping-

Because i am awfully new in this i will likely give more details than need be, better more than too few.

We are working with two tables and a form.
tblAgreement (attach: tblAgreement.png): User input of items such as the term, in months, that the agreement is for, the monthly payment, etc., and foreign keys to other tables
tblTrans (attach: tbleTrans.png): To hold the debit/credit transactions along with info on those transactions and foreign keys.
frmInputAgreement (attach: frmInputAgreement.png): A user form for information input, based on tblAgreement, with some of the completed form to be used to establish records in tblTrans.

frmInputAgreement is brought up from another form which has the information for a customer order to install equipment on their property. That information is stored in tblAgreement. Once all the needed fields are completed a cmd button becomes visible that the user would click and a basic cash flow set of records are established in the table tblTrans. Instead of creating a new table for every customer specific work order/installation, one is being used with accounts identified by both the customer number and the order number (as a customer sometimes has more than one installation), and the tblTrans holds both debit and credit information.

The issue i am facing is that though tblTrans plainly has the fields in it that i wish to store the information from the form (frmInputAgreement) in, Access is telling me it cannot find the fields (attach: Error 3127.png). The VBA entering of the SQL is shown in attachment "VBA SQL.png" where you will see i am using dummy values to eliminate possible issues in the VALUES from the form - i am right now focused on just getting the record to be appended in the table. The resultant SQL is shown in the image i included from the VBE Immediate Window, attach: "Immediate SQL.png".

I know i must be doing something technically stupid, but i have tried renaming the fields, running the compact and repair routine, creating a new table by another name, wrote the SQL statement with the table name and field name, and wrote it without the table name, tried brackets (wrong), repeated internet searches and watching many videos and probably several other shots in the dark, besides swearing and getting stressed out, for i know i probably have seen the solution and i likely just didn't catch it.

Would someone please help out, it would seem that yelling at the screen "It is NOT unknown, it is right there!!!" does not help.

Many thanks for your expertise and kind consideration,
-Bruce

I can't test it, but if you insist then try this:

DoCmd.RunSQL "INSERT INTO tblTrans (IDFCustomer, IDFAgreement, TransDate, IDFTransType, TransDebit, TransCredit, TransTax, TransNotes) VALUES (9, 9, 01/01/2025, 1, 2, 3, 4, 'Pigeon');"

Note the semi-colon at the end

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

 

[Gasman]

Note the semi-colon at the end

I never use them in Access SQL?

 

[The_Doc_Man]

The semicolon is part of the standard, but there IS such a thing as a "vendor's implementation option" that would allow omission.

According to ISO/IEC 9075-2:1999, the semicolon is a mandatory delimiter following an "SQL procedure statement" - though that document DOES note that the 1992 version allowed the omission. If I remember correctly, Access follows the 1992 standard, not the more modern one.

 

[LarryE]

May customers make Agreement payments on different dates or is all at one time required? Are the amounts paid in the Agreement table estimates as to the actual installation labor and equipment costs? Are customers billed for any costs over the amounts they paid?