Adding data to a table

[Jbelle7435]

I have a form with one text box and one button. The text box is labeled part name and named txtpartname. The button is named cmdadd. Then I have a table with a column called partname.

I want to input a part name into the text box, select the button and this will add that part name to the table(one at a time). Could someone help me with the programming in the CMDadd_Click() to be able to do this.

Example


1. I type block. Click Add button, Block will appear as the 1st part name
2. I type ball. Click Add button, Circle will appear as the next part name and so forth...
...

[khalid]

Private sub CMDadd_Click()
Dim strSQL As String
Dim db As dao.Database
    Set db = CurrentDb
    strSQL = "INSERT INTO YourTable (TableField)"
    strSQL = strSQL + "VALUES ("   
    strSQL = strSQL + "'" & ControlOnForm & "' )"   
    db.Execute (strSQL)

hth.

[Jbelle7435]

Private sub CMDadd_Click()
Dim strSQL As String
Dim db As dao.Database
    Set db = CurrentDb
    strSQL = "INSERT INTO YourTable (TableField)"
    strSQL = strSQL + "VALUES ("   
    strSQL = strSQL + "'" & ControlOnForm & "' )"   
    db.Execute (strSQL)

hth.

thanks will try this out later and give the results..

PS. type in ball and ball will appear!! not circle

[Jbelle7435]

update(01): I did what you said to the best of my ability and this is what happened.

I entered data(apple, banana, carrot) into the textbox and clicked the button and the table did not automatically update as I kept it open. I then closed it and opened it again and the ID field became 23,24,25 and the Part_Name was left blank ???

Then I added duck, elephant and the ID field became 23,24,25,26,27
I will try to make the Part_Number as the Primary key and see what happens??
- - - - - - - - - - - - - - - - - - -
These are the steps that got me to this point.

1. I created "Table1" with a column called Part_Name in field1
concerns: Do I still keep the actual 1st column named ID with Autonumber and use that as the primary key or do I remove it and only keep the Part_Name Column only?

2. I create the form based off table1 and only inserted the Part_Name as one of the fields creating a textbox lnked to the table.
Concerns: Is this the way to go about linking the table to my textbox?

3. I added the "CMDadd" button and placed this code inside it.

Private sub CMDadd_Click()
Dim strSQL As String
Dim db As dao.Database
    Set db = CurrentDb
    strSQL = "INSERT INTO Table1 (Part_Name)"
    strSQL = strSQL + "VALUES ("   
    strSQL = strSQL + "'" & ControlOnForm & "' )"   
    db.Execute (strSQL)
End Sub

Concern:I updated the code as per your requested areas to match my information but am I missing anything else??

[khalid]

I did what you said to the best of my ability and this is what happened.

I entered data(apple, banana, carrot) into the textbox and clicked the button and the table did not automatically update as I kept it open. I then closed it and opened it again and the ID field became 23,24,25 and the Part_Name was left blank ??

?
If table is open, it need to refresh to see the updates/changes to the table, you have to press the refresh icon from the menu to refresh the records.

Then I added duck, elephant and the ID field became 23,24,25,26,27
I will try to make the Part_Number as the Primary key and see what happens??

- - - - - - - - - - - - - - - - - - -
The code is doing well what you have given to do. You are giving the IDs of you items on you form. I am not sure if you are using combo box/text box or what on your form, which bound column is 1 (the id of the item) so the code is taking the ids and INSERTing them in the table:

These are the steps that got me to this point.

1. I created "Table1" with a column called Part_Name in field1
concerns: Do I still keep the actual 1st column named ID with Autonumber and use that as the primary key or do I remove it and only keep the Part_Name Column only?

The logical and best way is to keep the IDs of the table, which is useful for relationships and identification of the records.

2. I create the form based off table1 and only inserted the Part_Name as one of the fields creating a textbox lnked to the table.
Concerns: Is this the way to go about linking the table to my textbox?

how do you link the table? The form should have the underline data source the table you have created, just create a form based on the your table and bring the fields from your field list. (if you want to create un-bound field, then its the case you are after and using the code)

3. I added the "CMDadd" button and placed this code inside it.

Private sub CMDadd_Click()
Dim strSQL As String
Dim db As dao.Database
    Set db = CurrentDb
    strSQL = "INSERT INTO Table1 (Part_Name)"
    strSQL = strSQL + "VALUES ("   
    strSQL = strSQL + "'" & ControlOnForm & "' )"   
    db.Execute (strSQL)
End Sub

This "'" & ControlOnForm & "' ) should be the textbox/Combo box name on your form to be interred in the column Part_Name.

Hope this is clear now?

[Jbelle7435]

?
If table is open, it need to refresh to see the updates/changes to the table, you have to press the refresh icon from the menu to refresh the records.

OK

?
This "'" & ControlOnForm & "' ) should be the textbox/Combo box name on your form to be interred in the column Part_Name.
Hope this is clear now?

OK..... We have a winner....

The ID still begins at 22 and works its way from there as per each entry but I am not concern with that so hope it won't be a factor.

