I have an excel spreadsheet set up like a form. This form has 6 pieces of
data the user puts in. Once they are filled in the user needs to click on a
command button and have those 6 pieces of data dumped (appended) into an
Access database. After that is done I want to clear the 6 fields where the
data was in Excel.

Can someone help me?

Thanks,

Les

Les,

If you know VBA you can try writing a reinitialization statement in the
module and call it after the append to ACCESS, else, write a macro to clear
the fields and call the macro after the command button to append the data to
ACCESS. The best approach depends on your comfort with VBA and/or macros.

Rick



"Les" wrote in message
...
I have an excel spreadsheet set up like a form. This form has 6 pieces of
data the user puts in. Once they are filled in the user needs to click on
a
command button and have those 6 pieces of data dumped (appended) into an
Access database. After that is done I want to clear the 6 fields where
the
data was in Excel.

Can someone help me?

Thanks,

Les

Hi Rick,

Unfortunately I am not as familiar with VBA as I am in VB-6. Can you show
me how to set up the command button to send data from XL to Access (ie send
XLS.fileABC.column to ACCESS.fieldname etc....)? What command button
event should I use and how to null out those 6 fields. If it is involved
then I understand.

Lets say for example I have 3 fileds called field1, field2 and field3 that
has data in them and that upon clicking the button I want to send it to
(appended of course) to Access with fields access1, access2 and access3.
Does this example make life easier?

Thanks,

Les

"Rick S." wrote in message
...
Les,

If you know VBA you can try writing a reinitialization statement in the
module and call it after the append to ACCESS, else, write a macro to
clear
the fields and call the macro after the command button to append the data
to
ACCESS. The best approach depends on your comfort with VBA and/or macros.

Rick



"Les" wrote in message
...
I have an excel spreadsheet set up like a form. This form has 6 pieces
of
data the user puts in. Once they are filled in the user needs to click
on
a
command button and have those 6 pieces of data dumped (appended) into an
Access database. After that is done I want to clear the 6 fields where
the
data was in Excel.

Can someone help me?

Thanks,

Les

Les,

Sending the data to Access is a bit more complicated, more for the fact that
usually the data transfer is the other directions (at least in my
experience). Let me think about the easiest method to send you an example
that you can apply with little complication. I will try to get back to you
by Tuesday if possible....Monday's are horrible for me.

