Solved Copy query records into form fields (datasheet view) (1 Viewer)

[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.

 

[Uncle Gizmo]

Please show the SQL Statement of Query 1....

 

[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.

 

[Uncle Gizmo]

What defines a record that needs copying in other words could you write a where clause to identify the records that you need?

If you can't identify the records you need then you might have a problem.

 

[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.

 

[Uncle Gizmo]

I answered a similar question a while back and I blogged about it on my website Here:-

Move Info from One Table to Another - Nifty Access

Move Info from One Table to Another A brief summary summary of the "OP's" (Original Posters) question HERE. I've done a set of YouTube videos to answer the question. The first answer demonstrates the INSERT INTO method of adding records to another table. This method is where you update the...

www.niftyaccess.com

There are 6 videos demonstrating the process. Video number four; below gives you an idea of how it works so you can decide if you want to attempt it:-

Move Info from One Table to Another 4 - Nifty Access​

The following video shows how the records are added, leaving some blanks for adding more information.

Move Info from One Table to Another 5 - Nifty Access​

A download file is available from the Nifty Access Website HERE:-

Move Info from One Table to Another - Nifty Access

Move Info from One Table to Another A brief summary summary of the "OP's" (Original Posters) question HERE. I've done a set of YouTube videos to answer the question. The first answer demonstrates the INSERT INTO method of adding records to another table. This method is where you update the...

www.niftyaccess.com

The download file contains examples of both methods, the method of "Insert Into" with an INSERT INTO SQL Statement.

The other method I demonstrate is where you you transfer the information into a textbox default value. This way you can view the information in the Form without saving the record. As soon as you make a change or addition, then the record is saved.

If you want a free copy of this sample file then contact me via "private message on this Forum" and I will explain how you can get a free copy.

 

[Pat Hartman]

Forms don't store data. Tables store data. So as Doc suggested, you would run an append query to copy rows from one table to another. Open the QBE. Choose the source table. Select the fields you want. Change the query type to Append. Then choose the target fields for the columns you selected. Column names don't have to match, although Access will automatically map matching names.

 

[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.

 

[Uncle Gizmo]

Excellent!

I would be really grateful if you could subscribe to my video channel, as if I get to 1000 subscribers, ... (and I've only got a few to get!) I get an upgrade in my permissions on YouTube.

I'm on my mobile at the moment, so I can't post a link, but if you click on any of my videos there should be an option to subscribe.

If you've already subscribed, please ignore this message!