Export form contents to a different table

[Geordie2008]

Hi All,

Im trying to export the contents of a filtered form into a table called:

Tbl_Temp_Historical_data **NOTE - This is not the table the form is bound too **

I want to export the data to this table everytime the form is opened and the values are displayed within the form's controls, hence I was going to use the "On Load" property of the form...

Does anyone have a link to some code that I could tweek to do this?!!? Ive done lots of esarches, but have found not-a-thing...

Thanks,
Mandy

 

[namliam]

You are using the "filter by" of the open form?? Or another way?

If you are using the filter by you can execute an Append query to send the results to the table desired

 

[Geordie2008]

I have a seperate form which filters the main form....

So it is possible that I will get many filtered results...

e.g. if I filter on smith, I may get 4 results..

smith, Mark
Smith, andrew
Smith, Colin
Smith, John....

If I exported this data to a table.... I wouldn't mind if all 4 records got sent (as 4 seperate rows)

How would I use an apend query? Do I need to write any code?

thanks,
Mandy

 

[namliam]

In the "On Open" event of the form in question:
Currentdb.execute " insert query "

The insert query you can copy paste from a query window

Why do you need these records?? I can understand maybe you want the filters that are executed on the form, so you can examen them.
But storing all the searched records?? Why?? Maybe there is a better way of doing what you want?

 

[Geordie2008]

The insert query you can copy paste from a query window

I dont understand what you mean by this? I expected to need code such as:

"me.selectall.copy"
and then
"open.Tbl_history.paste"

I just need to keep a copy of the historical data in a historied table before I make changes to the current record in the current table....(I think we discussed this solution last week sometime.... as a response to my ~~HELP PLEASE~~ post. I decided to go with 2x tables as it was easier to keep track of the "current" data....)

Why do you need these records?? I can understand maybe you want the filters that are executed on the form, so you can examen them.
But storing all the searched records?? Why?? Maybe there is a better way of doing what you want?

There probably is a better way... im hoping to filter on teh exact row I need pre this step... so Im ignoring this problem for now....

 

[namliam]

If you have a follow up on an excisting question, stick to the original question... Saves from having 10 mio threads out there with the same matter.

Currentdb.execute is code much like what you were expecting....

Do you know how to make an Append query in the query wizard?

 

[Geordie2008]

Kind of.... I used them last about 2 years ago... I get what they do, just cant remember how to set them up....

I'll do a search on this forum and see what I can dig up

How would I get it to append a value from my form however...?

 

[namliam]

Well you know what is the source of your form....
Select ...
From

now... Your filter is your " Where " clause and you need an insert query

Insert into
Select
From
where

Execute that and presto.... It is the easiest if you can copy paste the majority of the SQL.

 

[Geordie2008]

Do you mean something like this:


Private Sub Form_Open(Cancel As Integer)

Currentdb.execute

INSERT INTO tbl_History

SELECT (*) FROM frmStaffDetails_ComboBox [This is the form with the 50 fields I want to exposrt into a table]

WHERE

[I dont really have a where clause as the form is already filtered]

End Sub

 

[namliam]

Yes now there you go!

Now the proper way to execute it is...
currentdb.execute "query"

And your filter/where clause is in the "filter" property of the form... Me.Filter should retrieve it.

 

[Geordie2008]

I have tried teh following sql in my append query (as I cannot select the form as an I had to use the sql to try and build it)

It does not work and hence will not allow me to save the query...

How do I get the append query to pick up values from the filtered form?


INSERT INTO Tbl_MAIN_Staff_Details_HISTORY
SELECT *
FROM forms!frmStaffDetails_ComboBox
WHERE
me.filter;

The red bit currently falls over...

Thanks,
Mandy

 

[Geordie2008]

My append query "qApp_Staff_Details_HISTORY" sql now reads:

INSERT INTO Tbl_MAIN_Staff_Details_HISTORY
SELECT *
FROM frmStaffDetails_ComboBox
WHERE me.filter;

and on the form properties "on open"

reads:

Private Sub Form_Open(Cancel As Integer)
CurrentDb.Execute qApp_Staff_Details_HISTORY
End Sub

