Not having much luck with the coding so have added the info added with added information HELP!!!!
The situation is:
1 tble called Assembly from this is a main form called Assembly
1 tble called Assembly details from this a sub form called Assembly Details on the main form called Assembly.
The main for just has a few cells for entry but one of them is an autonumber that I have created plus one type thing.
The designer wants to enter some brief information into the main form in order to get an autonumber as the next number for their drawings. They will then go and do as they want with it and later an excel sheet is created from 2 other cad programs. I upload the excel to Access and have coded this and it works.
So now I have it sitting in Access as a 'temp table' but what I need to do is now give it a code to upload it to the assembly details form against the correct auto number.
What I have managed to code so far is to upload excel to Access 2010 and to be able to upload it to the Assembly Details tbl however, it adds it underneath the last entry but not against the appropriate autonumber i.e sub form and not showing on the sub form. What I want it to do is for the code to recognise the autonumber in the main form find it on the subform and load all the rows not just the row with the matching number the matching number will be on the first row under the headings of the excel sheet that has been uploaded as a table.
This is what I have so far:
Option Compare Database
Option Explicit
Private Sub cmdOK_Click()
'-- Declare some useful variables
Dim StrFileName As String
Dim StrTableName As String
Dim StrSQL_rptImportAssembly As String
Dim StrSQL_rptBillofMaterials As String
Dim StrSQL_Append1 As String
'-- Read the file and user names entered by the user into the text boxes
StrFileName = Me.txtFileName
StrTableName = Me.txtUserName
'-- Link the spreadsheet to Access
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12Xml, Me.txtUserName, "C:\LinkExcel\" & Me.txtFileName, True
'-- Append some data
DoCmd.SetWarnings False
StrSQL_Append1 = "INSERT INTO tblAssemblyDetails (ItemNumber,Quantity,PartNumberId,Drawing,PartNumb erDescription,Material,Supplier,Length,WidthOD,Thi ckID,Finish)SELECT Item,Qty,PartNumber,Drawing,Description,Material,S upplier,Length,Width_OD,Thickness_ID,Finish FROM " & StrTableName
DoCmd.RunSQL StrSQL_Append1
'-- Finally, remove the linked table (note that this just deletes the link, the spreadsheet file is not actually deleted)
DoCmd.DeleteObject acTable, StrTableName
'-- Give feedback to the user
MsgBox "Operation completed"
End Sub
In the temp table on the first row it tells me for example what order number is relevant. All the rows and columns are filled in, but on the first row in one of the columns the relevant number is there call it 12345 and I want a code to basically find that number 12345 either on the main form and fill in the sub form automatically with all the details in the temp table as if I was typing in the details they are all rows for that order or find it on the order details table and enter all the details from the spreadsheet/temp table to that subform or relevant sub table. I hope this makes some sort of sense.
Main Form AssemblyID 100001 / main table
Sub Form / sub table first few columns are this: this is how it would look if I typed it in myself the first 2 columns would be filled in automatically by computer
Record No Assembly ID Item Number Part Number Qty
1 100001 1 100001 5
2 100001 1a 464646 7
3 100001 2 100001a 2
4 100001 3 4545 1
Temp Table/Excel Sheet will show this: this is what I want to upload and get it to do the above as soon as it picks up 100001 find the correct record and upload all what is underneath to that record also
1 100001 5
1a 464646 7
2 100001a 2
3 4545 1
