Using a DoCmd.RunSQL to create a column in a table, now I need to set the default value and I'm unsure how to do that. Is it in the syntax? What I've found thus far hasn't worked for me.
The inventory reporting I do has about 16 columns in an Excel template. Of which I only populate about 6 fields from data I have others contain info such as date, filetype ("IN" for inventory), etc. So I'm importing the data in, running a make table query and adding columns with the default values set so I can export/email to the customer.
Seems to me that the concept is correct but the logic is wrong. If you import all the columns from spreadsheet, or even easier simply link the spreadsheet and have seperate queries that bring in the desired columns/fields depending on the criteria.
O.k. it did run, here's the problem. I need it to update for each row already in the table, not just new entries. Let me post the whole code and let you see what I'm doing.
I have a form that I want to use to import the data as well as write the reports.
INC code
Code:
Private Sub Import_Data_Click()
'Error Handling
On Error Resume Next
'Table removal and recreation with data field changes
DoCmd.Close acTable, "tblInventory"
DoCmd.DeleteObject acTable, "tblInventory"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblInventory", "C:\Myfile.xls", True
DoCmd.RunSQL "ALTER TABLE tblInventory ALTER COLUMN Size Double"
DoCmd.RunSQL "ALTER TABLE tblInventory ALTER COLUMN Available Integer"
'Toggles Query Warnings and changes negative to 0
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblInventory SET [Available]=0 WHERE [Available]<0"
DoCmd.SetWarnings True
End Sub
This is my import command button It locates the file, brings it in, changes a few column data types and updates all negative numbers to "0"
The next part is the output table, I have a query written to pull styles "XYZ" where size and width = "Whatever".
Report comand button is where I'm currently working.
FileType should be "IN" for all 314 records returned. InStock Yes if >0, No if =0 in "Available" for the UPC(linked via relationship). UnitCost is pulled from a seperate table for this customer's pricing. UoM is EA for each record returned.
Now this is the part where you drop your face into the palm of your hand and groan. >.<
I've probably taken off way more than I can chew but it's fun trying to figure it out.
Also, I am aware of the fact I'm removing and replacing my data daily. We run a db here but I'm not allowed access to it. I just get a spreadsheet emailed to me daily so I figured I'd start fresh each day.
Lol, again I'm very new at this and my biggest resource is the web. Most of this is snippets of code I've found that I've managed to get to work for me.
Automation. Or maybe I'm not following what you mean. I can do all of this in Excel manually which takes several hours or I can experiment with some code and have a one-click solution.