When I hover over "qApp_Staff_Details_HISTORY" in debug mode it says it is empty.. if this due to the timing of when the query is run...? I am getting to following error:

Run-time error '3078';

The microsoft jet database engine cannot find the input table or query ". Make sure tit exists and that its name is spelled correctly.

Thanks,
Mandy

 

[namliam]

Nope not there...

The FROM clause of any query ALLWAYS has to contain tables!
In this case the same table the form is based on, but still it needs a table!

The where clause you need to "substitute"
Dont use an external db query, this will only make things harder. You use the db query to find your basic sql, but augment the basic sql in code.... (copy paste)

Now...
Currentdb.Execute "insert into Tbl_MAIN_Staff_Details_HISTORY Select * from table"

Now you have to add the Where... because above query will send ALL records to the history table.

Currentdb.Execute "insert into Tbl_MAIN_Staff_Details_HISTORY Select * from table where "

But we dont know yet what to "where" ... We know it is in the form filter, which is (if you put it in the "On Open" Me.Filter.
Now use concatination (&) to concatinate (append if you will) the content of Me.Filteer to your query.

Currentdb.Execute "insert into Tbl_MAIN_Staff_Details_HISTORY Select * from table where " & Me.Filter

Now all you have to do is "find" your table and substitute that with "table" in above query... and you are done after learning a little bit I hope...

 

[Geordie2008]

Namliam,

Thanks so much for all of your help, but your post is WAY above my head, I have no idea how to do this.... I did another search on the forums in general and found the following post within the tables forum that may be able to help me out of this problemo:

http://www.access-programmers.co.uk/forums/showthread.php?t=33374

I think Im going to give this a try as I think I know how to adapt this for my table names etc...

Where would I need to store this code however?
Would it be in its own module? The form I am exporting or the table I am exporting to?

Im still rather confused...

Thanks,
Mandy

 

[namliam]

You could do something like the linked post, BUT it only stores the record that is currently visible on the form. NOT all records that are filtered.

It depends a little where you want to put this code... Probably the "On Dirty" event of the form... This event is triggered as soon as someone starts typing/altering the record. This doesnt guarantee the record is changed offcourse because the changes can be undone... but... it is a good start.
Also you can try and/or look into the following events
Before update
After update
On Delete
On Undo

 

[Geordie2008]

Mmm I put it on the Form_Open event....

Private Sub Form_Open(Cancel As Integer)

Dim DB As Database
Dim RST As Recordset
Set DB = CurrentDb
Set RST = DB.OpenRecordset("Tbl_MAIN_Staff_Details_HISTORY")

RST.AddNew

RST![Staff_Number] = Forms![frmStaffDetails_ComboBox]![Staff_Number]
RST![First_Name] = Forms![frmStaffDetails_ComboBox]![First_Name]

Set RST = Nothing
Set DB = Nothing

End Sub

It is bombing out on the red line... does the syntax look wrong? this is the name of the table that I wish to export to...

Regards
Mandy

 

[Geordie2008]

Woweeeee.......

Following code works and Im all happeee......


Private Sub Form_Open(Cancel As Integer)

Dim DB As DAO.Database
Dim RST As DAO.Recordset

Set DB = CurrentDb
Set RST = DB.OpenRecordset("Tbl_MAIN_Staff_Details_HISTORY")

RST.AddNew

RST![Staff_Number] = Forms![frmStaffDetails_ComboBox]![Staff_Number]
RST![First_Name] = Forms![frmStaffDetails_ComboBox]![First_Name]

RST.Update
Set RST = Nothing
Set DB = Nothing

End Sub

 

[namliam]

By using the form open event, you will only save the FIRST record shown... Not all the records ...

 

[Geordie2008]

yeah I know... Im going to have to work on ensuring that the user is FORCED (using whatever means possible!) to select the user they wish to update so that when they filter, they can only possibly select 1 person and update that persons record...

So thats my next challenge....! I think I got some good pointers on this yesterday... using an unbound list box I think!

Thanks again for your help, cant believe its almost working!

Im sure we'll chat again soon!
Mandy