SQL INSERT INTO via VBA; hates me.

[brucemc777]

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

 

[Gasman]

See if this post helps

Post in thread 'Insert Into Not Working'

Jul 15, 2024

A simpler and more robust solution is to use a temp QueryDef...

    Const SQL_INSERT As String = _
        "Insert Into tblFileRequestData " & _
            "(File_Number, File_Name, File_Title, File_Requested_By, File_Requested_Date, File_Data_Entry_Date, File_Status) " & _
        "Values " & _
            "( p0, p1, p2, p3, p4, p5, p6);"
    
    With CurrentDb.CreateQueryDef("", SQL_INSERT)
        .Parameters(0) = flnum
        .Parameters(1) = flnam
        .Parameters(2) = fltitle
        .Parameters(3) = usern
        .Parameters(4) = flreqdate
        .Parameters(5) =...

• MarkK

• 
• 

 

[The_Doc_Man]

First... save yourself a mound of headaches. When you have only one table involved (as is the case for the INSERT INTO, but it is also valid for UPDATE and SELECT and DELETE), you do not need to qualify the location of each field with a table name. After all, fields are going to be involved with that targeted table or nowhere. Shortening the typing might clarify what is missing or wrong.

Offhand, I don't see in any of your presentations that the reason for error 3127 "leaps out of the page" at me. The name appears in the table, so isn't a clear-cut spelling error. It MIGHT be a hidden non-printing character in the name. Are you using the extended character set option here? (I.e. UNICODE?)

I believe there IS a quoting/inclusion error in the way you handled the date field. See /compare VBA SQL and Immediate SQL date fields. I think you need to include the octothorpes inside the quotes so that the final string contains them. Could be wrong, but I think you need that. The invalid field is the FIRST error hit so you never get the second error. And you handled the text field correctly with doubling the quotes, but you COULD have included single-quote/apostrophe rather than doubling the quotes. What you did there WASN'T in error but using apostrophes reduces the amount of typing by a small amount. So that's just a nit-pick.

 

[Isaac]

As much as I enjoy unbound forms, this just may be a time to realize there are such a thing as bound forms and man, are they handy.

 

[brucemc777]

@Gasman: Will try and report!
@The_Doc_Man - With my rather limited knowledge, though i started with only using the field names i included the table name later out of frustration!

Though i honestly didn't know i could switch character sets, and frankly neither do i have a clue as to when this would be of advantage,, wondering if something could have crept in had motivated me to not only rename the field a number of times, but after that, i even deleted and re-created the table under two or three different names. And as you specifically cite, i was just waiting for that to throw an error, but i thought i might as well let'er run and then if there was an issue address it, yet i never got that far. On the other hand, when working in Excel VBA i have found that something you would never thought was related to an error would sometimes cause one to show and the description had nothing to do with the actual cause, so i believe it best on your observation that i address that now.

@Isaac: Presently this puppy is bound to tblAgreement; i'm afraid i don't have the knowledge to perhaps transfer info to another table specifically bound to tblTrans nor double up tables on a single form in order to append a record, i am learning, but it is going step by step!!

Thank you, everyone. I'll now get back to solving this and praying that some of what i do for a living comes through to distract me in the meantime... This project is a favor for a business that sends me business!

 

[plog]

Agree with Isaac, why do have to write an INSERT statement at all. Make it a bound form and this issue goes away.

With that said, I don't think the error is with that field, but something more general. That's just the first field of the statement so its the one getting flagged. I suggest you switch the first two fields, swap the order ofIDFCustomer and IDFAgreement, and I bet it fails with an error about IDFAgreement.

The real way to solve this (and most coding issues) is to divide, conquer and just get something working. Remove everything from that INSERT but one field. Get that one field to work and then add more after it does.

Lastly, you shouldn't have separate fields for credits and debits. Put them all in the same field and math becomes a lot easier.

 

[plog]

Presently this puppy is bound to tblAgreement; i'm afraid i don't have the knowledge to perhaps transfer info to another table specifically bound to tblTrans

Looking at that form, you definitely need to learn about subforms:

Create a form that contains a subform (a one-to-many form) - Microsoft Support

Learn how to create a form in Access that contains a subform (also known as a one-to-many form, a parent/child form, or a hierarchical form).

support.microsoft.com

tblTrans should be its own form based on tblTrans, then you can embed it on that form just like you did frmCXPG and frmCXEMP.

However, you overcomplicated those forms as well. You made a button to presumably open another form for users to add data to those subforms, that doesn't need to be done. You can make frmCXPG and frmCXEMP have the ability to accept edits and additions right on that main form. Both forms would just have a new blank line where users could select more people to add. tblTrans should work similarly.

 