Thank you until the next concern! I will try to now figure out how to do this for multiple boxes with multiple fields for one click!

[Jbelle7435]

OK
Thank you until the next concern! I will try to now figure out how to do this for multiple boxes with multiple fields for one click!

ok round #2 is not getting the best of me but I need a push...

1. Inputting Data into two textboxes named txtPart_Name, txtDescription
2. Clicking CMDadd
3. Data should be placed in table as per each entry(2 columns filled)

Dim strSQL As String
Dim strSQL1 As String
Dim db As DAO.Database
Dim db1 As DAO.Database

    Set db = CurrentDb
    Set db1 = CurrentDb
    strSQL = "INSERT INTO Table1 (Part_Name)"
    strSQL1 = "INSERT INTO Table1 (Description)"
    strSQL = strSQL + "VALUES ("
    strSQL1 = strSQL1 + "VALUES ("
    strSQL = strSQL + "'" & txtPart_Name & "' )"
    strSQL1 = strSQL1 + "'" & txtdescription & "' )"
    db.Execute (strSQL)
    db1.Execute (strSQL1)

RESULTS:
It is adding the data to both columns so I am getting the data to the table which is a great start except.... the description is one row below the Part Name(They are diaganol of each other). How to get both on the same row per entry.???? I'll assume it has something to do with the strSQL=strSQL+... because that reminds me of a loop like x = x+1

So close but will keep trying....

[June7]

The code is adding two records. Each Execute adds a record. Try:

db.Execute = "INSERT INTO Table1 (Part_Name, Description) VALUES ('" & txtPart_Name & "', '" & txtDescription & "')"

I am curious about why you do this. Do you have a table of parts and info about the parts? You are saving this info to a table of orders or something like that? If so why don't you just save the unique id for that part record? That could be done without any coding.

[khalid]

Yupe! you are adding the record twice in a table, it should add in one go. June7 gave you the way how to insert data in multiple columns?
Each column should be separated with comma , and then for each column/field the order of the inserting data should be same as the order of the fields in your INSERT statement.

hth.

[Jbelle7435]

deleted post...

[Jbelle7435]

The code is adding two records. Each Execute adds a record. Try:

db.Execute = "INSERT INTO Table1 (Part_Name, Description) VALUES ('" & txtPart_Name & "', '" & txtDescription & "')"

I am curious about why you do this. Do you have a table of parts and info about the parts? You are saving this info to a table of orders or something like that? If so why don't you just save the unique id for that part record? That could be done without any coding.

Is this how my code should look.

Dim db As DAO.Database
Set db = CurrentDb
db.Execute = "INSERT INTO Table1 (Part_Name, Description) VALUES ('" & txtPart_Name & "', '" & txtDescription & "')"

Steps taken.

1. Added the description column
2. Added the txtdescription textbox
3. Modified the code as above
4. Clicked and got an error
(COMPILE ERROR ARGUMENT NOT OPTIONAL)
and it refers to the txtdescription

db.Execute = "INSERT INTO Table1 (Part_Name, Description) VALUES ('" & txtPart_Name & "', '" & txtDescription & "')"
??

It does update the table which is nice except
1. The ERROR pops up each time
2. It overwrites ID22 which contains the part name and description I input in the textboxes each time.

[June7]

Don't understand why any record would be overwritten as the INSERT sql adds a new record. What table is the ID22 record in? Are the textboxes bound to table fields?

Never seen that error message. But try:
Me.txtPart_Name & "', '" & Me.txtDescription

Still curious why doing this.

[Jbelle7435]

Don't understand why any record would be overwritten as the INSERT sql adds a new record. What table is the ID22 record in? Are the textboxes bound to table fields?

Never seen that error message. But try:
Me.txtPart_Name & "', '" & Me.txtDescription

Still curious why doing this.

I am more worried about the error that is leading to this overlapping condition. I attached my file to get a better look at it. Instead of description I changed that to revision.

[June7]

As this form is setup, the INSERT sql is not needed. The form is bound to the table and textboxes are bound to fields of the recordsource. Entries to textboxes are entered directly to table. The code, if it didn't error and not sure why it is, would create a duplicate record. What you want to do is commit the new or edited record to the table. This is accomplished by one of:
1. close form
2. move to another record
3. code (VBA or macro) - DoCmd.RunCommand acCmdSaveRecord

[Jbelle7435]

As this form is setup, the INSERT sql is not needed. The form is bound to the table and textboxes are bound to fields of the recordsource. Entries to textboxes are entered directly to table. The code, if it didn't error and not sure why it is, would create a duplicate record. What you want to do is commit the new or edited record to the table. This is accomplished by one of:
1. close form
2. move to another record
3. code (VBA or macro) - DoCmd.RunCommand acCmdSaveRecord

I guess I am trying to do it by code(3) unless there is a more simpler way?
A couple of text boxes corresponding to a couple of columns in a table. Input into the text boxes then one click will fills those columns accordingly.

I got the search down but now its loading the table and then searching for it via FORM!

Thank you for all your help up to now and Khalid as well.