VBA Code to copy partial data to another form/table (1 Viewer)

Rgrea · Jul 16, 2014

Hello i have prepared a table in a database which includes a yes no box to one of the fields.

The idea of the yes/no field is that this is recording if the record is awarded. What i would like to achieve is that if the yes no box is checked "True" i would like to be able to copy some of the data from this table into a new table and that the form associated with this table opens up to enable the user to populate the remaining fields.

I believe i need to run this as an on click event procedure but cannot identify the code to enable the data to be copied accross to the other table. Any help would be much appreciated.

Uncle Gizmo · Jul 16, 2014

Will you be checking off only one record or multiple records?

Rgrea · Jul 16, 2014

Hi Uncle Gizmo

The intention is that only one record would be copied if the check box is ticked for that record.

Uncle Gizmo · Jul 16, 2014

Using the form wizard create a form based on the table that contains the check box. Set this forms display a property to datasheet.

Close this form and create another form. Drag the data sheet form on to this new form.

Add a command button to this form. You don't need to put any code behind this button yet we do that later.

Closed and open the form again and now you should see a list of the records from your table in it. Check just one of the check boxes and close the form.

Now write a query that returns this solitary checked record.

Rgrea · Jul 16, 2014

Many Thanks Uncle Gizmo

i have a managed to complete the forms and generate the query which is returning the single transaction i was expecting.

Uncle Gizmo · Jul 16, 2014

Post the SQL of your new query here.

Also the destination table, the table where you want the data from the check box selected data copied to, open the query designer window, select "that" table, drag all of it fields into the designer grid, select view this query as sql and post this sql statement here as well.

Uncle Gizmo · Jul 16, 2014

>>> i would like to be able to copy some of the data from this table into a new table<<<

Will this be a new record?

Uncle Gizmo · Jul 16, 2014

Does the new table have an autonumber field? If it does are the auto numbers generated in ascending order?

When you open the form based on this new table, does it automatically go to the last record entered. If not, can you arrange the sort order so that it does, and let me know if it is or isn't.

Rgrea · Jul 17, 2014

Thank you for helping me with this.

Code:

When i run the SQL on the query i get the following data 
SELECT TblApplications.ID, TblApplications.ShortTitle, TblApplications.ProjectTitle, TblApplications.Funder, TblApplications.Scheme, TblApplications.InternalApplication, TblApplications.DPAGLeadDept, TblApplications.DPAGInvestigator, TblApplications.IsthisaStudentship, TblApplications.ProposedStartDate, TblApplications.Months, TblApplications.ProposedEndDate, TblApplications.FunderDeadline, TblApplications.RSODeadline, TblApplications.ArethereExternalCollaborators, TblApplications.X5reference, TblApplications.Workshop, TblApplications.EM, TblApplications.Histology, TblApplications.Confocal, TblApplications.[Microscopy Required], TblApplications.ResourceRequirements, TblApplications.ApplicationReceived, TblApplications.[X5&CoversheetSenttoPI], TblApplications.ApplicationSubmittedtoRSO, TblApplications.[PIapprovedX5 & Coversheet], TblApplications.ApplicationSubmittedtoFunder, TblApplications.Withdrawn, TblApplications.IsthereanyconflictofInterest, TblApplications.[Isthereanyclinicalwork?], TblApplications.[Isethicsapprovalrequired?], TblApplications.SpaceRequired, TblApplications.RefurbishmentRequired, TblApplications.X5SentforHoDapproval, TblApplications.[HODApproval received?], TblApplications.FunderType, TblApplications.[ExpectedOutcome Due], TblApplications.PriceBand, TblApplications.MatchingFundsRequired, TblApplications.MatchingFundsNeeded, TblApplications.Awarded
FROM TblApplications
WHERE (((TblApplications.Awarded)=True));

When i open the Table i want the Data to be entered into the SQL for this data is

Code:

SELECT TblAward.ID, TblAward.ShortTitle, TblAward.ProjectTitle, TblAward.Funder, TblAward.Scheme, TblAward.DPAGPI, TblAward.StartDate, TblAward.Months, TblAward.EndDate, TblAward.ExternalColaborators, TblAward.X5reference, TblAward.Workshop, TblAward.EM, TblAward.Histology, TblAward.Confocal, TblAward.Microscopy, TblAward.ConflictofInterest, TblAward.ClinicalWork, TblAward.EthicsApproval, TblAward.SpaceNeeded, TblAward.RefurbishmentNeeded, TblAward.FunderType, TblAward.MatchingRequired, TblAward.MatchingValue, TblAward.ProjectRef, TblAward.AwardLetterDate, TblAward.AwardCostingSenttoRSO, TblAward.AwardLetterSenttoRSO, TblAward.BudgetCut, TblAward.StaffBudget, TblAward.NonStafBudget, TblAward.TotalDIC, TblAward.Overheads, TblAward.TotalAward, TblAward.NoOfPosts, TblAward.AdminOutputHR, TblAward.AwardLetterHR, TblAward.RPF1Received, TblAward.SetupValidated, TblAward.ProjectDetailsSenttoPI, TblAward.StartDocumentsent, TblAward.[Acceptance Document Sent]
FROM TblAward;

