473,320 Members | 1,991 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Event Procedures

I am working with a database that isn't automated ie no switchboard or
similar device was made. I've pretty much gotten everything added to
the
switchboard except for two related key parts.

Right now, what I do is open a table called tbl-Temp, then use the
import
feature to point to an Excel spreadsheet. I select the area to be
imported
and let it rip. The tbl-Temp was set up to match the Excel spreadsheet
so I
don't have to worry about names/headers etc. Once I have the data
imported I
run various queries and reports and then delete the data from the table
and
repeat as necessary.

So what I did was create a form that can be accessed from the
switchboard I
am making and added two command buttons. I would like one to be an On
Click
[event procedure] (import data from Excel sheet to tbl-Temp), and the
other
to be an On Click [event procedure] (delete data from tbl-Temp).

I import from the same spreadsheet everytime, but if it would allow me
to
select one after clicking the command button that would be a better
option in
case things change in the future.

So I have said all of that to say I'm having trouble writing the
procedure.
So if anyone could tell me what to write for each of them or point me
in the
right direction for online resoures/explainations etc I would
appreciate it.

Thank you.

Jan 25 '07 #1
8 1651
Sub Command0_Click() ' change this to the name of your command button
Dim strFilter As String
Dim strInputFileName As String
Dim YourPath As String
YourPath = strInputFileName

'open a dialog box to select the excel file
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)

'import the data from the selected spreadsheet to your table name
DoCmd.TransferSpreadsheet acImport, , "tbl-temp", YourPath, True

DoCmd.Close

End Sub

This code should open a dialog box to select an excel spreadsheet then
import it into your table name. Check in your help file but I think
that the line that starts DoCmd.TransferSpreadsheet the "true" part
relates to whether line one on the imported spreadsheet contains line
headers. You will need to change this to suit you.
This should help you with the import part.

Jan 25 '07 #2
Ok, so to be sure I'm doing this correctly, if I right click on my
command button that I placed on the form and select the Event tab,
where it says On Click I write [Event Procedure] then select the three
dot box on the right and where my cursor is blinking inbetween the
lines of code is where I'm going to type this code in at?

On Jan 25, 12:12 pm, "keri" <keri.dow...@diageo.comwrote:
Sub Command0_Click() ' change this to the name of your command button
Dim strFilter As String
Dim strInputFileName As String
Dim YourPath As String
YourPath = strInputFileName

'open a dialog box to select the excel file
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)

'import the data from the selected spreadsheet to your table name
DoCmd.TransferSpreadsheet acImport, , "tbl-temp", YourPath, True

DoCmd.Close

End Sub

This code should open a dialog box to select an excel spreadsheet then
import it into your table name. Check in your help file but I think
that the line that starts DoCmd.TransferSpreadsheet the "true" part
relates to whether line one on the imported spreadsheet contains line
headers. You will need to change this to suit you.
This should help you with the import part.
Jan 25 '07 #3
The way I do this (produces the same results) is right click on the
button and select properties. Then on the event tab click the little
button with 3 dots next to the on click line. (This may or may not
bring up a box saying choose builder - if it does select code
builder.)Your vba window will then open up with two lines of code

Sub Command0_click ()

End Sub

With your cursor flashing in between. The command0 part will be the
name of your button so may be something different. You can paste the
code I gave you in between these lines - you will need to get rid of
the Sub Command0_click() line in my code and the End Sub line in my
code otherwise you will have duplicates.

Good luck - and it may not work first time so post back if you're
stuck.

Jan 25 '07 #4
I get a compile error (sub or function not defined)?

On Jan 25, 12:50 pm, "keri" <keri.dow...@diageo.comwrote:
The way I do this (produces the same results) is right click on the
button and select properties. Then on the event tab click the little
button with 3 dots next to the on click line. (This may or may not
bring up a box saying choose builder - if it does select code
builder.)Your vba window will then open up with two lines of code

Sub Command0_click ()

End Sub

With your cursor flashing in between. The command0 part will be the
name of your button so may be something different. You can paste the
code I gave you in between these lines - you will need to get rid of
the Sub Command0_click() line in my code and the End Sub line in my
code otherwise you will have duplicates.

Good luck - and it may not work first time so post back if you're
stuck.
Jan 25 '07 #5
keri wrote:
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
This code should open a dialog box to select an excel spreadsheet then
import it into your table name.
No it won't, not by itself - the OP will need to use the above lines in
conjunction with a Ken Getz API module found at
http://www.mvps.org/access/api/api0001.htm

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me
Jan 25 '07 #6
Sorry - I thought I had posted a similar link & explantions.

In your VBA window go to new and module. A new VBA page will open with
Option Explicit at the top. Then you need to go to the link Tim has
posted above and copy all of the green text into this new VBA page
(delete the line that starts ********** at the top as this will cause
errors). Save and close your vba window and click your button again.

Jan 25 '07 #7
Ok, I got it worked out now whoohoo! Thanks everyone.

On Jan 25, 1:19 pm, "keri" <keri.dow...@diageo.comwrote:
Sorry - I thought I had posted a similar link & explantions.

In your VBA window go to new and module. A new VBA page will open with
Option Explicit at the top. Then you need to go to the link Tim has
posted above and copy all of the green text into this new VBA page
(delete the line that starts ********** at the top as this will cause
errors). Save and close your vba window and click your button again.
Jan 25 '07 #8
Well done

Jan 25 '07 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Perion | last post by:
I have a simple form with, among other things, an ADODC data control (called "Adodc1"). The ADO connection to the database tests fine but when I try to add code to any of the Adodc1 control's event...
4
by: Corepaul | last post by:
I am a newbie using Access 2000. I am using the following test in the txtAlbum OnExit procedure to prevent leaving an empty text box for a required field If (IsNull(txtAlbum.Value)) Or...
5
by: cwm137 | last post by:
I am trying to call the click event of a button located in a subform called "Order Subform" from a button in the parent. However, since the subform was made with the wizard, it contains a space in...
3
by: R Millman | last post by:
under ASP.NET, single stepping in debug mode appears not to stop within event procedures. i.e. 1) Create web page with submit button and event procedure for the click event in the code behind...
10
by: Siv | last post by:
Hi, I am struggling with VB.NET's lack of indexing on controls. I have a form with 23 text boxes and I want to be able to drag some items in a list to any one of those text boxes. In VB6 I...
41
by: JohnR | last post by:
In it's simplest form, assume that I have created a usercontrol, WSToolBarButton that contains a button. I would like to eventually create copies of WSToolBarButton dynamically at run time based...
4
by: glen | last post by:
I have a multiproject solution; one of the projects is a group of classes doing data massage and insertions. One of the other projects is a UI that kicks off the process in certain situations. I'd...
4
by: Denis | last post by:
Hi, Looking at the template "Order Entry" in Ms Access, how do you get the following to work? In the customer form there is a sub-form giving a summary of orders by customers. After entering...
16
by: Neil | last post by:
I am using Access 2003, and have an ongoing problem of every once in a while losing an event procedure for a form or control. The procedure's still in the code module, but the form or control's...
4
by: =?Utf-8?B?bXIgcGVhbnV0?= | last post by:
Usually when a method is executing and an event occurs the event handler executes then the thread is returned to the original method. I have a case where I want my event handler to cancel...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.