Copying specific fields from a form to a table

[Shannon88]

I am trying to create a command button in a form that will copy only three fields on the current form into a table. I have tried creating a query but the query will give me all the RECORDS from that vendor. I also attempted to create a macro that I miserably failed at.

Any Help would be greatly appreciate!

Thanks,
Shannon88

 

[jbehrne]

Hi,

I hope this helps - everything is not entirely there, but it should be a start.This will be run by a button called "Insert":
(Clean up the code!!! - I'm not sure if I'm missing anything so anyone who can contribute/correct my mistakes please feel free!)

Private Sub Insert__Click()

Dim TempText1, TempText2, TempText3
Dim db As Database
'This will copy the text values from your form
TempText1 = Me!YourFirstTextField
TempText2 = Me!YourSecondTextField
TempText3 = Me!YourThirdTextField

Set db = CurrentDb
db.execute "INSERT INTO YourTableName (Field1, Field2, Field3) SELECT TempText1, TempText2, tempText3;"

End Sub

 

[Shannon88]

Thanks for the great start! I am still running into some errors. This is what I got:

Private Sub roll_history_Click()
On Error GoTo Err_roll_history_Click

Dim TempTex1, TempText2, TempText3 As String
Dim db As AcDataObjectType
'This will copy the text values from form'
TempText1 = Me!VenNumber
TempText2 = Me!IncentiveYear
TempText3 = Me!IncentiveTotal

Set db = CurrentDb

db.Execute "INSERT INTO TbleIncentive History(Ven#,IncentiveYear,IncentiveTotal)SELECT TempText1, TempText2, TempText3;"

Exit_roll_history_Click:
Exit Sub

Err_roll_history_Click:
MsgBox Err.Description
Resume Exit_roll_history_Click

End Sub

First it flagged me for Dim db As Database, then I changed it to AcDataObjectType and it accepted it. I still don't know if that will work yet. Then it flagged me on Ven#, so I renamed it to VenNumber. Now I am to this next error that I can't get passed. Set db = CurrentDb. I tried different things but no go. I really am a beginner in the writing code portion of Access. Any help would be GREAT. Thanks!

 

[jbehrne]

Hi Shannon88,

You can also try the DoCmd.RunSQL (see the Access VB Help for more info). I don't think that you will need to set a connection to your db. So your code will look like this:

Dim TempTex1, TempText2, TempText3 As String
Dim db As AcDataObjectType

'This will copy the text values from form'
TempText1 = Me!VenNumber
TempText2 = Me!IncentiveYear
TempText3 = Me!IncentiveTotal

DoCmd.SQL "INSERT INTO TbleIncentive History(Ven#,IncentiveYear,IncentiveTotal)SELECT TempText1, TempText2, TempText3;"

Also, I don't know if the ";" (the one after TempText3)should be inside or outside of the sql statement so the end of that statement may look like this:

TempText3";

- aanyone who can contribute/correct my mistakes please feel free to post!

jbehrne

 

[Shannon88]

OK, I am so close I can taste it! When I run the code, it asks me for the values of temptext 1, 2 and 3. When I type in Test Data, IT WORKS. So something is wrong with the first part of the code. I have been searching for a long time and only came up with is acCopy, but I can't get it to work. Here is what I have NOW.

Dim TempText1, TempText2, TempText3 As String
Dim db As AcDataObjectType
'This will copy the text values from form'
TempText1 = Me!VenNumber
TempText2 = Me!IncentiveYear
TempText3 = Me!IncentiveTotal

DoCmd.RunSQL "INSERT INTO TbleIncentiveHistory (VenNumber,IncentiveYear,IncentiveTotal) VALUES (TempText1, TempText2, TempText3);"

Thanks so much! I would have never gotten this far without you.

Shannon

 

[jbehrne]

Hi,

A quick question:

- Are your temp values (TempText1, TempText2, TempText3) grabbing any values from the form?

If they are not, try this instead (you may have to make some changes in the format of the string after VALUES - I.E. [Me!VenNumber]...):

DoCmd.RunSQL "INSERT INTO TbleIncentiveHistory (VenNumber,IncentiveYear,IncentiveTotal) VALUES (Me!VenNumber, Me!IncentiveYear, Me!IncentiveTotal);"

Let me know if it works,

jbehrne

 

[Shannon88]

WE DID IT!!! Here is the code I needed! I didn't even need the previous code.

DoCmd.RunSQL "INSERT INTO TbleIncentiveHistory (VenNumber,IncentiveYear,IncentiveTotal) VALUES (VenNumber,IncentiveYear,IncentiveTotal);"

Thank you so much jbehrne. I really appreciate it! You just made my night!

Shannon88

 

[jbehrne]

Glad I could be of help!

jbehrne

 

[Shannon88]

