Copy value to 2nd table (1 Viewer)

[DMerchen]

I have a database with two tables, and a form that is bound to the 1st table. I have a duplicate record button on the form. One of the functions I want to happen is when the duplicate button is pressed, one value is copied from the form and added to the 2nd table. The tables are basically set up the same with an ID column which is the key and an autonumber, and the second value is a number. So I want to copy the number to the other table and add a record. What is the best method? Can I use an insert into and just copy from a variable, or is the better method with a recordset. Either way, I have not been able to figure this silly thing out and it is preventing me from moving forward with this database. Any help is greatly appreciated!!

Dave

 

[BlueIshDan]

Might as well just use your currentdb.execute "Insert into

...." method.

I cannot tell, because of the way you describe your knowledge in your post, if you know how to do this or not lol.

Let me know.

 

[DMerchen]

I have managed to do many things with this database, but this has definitely kicked my butt! I found this option you are talking about, but I could not get it to work, so I am not sure which piece of the puzzle I was missing. If you know how to do this I am all ears!

 

[BlueIshDan]

Is your form bound to a table and traversing through its records by use of the navigation buttons?

 

[DMerchen]

Yes, it is bound to one table.

 

[BlueIshDan]

Ok so when you do the insert command you can reference the form's current record simply my just stating !field_name

For example:

    CurrentDb.Execute "INSERT INTO [table] (first_name, last_name, birth_date) " & _
                      "VALUES(""" & !first_name & """, """ & !last_name & """, CDate(""" & !birth_date & """));"

 

[ButtonMoon]

The tables are basically set up the same with an ID column which is the key and an autonumber, and the second value is a number. So I want to copy the number to the other table and add a record.

Whenever you encounter two tables with the same structure and meaning that is usually considered a reg flag - not necessarily completely unjustified in every case but at least worth a careful second look to see if your design really makes sense. The reason it's considered a questionable design to have multiple identical tables is that it implies you are repeating business logic and potentially creating ambiguity in your data and code. The design principle here is called D.R.Y. - "Don't Repeat Yourself".

 

[jdraw]

Dave, I agree with Buttonmoon -- 2 tables with same structure is a red flag generally.

Could you please describe to readers the business issue that you are resolving by means of 2 tables with similar structures?

Good luck.

 

[DMerchen]

Sorry, I don't mean to be confusing. I have two tables with that one column in common along with the ID column which is the key for the separate tables. There is more data in these two tables, but I want to copy the number to the other table so that when the user clicks a button on the form, then another form pops up linking the two forms. So I have two tables and two forms that are bound to each of the tables, and the thing linking these two is this one number I simply want to copy to the second table, otherwise when I try to open the second form, it is blank because there is no link between the two tables. Again, sorry to be confusing.

 

[DMerchen]

So, possibly to clear things up better. I have one table and form that tracks our Customer Returned item. This table is all of the details concerning the returned item (customer, date shipped, problem, etc.) The second table and form is the incoming information. This includes everything we check when we receive a returned item (packaging, serial numbers, etc.) These two tables are separate, but they have the RMA number in common. I tried to separate these two tables so that one table didn't become so large. We have a few individuals that like to look at the table and they don't like scrolling forever for some reason. I hope this makes it a bit clearer what I am trying to accomplish.

 

[DMerchen]

This is what I placed into my code, but I can not seem to get it to work. Any ideas? What am I missing?

CurrentDb.Execute "Insert Into [tblIncoming](RMA_Number) Values ('" & Me.RMA_Number & "')", dbFailOnError

 

[DMerchen]

Ha!! Got it! It does work, another portion of my code was not working. but once I figured that one out then it inserted fine. Thank you to everyone who prodded me in the right direction!