I am only hoping to copy some of the data into the table and not all of the data as some of this needs to be re-verified by the user to confirm if items were agreed.

The ID field is an autonumbering field. When i open the form it does appear to go to the last record entered.

vbaInet · Jul 17, 2014

Which fields are you copying?

Rgrea · Jul 17, 2014

Hi vbaInet,

The Fields which i intend to copy will be

TblApplications.X5reference
TblApplications.DPAGInvestigator
TblApplications.Funder
TblApplications.FunderType
TblApplications.Scheme
TblApplications.ShortTitle
TblApplications.ProjectTitle

The intended locations for this information would be

TblAward.X5reference
TblAward.DPAGPI
TblAward.Funder
TblAward.FunderType
TblAward.Scheme
TblAward.ShortTitle
TblAward.ProjectTitle

Uncle Gizmo · Jul 17, 2014

>>>I am only hoping to copy some of the data into the table and not all of the data <<<

i wasn't expecting such a big query!

as anticipated by vbalnet, I don't need the whole query.

If you could shorten them by removing the fields you don't need that would speed things along.

I am out for the rest of the afternoon, so vbalnet or others might want to step in if they can see how to progress it. otherwise I will pick it up this evening or tomorrow.

vbaInet · Jul 17, 2014

You can do it two simple ways:

* Save each one into public variables and when the form loads (using the Load event) get the values from the variables into the controls/fields. An example of saving to a variable:

Code:

Public strPorjectTitle As String

strProjectTitle = Me!ProjectTitle

The LHS is being assigned the value of the RHS. You can imagine what the opposite does.

* Create a Type with all the relevant public variables in it. It only helps in the sense that you can get intellisense to show you which fields to use. Think of it as a record in a variable. So as an example:

1. you declare the Type in the top part of a Module (not a Class Module, just a Module) and also declare another public variable as the Type itself:

Code:

Type MyTypeName
    SgortTitle As String
    ProjectTitle As String
End Type

Public AppCopy As MyTypeName

2. To use the AppCopy you call it like so:

Code:

AppCopy.ProjectTitle = Me!ProjectTitle

Or

Code:

With AppCopy
    .ProjectTitle = Me!ProjectTitle
End With

The latter is just a shortened version of the former.

Rgrea · Jul 17, 2014

many Thanks Uncle Gizmo

Sorry i should have thought of that.

I have updated the query as below

Code:

[COLOR=black][FONT=Courier New]SELECTTblApplications.ShortTitle, TblApplications.ProjectTitle, TblApplications.Funder, TblApplications.Scheme, TblApplications.DPAGInvestigator, TblApplications.X5reference, TblApplications.FunderType, [/FONT][/COLOR]
[COLOR=black][FONT=Courier New]FROM TblApplications[/FONT][/COLOR]

[COLOR=black][FONT=Times New Roman]WHERE (((TblApplications.Awarded)=True));[/FONT][/COLOR]

and

Code:

[COLOR=black][FONT=Courier New]SELECT TblAward.ShortTitle, TblAward.ProjectTitle, TblAward.Funder, TblAward.Scheme, TblAward.DPAGPI, TblAward.X5reference, TblAward.FunderType, [/FONT][/COLOR]

[COLOR=black][FONT=Times New Roman]FROM TblAward;[/FONT][/COLOR]

Rgrea · Jul 18, 2014

vbaInet

Many thanks for this information. When i go to set this up and run the process i end up with Run-Time error'94': Invalid use of Null.

The code which i have entered in form Load is

Code:

Private Sub Form_Load()
 
AppCopy.X5reference = Me!X5reference
 
End Sub

My AppCopy Data code is

Code:

Option Compare Database 
 
Type MyTypeName
    X5reference As String
    
End Type
 
Public AppCopy As MyTypeName

Any help with where i have gone wrong would be much appreciated. Sorry i am new to this VBA

vbaInet · Jul 18, 2014

By the way are you sure you want to copy it when the form loads or is this just for testing? I would have thought you were looking to copy it at the click of a button.

So the problem here is that X5reference's datatype is String (or Text) so it cannot accept Null because Null isn't a value or a reference.

You have two options:
1. Change the String datatype to Variant
2. Leave it as Text but before you assign it a value, check that the value being assigned Is Not Null.

There's something else you have to handle with option 2 and I don't want to bug you with the details so go with option 1.

Uncle Gizmo · Jul 18, 2014

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.

VBA Code to copy partial data to another form/table (1 Viewer)

Rgrea

New member

Uncle Gizmo

Nifty Access Guy

Rgrea

New member

Uncle Gizmo

Nifty Access Guy

Rgrea

New member

Uncle Gizmo

Nifty Access Guy

Uncle Gizmo

Nifty Access Guy

Uncle Gizmo

Nifty Access Guy

Rgrea

New member

vbaInet

AWF VIP

Rgrea

New member

Uncle Gizmo

Nifty Access Guy

vbaInet

AWF VIP

Rgrea

New member

Rgrea

New member

vbaInet

AWF VIP

Uncle Gizmo

Nifty Access Guy

Move Info from One Table to Another - Nifty Access

Similar threads

Users who are viewing this thread