Send records from a form to anther form with subform

[tanolalano]

Hi all,
I have a search form(#1) to retrive some records. As well, when I filter records with combos I get a list of records. I create a button to send a single record to another form(#2) and It works well.
But I need, sometimes, to send all filtered records to a subform in onother form(#3).
How do I send records to the subform of the form(#3)?
Thanks in advance

 

[TheAceMan1]

How are ya tanolalano . . .

You can use an insert query/SQL to append the records to the underlying table of form3 . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[tanolalano]

Thanks TheAceMan1...can you be more explicit
I'm newbie of VBA.
Hope this help you...
I need on click event (btn#x) to open a form(#3) and send all view records.
Then adding new record in the form#3(on open) I relate information to all records present in the subform (sended from form#1).


Thanks

 

[TheAceMan1]

tanolalano . . .

In microsoft help (Contents Tab), navigate to:

[blue]Microsoft Jet SQL Reference
Microsoft Jet SQL Reference
Data Manipulation Language
Insert Into Statement[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[tanolalano]

Private Sub Comando45_Click()
I try to figure out the problem with this code:

Private Sub btn1_Click()
Dim strSql As String
Set RS = Me.RecordsetClone
Do While Not RS.EOF
strSql = "INSERT INTO riepilogo_mov_dare (id_acq)" _
& " SELECT Acquisti.id_acq FROM Acquisti " _
& " WHERE id_acq = RS.id_acq;"
CurrentDb.Execute strSql, dbFailOnError
Loop
End Sub

But I Get an error: Runtime error '3061'...
I'm going to be crazy!!

I need to insert the found records in the tbl "riepilogo_mov_dare" (many-to-many Table) and relate to one id of the 2nd Key.
Any help will be appreciated

 

[PHV]

Replace this:
& " WHERE id_acq = RS.id_acq;"
with this:
& " WHERE id_acq = " & RS.id_acq

Note: the above code assumes that id_acq is defined as numeric in Acquisti.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tanolalano]

You're right PHV. The id_acq is numeric.
I try your change but Access crashes.

 

[PHV]

Perhaps RS.MoveNext before the Loop instruction ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tanolalano]

Now on click the code create over 300.000 records in the table whit the same id_acq (the first in the continuos form#1)

 

[PHV]

What is YOUR actual code ?

 

[tanolalano]

Here the actual code

Private Sub btn1_Click()
Dim strSql As String
Set RS = Me.RecordsetClone
Do While Not RS.EOF
strSql = "INSERT INTO riepilogo_mov_dare (id_acq)" _
& " SELECT Acquisti.id_acq FROM Acquisti " _
& " WHERE id_acq = " & RS.id_acq
CurrentDb.Execute strSql, dbFailOnError
RS.MoveNext
Loop
End Sub

 

[tanolalano]

Thanks a lot for helping me, PHV and TheAceman.

I add the 2 red lines and now code works well. It do the right thing

[COLOR=#ff0000]If Not RS.EOF Then [/color]
Dim strSql
    Do While Not RS.EOF
        strSql = "INSERT INTO riepilogo_mov_dare (id_acq)" _
        & " SELECT Acquisti.id_acq FROM Acquisti " _
        & " WHERE id_acq = " & RS.id_acq
        CurrentDb.Execute strSql, dbFailOnError
        RS.MoveNext
    Loop
[COLOR=#ff0000]End If[/color]

 

[TheAceMan1]

Good for you tanolalano . . .

Bravo! . . . Bravo! . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[tanolalano]

AceMan, do you speak italian?
So when i'm in difficoul with english i use italian whit you

 

[TheAceMan1]

tanolalano . . .

Unfortunately no. However I had no problem with the english you presented here!

See Ya! . . . . . .

Be sure to see FAQ219-2884: