Forms with Options?

[NFL]

Hello:

I hope this is not an off-the-wall question. If it sound crazy, I
apologize. I have a simple database with one table, one query, one form, and
one report. When I collect new information, it is all put on the
spreadsheet, I would export that data to the database, delete the old table
and rename the new table. Is there a way I can create a button on the form
to ask which table to use? The structure of the table is consistent with the
forms, queries, and reports. It sounds weird, but when I gather the data on
the spreadsheet, I link all the information to one worksheet and then export
to the database which makes it easy to screen the high volume of records and
even add notes. Right now, I just import, compact/repair, exit the dbase,
rename it with a new name, and pass the dbase to the user to work on.

 

[PJFry]

Let me throw this out:

Would it also suit your purposes to simply delete everything in the table
(instead of deleting the table itself) and append the new records? If
nothing else, it makes for a cleaner database.

 

[NFL]

That would work. What I also noticed was every time I import an Excel
spreadsheet to a database, Access will create 2 other error tables. I always
delete them, because I don't need it. First I thought there was a problem
with the process and after checking the records, I didn't see any errors
other than changing the data type.

What are the steps required based on your suggestion? I'm willing to give
it a try..

Thank you,

 

[PJFry]

Give this a try:

On the OnClick event of a command button, put this code.

DoCmd.SetWarnings False 'Turn off warning messages

Dim sSQL As String 'For the delete statement
Dim tName As String 'Holds the name of the table to be modified

tName = InputBox("Please enter the target table", "Table Name") 'Input the
table name

sSQL = "DELETE * FROM " & tName 'Deletes everything from that table
DoCmd.RunSQL sSQL

'Import the new spreadsheet
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12, tName, "Your Excel
file path and name here", True

DoCmd.SetWarnings True 'Turn on warning messages

Couple of notes:
1. You may need to change the acSpreadsheetTypeExcel12 to an earlier
version of Excel, depending what you are running.
2. If you change either the structure of the Excel file or the table you are
importing into, this process will break. They have to match. From what you
said I don't think that will be a problem.
3. At the end of the TansferSpreadsheet is a True. This refers to the
column headers. If your excel file does not have then, change that to False.
4. If I remember correctly, this will import the first Worksheet in the
Workbook. Make sure your data is there.
5. If you need to reference different Excel files, post back and I can
change the code to ask for the location and name of the file to be used.

--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.

 

[NFL]

Thank you so much for your input. It works!

Give this a try:

On the OnClick event of a command button, put this code.

DoCmd.SetWarnings False 'Turn off warning messages

Dim sSQL As String 'For the delete statement
Dim tName As String 'Holds the name of the table to be modified

tName = InputBox("Please enter the target table", "Table Name") 'Input the
table name

sSQL = "DELETE * FROM " & tName 'Deletes everything from that table
DoCmd.RunSQL sSQL

'Import the new spreadsheet
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12, tName, "Your Excel
file path and name here", True

DoCmd.SetWarnings True 'Turn on warning messages

Couple of notes:
1. You may need to change the acSpreadsheetTypeExcel12 to an earlier
version of Excel, depending what you are running.
2. If you change either the structure of the Excel file or the table you are
importing into, this process will break. They have to match. From what you
said I don't think that will be a problem.
3. At the end of the TansferSpreadsheet is a True. This refers to the
column headers. If your excel file does not have then, change that to False.
4. If I remember correctly, this will import the first Worksheet in the
Workbook. Make sure your data is there.
5. If you need to reference different Excel files, post back and I can
change the code to ask for the location and name of the file to be used.

--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.

 

[NFL]

I have one more question about your code. If a user decides to click
'Cancel' a RuntimeError 3131 and Syntax error shows up. Is there a way to
modify the code to return to the form when you click 'Cancel'? Also, if a
user forgets to enter a proper table name and clicks 'OK' the same message
would appear. Is there a way to override the program to create your own
message to say something like 'please enter a tablename or cancel to return
to the main form'.

Thank you!