By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,955 Members | 1,756 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,955 IT Pros & Developers. It's quick & easy.

How to Add data to a Table from a form in Access 2007?

P: 10
Hi,
I would like to be able to have a button on a form, that when clicked will add specific information from that form to a table.
I would be greatful to anybody who can tell me how to do this! I assume that I have to give the 'text boxes' a specific name, but I need the code for the button that will search for that specific text box, then add it to a specified table.

Many thanks in advance,

Tim Matthews
Mar 17 '07 #1
Share this Question
Share on Google+
14 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Hi,
I would like to be able to have a button on a form, that when clicked will add specific information from that form to a table.
I would be greatful to anybody who can tell me how to do this! I assume that I have to give the 'text boxes' a specific name, but I need the code for the button that will search for that specific text box, then add it to a specified table.

Many thanks in advance,

Tim Matthews
Hi Tim

Assuming ...
Command button called cmdAdd
Table called Test
Fields called Field1 and Field2
Textboxes on Form called textbox1(Number) and textbox2(Text)

Then the code for the button would be:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdAdd_Click()
  2. Dim strSQL As String
  3.  
  4.    strSQL = "INSERT INTO Text (Field1, Field2) VALUES (" & Me!textbox1 & ", '" & Me!textbox2 & "');"
  5.    DoCmd.RunSQL strSQL
  6.  
  7. End Sub
  8.  
Mary
Mar 17 '07 #2

P: 10
Hi Mary,
Thanks for the quick reply!

However, I'm recieving a message box:

You are about to append 1 row(s)
Once you click Yes, you can't use the Undo command to reverse the changes.
Are you sure you want to append the selected rows?

I click "Yes", but when I check the table the information hasn't been inserted.

I have one other question, will this command add the information into a new row, or will it overwrite the information already in the table? I need it to add the information into a new row.

Thanks again,

Tim Matthews
Mar 17 '07 #3

P: 10
Well now I feel stupid!!!!
I've just found its necessary to click the "refersh" button!!!!

Sorry!

Tim
Mar 17 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
Well now I feel stupid!!!!
I've just found its necessary to click the "refersh" button!!!!

Sorry!

Tim
You can add the line

Expand|Select|Wrap|Line Numbers
  1. Me.Requery
or

Expand|Select|Wrap|Line Numbers
  1. Me.Refresh
after the DoCmd.RunSQL line
Mar 17 '07 #5

P: 10
Thanks again Mary!
Just two more questions, (sorry my coding skill is very limited!), I also want to insert "raw" text into one of the Fields in the table, or info from a "Label".
Also how do I add more than one row at a time to a table?

Thanks,

Tim
Mar 17 '07 #6

P: 10
Hi,
I've figured out how to add more than one line at a time to the table :)
Could you please tell me if it is possible to stop the "You are about to append 1 row(s)" message box that appears?

Thanks

Tim
Mar 17 '07 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi,
I've figured out how to add more than one line at a time to the table :)
Could you please tell me if it is possible to stop the "You are about to append 1 row(s)" message box that appears?

Thanks

Tim
In Tools - Options under the Edit/Find tab there is a confirm section. Untick the Action Queries box.

Mary
Mar 17 '07 #8

P: 10
Hi,
I've got the code that enters data into a table from text boxes in a form working well, but I would like to be able to insert raw text, or information from a label, into the same row as the data from the text boxes.
Can someone help me with this please?

Thanks for all your help so far,

Tim
Mar 17 '07 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi,
I've got the code that enters data into a table from text boxes in a form working well, but I would like to be able to insert raw text, or information from a label, into the same row as the data from the text boxes.
Can someone help me with this please?

Thanks for all your help so far,

Tim
Tim

Not sure what you mean. Do you want an unbound textbox on the form for some reason? Sorry I just can't comprehend what you're looking for. Maybe if you explain what you are doing and why it will make more sense.

Mary
Mar 17 '07 #10

P: 10
Hi,
I'll try and make more sense....
On a form I want to be able to select a "Object Name" from a combo box, (Taken from a table), and then enter some parameters into a text box, ("Length", "Height", "Depth"). Then export that information to another Table.
For example, on a form I'll have a combo box, from which I'll select:
Object Type "Box1"
And then enter the following parameters:
Length: "200"
Height: "300"
Depth: "100"
I then want to "click" a button to export all the information to a table, so the table would read:
id Field1 Field2 Field3 Field4
1 Box1 200 300 100

I can enter all the numerical values with the code you showed me, but I can't find out how to get the text entered into the field.

Thanks,

Tim
Mar 17 '07 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi Tim

Go back to my example in post# 2 and look at how I handled inserting the value into textbox2 which held text. The value has single quotes on either side of it.

Mary
Mar 17 '07 #12

P: 10
Hi,
In Field1 instead of "Box1" bieng inserted I get the value of the ID from the table that contains "Box1" instead of the text, (Box1).
So the table reads:
ID Table1 Table2 Table3 Table4
1 1 200 300 100

instead of:
ID Table1 Table2 Table3 Table4
1 Box1 200 300 100

This is the code I'm using:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cmdAdd_Click()
  3. Dim strSQL As String
  4.  
  5.    strSQL = "INSERT INTO List (Field1, Field2, Field3, Field4) VALUES ('" & Me!Type & "', " & Me!Lenght & ", " & Me!Height & ", " & Me!Depth & ");"
  6.    DoCmd.RunSQL strSQL
  7.  
  8. End Sub
  9.  
  10.  
Thanks,

Tim
Mar 17 '07 #13

P: 10
I've found what I've done wrong, I didn't set the "Row Source" Correctly in the combo box property sheet.
Thanks for all your help :)

Tim
Mar 17 '07 #14

MMcCarthy
Expert Mod 10K+
P: 14,534
I've found what I've done wrong, I didn't set the "Row Source" Correctly in the combo box property sheet.
Thanks for all your help :)

Tim
No problem Tim. Glad you got it working.

Mary
Mar 18 '07 #15

Post your reply

Sign in to post your reply or Sign up for a free account.