Append a specific record from excel to my table

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

"Raymond" wrote 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.


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

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
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"Raymond" wrote 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?
Thanks
--
Ray J
Brooksville, Florida

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
--
Ray J
Brooksville, Florida


"Dirk Goldgar" wrote:

"Raymond" wrote 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.


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

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.
--
Ray J


"Dirk Goldgar" wrote:

"Raymond" wrote 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.


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

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?

--
Ray J


"Dirk Goldgar" wrote:

"Raymond" wrote 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.


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

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

--
Ray J


"Dirk Goldgar" wrote:

"Raymond" wrote 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.


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

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...
--
Ray J


"Dirk Goldgar" wrote:

"Raymond" wrote 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.


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

"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 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]