Messing with tables from visual basic

[benu302000]

I've got a form that allows you to select a record out of a combo box. from there, I have a button to launch a new form that will pull data from a different table using the record from that combo box as the relation. What I want to do, is then, when the new form loads up, take the record from the old form and its table, and place it into the newer form, and its table.

The concept is, I have a database full of billets, and each billet also has a history, which is another table in the database. when the command button is pressed what, idealy, will happen is the record will be taken out of the billet table, and placed into the history table for that billet. I think I can do this with some VB either when the button is pressed, or the second form loads, but I don't know how to get in and dinker with tables and rows from VB. Does anyone know of a good FAQ or reference I could use for that? Or does anyone know perhaps just the basic objects I need to work with in order to get rows out of tables and compare values etc... thanks

 

[KenReay]

Hi

How about an INSERT query, eg

INSERT INTO HistoryTable SELECT * FROM BilletTAble WHERE Id = 1;

see help for INSERT

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[ptpaton]

Benu302000,

I would perform the kinds of actions your talking about by running append and delete queries.

You can use an append query to move data from the billet table to the history table, then delete the record from the billet table with a delete query.

Likewise you can transfer something out of the history table using the same method.

You can run them successively either in VBA code using the "OpenQuery" method, or you can run them using a macro. Either way they achieve the same effect.

When the "Are you sure you want to add these records to the table" messages get annoying, you can turn them off with the SetWarnings command.

YOu can also use Echo Off to make the screen transitions seamless. If you use VBA for this, remember to turn them back on!

HTH
P

-Patrick

Nine times out of ten, the simplest solution is the best one.

 

[benu302000]

Alright, definetly making some progress now, but I'm having troubel tweaking the append query to work the way I want it to. For instance, it won't copy from the source table to the destination table unless there is already a record with the same billet number (the two table's related field) in the destination table. And then, if I artificially place such a phoney record in the destination table to get it to copy, for some reason it not only copies the record from the source table, but re-copies the records in the destination table (this is if I run the query multiple times in a row). Maybe I have my relationships set up wrong or something, any ideas?

 

[KenReay]

cannot answer this without knowing the relationships you have set between the tables,

also

Post the SQL

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[benu302000]

already solved it, thanks anyway