Expand|Select|Wrap|Line Numbers
- Dim strSQL As String
- Dim CSVTable As String
- Dim FilePath As String
- Dim Result As String
- strSQL = "DELETE * from csvordlin"
- CSVTable = "csvordlin"
- FilePath = InputBox("Please Enter a Path File for the CSV Location!", "Criteria Required")
- If FilePath = "" Then
- Else
- DoCmd.SetWarnings False
- DoCmd.RunSQL strSQL
- DoCmd.SetWarnings True
- DoCmd.TransferText acImportDelim, , CSVTable, FilePath, False
- Result = MsgBox("You have Successfully Imported your chosen CSV file!", vbInformation, "Import Status")
- End If
I have created a Customer Order Form where there is an OrderHeader and OrderLine contained within. I have OrderNo set as the Primary Key for the OrderHeader and OrderNo and StkID for the OrderLine so i can have many lines for one OrderHead.
So as a Step-by-Step approach, i am wanting the following to happen:
1. Create the OrderHead
2. Click on a Button to Import the CSV file as shown above.
3. Check the Imported Data exists within existing Stk file.
4. If not have the option to add to the existing Stk file.
5. Import the data into the Form OrderLine where it will have to assign an OrderNo with each row of data.
6. Assign correct Stk ID's or (New ones) with the Stk Descriptions imported. (Will explain below **).
7. Assign any Price that belongs to the existing "stkmas" table. (D-LOOKUP most likely)
This is my crappy attempt of transferring the data from the csvordlin table to the ordlin table:
Expand|Select|Wrap|Line Numbers
- Dim strSQL As String
- strSQL = "INSERT INTO ordlin(StkID,Qty,Width,Depth,Height)SELECT F1,F4,F5,F6,F7 FROM csvordlin"
- DoCmd.RunSQL strSQL
** When sorting this database out i assigned StkID's to Stock Items, where as the Imported data uses the StkDesc as there IDs, so i am gonna have to match my StkShortDesc with there StkID as they dont use numbers. For example;
Expand|Select|Wrap|Line Numbers
- StkID = 133, StkShortDesc = Base, Qty = 1 - Mine
- StkID = Base, StkDesc = Base Board, Qty = 1 - CSV File (Theirs)
Thanks in advance!