Pasting Data.....

[Eddyoftheyear]

I have a form and subform. The subform is linked to the form Via ActivityID and StaffID, I will get alot of data in excel format. I want to be able to copy the rows from excel and paste it in the subform. I tried it and it worked when there is no linked relationship. but for the data to be correct, I need the activityID and staffID to be included after pasting the data so it will link to the right record.

Form Name: Travel_frm
Subform: Activities_subform
StaffID
ActivityID

 

[randy700]

You should set up a method to import the data into a table. Try linking to the spreadsheet and using an append
query. The form/subform should be based on the table or (preferably) a query.


Randy

 

[Eddyoftheyear]

The subform is linked and based on the table. But the excel records I get weekly for different individuals. I have to paste each individuals records to the database to the right individual.

 

[SkipVought]

do you mean that you have individual TABLES for each individual?

Otherwise it's just a table join relationship on the individual's ID.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Eddyoftheyear]

No, one table for activities. but the records I am pasting will be for different individuals. What I was planning to do is that you would select the individual name from a combobox, then the form open with the subform linked to it using activityID and StaffID. Actually I can drop staffId and use activityID only.

The staff will paste the records pertining to the individual to the subform. I was hoping that there will be away when they paste the records, it will copy the activitiesID to all the pasted records.

Crazy Idea...Hu....

 

[randy700]

I don't know how you would go about inserting the data via copy and paste.
I still think you'd be much better off building an append query. You can include the ID numbers in your query.


Randy

 

[Eddyoftheyear]

How? If the data is in excel, how am I going to create a query that will insert the foriegn key to the table in the append query?

 

[MajP]

Create a lint to the Excel data from access.

http://office.microsoft.com/en-ca/a...ata-between-access-and-excel-HP001095095.aspx

 

[TheAceMan1]

How are ya Eddyoftheyear . . .

[blue]I need the activityID and staffID to be included after pasting . . .[/blue]

Since your pasting ... you need a method to include [blue]ActivityID[/blue] and [blue]StaffID[/blue] ... which you can get from the mainform! It seams your pasted data doesn't include these fields. Otherwise you woulen't have this problem.

So ... my question is ... does a paste for each record [blue]include the link fields you seek?[/blue] If they don't, you ned to pick them-up from the mainform and [blue]append[/blue] approriately ...


See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

Eddyoftheyear . . .

? ... bump!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Eddyoftheyear]

The Aceman, that is exactly what I need, I need to pick the staffID and activityId from the main form. The table where will will paste the data in has those IDs available, But I am looking for the method that can take the Ids from the mainform and insert them when I paste.

 

[Eddyoftheyear]

Weekly, the data I get from excel is for all the staff. I may get 50 to 100 records that are for maybe 10 to 15 staff member.
The fields that I am copying from excel as rows are
Activitystartdate activityenddate type starttime endtime location name (which is the staffed but most case they are spelled differently, sometime first name only)
And I have a table in access with the same fields but I added staffID and activityID

So when I paste the data, I want the staffID and activityID to be filed from the mainform to the subform.

 

[TheAceMan1]

Eddyoftheyear . . .

Your not going to accomplish this by just pasting. You need to link/ or import the data in an access table 1st ([blue]MajP's[/blue] suggestion). Then you'd use an append query or recordset (randy700's suggestion) to polish it off.

All the above depends on your table structure of which we have no idea. What are form & underlying table names? What are the table relationships? are you using autonumbers for your primarykeys or generating them yourself? [blue]We could also use some assemblence of what the data looks like.[/blue] But 1st ... you got to get the data into access!

You could have a seperate form for viewing & selecting imported/linked records, with two comboboxes for selecting staff & activity ... [green]hence the ID's![/green]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Eddyoftheyear]

Ace Man, thank you. that is a perfect.

"You could have a seperate form for viewing & selecting imported/linked records, with two comboboxes for selecting staff & activity ... hence the ID's!"


The question I have to get the data to access, is that in a temp table? Also, am I going to link the data manually everytime I want to bring the data in?

I set all the fields to be text fields but the the primary key is ActvityID which is autonumber. The staffID is a lookup number.

The maintable which is used for the main form is Project_tbl and the second table for the subform is Activity_tbl.

The relationship is one to many relationship ActivityID

Main form called Project_frm
The sumform is Activity_subform

Any help would be appriciated!

 

[TheAceMan1]

[blue] ... is that in a temp table?[/blue]

No ... its a permanent linked table.

[blue] ... Also, am I going to link the data manually everytime I want to bring the data in?[/blue]

No! ... you link once and that should be it (I've proven this to myself over the years). I've had db's that woundup needing relinking, but it was all a matter of finding out why the link was dropped in the first place. Typically it can occur when moving to an access newer version.

So in the database window from the menubar its:
[blue]File
Get External Data
Link Tables ...[/blue]

See if you can get the spreadsheet linked ... Let us know of any success of failure.



See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]