[brucemc777]

@plog: Ahhhhhh, yes, i should have so stated but i was a little too wound up with everything, the error does progress to the next item in line when i eliminate the preceding one. I do apologize for not noting this fact. That is why i am first attacking the date field issue The_Doc_Man brought up, just in case.

As to "divide & conquer", either i read one of your replies to someone else or coincidence, but this is what i was in the process of doing when i chucked out all of the actual data in the VALUES section and put in the generic items; my brain simply skipped a track (OK, that is from when we had something called "records" for music) and i did not take it to the level you are advising, but will!

And last, i was going to go with one field and simply go with positive and negative, but i came by an article from someone who seemed like they had experience at this before who said making it into separate fields made reporting easier, so i changed to that. I suspect i will have to experiment at some point, but for now i want to find out why the heck this INSERT isn't working!

Many thanks,
-Bruce

 

[LarryE]

The error is telling you there is no field named tblTrans.IDFCustomer because there isn't. The field name is IDFCustomer. You will continue to receive this error for every field name until you remove the tblTrans. part in every one of the field names in the statement.

Make things easier on yourself by attaching the file to a new post so someone can look at you overall design and table relationships. Why you are not using bound forms or subforms I have no idea, but maybe the design can tell us something. All ACCESS projects start with a properly designed table and relationship structure. We need to see yours. If you cannot attach the file, then at least post a screenshot of your Relationships window. That will help a lot.

 

[brucemc777]

@LarryE: Thanks for pitching in to help!!! I'm sorry i did not make it more prominent, but i only started throwing in the table designation on each after repeated failures without it. As to bound forms and subforms, perhaps i do not understand the terminology due to my lack of history and experience with Access, for by some other definitions i am extensively using them.

In this specific instance i need to rethink the use of a subform with its fields derived from the parent form frmInputAgreement, much like in frmInputAgreement the people who will be personally guaranteeing the transaction (subform frmCXPG) are selected from the bank of employees that are employed by that firm (frmCXEMP) and recorded in a table containing records of all companies (by their key) and all of their employees by a many to many relationship through a joining table or query. I'll stick that relationship table onto this- By the way, i found some possible relationships had to be unlinked as the link was interfering in some processes, and there are probably a lot of better ways to handle some structures, but once again, i've now been working in Access for the last two or three weeks, maybe four, after not touching it for over ten years, and this isn't some idiot homeowner's association db...

In any case i would still like to find out what is going wrong with my INSERT INTO approach rather than simply working around it, though i agree sometimes a work around is the only reasonable way!

 

[brucemc777]

@plog I was trying to reply to your post, but as long as i included your post the board kept refusing to take it! OK, so you might have to go between what you wrote elsewhere and here-

The implementation of the subform technique you see was scratched together afrer i posted in several (yup, cross posting) forums looking to see if the same basic routine like what is used by microsoft when adding or subtracting items from the ribbon is done with side-by-side displays that one can add or subtract from. Seemed to me that this should already be a pre-canned procedure, but no one responded so i took it on myself and came up with that. It works great! What's the problem? It does the job and doesn't look too horrible- Wait a minute, i'll look at it again... Nope, on the left is the complete bank of employees of the company from which to select the one or two actual personal guarantors from, which get listed in the subform on the right, and it does not look like [expletive deleted] (i am from the Nixon years...), does it?



I like the idea of tblTrans having it's separate form as a subform and will be working that-



As to overcomplicating, it is intended. I turned off allowadditions to clean the screens up and allow them to toggle it on when needed. The links from the comboboxes embedded in the subforms provide nice formal forms for information entry that encompass much more information for use when needed.

 

[ebs17]

In any case i would still like to find out what is going wrong with my INSERT INTO approach

1) In the field list, the diw table name must be omitted, the table name is already after the INSERT INTO.
2) 1/1/2024 => #1/1/2024#

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

 

[brucemc777]

Ahhhh, once again when i try to reply with your text in quote hereabove, the website will not accept it...