the issue of clearing the cells....thats very simple. Create your macro
( or hidden command that you reference through code or button (which we we
chat about next week) using the code below:

Private Sub CommandButton1_Click()
ActiveSheet.Unprotect
Range("B4").ClearContents
Range("B6").ClearContents
Range("C4").ClearContents
Range("C6").ClearContents
Range("D4").ClearContents
Range("D6").ClearContents
ActiveSheet.Protect
End Sub

I did this the easiest method possible for you to see how its done
basically.

Go to Tools, Macros, Visual Basic Editor and double-click on the sheet name
containing the cells. Paste the above code in the sheet. Change the cells
to reference your specifc cells, then save the sheet. Close it and go back
to your form(worksheet).

Now, create the command button and link the code you pasted to its name.

When you save and go back to your sheet it will clear the cells upon
clicking it.

Hope this helps and I will try to get back with you when possible. And, if
I can come up with an easy way to send an example.

Maybe someone else has a good clear example already?

Best of luck....

Later,

RS
"Les" wrote in message
...
Hi Rick,

Unfortunately I am not as familiar with VBA as I am in VB-6. Can you show
me how to set up the command button to send data from XL to Access (ie
send
XLS.fileABC.column to ACCESS.fieldname etc....)? What command button
event should I use and how to null out those 6 fields. If it is involved
then I understand.

Lets say for example I have 3 fileds called field1, field2 and field3 that
has data in them and that upon clicking the button I want to send it to
(appended of course) to Access with fields access1, access2 and access3.
Does this example make life easier?

Thanks,

Les

"Rick S." wrote in message
...
Les,

If you know VBA you can try writing a reinitialization statement in the
module and call it after the append to ACCESS, else, write a macro to
clear
the fields and call the macro after the command button to append the
data
to
ACCESS. The best approach depends on your comfort with VBA and/or
macros.

Rick



"Les" wrote in message
...
I have an excel spreadsheet set up like a form. This form has 6
pieces
of
data the user puts in. Once they are filled in the user needs to
click
on
a
command button and have those 6 pieces of data dumped (appended) into
an
Access database. After that is done I want to clear the 6 fields
where
the
data was in Excel.

Can someone help me?

Thanks,

Les

This might be a help for getting data to and from Excel and Access: It
includes examples of using variables in SQL queries.
http://www.bygsoftware.com/examples/sql.html

Or you can get there from the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

It demonstrates how to use SQL in Excel's VBA to:

* create a database,
* create a table and add data to it,
* select data from a table,
* delete a table,
* delete a database.

You can also download the demonstration file called "excelsql.zip".

The code is open and commented.


--

Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"


"Les" wrote in message
...
I have an excel spreadsheet set up like a form. This form has 6 pieces of
data the user puts in. Once they are filled in the user needs to click on
a
command button and have those 6 pieces of data dumped (appended) into an
Access database. After that is done I want to clear the 6 fields where
the
data was in Excel.

Can someone help me?

Thanks,

Les

Hi Rick,

First let me thank you for your help here!! I will try to play with some
ideas and hope to hear from you if you find the time.

Once again thanks my friend,

Les

"Rick S." wrote in message
...
Les,

Sending the data to Access is a bit more complicated, more for the fact
that
usually the data transfer is the other directions (at least in my
experience). Let me think about the easiest method to send you an example
that you can apply with little complication. I will try to get back to
you
by Tuesday if possible....Monday's are horrible for me.

the issue of clearing the cells....thats very simple. Create your
macro
( or hidden command that you reference through code or button (which we we
chat about next week) using the code below:

Private Sub CommandButton1_Click()
ActiveSheet.Unprotect
Range("B4").ClearContents
Range("B6").ClearContents
Range("C4").ClearContents
Range("C6").ClearContents
Range("D4").ClearContents
Range("D6").ClearContents
ActiveSheet.Protect
End Sub

I did this the easiest method possible for you to see how its done
basically.

Go to Tools, Macros, Visual Basic Editor and double-click on the sheet
name
containing the cells. Paste the above code in the sheet. Change the
cells
to reference your specifc cells, then save the sheet. Close it and go
back
to your form(worksheet).

Now, create the command button and link the code you pasted to its name.

When you save and go back to your sheet it will clear the cells upon
clicking it.

Hope this helps and I will try to get back with you when possible. And,
if
I can come up with an easy way to send an example.

Maybe someone else has a good clear example already?

Best of luck....

Later,

RS
"Les" wrote in message
...
Hi Rick,

Unfortunately I am not as familiar with VBA as I am in VB-6. Can you
show
me how to set up the command button to send data from XL to Access (ie
send
XLS.fileABC.column to ACCESS.fieldname etc....)? What command button
event should I use and how to null out those 6 fields. If it is
involved
then I understand.

Lets say for example I have 3 fileds called field1, field2 and field3
that
has data in them and that upon clicking the button I want to send it to
(appended of course) to Access with fields access1, access2 and access3.
Does this example make life easier?

Thanks,

Les

"Rick S." wrote in message
...
Les,

If you know VBA you can try writing a reinitialization statement in
the
module and call it after the append to ACCESS, else, write a macro to
clear
the fields and call the macro after the command button to append the
data
to
ACCESS. The best approach depends on your comfort with VBA and/or
macros.

Rick



"Les" wrote in message
...
I have an excel spreadsheet set up like a form. This form has 6
pieces
of
data the user puts in. Once they are filled in the user needs to
click
on
a
command button and have those 6 pieces of data dumped (appended)
into
an
Access database. After that is done I want to clear the 6 fields
where
the
data was in Excel.

Can someone help me?

Thanks,

Les

Les,

I did some looking over the weekend and did not have any examples handy to
send to you but, my suggestion that follows should work.

Try having your button call a VBA module that sets up an ADO connection to
your ACCESS dbase and runs an append qry. Then have it clear the fields as
indicated previously.

For example:

Step 1: Create your connection from Excel to Access using ADO
Dim oConn As New ADODB.Connection

Set oConn.ConnectionString = "DSN=AccessDB;...."

Step 2: Write an append query using your fields and the ADO connection
Step 3: Clear the cell contents as before.




I think that if you look on google or elsewhere you can locate the complete
syntax for the statement.

Despite not knowing VBA very well you should be able to use your VB skills
and the resources you locate to build the statement. I dont have an example
or I would send it to you.

Once again, perhaps someone else has one handy.

Hope this at least serves to guide you in a diretion that works until other
offer their opinions as well.

Best of luck!

RS




"Les" wrote in message
...
Hi Rick,

First let me thank you for your help here!! I will try to play with some
ideas and hope to hear from you if you find the time.

Once again thanks my friend,

Les

"Rick S." wrote in message
...
Les,

Sending the data to Access is a bit more complicated, more for the fact
that
usually the data transfer is the other directions (at least in my
experience). Let me think about the easiest method to send you an
example
that you can apply with little complication. I will try to get back to
you
by Tuesday if possible....Monday's are horrible for me.

the issue of clearing the cells....thats very simple. Create your
macro
( or hidden command that you reference through code or button (which we
we
chat about next week) using the code below:

Private Sub CommandButton1_Click()
ActiveSheet.Unprotect
Range("B4").ClearContents
Range("B6").ClearContents
Range("C4").ClearContents
Range("C6").ClearContents
Range("D4").ClearContents
Range("D6").ClearContents
ActiveSheet.Protect
End Sub

I did this the easiest method possible for you to see how its done
basically.

Go to Tools, Macros, Visual Basic Editor and double-click on the sheet
name
containing the cells. Paste the above code in the sheet. Change the
cells
to reference your specifc cells, then save the sheet. Close it and go
back
to your form(worksheet).

Now, create the command button and link the code you pasted to its name.

When you save and go back to your sheet it will clear the cells upon
clicking it.

Hope this helps and I will try to get back with you when possible.
And,
if
I can come up with an easy way to send an example.

Maybe someone else has a good clear example already?

Best of luck....

Later,

RS
"Les" wrote in message
...
Hi Rick,

Unfortunately I am not as familiar with VBA as I am in VB-6. Can you
show
me how to set up the command button to send data from XL to Access (ie
send
XLS.fileABC.column to ACCESS.fieldname etc....)? What command
button
event should I use and how to null out those 6 fields. If it is
involved
then I understand.

Lets say for example I have 3 fileds called field1, field2 and field3
that
has data in them and that upon clicking the button I want to send it
to
(appended of course) to Access with fields access1, access2 and
access3.
Does this example make life easier?

Thanks,

Les

"Rick S." wrote in message
...
Les,

If you know VBA you can try writing a reinitialization statement in
the
module and call it after the append to ACCESS, else, write a macro
to
clear
the fields and call the macro after the command button to append the
data
to
ACCESS. The best approach depends on your comfort with VBA and/or
macros.

Rick



"Les" wrote in message
...
I have an excel spreadsheet set up like a form. This form has 6
pieces
of
data the user puts in. Once they are filled in the user needs to
click
on
a
command button and have those 6 pieces of data dumped (appended)
into
an
Access database. After that is done I want to clear the 6 fields
where
the
data was in Excel.

Can someone help me?

Thanks,

Les