Help on duplicating a record in table

[jen1701]

Hi Friends,

I have a entry screen which has MainForm and a Subform. If the user select the status of "Repeat" in the subform, it should create a record in 2 tables with all the information in the Main Form and sub form. I guess I should use DAO.recordset to achieve this, but I am not sure where to start. Can someone give me some advice? Thanks a lot in advance.

Jen

 

[Aerin]

You could create the append query, using query designer and run it when the user will click on the button Repeat.

To turn off MS Access warnings use the command:

docmd.SetWarning False
'run the query
docmd.OpenQuery "Query Name"
'don't forget to turn warnings back
docmd.SetWarnings True

 

[willir]

Hi Jenn

create a record in 2 tables

To follow up on Aerin's post, you need to populate your query. Since your info is a little sparse, specifics can not be provided.

After you have created the APPEND query with Query Builder - it is okay to fudge some numbers so you can simulate an insert record event. Switch the view for Query Builder to SQL. (from menu, "View" -> "SQL View") Cut and paste the SQL statement into the code responsible for "Repeat".

The SQL statement will be displayed in red because you have broken a rule or two for VBA. Don't panic.

Add the lines...

Dim strSQL as String
'this line should appear near the top of the sub routine

strSQL = "      "  'add your SQL statment between the double quotes

'from Aerin post
docmd.SetWarning False
'run the query
docmd.OpenQuery strSQL
'don't forget to turn warnings back
docmd.SetWarnings True

BUT, there is one more step. You need to reference the text boxes on the form / subform1, and substitute this information in your query.

Here is an example based on the following assumptions...
Me.textboxString1 - text string in a textbox on main form
Me.textboxDate2 - date in a textbox on main form
Me.textboxNumber3 - number in a textbox on main form
Me.YourSubForm.Form.textboxNumber4 - number in text box on subform

Dim strSQL as String, strQ as String

strQ = Chr$(34)

strSQL = "INSERT Into YourTable (YourText1, YourDate2, YourNumber3, YourNumber4) " _
& "Values (" & strQ & Me.textboxString1 & strQ & ", #" & Me.textboxDate2 _
& "#, " & Me.textboxNumber3 & ", " & Me.YourSubForm.Form.textboxNumber4 & ")"

Note:
& - joins string and variables together.
_ - used to wrap text onto the subsequent line
" - string variables must be encapsulated with quotes. My approach is to use strQ (assigned the character code 34 = "). Other approaches is to use """ (double quote within double quotes), and "'" (single quote within double quote)

Using a DAO approach, same assumptions...

Dim dbs as DAO.Database, rst as DAO.Recordset

Set dbs = CurrentDB()
Set rst = dbs.OpenRecorset("YourTable")

With rst
   .AddNew
   !YourText1 = Me.textboxString1
   !YourDate2 = Me.textboxDate2
   !YourNumber3 = Me.textboxNumber3 
   !YourNumber4 = Me.YourSubForm.Form.textboxNumber4 
   .Update
End With

rst.Close
dbs.Close

Depending on your data, you would use a similar approach to update the second table per your objectives.

Hope this gives you enough ideas on how you can handle your problem.

Richard

 

[jen1701]

Thank Willir and Aerin so much for the replies. I will follow your advice and try it. I am working on another project right now. But I will go back to work on this problem and try your suggestions. If I have any problem i will ask you again. Thank you so much for your time and help again.

Jen