Solved Copy all query records into Form Datahseet

[Juett]

Hi guys,

I have a combo box that displays the results of a query. The results are always more than one record, which of course translates to multiple rows being displayed in the combo box.

I'd like to copy all records / rows from this combo box and insert them into a subform ( in datasheet view), with one record/row from the combo box corresponding to one record/row on the subform. I can copy the first and/or currently selected row of the combo box using simple VBA, such as:

 Me!Form1.Form![Qty] = Me.[Combo1793].Column(0)
 Me!Form1.Form![Type] = Me.[Combo1793].Column(1)
 Me!Form1.Form![Name] = Me.[Combo1793].Column(2)

However ...I'm struggling to find a way to copy and insert ALL the rows from the combo box into the form.

Any ideas would be very much appreciated.

 

[Gasman]

Use the same recordsource the combo uses.

 

[Juett]

I don't think I can in this case, as the user also wants to (and does use) the option to add manual entries to the form as well. It cannot be bound to the query. They wish to copy the combo box rows AND enter their own data as well.

 

[Gasman]

I am not aware of any limitation in adding new records to a select query?
Create a form with a Select query as it's source. Add some new records. Do they appear in the table?

 

[Juett]

So the subform is already bound to its own data source, and currently the user enters data into the form, and some of the fields then auto populate. For example, if they enter a code in the 'code' field, the corresponding description appears in the 'description' field. They want to be able to continue to do this AND copy data from a combo boxes into the form as well. To save them time typing out individual codes.

 

[bastanu]

You can use the row index to loop through the columns:

Dim i as Integer

For i = 0 To Combo1793.ListCount - 1
    CurrentDb.Execute "INSERT INTO tblYourTable (Qty,Type,Name) VALUES ("'" & Me.[Combo1793].Column(0,i) & _
                        "','" & Me.[Combo1793].Column(1,i) & "','" & Me.[Combo1793].Column(2) & ",",dbFailOnError
Exit For

'set recordsource of the form to show the newly added records or do a requery if the form\subform linking is in place

Me.sfrmYourDatasheet.Form.Requery

ComboBox.Column property (Access)

Office VBA reference topic

learn.microsoft.com

Cheers,

 

[Pat Hartman]

You can use ONE line of code to run an append query. Assuming the RowSource of the combo is a query or a table, use that as the select part of the append query.

I should remind you that keeping duplicate data in multiple places is wrong unless this is an audit trail. Duplicate active records are wrong. An audit trail is a different situation. The rows in the audit table are locked and can never be changed so there is no expectation that they would match an existing row in the active table.

Action queries are ALWAYS more efficient than VBA code loops. Also easier to "code"/test.

 

[Juett]

Update - I managed to achieve this by running a query from a combo box and storing the results in another combo box, then from this second combo box I copied and inserted the results into the form datasheet using a loop.

Dim i As Integer
For i = 0 To Me.Combobox2.ListCount - 1
Me!Datasheet1.Form![Qty] = Me.[Combobox2].Column(0)
Me!Datasheet1.Form![Product Code] = Me.[Combobox2].Column(1)
Me!Datasheet1.Form![Product Name] = Me.[Combobox2].Column(2)
Me!Datasheet1.Form![Price] = Me.[Combobox2].Column(3)
Me.Combobox2.SetFocus
Me!Combobox2.Value = Me.Combobox2.ListIndex + 1
Me.Combobox2.Requery
Datasheet1.SetFocus
DoCmd.GoToRecord , , acNewRec
Next i

Its worth nothing that I did not build this database and do not recommend using such a workaround as there are other, better methods to pull the data required, as detailed above. But - this code did do the job initially needed it to do.