Append Query Button (posssibly to do with VBA as well?)

[justinwright]

I've searched through a lot of places, but I can't seem to find the correct solution for my problem (probably because I'm almost a complete VBA newbie, and even then not so well-versed in Access).

Anyways, I'm trying to make a button on a form in Access 2007 (which serves at the input form - called Input_Screen - for a table called List) to append the data from List to a larger database called CHARGES.

The fields in List are:
SITE
PDATE
PAMOUNT
DESC
DESC1
ACHGROUP
Deduct/Add
Initial

The fields in CHARGES are:
SITE
PDATE
PAMOUNT
DESC
DESC1
ACHGROUP

I only want to append the data for the fields that are currently in CHARGES from List (disregarding the Deduct/Add and Initial fields). I'm also trying to figure out a second button to clear the List table completely, so I can start over with a clean database (I'm generating reports and such off of it that will change, but I want to keep the DB CHARGES for sort of an archive, as well as a few more uses I have in mind.

Any help is much appreciated... again, sorry that I'm so entirely helpless in VBA .

 

[G81]

Hi and Welcome Justin,

basically all you need to do is execute a couple of sql queries within vba. One being an "Insert into" query and the other being a "delete" query.

You can use DoCmd.RunSQL to run your sql queries in vba (there are plenty of other methods too)

 

[justinwright]

Ah that's what I've been hearing... do you know of anywhere that has some decent information that someone newer to coding VBA and SQL might be able to follow?

 

[G81]

sure. I usually use http://www.w3schools.com/sql/default.asp

and http://www.blueclaw-db.com/docmd_runsql_example.htm

and I search this forum. Nearly always has my questions already solved.

 

[justinwright]

Thanks! I'll be messing with this today, so I'll try to get somewhere with it and let everyone know how it goes. Thanks!

 

[G81]

No probs. Any questions about it, ask away.

 

[justinwright]

The following code worked perfectly for me:

Private Sub Command0_Click()
Dim myAppendQuery As String
Dim myDeleteQuery As String
myAppendQuery = "INSERT INTO CHARGES ( SITE, PDATE, PAMOUNT, [DESC], DESC1, ACHGROUP) "
myAppendQuery = myAppendQuery & " SELECT List.SITE, List.PDATE, List.PAMOUNT, List.DESC, List.DESC1, List.ACHGROUP FROM List"
myDeleteQuery = "DELETE *FROM List"
 
CurrentDb.Execute myAppendQuery, dbFailOnError
CurrentDb.Execute myDeleteQuery, dbFailOnError
 
End Sub

I rethought it a bit, though, and I think instead of making an Import button, an Export button, and going through all the trouble of overwriting and moving the files, I'll just link to the dBase III (at least I think it's dBase III, it's a FoxPro 2.6 database) and use the code above to append to the main FoxPro database.