If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Append a specific record from excel to my table
On Aug 30, 6:43*pm, Raymond wrote:
I would like to import a single unique record from excel based on a 6 digit number search and append it to my table. Anyone know the best way to do this? Thanks -- Ray J Brooksville, Florida Hi, Although there are good suggestions here, you can use query and filter the record you want in Access: SELECT * FROM [Excel 8.0;DATABASE=X:\PathToFile\FileName.XLS;HDR=No;IME X=1]. [WorksheetName$]; You can change the query type to Append query (don't forget to filter data in query) and append Excel row you want. If your Excel file has headers, then change HDR=No to HDR=Yes. Regards, Branislav Mihaljev Microsoft Access MVP |
#12
|
|||
|
|||
Append a specific record from excel to my table
At one point I had these tables related but I took that out, the fix I found
was marking a property unique. Going back to my earlier problem with field display and will post what those sources are... INSERT INTO vrc ( UBL, NAME, PHONE, SNAME, OTHERUBL ) SELECT DISTINCTROW residents.num, residents.name, residents.phone, residents.sname, residents.special FROM residents, vrc WHERE ((([Forms]![APPEND]![Combo8])=[residents]![num])); -- Ray J "John W. Vinson" wrote: On Sun, 30 Aug 2009 15:53:01 -0700, Raymond wrote: The code in my append comm button... I'll check back monday, thanks again, Ray Private Sub Command11_Click() On Error GoTo Err_Command11_Click Dim stDocName As String stDocName = "Query3" DoCmd.OpenQuery stDocName, acNormal, acEdit Exit_Command11_Click: Exit Sub Err_Command11_Click: MsgBox Err.Description Resume Exit_Command11_Click End Sub Please post the SQL view of query3. My GUESS is that you're joining the target table to the source table (hence the multiple records); the append query should be based on the linked Excel spreadsheet (only), not on it joined to the table. -- John W. Vinson [MVP] |
#13
|
|||
|
|||
Append a specific record from excel to my table
form name is append
combo8 row source = residents.num & residents.name fields control sources are Residets.name ; addr101; addr102 The fields are showing the first record in the residents table no matter which record I search, but it is appending 1 record and the right record, so not all dome and glome...lol -- Ray J "Dirk Goldgar" wrote: "Raymond" wrote in message ... Thanks, I have a combo box linked to the excel so my user can type and select the record to append. I also added 3 fields to the form to show the user the address but when i select the record and tab the fields change to another record. Why is that? It's not clear to me what you've done to make the fields show the information from the selected record. What is the RecordSource property of the form? what is the Row Source property of the combo box? What are the Control Source properties of the fields on the form, that you want to show data from the selected record? -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
#14
|
|||
|
|||
Append a specific record from excel to my table
On Mon, 31 Aug 2009 14:21:01 -0700, Raymond wrote:
At one point I had these tables related but I took that out, the fix I found was marking a property unique. Going back to my earlier problem with field display and will post what those sources are... INSERT INTO vrc ( UBL, NAME, PHONE, SNAME, OTHERUBL ) SELECT DISTINCTROW residents.num, residents.name, residents.phone, residents.sname, residents.special FROM residents, vrc *********************** WHERE ((([Forms]![APPEND]![Combo8])=[residents]![num])); That's your problem. You're combining *EVERY SINGLE RECORD* in residnets with *EVERY SINGLE RECORD* in vrc, and appending the resulting records into vrc. Just remove the ", vrc" from the FROM line. You want to append INTO vrc, but FROM residents. You con't need to append from vrc into vrc, and your attempt to do so is causing the errors and the duplicates. -- John W. Vinson [MVP] |
|
Thread Tools | |
Display Modes | |
|
|