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

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]

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)

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]