Creating a new record from form data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form (with subforms) which enters data into various tabes in my
database. I also want to enable users to enter part of the data on the form
to another table at their discretion (by clicking a button on the form). I've
scoured the help and these forums, but evry idea I have seems to quickly meet
a brick wall. Any pointers to the right direction would be helpful.

---More detail---
This problem arises from the solution to a bigger problem: My database has a
fixed number of records, with people updating informtion for 'their' subset
of records. At specific times, they may need to flag a particular record for
action by a third party, and this flag must be in a log in Excel. My idea is
to import the current log from Excel to a new table in the database, add the
record (with information from the current form) to this table, and export the
table back to Excel. I've got the importing and exporting working at the
click of a button, but I can't find the code to add the record/information to
the table in between the import and Export:

Private Sub Command0_Click()

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Sheet",
"Filename.xls", True

'add data to table "Sheet" ??

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "sheet",
"Filename.xls", True

DoCmd.DeleteObject acTable, "Sheet"

End Sub

This sound like a realy simple question, but either it isn't or I'm
searching so hard for the answer I'm looking right past it.

Any help appreciated.

Thanks

Ray
 
I also want to enable users to enter part of the data on the form
to another table at their discretion (by clicking a button on the form).
Click to expand...

you can write an Append query to append the data from the table (TableA)
underlying the form to the "other" table (TableB). make sure you set
criteria on TableA's primary key field, in the query, to pull the current
record on the form, as

WHERE TableA.PrimaryKeyFieldName = Forms!FormName!PKFieldName

the sequence of your code would be something along the lines of

DoCmd.TransferSpreadsheet acImport...
DoCmd.RunCommand acCmdSaveRecord
DoCmd.SetWarnings False
DoCmd.RunQuery "NameOfAppendQuery"
DoCmd.SetWarnings True
DoCmd.TransferSpreadsheet acExport...
DoCmd.DeleteObject...

also, you might consider importing the Excel data into an existing table
(TableB), appending the TableA data to TableB, exporting the data back to
Excel, then deleting all data from TableB - rather than creating a table
object and then deleting it, each time the code runs.

hth
 
Thanks,

I came up with a variation on your second idea earlier, whilst out shopping.
I'm not quite sure how margeriene and dairy produce helped me work it out,
but there you go! Your first suggestion seems more elegent and transparent
though, so I'll see if I can get that to work.

Cheers

Ray
 
well, there really was no first and second idea - just one suggested
solution, with a further suggestion dump data into and out of an existing
table rather than creating and deleting a table each time.
 
Back
Top