I am new to ACCESS, however I could do this in SQL, infact if someone could tell me how to use the docmd.RunSQL command I would be fine. this is what I have.

I have a database named Marlin in the database I have 2 tables one called alldata and the other called todaydata, in the todaydata, I have the following fields, custID, Exchangerate, todaysdate, contactname, openticketdate, closeticketdate, openclose,

the form is setup as a datasheet view, I want to create a button that takes the information enter in the todaysdata and copies it into the alldata table, all the fields are the same, after the copy I want it to clear the todaydata table.

Also I need a Button that looks at the table alldata and brings over all the data to the todaysdata that is from the prior day.

I am at a total loss, can someone help?

I would do this in SQL like this
insert dbo.alldata
(custid,
exchangerate,
[todaysdate],
contactname,
[openticketdate],
closeticketdate
openclose)

select
custid,
exchangerate,
[todaysdate],
contactname,
[openticketdate],
closeticketdate
openclose

from dbo.Todaysdata
delete from dbo.Todaysdata

Hi,

To run sql in vb is pretty simple

strsql = "your statement here"
docmd.runsql (strsql)


good luck.

Ali

Tried it, and it didnt work, this is what I got wrote

Private Sub Command14_Click()
strsql = "insert marlins.alldata (custid,exchangerate,[todays date],contactname,[openticketdate], closeticketdate,openclose) select custid,exchangerate,[todays date],contactname,[openticketdate], closeticketdate,openclose from marlins.todaydata"
DoCmd.RunSQL (strsql)

ANd I get an error that states Run-Time error '3134': Syntax error in insert into statement

So I must be doing something wrong

Oops, sorry I didn't look at the whole thing here.

For the first sql statement you should do it the way I said...

i.e.

strsql = "insert into..."
docmd.runsql (strsql)

At that point all you're doing is executing the statement. Remember for variables which you are porting into the system, you'll have to put together the string with something like this:

strsql = "insert into mytable (myfield1, myfield2) Values (" & myfield1 & ", " & myfield2 & ")"


For the second sql statement, you need to open a recordset. The recordset is what will store the results of your query.

For example:

dim rs as dao.recordset
dim strsql as string
strsql = "select * from [mytable]
set rs = currentdb.openrecordset(strsql)

Hope this helps.

Ali

Once your SQL string is built, you can also use currentdb.execute strSQL to execute it.