field assignments in vba

[alexlds]

Would anyone be kind enough to help with what I presume is some extremely basic VBA?

I have an archive table, which is normally closed. I also have a data entry table which only ever contains one record at any time.

What I want to do in VBA is this. Use a button to add the contents of selected fields in the data entry table to a new record in the archive table

I had assumed that the procedure would be quite straightforward and would be something like this

Open the archive table in data entry mode
Then make various assignments such as
[archivetable].[surname]=[dataentrytable].[surname]
[archivetable].[address1]=[dataentrytable].[address1]
[archivetable].[tel]=[dataentrytable].[tel]
Etc etc
Save the new record in the archive table and close it
Delete the existing record in the dataentry table

Im sure that it must be very simple - but Im getting all sorts of grief trying to write VBA for a button to do this.

My problem is that I don’t use VBA often enough to be very confident about the syntax and commands - eg even simple stuff such as when and when not to use . or ! separators. And do I need to use the (full?) syntax as in [tables].[archivetable].[surname] etc

Any help will be much appreciated - thanks

 

[MikeLeBen]

Normally you wouldn't use a table for data entry. Tables are only for data storage, and should never be used for anything else.

You want all the operations on records and tables to be controlled by forms and therefore reducing the risk of data loss or corruption.

I might be wrong, but I think you cannot write vba code to capture events (i.e.: clicks, mouseovers etc.) on tables directly, but need a form to do so.

Hence my conclusion: design a (even basic) form and use that instead.

 

[Chintsapete]

Why don't you make a form from your data entry table and run a append and delete query after update? Or make a save button to run the queries?
Pete

 

[plog]

I agree with Mike.

I also have a data entry table which only ever contains one record at any time

What do you or hope to accomplish with this method?

 

[alexlds]

I never said that I was using a table for data entry.

I just need some vba to take data from selected fields in one table (which at any time only has one record) and copy it to fields in the new record of another table.

 

[MikeLeBen]

I must've misread your post. Should I assume the "selected fields in one table" you mention are in fact the rows of a datasheet form?

If this is the case, a routine would go along the lines of:

Private Sub UpdateTable_Click()
dim strSQL         as String

strSQL = "INSERT INTO yourtable... VALUES(" 'your insert statement

strSQL = strSQL & Me.your(sub)form.form.yourcontrol & ");"

DoCmd.RunSQL strSQL
End Sub

 

[alexlds]

Ok I was wrong to call it a data entry table. Even I know that you dont enter data directly into tables. My so-called data entry table is probably better described as a table where a current record is temporarily stored.

All I need is some vba to copy data from selected fields in that one record table into various fields of the new record of the archive table and then save it.

 

[alexlds]

sorry -a previous post (now deleted) was sent before I had properly composed it

 

[MikeLeBen]

Please note that I don't care and don't want to argue about who's right or wrong. I'm just trying to help, and in programming precision is usually crucial to success.

Please refer to my last post, and elaborate further if I haven't answered your question.

 

[alexlds]

Sorry Mike that certainly wasnt my intention at all to give that impression - as I said a post was accidenly sent sent before I had properly composed it.

Ive pretty well completed what for me is quite a large project with dozens of queries forms and reports and everything seems to be working ok. The final thing I need to do is to take records or parts of records from a query where some of the fields are calculated and copy them in a passive or snapshot (???) type format into an archive table where the results are not subject to being changed by future changes to the parameters of the calculated fields. - I dont know if I am explaining myself very well or using the correct terminology here. . . .

 

[MikeLeBen]

I think I get it, althought I'm not too sure what you mean by passive or snapshot format.

You would need to go into further detail and try to explain what is going wrong and where you're not succeeding, if you want me (or anyone else) to help.

 

[alexlds]

ok thanks I think I may have cracked it - ill see how i get on

 

[MikeLeBen]

Best of luck to you!