Solved Copy query records into form fields (datasheet view)

[Juett]

Hi guys,

I have a question about copying records from a simple query (qry1) into fields on a form (frm1) in datasheet view. I wish to copy all records from qry1 and paste them as individual rows/records/entries in frm1 in datasheet view.

The catch is this....while I want to copy all the rows/records from qry1, I do NOT want to copy every column/field in that row. I only want to copy column/fields 1, 2 and 4.

Example:

Qry1 has 4 fields - Title, Name, Surname and Town

e.g: Mr John Smith London

I wish to copy Title, Name and Town, and skip Surname, for every record in the query and paste them into Frm1 datasheet view as individual records/entries.

I would like to do this with the press of a command button on frm1.

Does anyone know how this can be achieved?

Thanks a lot.

 

[Gasman]

Use a query that has qry1 as it's source and select required fields in that query?

 

[Juett]

ApologiesI should have stated that the from is not always going to utilise this function, hence why it is needed as a button. There will be circumstances where the form will have data entered manually by a user, and other times when data could be conveniently pulled from qry1. So setting a new qry based on qry1 as the from's source will not work here I don't think.

 

[Juett]

SELECT tblUK.Name, tblUK.Surname, tblUK.Town, tblUK.Car, FROM tblUK

The user has currently been using this code at the moment to copy individual fields/columns from the first row of the query - which works - but it doesn't go on to copy the same fields from the next record:

Me.Form1.Form![Title] = fnGetTitleUK()

Public Function fnGetTitleUK()
With CurrentDb.QueryDefs("qry1")
    fnGetTitleUK = .OpenRecordset()(1)
End With
End Function

Me.Form1.Form![Name] = fnGetNameUK()

Public Function fnGetNameUK()
With CurrentDb.QueryDefs("qry1")
   fnGetNameUK = .OpenRecordset()(2)
End With
End Function

Me.Form1.Form![Town] = fnGetTownUK()

Public Function fnGetTownUK()
With CurrentDb.QueryDefs("qry1")
   fnGetTownUK = .OpenRecordset()(4)
End With
End Function

I'm at a loss as to what to do to achieve what they want. The issue is they have several queries based on different countries, and all could be potentially entered into the same form.

 

[Juett]

It is all records in the query that need copying, but only certain columns/fields from each record (as indicated above), then those records being pasted into the datasheet on form1 (as individual records)/entries).

 

[The_Doc_Man]

We get back to the obvious method - base a query off of your Query1 but omit the fields you don't want to include. Then the problem becomes knowing when to do this.

But here is the 2nd part of the issue. If you are talking about putting data into a form in Datasheet view, it can only hold so much data in the form itself. It would normally hold its data from an underlying recordset. You said people could also enter records into it manually, but that still means there has to be a place to hold the input data.

Therefore, the form is a distraction. The solution is to have a button that does an INSERT INTO for the selected fields of that datasheet's underlying table whenever you wanted to do that, and there is nothing wrong with have two INPUT sources for a table - i.e. manual via form entry or programmed via an INSERT INTO query. You don't care about the form. You care about the data. So focus on moving the data when and as you need.

 

[Juett]

Uncle Gizmo's suggestion solved the problem. Copying the records from qry1 and inserting them into the table that feeds form 1 has resolved the issue. I used his videos to guide me. Thanks everyone.