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

Create New Record using VBA code from two tables

P: 61
I have a button on a form that creates a new record, based on the current record, when the user clicks the button. The original code from the MASTER table worked fine. Now I need to add additional fields to the new record from another table: Related. When I added the code for the Related table, the fields in the Related table did not populate on the new record. Can someone please help? The code is below. Thanks

Expand|Select|Wrap|Line Numbers
  1. Private Sub Add_Click()
  2.     Dim db As DAO.Database
  3.     Dim rs As DAO.Recordset
  4.     Dim rs2 As DAO.Recordset
  5.  
  6.     On Error Resume Next
  7.  
  8.     Set db = CurrentDb
  9.     Set rs = CurrentDb.OpenRecordset("SELECT * FROM [MASTER]")
  10.     rs.AddNew
  11.     rs![Number] = Me. Number.Value
  12.     rs![Title] = Me.Title.Value
  13.     rs![Year] = Me.Year.Value
  14.     rs![Schedule] = Me.Schedule.Value
  15.     rs![Comments] = Me.Comments.Value
  16.     rs![Statement] = Me.Statement.Value
  17.     rs![HistoryChanges] = Me.HistoryChanges.Value
  18.     rs![Orig_PubDate] = Me.Orig_PubDate.Value
  19.     rs![Last_PubDate] = Me.Last_PubDate.Value
  20.     rs![Keywords] = Me.Keywords.Value
  21.     rs.Update
  22.     rs.Close
  23.     db.Close
  24.     Set rs = Nothing
  25.  
  26.     Set db = CurrentDb
  27.     Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM [Related]")
  28.     rs2.AddNew
  29.     rs2![Related_1] = Me.Related_1.Value
  30.     rs2![Related_2] = Me.Related_2.Value
  31.     rs2![Related_ 3] = Me.Related_3.Value
  32.     rs2![Related_ 4] = Me.Related_4.Value
  33.     rs2![Related_ 5] = Me.Related_5.Value
  34.     rs2.Update
  35.     rs2.Close
  36.     Set rs2 = Nothing
  37.  
  38.     db.Close
  39.     DoCmd.Close
  40. End Sub
  41.  
Mar 25 '14 #1

✓ answered by zmbd

ah... ok,

Just noticed line23 and line 26 in your original post, delete these two lines.
Insert after line38 me.requery the form normally only looks at the table once unless something happens to force it to update.

you might want to take a look at what Allen Browne has done here:

Simpler method:
Assign default values from the last record

And then the more complex situation:
Duplicate the record in form and subform

This is post in a related thread Thread955552/Post#6

Share this Question
Share on Google+
7 Replies


zmbd
Expert Mod 5K+
P: 5,397
Expand|Select|Wrap|Line Numbers
  1. rs2![Related_1] = Me.Related_1.Value 
  2.     rs2![Related_2] = Me.Related_2.Value 
  3.     rs2![Related_ 3] = Me.Related_3.Value 
  4.     rs2![Related_ 4] = Me.Related_4.Value 
  5.     rs2![Related_ 5] = Me.Related_5.Value 
  6.  
Note the spaces between the underscore and the numerics 3 thru 5? Was that intentional as there is no such space for 1 and 2.

By, simply stating that your code "doesn't work," not posting the error along with code (and omitting on which line the error occured), that appears to have had very little if any troubleshooting performed, and expecting someone to help doesn't usually result in much of an answer and may result in your thread being deleted.
Mar 25 '14 #2

P: 61
@zmbd
Hi zmbd,

My mistake! The original code does not have the spaces. I don't get an error. I tried to create a record using this code, but only part of the new record was created. The fields in the Related table did not pull over to the new record and I don't know why. I am sorry if this is vague. Thanks
Mar 25 '14 #3

zmbd
Expert Mod 5K+
P: 5,397
You should double check that all of your Me.Related_1.Value type lines are actually returning what you think they are... debug.print them out and check the immedate window by pressing <ctrl><g>

Why are you duplicating data between tables, that isn't done in a > Normalized Table Structures.
Mar 25 '14 #4

P: 61
I guess what I am asking is the structure of my code right, assuming I have all of the table/field names correct? If it is, then I will have to find the problem somewhere else. I'm not sure why the new record does not include the fields Related_1, Related_2, etc.
Mar 25 '14 #5

P: 61
Hi zmdb,I am not duplicating data between tables. I am pulling data from both tables on a form using a query. I then need to create a new record with most of the fields on the form. The reason is so that the users don't have to re-enter the same data on the new record as only a few fields are being changed on the new record.
Mar 25 '14 #6

zmbd
Expert Mod 5K+
P: 5,397
ah... ok,

Just noticed line23 and line 26 in your original post, delete these two lines.
Insert after line38 me.requery the form normally only looks at the table once unless something happens to force it to update.

you might want to take a look at what Allen Browne has done here:

Simpler method:
Assign default values from the last record

And then the more complex situation:
Duplicate the record in form and subform

This is post in a related thread Thread955552/Post#6
Mar 25 '14 #7

P: 61
Thanks zmbd. Good information. I will read through this and try and fix my code. I appreciate your help
Mar 25 '14 #8

Post your reply

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