Hmmmmm, i went over that full article hoping to learn something new - exactly what was it that you are referring to? I know i am so new to this that i simply might not understand my deficiencies (Known as the "Dunning-Kruger Effect"), but i am using a number of master forms with subforms not only linked by selected fields but also by embedded fields so a subform can have another subform in that, and another in that ad infinitum depending on space, modeled so they simultaneously present defined listings of equipment on site, authorized parties, technicians, and from each of those subforms by double clicking on the equipment description row items you get detailed information about the specific equipment's capabilities, equipment detail webpage link if available, model numbers and production information and within that form by clicking on the supplier's name you get detailed information on the supplier such as address, location, phone number, contacts, main company webpage, and with any employee by clicking on their line item you can bring up full information including contact info, capabilities, certification and so on and so forth (a more common way to say "ad infinitum!", so for my development could you please tell me exactly what part of that page should i focus upon?

 

[LarryE]

@LarryE: Thanks for pitching in to help!!! I'm sorry i did not make it more prominent, but i only started throwing in the table designation on each after repeated failures without it. As to bound forms and subforms, perhaps i do not understand the terminology due to my lack of history and experience with Access, for by some other definitions i am extensively using them.

In this specific instance i need to rethink the use of a subform with its fields derived from the parent form frmInputAgreement, much like in frmInputAgreement the people who will be personally guaranteeing the transaction (subform frmCXPG) are selected from the bank of employees that are employed by that firm (frmCXEMP) and recorded in a table containing records of all companies (by their key) and all of their employees by a many to many relationship through a joining table or query. I'll stick that relationship table onto this- By the way, i found some possible relationships had to be unlinked as the link was interfering in some processes, and there are probably a lot of better ways to handle some structures, but once again, i've now been working in Access for the last two or three weeks, maybe four, after not touching it for over ten years, and this isn't some idiot homeowner's association db...

In any case i would still like to find out what is going wrong with my INSERT INTO approach rather than simply working around it, though i agree sometimes a work around is the only reasonable way!

This appears to be some kind of equipment leasing and servicing process where some customers need guarantors. Can you tell us what you are tracking here?

 

[brucemc777]

Good Lord! Every time i leave the original writing from anyone in the website blocks my response post!

Agreed! As mentioned, it was a frustration attempt! And thanks for the help with fixing up the date. I've had a bad habit for years (OK, decades) now just noting a date in string and converting it to a date datatype when needed. I finally decided to change - old dog, new tricks!

 

[brucemc777]

This appears to be some kind of equipment leasing and servicing process where some customers need guarantors. Can you tell us what you are tracking here?

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. My wife refuses to ask me to help with anything simple in Excel because i always do that. Matter of fact, one Excel project i did for my brother i named Frankenstein due to that, and another that i use for my own purposes is named Kraken - it started off as Octopus. This "thing" i am "slapping together" started off named "Guardian Angel" and now is "Arc Angel"...

OMG!!! This board allowed me to reply to you with your original message intact! That's the first out of three or four!

 

[plog]

Hmmmmm, i went over that full article hoping to learn something new - exactly what was it that you are referring to?

Sorry I wasn't more specific. It was just the general idea that subforms are a way to allow data entry into multiple tables from one main form. When you have a one-many relationship you can make the main form bound to the one side and have a subform bound to the many side and users never have to leave the main form to add/edit/delete data from any of those tables nor do you have to craft INSERT/UPDATE queries to make them work in that manner.

 

[brucemc777]

Ah! Like i did in having a main form show the company data and a subform on it in continuous configuration with the orders placed, editable for corrections! Is that it? To be quite honest, this isn't the first time someone has had to point out the exact same mistake i've made in this same project. I'm afraid between handling different things i get confused as to how the heck will i get from point A to point C, and many times not even clear on B, so instead of the integration i model separate forms and then find i would have been far better off and not stuck at an impasse if i simply used a subform! Thank you!!!

 

[brucemc777]

This board hates me. It keeps rejecting posts with any links. Over at excelforum dot com there is a further infuriating issue i am dealing with; any help is appreciated. The post's title is

VBE F5 is only working like F8 after F8 has been pressed​

I've tried using the link provided in the tools for it and posting the link in plain form, but like above when quoting, the board rejects the post.

 

[Pat Hartman]

Here are a couple of overall issues.
1. queries do not belong in the schema dialog
2. your naming is inconsistant. In some places you use IDF and in other cases just ID for foreign keys
3. several tables include IDCustomer or IDFCustomer in addition to the actual foreign key. So tblAgreement, for example, has IDOrders so it does not need IDCustomer. tblTrans has IDFAgreement and so does not need IDFCustomer.
4. tblPersonalGuarantor can be problematic since unless you are careful, you could end up with an employee that belongs to a different customer.
5. There may be a better way to draw the schema. For example, should the relationship be Customer --> Agreement --> Order rather than the way you have it. Does every order have a separate agreement? That sounds unusual. I would expect it to be the other way around.

I don't know where you will be getting the data to create the Trans record so I'm not sure why this would be done with an append query. I think you need either a subform or a popup form.

Here are two slightly different examples that show 1-m and m-m form sets.

P

Thread 'Many-to-many example'

Jul 20, 2008

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one.

The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with the booked by info in the subform and a Booker main form with the venue booked info in the subform.

• Pat Hartman
• Replies: 3
• Forum: Sample Databases

•