SQL INSERT INTO via VBA; hates me.

[brucemc777]

Thank you Pat!

"Queries do not belong in the schema dialog" Forgive me, i suspect that is what i think of as the relational display? I honestly am still a bit confused as to when i should link two tables and when i should just let Access either handle it or me handle it when linking a Parent to a Child form, and i suspect that is why i put it there (presuming that my suspicion is correct).

The naming at one point was actually consistent and anything that referred to a key was named ID<name>. Those variations which you have pointed out, and very probably others, will be fixed. They were implemented, like including the table name with fields in the SQL, out of frustration, thinking maybe there is some remnant of the old field name hanging around causing the issues.

I must study your point #3. I think i had hoped what you state was the case when designing those, but chose rather to chance redundancy rather than omit something i might need. That will take some review on my part and i thank you for the education!

Regarding the personal guarantors, they are selected from that "left list", which is specifically filtered to only include people who are employees of the subject company. I appreciate you pointing that out as it would be a major embarrassment for me!

In my world an "order" is generally an installation at a specific site, which might be done days or years apart. Each one does get a separate agreement, even when they honestly could be done contemporaneously because if a business owner wanted to sell off one operation it makes life SOOOOOOO much easier!

I really, REALLY, like the subform solution. I'm afraid brain cells don't connect as well as they used to, and i suspect they honestly never connected as well as i like to pretend they once did. I am pursuing that soon, but... I've got to thank plog once again for the "divide & conquer" motivation. It was my misuse of the date value that bunged it all up. I do have another issue now that i am trying to nail down - i messed up a "type conversion" and a "key violation" which i suspect might be based on the same issue., but i'm no longer banging my head against a wall out of frustration.

I really want to thank all of the good folk on this board for their guidance; i'm afraid when i get frustrated i get tunnel vision, i suspect a bit of autism might be present but i have enough to deal with without going to figure that one out, so we'll leave it at my most sincere thanks to you all-

And now to make dinner as it's my turn and don't want the wife to shoot 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.

 

[Pat Hartman]

i have yet to completely grasp INSERT INTO, i am going to figure out how to make what i was doing work,

But you shouldn't be doing this. You should use a subform. It is a cleaner interface and no code. "no code" is always the best solution. I've written my million lines of code and I don't need the practice so that is one of the things I like about Access. The vast majority of form work can be done with no code. The only code you always need in forms is validation code to minimize the possibility of saving invalid or empty records. This isn't to say I never write code but I never write code I don't need to write. If Access has a feature, I use it and subforms are invaluable. In fact, Access is one of the few development platforms that actually supports the concept. One of the things Access does is when you add a subform to another form and set the master/child links, Access will ensure that every subform record always has the correct FK and you don't have to worry about it.

Subforms are simply regular forms that you add on another form by using a special subform control which has the properties needed to link the two forms. Originally, Access supported only 3 levels of nesting. I think they're up to 7. I cannot envision a form with six nested subforms so I won't even think about it but 1 and 2 are very common.

 

[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.

 

[Pat Hartman]

Since the payment records do not exist anywhere, a select append doesn't solve the problem although it would be the most efficient method.

Your current solution is the least efficient. Using an append query for each record runs a separate query. Your query starts as an SQL string rather than a querydef which makes it even worse. When you start from a text string, the query engine has to compile the query and develop an execution plan and then run the query. This takes time. Not a lot for one execution but you are doing 60 and that is probably enough to show a slight delay.

A better solution if you insist on inserting empty records, is to use DAO and .AddNew to add each row. You also need a variable that tells you the term. So that variable controls how many times the loop runs and how many records get appended. Do NOT hard code the 60.

The best solution is to not do this at all. What happens if the contract is cancelled? Do you leave the empty records? Do you delete them? have you thought about this?

What you do instead is run queries that take the start date of an agreement and figure out when the next payment is due. Do you send notices to people? You also can calculate the amount due to date and compare that to the amount paid and determine who is in arrears and how far. We can help to create the queries you need based on what you are looking for.

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

This reminds me of an application I built for American Express. You may remember the "take one's" that were on top of every cash register of every business that accepted Amex. These folders were applications for an Amex green card and had a list of questions. Well I wrote the application that scored the answers and determined whether or not you got a card. There was a gray area where your score was such that a human had to decide. One of the questions was "Have you ever taken a loan from Household Finance?" Those people were the loan sharks of the period and only people who couldn't get a loan from anyone else would use them. I think that was the name of the loan company. If not, I apologise to Household Finance. Answering yes to that question got you a -40 so it almost always disqualified you because answering "correctly" to the other questions your max score was 60 and 60-70 meant a human would intervene.

 

[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?