Solved Take three user selected fields and use an INSERT INTO statement to create a new record in a table that only has those three field AND a Key

[brucemc777]

I forgot what i was doing and typed practically the entire dilemma into the title...

Anyway, we have a table that has four fields, the first being a key autonumber field.

In a form there is an area i provide for the user to select three values, which happen to coincide with those three remaining record fields after the key field.

I can easily access (no pun...) the three form fields, but what the heck am i supposed to do for the key field?

I am using INSERT INTO and VALUES - is that my mistake? I am rather (VERY) new to this.

re: Attachments:
tbleOrderEquipment is design view of, well, the table.
SQLline was the SQL without the key autonumber field and simulation for the other values, which failed miserably
SQL Error was Access giving me the finger.

Thank you VERY much for considering helping me!

 

[theDBguy]

Anyway, we have a table that has four fields, the first being a key autonumber field.

In a form there is an area i provide for the user to select three values, which happen to coincide with those three remaining record fields after the key field.

I can easily access (no pun...) the three form fields, but what the heck am i supposed to do for the key field?

Since it's an Autonumber field, you shouldn't have to insert it. Access will do it for you automatically.

 

[plog]

Does tblEquipment have a record with IDEquipment=1? Does tblOrders have a record with IDOrders=2? My guess is you are trying to add records to this table which don't have matches in their respective tables.

However, I question if this is the best method at all. Why not use a bound form and let it do the INSERTing for you?

 

[LarryE]

I forgot what i was doing and typed practically the entire dilemma into the title...

Anyway, we have a table that has four fields, the first being a key autonumber field.

In a form there is an area i provide for the user to select three values, which happen to coincide with those three remaining record fields after the key field.

I can easily access (no pun...) the three form fields, but what the heck am i supposed to do for the key field?

I am using INSERT INTO and VALUES - is that my mistake? I am rather (VERY) new to this.

re: Attachments:
tbleOrderEquipment is design view of, well, the table.
SQLline was the SQL without the key autonumber field and simulation for the other values, which failed miserably
SQL Error was Access giving me the finger.

Thank you VERY much for considering helping me!

Are you sure you want a space before the second VALUES argument? It looks like there is a space there. In addition, you need a ; (semi-colon) at the end.
DoCmd.RunSQL "INSERT INTO tblOrderEquipment (IDEquipment,IDOrders,UnitCount) VALUES (1,2,3);"
should work

 

[brucemc777]

Thank you EVERYONE!
I had expected email notification of replies; i was surprised to see that there were any! Time for me to chat with GoDaddy about forwarding my email (again), but i switched my email address here to the direct one.

@theDBguy: When i try

SQL = "INSERT INTO tblOrderEquipment(IDEquipment,IDOrders,UnitCount)VALUES(1,2,3);"
    DoCmd.RunSQL SQL

I get that same error as before as seen in "SQL error.png". Due to the number of failures i am starting to wonder if something, ANYTHING, else that i might have done that is causing this issue.

@plog: Re: Does tblEquipment have a record with IDEquipment=1? Does tblOrders have a record with IDOrders=2?
Ohhhhh boy.... I tried

SQL = "INSERT INTO tblOrderEquipment(IDEquipment,IDOrders,UnitCount)VALUES(1,6,3);"

and that worked fine. Now i need to find out if the Combobox and two textbox values are being converted compatibly - i recently tried CInt(), but the test was brief and need to retry both CInt() and CLng(). Progress!!!
The form (frmAddEquip) is actually bound to that table with the table displayed in it. The textbox IDOrders field is tied to the parent form of frmAddEquip being frmEquipOrder, and frmEquipOrder is the child form of frmInputOrder. frmInputOrder has basic customer information and the key field IDCustomer in it, which is tied to the subform frmEquipOrder which displays all of the orders associated with that customer along with their respective IDOrders. frmAddEquip has two compnents: 1) the equipment so far associated with that specific order, and 2) four controls: the textbox showing IDOrders (the specific order number from frmEquipOrder), a combobox from which the user can select a specific item of equipment that are maintained in a table that has a catalogue of items they prefer, 3) a textbox to enter the quantity they are going to use on that order, and 4) a command button to commit those previous three values to the db tblOrder Equipment. Convoluted enough???

frmInputOrder (Display customer info and have the IDCustomer key field)
frmEquipOrder (General info on the order such as location, typed of order, installation date, relevant notes, and the IDOrder field)
frmAddEquipment(Display all items of equipment added to the order and allow for the entry of new items as a selection process)

Anyway, i need to now follow that path you have put me on and find out what is not going right between my selection controls and the INSERT INTO process!

@ LarryE: Some of those spaces were directly from when i let Access generate the SQL code, some were me, some were from copying examples - heck, i was also sometimes throwing in extra doble quote marks, trying anything. Every once in a while i actually hit on a solution, but all the trial and error can waste time. I am stubborn and like to solve things myself, but at some point i yell for help!

OK! Thank you EVERYONE for your input. Though i have had years (decades) working with Excel and VBA, this is Access and anyway my memory is the opposite of my daughter the doctor's (how does that happen?), i truly appreciate each of you.

 

[gemma-the-husky]

Generally you can't have too many spaces. I add them between all terms in a SQL string even if they aren't necessary. Sometimes they are, and it saves hunting for the syntax error.

 

[brucemc777]

It's ALIIIIVE! (Frankenstein)

Thank you, everyone!

 

[Pat Hartman]

If you use a bound form, you don't need to write code to append records. Access automagically saves dirty forms.

 

[brucemc777]

If you use a bound form, you don't need to write code to append records. Access automagically saves dirty forms.

Once i got it all working the way i envisioned and looked at it i remembered what you wrote and realized i basically invented a car with two steering wheels. My way of adding a record at it's most basic user related form was no easier than just using the continuous table display, which might take a new user about 15 seconds of training, but i suspect that wouldn't be a major concern.

Having to relearn everything, including the user interface, is a bit rough because i told them "no problem, have it for you in a week or two!". Yeah, and it's in the mail...

 

[Pat Hartman]

KISS is always the best method, especially when you are using a RAD tool. Know your tool and use it to its advantage. Think positively about how much spare time you will have

 

[brucemc777]

KISS is always the best method, especially when you are using a RAD tool. Know your tool and use it to its advantage. Think positively about how much spare time you will have

KISS was taught to me by my father when i was about 12. Unfortunately i learned SWAG in college and it would seem i adopted it-

 

[Pat Hartman]

Unfortunately i learned SWAG in college and it would seem i adopted it-

That's why I tried to talk the grandchildren out of going to college.