Append a specific record from excel to my table

[Raymond]

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

 

[Dirk Goldgar]

in message

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?

I'm not sure I completely understand what you have in mind, but if I do,
this is how I would go about it:

1. Link to the Excel worksheet, so that it is available to your database as
a linked table.

2. Have a form with a combo box whose rowsource is a query that returns the
search field from the linked Excel table.

3. In the AfterUpdate event of that combo box, or else in a command button
on that form to be clicked after the user has made a selection in the combo
box, execute an append query that selects the record from the linked table
that matches the combo box value, and appends it (or the appropriate fields
from it) to the target table.

 

[Arvin Meyer [MVP]]

Rather that a myriad of VBA code for a simple task, I'd consider an lookup
in Excel, then a copy/paste. If this is something that must be done
frequently, especially by untrained staff, I'd look at the Excel automation
code at Ken Snell's Access/Excel pages:

http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

 

[Raymond]

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?

As far as appending the record to my table I'd like to use a command button
once they are sure they have the correct record, will the wizard help me
there or is there code I need to enter?
Thanks Again

 

[Raymond]

Well I made some headway with your tip but I need some follow up.
I made the append query and added it to a command button. When I enter the
numbers in my combo box and click the command button it will add 16 duplicate
records. The fields and the requested record all match, so thats good. Can't
figure why 16, its not amount of fields I appending. The source data records
are all unique, 1 record per number.

 

[Raymond]

its appears it appending as many records as there are in the source table.
I delete some records and the next query wants to add as many records are in
there. Not sure why?

 

[Raymond]

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

 

[Raymond]

I found the err sunday night so thanks. The unique records I changed to yes
in query properties and that seems to be the trick.

I still have not figured why my form fields do not show the record I choose
but thats a job for monday, thanks again...

 

[Dirk Goldgar]

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?

 

[John W. Vinson]

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.

 

[[MVP]]

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

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;IMEX=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

 

[Raymond]

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]));

 

[Raymond]

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

 

[John W. Vinson]

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.