Sorry to bother you again but I have one more question! I have been trying to write code to delete specific fields that we just copied into a table. I have been searching EVERYWHERE for just an example of this code. Do you have an example of the DoCmd.RunSQL DELETE code.

Thanks Again,

Shannon88

 

[jbehrne]

Hi Shannon88,

Hmmm... I don't know if therer is a SQL command that lets you delete the specific fields. Are you trying to delete them right away or later on? Also, have you decided on how you will provide a way for the user to search for these records? If you are going to delete them right away (I.E. just after the recordset was created) then you can try using the Delete Method (DAO) - see Access VB Help for more info. Alternatively you can use the code from this page:

http://www.mvps.org/access/forms/frm0021.htm

HTH,

jbehrne

 

[Shannon88]

I would like to add it to the same code. So, after the user clicks on the button that specific information is copied to a table. Which we have done. I would like the FIELDS to become empty so the user knows that they have saved the specific information. I have been looking for a code to basically empty out 7 fields in the current form they are in. Maybe I am searching for the wrong thing. Should I be trying to make the field NULL, instead of DELETE? Everything that I search on the internet refers to records. Again, I am a novice at this, so thanks for all the great advice.

Shannon88

 

[jbehrne]

Hi,

Just to make sure - You want the users to see the fields that we copied from to go blank? Okay, here goes:

After the SQL command runs just enter this bit of code:
(change Text1, Text2, etc. to the names of the text boxes that you want to display the null value)

Me!Text1 = ""
Me!Text2 = ""
etc...

However, the record that you are pulling from will reflect these changes (I.E. - the null values will over write your records). Are you sure this is what you want to do? You can also change the .backcolor of the fields to show the user that the record was copied to TbleIncentiveHistory. If you are interested in that code let me know,

jbehrne

 

[Shannon88]

I got everything working perfectly. I just listed the field name = null. Thanks for all your help!

Shannon88

 

[puzzledinsc]

This is not an answer to Shannon88, but rather a similar problem. I have an employee database and the user wants to be able to archive a record from time to time after a promotion, etc. So I'm trying to take the record in the form and write it to another table (archive_emp) at the push of a button. The tables are on a SQL server connected by an ODBC driver in Access. I'm using the following code and what happens is that it prompts me for the local variables (insted of lifting them off the Me! form), then it gives me an error that it can't open the output table - archive_emp. I seem to be missing a fundamental grasp on writing from one table to another in Access.
Code:

Private Sub Archive_Record_Click()
On Error GoTo Err_Archive_Record_Click

Dim l_emp_id, l_last_name, l_first_name As String
Dim db As Database
l_emp_id = Me.EMP_ID
l_last_name = Me.LAST_NAME
l_first_name = Me.FIRST_NAME

Set db = CurrentDb
DoCmd.RunSQL "insert into archive_emp (emp_id, last_name, first_name) values (l_emp_id, l_last_name, l_first_name)"


Exit_Archive_Record_Click:
Exit Sub

Err_Archive_Record_Click:
MsgBox Err.Description
Resume Exit_Archive_Record_Click

End Sub

PS: I tried to change Dim db as DataBase to AcDataObjectType but it failed at Set db = Current and said "Object required"

Thanks

 

[jbehrne]

Hi,

I see 2 quick problems:
1. When you tell vb where to get the value from you put:
Me.Me.EMP_ID, it should be Me!EMP_ID (since you are
refering to a text box on a form)
2. You forgot to close your sql statement with a ';' -
instead of ending like this: l_first_name)" it should
end like this: l_first_name);"

Paste this code in and give it a try:

Dim lempid, llastname, lfirstname As String
lempid = Me!EMP_ID
llastname = Me!LAST_NAME
lfirstname = Me!FIRST_NAME

DoCmd.RunSQL "insert into archive_emp (emp_id, last_name, first_name) values (lempid, llastname, lfirstname);"

- One quick note: You should never leave spaces in anything that you name (it confuses VBA and you have to use the underscore '_'). Trust me, in the long run it is easier to just run the names togther and capitolize the letters of the new names (Ex: FirstName)

HTH,

jbehrne

 

[puzzledinsc]

I corrected my code, didn't work. Then I pasted your code in it's place. Same error. When I push the 'Archive' button the program asked for me to input the 3 parameter values (lempid, llastname, lfirstname) and then says 'could not file output table 'Archive_emp'.

 

[jbehrne]

Hi,

A few quick questions:

1. Are the values EMP_ID, LAST_NAME, FIRST_NAME on your
form?
2. Do you have a table created called 'Archive_emp'?
3. Are you passing values of the same data type (I.E. is
the field accepting EMP_ID, etc. in your 'Archive_emp'
the same data type as EMP_ID, etc. ?)

I would be happy to take a look at your db. Just send a zipped copy (without any sensitive info) to: jbehrne@hotmail.com

jbehrne