Importing data regularly

Doug,

I've changed the structure of theDB tables a little and now wish to import
the same file (see attached) into a table which has more field than which
I'm importing.

I get an error now when I use the following code:

Private Sub Command34_Click()
Dim lngAfter As Long
Dim lngBefore As Long
Dim strFilter As String
Dim strInputFileName As String

strFilter = ahtAddFilterItem(strFilter, "Text Files (*.TXT)", "*.TXT")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
lngBefore = DCount("*", "tblSections")

DoCmd.TransferText acImportDelim, "import", "tblSections", strInputFileName,
False

lngAfter = DCount("*", "tblSections")

MsgBox "You imported " & (lngAfter - lngBefore) & " sections."
End Sub

Is this because the number of fields are different. The txtfile has the
first 5 fields which correspond to the first 5 in the table. However the
table has a further 7 fields which can't come from the import. What I want
to do is import the 5 fields into the table and then add the remaining
fields manually.

Is this possible.

(The error I get is Runtime Error 3001, Invalid Argument)

Regards Andy Andy Roberts BSc (Hons) Senior Consultant
www.blue-bean.co.uk M +44 (0)7970 255 156 T +44 (0)151 428 6838 240 Mackets
Lane Woolton Liverpool L25 9NH United Kingdom
"Andy Roberts" wrote in message
...
Thanks yet again Doug,

Thats sorted that problem.

However... then numbers are coming in as whole numbers (missing the 4
decimal places) for each value.

Each field is set as a Long Integer and the decimal places are set to 4,
but it doesn't seem to work, even if I change the type to decimal.

Andy


"Douglas J. Steele" wrote in message
...
"acImportFixed" says that the columns are a fixed width. You want
"acImportDelim"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug

That works a treat - well nearly!

Everything works, except the resulting data is different from that
cointained in the incoming file. The data coming in is as follows (they
are
co-ordinates):-

0001,652.8655,349.5133,689.3182,468.6107
0002,822.5105,468.6107,861.7672,336.9030
0003,1020.1960,336.9030,1051.0406,505.0405
0004,1154.7904,505.0405,1330.0435,356.5190
0005,1531.9351,440.5878,1426.7832,573.6967
0006,1505.2966,743.2354,1799.7218,419.5706
0007,1913.2858,619.9345,1530.5330,928.1867
0008,1913.2858,619.9345,1530.5330,928.1867

It is REF, StartX, StartY, End X, EndY and saved in a txt file

On import an autonumber is assigned as a unique reference. No problem,
however when I import the file the message I receive is correct with the
number of entries, but the table looks like this:-

http://www.blue-bean.co.uk/images/screen.jpg

The numbers aren't formatted correctly - I thinks it must have something
to
do with number of decimal places. Any clues? It also imports the first
comma (but the field type is text in this case)



"Douglas J. Steele" wrote in message
...
Copy all of the shaded code (what's between Code Start and Code End),
and
paste it into a new module. When you save the module, name it something
like "mdlFileDialog" (the name of the module isn't really critical,
other
than the fact that you cannot name the module the same as any of the
functions or subs within it)

Rather than simply having:

DoCmd.TransferText acImportFixed, "importcoords", "tblSecCoords",
"C:\import.txt", False

you'll want something like:

Dim lngAfter As Long
Dim lngBefore As Long
Dim strFilter As String
Dim strInputFileName as string

strFilter = ahtAddFilterItem(strFilter, "Text Files (*.TXT)", "*.TXT")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
lngBefore = DCount("*", "tblSecCoords")

DoCmd.TransferText acImportFixed, "importcoords", _
strInputFIleName, "C:\import.txt", False

lngAfter = DCount("*", "tblSecCoords")

Msgbox "You imported " & (lngAfter - lngBefore) & " records."


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug for the quick response. I'm not really that up with VBA.
I
kind of understand the process.

I presume I need to place the code on the click event of the button?
Where does the import code go?
As for number 2 you have certainly lost me there!

Andy

"Douglas J. Steele" wrote in
message
...
For 1, see http://www.mvps.org/access/api/api0001.htm at "The Access
Web"

For 2, use DCount("*", "tblSecCoords") before and after the import.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
I want to import data regularly (the import format will always be the
same) and there are 2 things I wish to do as part of the process:-

First I want to hit an "Import button" on a form which then displays
the standard browse dialogue to allow the user to select the file.
Secondly I want the system to display a message of confirmation with
the number of records imported displayed in the message.

I have got it to work using an import specification (on the click
event
of the command button) :

DoCmd.TransferText acImportFixed, "importcoords", "tblSecCoords",
"C:\import.txt", False

which works fine except for the two issues raised above. Can anyone
help?

Andy

As long as your import specification (the 2nd argument in your TransferText:
"import" in your example) is correct, the import should work fine.

Try importing manually (through File | Get External Data) and click on the
Advanced button when the wizard appears. Click on the Specs button and
select "import". Correct it as required, and click on the Save As button to
replace the old version.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Doug,

I've changed the structure of theDB tables a little and now wish to import
the same file (see attached) into a table which has more field than which
I'm importing.

I get an error now when I use the following code:

Private Sub Command34_Click()
Dim lngAfter As Long
Dim lngBefore As Long
Dim strFilter As String
Dim strInputFileName As String

strFilter = ahtAddFilterItem(strFilter, "Text Files (*.TXT)", "*.TXT")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
lngBefore = DCount("*", "tblSections")

DoCmd.TransferText acImportDelim, "import", "tblSections",
strInputFileName, False

lngAfter = DCount("*", "tblSections")

MsgBox "You imported " & (lngAfter - lngBefore) & " sections."
End Sub

Is this because the number of fields are different. The txtfile has the
first 5 fields which correspond to the first 5 in the table. However the
table has a further 7 fields which can't come from the import. What I want
to do is import the 5 fields into the table and then add the remaining
fields manually.

Is this possible.

(The error I get is Runtime Error 3001, Invalid Argument)

Regards Andy Andy Roberts BSc (Hons) Senior Consultant
www.blue-bean.co.uk M +44 (0)7970 255 156 T +44
(0)151 428 6838 240 Mackets Lane Woolton Liverpool L25 9NH United Kingdom
"Andy Roberts" wrote in message
...
Thanks yet again Doug,

Thats sorted that problem.

However... then numbers are coming in as whole numbers (missing the 4
decimal places) for each value.

Each field is set as a Long Integer and the decimal places are set to 4,
but it doesn't seem to work, even if I change the type to decimal.

Andy


"Douglas J. Steele" wrote in message
...
"acImportFixed" says that the columns are a fixed width. You want
"acImportDelim"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug

That works a treat - well nearly!

Everything works, except the resulting data is different from that
cointained in the incoming file. The data coming in is as follows
(they are
co-ordinates):-

0001,652.8655,349.5133,689.3182,468.6107
0002,822.5105,468.6107,861.7672,336.9030
0003,1020.1960,336.9030,1051.0406,505.0405
0004,1154.7904,505.0405,1330.0435,356.5190
0005,1531.9351,440.5878,1426.7832,573.6967
0006,1505.2966,743.2354,1799.7218,419.5706
0007,1913.2858,619.9345,1530.5330,928.1867
0008,1913.2858,619.9345,1530.5330,928.1867

It is REF, StartX, StartY, End X, EndY and saved in a txt file

On import an autonumber is assigned as a unique reference. No problem,
however when I import the file the message I receive is correct with
the
number of entries, but the table looks like this:-

http://www.blue-bean.co.uk/images/screen.jpg

The numbers aren't formatted correctly - I thinks it must have
something to
do with number of decimal places. Any clues? It also imports the
first
comma (but the field type is text in this case)



"Douglas J. Steele" wrote in
message
...
Copy all of the shaded code (what's between Code Start and Code End),
and
paste it into a new module. When you save the module, name it
something
like "mdlFileDialog" (the name of the module isn't really critical,
other
than the fact that you cannot name the module the same as any of the
functions or subs within it)

Rather than simply having:

DoCmd.TransferText acImportFixed, "importcoords", "tblSecCoords",
"C:\import.txt", False

you'll want something like:

Dim lngAfter As Long
Dim lngBefore As Long
Dim strFilter As String
Dim strInputFileName as string

strFilter = ahtAddFilterItem(strFilter, "Text Files (*.TXT)",
"*.TXT")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
lngBefore = DCount("*", "tblSecCoords")

DoCmd.TransferText acImportFixed, "importcoords", _
strInputFIleName, "C:\import.txt", False

lngAfter = DCount("*", "tblSecCoords")

Msgbox "You imported " & (lngAfter - lngBefore) & " records."


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug for the quick response. I'm not really that up with VBA.
I
kind of understand the process.

I presume I need to place the code on the click event of the button?
Where does the import code go?
As for number 2 you have certainly lost me there!

Andy

"Douglas J. Steele" wrote in
message
...
For 1, see http://www.mvps.org/access/api/api0001.htm at "The Access
Web"

For 2, use DCount("*", "tblSecCoords") before and after the import.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
I want to import data regularly (the import format will always be
the
same) and there are 2 things I wish to do as part of the process:-

First I want to hit an "Import button" on a form which then
displays
the standard browse dialogue to allow the user to select the file.
Secondly I want the system to display a message of confirmation
with
the number of records imported displayed in the message.

I have got it to work using an import specification (on the click
event
of the command button) :

DoCmd.TransferText acImportFixed, "importcoords",
"tblSecCoords",
"C:\import.txt", False

which works fine except for the two issues raised above. Can
anyone
help?

Andy

Thanks Doug

Saved the day again - your approach seems so logical now - I'll try and work
these things out with that outlook.

Managed to get the co-ordinates in as decimals by setting the field type to
Double. However, the number of decimal places differs depending on the
trailing zeros e.g.456.700 shows as 456.7 and 456.760 shows as 456.76.

I've tried setting the dec places to 3 but doesn't make any difference. Not
too worried how they are stored in the tables, but the form displaying the
values should be to 3 decimal places. I've tried using the ROUND function
as part of the control source on of the textbox on the form, but this
doesn't work. Am I on the right path?

Andy


"Douglas J. Steele" wrote in message
...
As long as your import specification (the 2nd argument in your
TransferText: "import" in your example) is correct, the import should work
fine.

Try importing manually (through File | Get External Data) and click on the
Advanced button when the wizard appears. Click on the Specs button and
select "import". Correct it as required, and click on the Save As button
to replace the old version.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Doug,

I've changed the structure of theDB tables a little and now wish to
import the same file (see attached) into a table which has more field
than which I'm importing.

I get an error now when I use the following code:

Private Sub Command34_Click()
Dim lngAfter As Long
Dim lngBefore As Long
Dim strFilter As String
Dim strInputFileName As String

strFilter = ahtAddFilterItem(strFilter, "Text Files (*.TXT)", "*.TXT")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
lngBefore = DCount("*", "tblSections")

DoCmd.TransferText acImportDelim, "import", "tblSections",
strInputFileName, False

lngAfter = DCount("*", "tblSections")

MsgBox "You imported " & (lngAfter - lngBefore) & " sections."
End Sub

Is this because the number of fields are different. The txtfile has the
first 5 fields which correspond to the first 5 in the table. However the
table has a further 7 fields which can't come from the import. What I
want to do is import the 5 fields into the table and then add the
remaining fields manually.

Is this possible.

(The error I get is Runtime Error 3001, Invalid Argument)

Regards Andy Andy Roberts BSc (Hons) Senior Consultant
www.blue-bean.co.uk M +44 (0)7970 255 156 T +44
(0)151 428 6838 240 Mackets Lane Woolton Liverpool L25 9NH United Kingdom
"Andy Roberts" wrote in message
...
Thanks yet again Doug,

Thats sorted that problem.

However... then numbers are coming in as whole numbers (missing the 4
decimal places) for each value.

Each field is set as a Long Integer and the decimal places are set to 4,
but it doesn't seem to work, even if I change the type to decimal.

Andy


"Douglas J. Steele" wrote in message
...
"acImportFixed" says that the columns are a fixed width. You want
"acImportDelim"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug

That works a treat - well nearly!

Everything works, except the resulting data is different from that
cointained in the incoming file. The data coming in is as follows
(they are
co-ordinates):-

0001,652.8655,349.5133,689.3182,468.6107
0002,822.5105,468.6107,861.7672,336.9030
0003,1020.1960,336.9030,1051.0406,505.0405
0004,1154.7904,505.0405,1330.0435,356.5190
0005,1531.9351,440.5878,1426.7832,573.6967
0006,1505.2966,743.2354,1799.7218,419.5706
0007,1913.2858,619.9345,1530.5330,928.1867
0008,1913.2858,619.9345,1530.5330,928.1867

It is REF, StartX, StartY, End X, EndY and saved in a txt file

On import an autonumber is assigned as a unique reference. No
problem,
however when I import the file the message I receive is correct with
the
number of entries, but the table looks like this:-

http://www.blue-bean.co.uk/images/screen.jpg

The numbers aren't formatted correctly - I thinks it must have
something to
do with number of decimal places. Any clues? It also imports the
first
comma (but the field type is text in this case)



"Douglas J. Steele" wrote in
message
...
Copy all of the shaded code (what's between Code Start and Code End),
and
paste it into a new module. When you save the module, name it
something
like "mdlFileDialog" (the name of the module isn't really critical,
other
than the fact that you cannot name the module the same as any of the
functions or subs within it)

Rather than simply having:

DoCmd.TransferText acImportFixed, "importcoords", "tblSecCoords",
"C:\import.txt", False

you'll want something like:

Dim lngAfter As Long
Dim lngBefore As Long
Dim strFilter As String
Dim strInputFileName as string

strFilter = ahtAddFilterItem(strFilter, "Text Files (*.TXT)",
"*.TXT")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
lngBefore = DCount("*", "tblSecCoords")

DoCmd.TransferText acImportFixed, "importcoords", _
strInputFIleName, "C:\import.txt", False

lngAfter = DCount("*", "tblSecCoords")

Msgbox "You imported " & (lngAfter - lngBefore) & " records."


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug for the quick response. I'm not really that up with
VBA. I
kind of understand the process.

I presume I need to place the code on the click event of the button?
Where does the import code go?
As for number 2 you have certainly lost me there!

Andy

"Douglas J. Steele" wrote in
message
...
For 1, see http://www.mvps.org/access/api/api0001.htm at "The
Access
Web"

For 2, use DCount("*", "tblSecCoords") before and after the import.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
I want to import data regularly (the import format will always be
the
same) and there are 2 things I wish to do as part of the process:-

First I want to hit an "Import button" on a form which then
displays
the standard browse dialogue to allow the user to select the file.
Secondly I want the system to display a message of confirmation
with
the number of records imported displayed in the message.

I have got it to work using an import specification (on the click
event
of the command button) :

DoCmd.TransferText acImportFixed, "importcoords",
"tblSecCoords",
"C:\import.txt", False

which works fine except for the two issues raised above. Can
anyone
help?

Andy

Set the Format property to #.000


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug

Saved the day again - your approach seems so logical now - I'll try and
work these things out with that outlook.

Managed to get the co-ordinates in as decimals by setting the field type
to Double. However, the number of decimal places differs depending on the
trailing zeros e.g.456.700 shows as 456.7 and 456.760 shows as 456.76.

I've tried setting the dec places to 3 but doesn't make any difference.
Not too worried how they are stored in the tables, but the form displaying
the values should be to 3 decimal places. I've tried using the ROUND
function as part of the control source on of the textbox on the form, but
this doesn't work. Am I on the right path?

Andy


"Douglas J. Steele" wrote in message
...
As long as your import specification (the 2nd argument in your
TransferText: "import" in your example) is correct, the import should
work fine.

Try importing manually (through File | Get External Data) and click on
the Advanced button when the wizard appears. Click on the Specs button
and select "import". Correct it as required, and click on the Save As
button to replace the old version.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Doug,

I've changed the structure of theDB tables a little and now wish to
import the same file (see attached) into a table which has more field
than which I'm importing.

I get an error now when I use the following code:

Private Sub Command34_Click()
Dim lngAfter As Long
Dim lngBefore As Long
Dim strFilter As String
Dim strInputFileName As String

strFilter = ahtAddFilterItem(strFilter, "Text Files (*.TXT)", "*.TXT")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
lngBefore = DCount("*", "tblSections")

DoCmd.TransferText acImportDelim, "import", "tblSections",
strInputFileName, False

lngAfter = DCount("*", "tblSections")

MsgBox "You imported " & (lngAfter - lngBefore) & " sections."
End Sub

Is this because the number of fields are different. The txtfile has the
first 5 fields which correspond to the first 5 in the table. However
the table has a further 7 fields which can't come from the import. What
I want to do is import the 5 fields into the table and then add the
remaining fields manually.

Is this possible.

(The error I get is Runtime Error 3001, Invalid Argument)

Regards Andy Andy Roberts BSc (Hons) Senior Consultant
www.blue-bean.co.uk M +44 (0)7970 255 156 T +44
(0)151 428 6838 240 Mackets Lane Woolton Liverpool L25 9NH United
Kingdom
"Andy Roberts" wrote in message
...
Thanks yet again Doug,

Thats sorted that problem.

However... then numbers are coming in as whole numbers (missing the 4
decimal places) for each value.

Each field is set as a Long Integer and the decimal places are set to
4, but it doesn't seem to work, even if I change the type to decimal.

Andy


"Douglas J. Steele" wrote in
message ...
"acImportFixed" says that the columns are a fixed width. You want
"acImportDelim"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug

That works a treat - well nearly!

Everything works, except the resulting data is different from that
cointained in the incoming file. The data coming in is as follows
(they are
co-ordinates):-

0001,652.8655,349.5133,689.3182,468.6107
0002,822.5105,468.6107,861.7672,336.9030
0003,1020.1960,336.9030,1051.0406,505.0405
0004,1154.7904,505.0405,1330.0435,356.5190
0005,1531.9351,440.5878,1426.7832,573.6967
0006,1505.2966,743.2354,1799.7218,419.5706
0007,1913.2858,619.9345,1530.5330,928.1867
0008,1913.2858,619.9345,1530.5330,928.1867

It is REF, StartX, StartY, End X, EndY and saved in a txt file

On import an autonumber is assigned as a unique reference. No
problem,
however when I import the file the message I receive is correct with
the
number of entries, but the table looks like this:-

http://www.blue-bean.co.uk/images/screen.jpg

The numbers aren't formatted correctly - I thinks it must have
something to
do with number of decimal places. Any clues? It also imports the
first
comma (but the field type is text in this case)



"Douglas J. Steele" wrote in
message
...
Copy all of the shaded code (what's between Code Start and Code
End), and
paste it into a new module. When you save the module, name it
something
like "mdlFileDialog" (the name of the module isn't really critical,
other
than the fact that you cannot name the module the same as any of the
functions or subs within it)

Rather than simply having:

DoCmd.TransferText acImportFixed, "importcoords", "tblSecCoords",
"C:\import.txt", False

you'll want something like:

Dim lngAfter As Long
Dim lngBefore As Long
Dim strFilter As String
Dim strInputFileName as string

strFilter = ahtAddFilterItem(strFilter, "Text Files (*.TXT)",
"*.TXT")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
lngBefore = DCount("*", "tblSecCoords")

DoCmd.TransferText acImportFixed, "importcoords", _
strInputFIleName, "C:\import.txt", False

lngAfter = DCount("*", "tblSecCoords")

Msgbox "You imported " & (lngAfter - lngBefore) & " records."


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug for the quick response. I'm not really that up with
VBA. I
kind of understand the process.

I presume I need to place the code on the click event of the
button?
Where does the import code go?
As for number 2 you have certainly lost me there!

Andy

"Douglas J. Steele" wrote in
message
...
For 1, see http://www.mvps.org/access/api/api0001.htm at "The
Access
Web"

For 2, use DCount("*", "tblSecCoords") before and after the
import.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
I want to import data regularly (the import format will always be
the
same) and there are 2 things I wish to do as part of the process:-

First I want to hit an "Import button" on a form which then
displays
the standard browse dialogue to allow the user to select the
file.
Secondly I want the system to display a message of confirmation
with
the number of records imported displayed in the message.

I have got it to work using an import specification (on the click
event
of the command button) :

DoCmd.TransferText acImportFixed, "importcoords",
"tblSecCoords",
"C:\import.txt", False

which works fine except for the two issues raised above. Can
anyone
help?

Andy

Thanks yet again Doug - Genius,

I'd actually tried this but didn't know to use the # as a wildcard. The
only problem with this is that it doesn't work on the form. In the table if
I use #.000 it works fine. I would expect the data to be displayed
identically on the form but it isn't, so I used the #.000 for the format of
the text box but it doesn't work.

Does the form not just display what the table tells it?

Andy


Regards Andy Andy Roberts BSc (Hons) Senior Consultant
www.blue-bean.co.uk M +44 (0)7970 255 156 T +44 (0)151 428 6838 240 Mackets
Lane Woolton Liverpool L25 9NH United Kingdom
"Douglas J. Steele" wrote in message
...
Set the Format property to #.000


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug

Saved the day again - your approach seems so logical now - I'll try and
work these things out with that outlook.

Managed to get the co-ordinates in as decimals by setting the field type
to Double. However, the number of decimal places differs depending on
the trailing zeros e.g.456.700 shows as 456.7 and 456.760 shows as
456.76.

I've tried setting the dec places to 3 but doesn't make any difference.
Not too worried how they are stored in the tables, but the form
displaying the values should be to 3 decimal places. I've tried using
the ROUND function as part of the control source on of the textbox on the
form, but this doesn't work. Am I on the right path?

Andy


"Douglas J. Steele" wrote in message
...
As long as your import specification (the 2nd argument in your
TransferText: "import" in your example) is correct, the import should
work fine.

Try importing manually (through File | Get External Data) and click on
the Advanced button when the wizard appears. Click on the Specs button
and select "import". Correct it as required, and click on the Save As
button to replace the old version.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Doug,

I've changed the structure of theDB tables a little and now wish to
import the same file (see attached) into a table which has more field
than which I'm importing.

I get an error now when I use the following code:

Private Sub Command34_Click()
Dim lngAfter As Long
Dim lngBefore As Long
Dim strFilter As String
Dim strInputFileName As String

strFilter = ahtAddFilterItem(strFilter, "Text Files (*.TXT)", "*.TXT")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
lngBefore = DCount("*", "tblSections")

DoCmd.TransferText acImportDelim, "import", "tblSections",
strInputFileName, False

lngAfter = DCount("*", "tblSections")

MsgBox "You imported " & (lngAfter - lngBefore) & " sections."
End Sub

Is this because the number of fields are different. The txtfile has
the first 5 fields which correspond to the first 5 in the table.
However the table has a further 7 fields which can't come from the
import. What I want to do is import the 5 fields into the table and
then add the remaining fields manually.

Is this possible.

(The error I get is Runtime Error 3001, Invalid Argument)

Regards Andy Andy Roberts BSc (Hons) Senior Consultant
www.blue-bean.co.uk M +44 (0)7970 255 156 T +44
(0)151 428 6838 240 Mackets Lane Woolton Liverpool L25 9NH United
Kingdom
"Andy Roberts" wrote in message
...
Thanks yet again Doug,

Thats sorted that problem.

However... then numbers are coming in as whole numbers (missing the 4
decimal places) for each value.

Each field is set as a Long Integer and the decimal places are set to
4, but it doesn't seem to work, even if I change the type to decimal.

Andy


"Douglas J. Steele" wrote in
message ...
"acImportFixed" says that the columns are a fixed width. You want
"acImportDelim"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug

That works a treat - well nearly!

Everything works, except the resulting data is different from that
cointained in the incoming file. The data coming in is as follows
(they are
co-ordinates):-

0001,652.8655,349.5133,689.3182,468.6107
0002,822.5105,468.6107,861.7672,336.9030
0003,1020.1960,336.9030,1051.0406,505.0405
0004,1154.7904,505.0405,1330.0435,356.5190
0005,1531.9351,440.5878,1426.7832,573.6967
0006,1505.2966,743.2354,1799.7218,419.5706
0007,1913.2858,619.9345,1530.5330,928.1867
0008,1913.2858,619.9345,1530.5330,928.1867

It is REF, StartX, StartY, End X, EndY and saved in a txt file

On import an autonumber is assigned as a unique reference. No
problem,
however when I import the file the message I receive is correct with
the
number of entries, but the table looks like this:-

http://www.blue-bean.co.uk/images/screen.jpg

The numbers aren't formatted correctly - I thinks it must have
something to
do with number of decimal places. Any clues? It also imports the
first
comma (but the field type is text in this case)



"Douglas J. Steele" wrote in
message
...
Copy all of the shaded code (what's between Code Start and Code
End), and
paste it into a new module. When you save the module, name it
something
like "mdlFileDialog" (the name of the module isn't really critical,
other
than the fact that you cannot name the module the same as any of
the
functions or subs within it)

Rather than simply having:

DoCmd.TransferText acImportFixed, "importcoords",
"tblSecCoords",
"C:\import.txt", False

you'll want something like:

Dim lngAfter As Long
Dim lngBefore As Long
Dim strFilter As String
Dim strInputFileName as string

strFilter = ahtAddFilterItem(strFilter, "Text Files (*.TXT)",
"*.TXT")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
lngBefore = DCount("*", "tblSecCoords")

DoCmd.TransferText acImportFixed, "importcoords", _
strInputFIleName, "C:\import.txt", False

lngAfter = DCount("*", "tblSecCoords")

Msgbox "You imported " & (lngAfter - lngBefore) & " records."


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug for the quick response. I'm not really that up with
VBA. I
kind of understand the process.

I presume I need to place the code on the click event of the
button?
Where does the import code go?
As for number 2 you have certainly lost me there!

Andy

"Douglas J. Steele" wrote in
message
...
For 1, see http://www.mvps.org/access/api/api0001.htm at "The
Access
Web"

For 2, use DCount("*", "tblSecCoords") before and after the
import.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
I want to import data regularly (the import format will always be
the
same) and there are 2 things I wish to do as part of the
process:-

First I want to hit an "Import button" on a form which then
displays
the standard browse dialogue to allow the user to select the
file.
Secondly I want the system to display a message of confirmation
with
the number of records imported displayed in the message.

I have got it to work using an import specification (on the
click event
of the command button) :

DoCmd.TransferText acImportFixed, "importcoords",
"tblSecCoords",
"C:\import.txt", False

which works fine except for the two issues raised above. Can
anyone
help?

Andy

In the absence of specific formatting in the text box, I would have expected
it to display the same as what's defined for the field in the table.
However, on testing just now, I see that isn't the case.

What happened when you used #.000 as the Format property for the text box? I
just tried it, and it worked fine for me.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks yet again Doug - Genius,

I'd actually tried this but didn't know to use the # as a wildcard. The
only problem with this is that it doesn't work on the form. In the table
if I use #.000 it works fine. I would expect the data to be displayed
identically on the form but it isn't, so I used the #.000 for the format
of the text box but it doesn't work.

Does the form not just display what the table tells it?

Andy


Regards Andy Andy Roberts BSc (Hons) Senior Consultant
www.blue-bean.co.uk M +44 (0)7970 255 156 T +44
(0)151 428 6838 240 Mackets Lane Woolton Liverpool L25 9NH United Kingdom
"Douglas J. Steele" wrote in message
...
Set the Format property to #.000


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug

Saved the day again - your approach seems so logical now - I'll try and
work these things out with that outlook.

Managed to get the co-ordinates in as decimals by setting the field type
to Double. However, the number of decimal places differs depending on
the trailing zeros e.g.456.700 shows as 456.7 and 456.760 shows as
456.76.

I've tried setting the dec places to 3 but doesn't make any difference.
Not too worried how they are stored in the tables, but the form
displaying the values should be to 3 decimal places. I've tried using
the ROUND function as part of the control source on of the textbox on
the form, but this doesn't work. Am I on the right path?

Andy


"Douglas J. Steele" wrote in message
...
As long as your import specification (the 2nd argument in your
TransferText: "import" in your example) is correct, the import should
work fine.

Try importing manually (through File | Get External Data) and click on
the Advanced button when the wizard appears. Click on the Specs button
and select "import". Correct it as required, and click on the Save As
button to replace the old version.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Doug,

I've changed the structure of theDB tables a little and now wish to
import the same file (see attached) into a table which has more field
than which I'm importing.

I get an error now when I use the following code:

Private Sub Command34_Click()
Dim lngAfter As Long
Dim lngBefore As Long
Dim strFilter As String
Dim strInputFileName As String

strFilter = ahtAddFilterItem(strFilter, "Text Files (*.TXT)",
"*.TXT")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
lngBefore = DCount("*", "tblSections")

DoCmd.TransferText acImportDelim, "import", "tblSections",
strInputFileName, False

lngAfter = DCount("*", "tblSections")

MsgBox "You imported " & (lngAfter - lngBefore) & " sections."
End Sub

Is this because the number of fields are different. The txtfile has
the first 5 fields which correspond to the first 5 in the table.
However the table has a further 7 fields which can't come from the
import. What I want to do is import the 5 fields into the table and
then add the remaining fields manually.

Is this possible.

(The error I get is Runtime Error 3001, Invalid Argument)

Regards Andy Andy Roberts BSc (Hons) Senior Consultant
www.blue-bean.co.uk M +44 (0)7970 255 156 T +44
(0)151 428 6838 240 Mackets Lane Woolton Liverpool L25 9NH United
Kingdom
"Andy Roberts" wrote in message
...
Thanks yet again Doug,

Thats sorted that problem.

However... then numbers are coming in as whole numbers (missing the 4
decimal places) for each value.

Each field is set as a Long Integer and the decimal places are set to
4, but it doesn't seem to work, even if I change the type to decimal.

Andy


"Douglas J. Steele" wrote in
message ...
"acImportFixed" says that the columns are a fixed width. You want
"acImportDelim"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug

That works a treat - well nearly!

Everything works, except the resulting data is different from that
cointained in the incoming file. The data coming in is as follows
(they are
co-ordinates):-

0001,652.8655,349.5133,689.3182,468.6107
0002,822.5105,468.6107,861.7672,336.9030
0003,1020.1960,336.9030,1051.0406,505.0405
0004,1154.7904,505.0405,1330.0435,356.5190
0005,1531.9351,440.5878,1426.7832,573.6967
0006,1505.2966,743.2354,1799.7218,419.5706
0007,1913.2858,619.9345,1530.5330,928.1867
0008,1913.2858,619.9345,1530.5330,928.1867

It is REF, StartX, StartY, End X, EndY and saved in a txt file

On import an autonumber is assigned as a unique reference. No
problem,
however when I import the file the message I receive is correct
with the
number of entries, but the table looks like this:-

http://www.blue-bean.co.uk/images/screen.jpg

The numbers aren't formatted correctly - I thinks it must have
something to
do with number of decimal places. Any clues? It also imports the
first
comma (but the field type is text in this case)



"Douglas J. Steele" wrote in
message
...
Copy all of the shaded code (what's between Code Start and Code
End), and
paste it into a new module. When you save the module, name it
something
like "mdlFileDialog" (the name of the module isn't really
critical, other
than the fact that you cannot name the module the same as any of
the
functions or subs within it)

Rather than simply having:

DoCmd.TransferText acImportFixed, "importcoords",
"tblSecCoords",
"C:\import.txt", False

you'll want something like:

Dim lngAfter As Long
Dim lngBefore As Long
Dim strFilter As String
Dim strInputFileName as string

strFilter = ahtAddFilterItem(strFilter, "Text Files (*.TXT)",
"*.TXT")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
lngBefore = DCount("*", "tblSecCoords")

DoCmd.TransferText acImportFixed, "importcoords", _
strInputFIleName, "C:\import.txt", False

lngAfter = DCount("*", "tblSecCoords")

Msgbox "You imported " & (lngAfter - lngBefore) & " records."


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug for the quick response. I'm not really that up with
VBA. I
kind of understand the process.

I presume I need to place the code on the click event of the
button?
Where does the import code go?
As for number 2 you have certainly lost me there!

Andy

"Douglas J. Steele" wrote in
message
...
For 1, see http://www.mvps.org/access/api/api0001.htm at "The
Access
Web"

For 2, use DCount("*", "tblSecCoords") before and after the
import.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
I want to import data regularly (the import format will always
be the
same) and there are 2 things I wish to do as part of the
process:-

First I want to hit an "Import button" on a form which then
displays
the standard browse dialogue to allow the user to select the
file.
Secondly I want the system to display a message of confirmation
with
the number of records imported displayed in the message.

I have got it to work using an import specification (on the
click event
of the command button) :

DoCmd.TransferText acImportFixed, "importcoords",
"tblSecCoords",
"C:\import.txt", False

which works fine except for the two issues raised above. Can
anyone
help?

Andy

If I use #.000 as the format on the text box it just displays without the
trailing zeros as it was in the table.

Does it make a difference if the text boxes are as a result of picking a
refernce from a cbo? i.e. I pick a reference from a cbo and it displays the
x&y of the start point and the x&y of the endpoint in 4 separate text boxes.
I also have an unbound txt box which calculates the distance bewteen the two
coordinates and this wont display to 3dp either irrespective of the format
used.

Andy


"Douglas J. Steele" wrote in message
...
In the absence of specific formatting in the text box, I would have
expected it to display the same as what's defined for the field in the
table. However, on testing just now, I see that isn't the case.

What happened when you used #.000 as the Format property for the text box?
I just tried it, and it worked fine for me.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks yet again Doug - Genius,

I'd actually tried this but didn't know to use the # as a wildcard. The
only problem with this is that it doesn't work on the form. In the table
if I use #.000 it works fine. I would expect the data to be displayed
identically on the form but it isn't, so I used the #.000 for the format
of the text box but it doesn't work.

Does the form not just display what the table tells it?

Andy


Regards Andy Andy Roberts BSc (Hons) Senior Consultant
www.blue-bean.co.uk M +44 (0)7970 255 156 T +44
(0)151 428 6838 240 Mackets Lane Woolton Liverpool L25 9NH United Kingdom
"Douglas J. Steele" wrote in message
...
Set the Format property to #.000


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug

Saved the day again - your approach seems so logical now - I'll try and
work these things out with that outlook.

Managed to get the co-ordinates in as decimals by setting the field
type to Double. However, the number of decimal places differs
depending on the trailing zeros e.g.456.700 shows as 456.7 and 456.760
shows as 456.76.

I've tried setting the dec places to 3 but doesn't make any difference.
Not too worried how they are stored in the tables, but the form
displaying the values should be to 3 decimal places. I've tried using
the ROUND function as part of the control source on of the textbox on
the form, but this doesn't work. Am I on the right path?

Andy


"Douglas J. Steele" wrote in
message ...
As long as your import specification (the 2nd argument in your
TransferText: "import" in your example) is correct, the import should
work fine.

Try importing manually (through File | Get External Data) and click on
the Advanced button when the wizard appears. Click on the Specs button
and select "import". Correct it as required, and click on the Save As
button to replace the old version.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Doug,

I've changed the structure of theDB tables a little and now wish to
import the same file (see attached) into a table which has more field
than which I'm importing.

I get an error now when I use the following code:

Private Sub Command34_Click()
Dim lngAfter As Long
Dim lngBefore As Long
Dim strFilter As String
Dim strInputFileName As String

strFilter = ahtAddFilterItem(strFilter, "Text Files (*.TXT)",
"*.TXT")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
lngBefore = DCount("*", "tblSections")

DoCmd.TransferText acImportDelim, "import", "tblSections",
strInputFileName, False

lngAfter = DCount("*", "tblSections")

MsgBox "You imported " & (lngAfter - lngBefore) & " sections."
End Sub

Is this because the number of fields are different. The txtfile has
the first 5 fields which correspond to the first 5 in the table.
However the table has a further 7 fields which can't come from the
import. What I want to do is import the 5 fields into the table and
then add the remaining fields manually.

Is this possible.

(The error I get is Runtime Error 3001, Invalid Argument)

Regards Andy Andy Roberts BSc (Hons) Senior Consultant
www.blue-bean.co.uk M +44 (0)7970 255 156 T +44
(0)151 428 6838 240 Mackets Lane Woolton Liverpool L25 9NH United
Kingdom
"Andy Roberts" wrote in message
...
Thanks yet again Doug,

Thats sorted that problem.

However... then numbers are coming in as whole numbers (missing the
4 decimal places) for each value.

Each field is set as a Long Integer and the decimal places are set
to 4, but it doesn't seem to work, even if I change the type to
decimal.

Andy


"Douglas J. Steele" wrote in
message ...
"acImportFixed" says that the columns are a fixed width. You want
"acImportDelim"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug

That works a treat - well nearly!

Everything works, except the resulting data is different from that
cointained in the incoming file. The data coming in is as follows
(they are
co-ordinates):-

0001,652.8655,349.5133,689.3182,468.6107
0002,822.5105,468.6107,861.7672,336.9030
0003,1020.1960,336.9030,1051.0406,505.0405
0004,1154.7904,505.0405,1330.0435,356.5190
0005,1531.9351,440.5878,1426.7832,573.6967
0006,1505.2966,743.2354,1799.7218,419.5706
0007,1913.2858,619.9345,1530.5330,928.1867
0008,1913.2858,619.9345,1530.5330,928.1867

It is REF, StartX, StartY, End X, EndY and saved in a txt file

On import an autonumber is assigned as a unique reference. No
problem,
however when I import the file the message I receive is correct
with the
number of entries, but the table looks like this:-

http://www.blue-bean.co.uk/images/screen.jpg

The numbers aren't formatted correctly - I thinks it must have
something to
do with number of decimal places. Any clues? It also imports the
first
comma (but the field type is text in this case)



"Douglas J. Steele" wrote in
message
...
Copy all of the shaded code (what's between Code Start and Code
End), and
paste it into a new module. When you save the module, name it
something
like "mdlFileDialog" (the name of the module isn't really
critical, other
than the fact that you cannot name the module the same as any of
the
functions or subs within it)

Rather than simply having:

DoCmd.TransferText acImportFixed, "importcoords",
"tblSecCoords",
"C:\import.txt", False

you'll want something like:

Dim lngAfter As Long
Dim lngBefore As Long
Dim strFilter As String
Dim strInputFileName as string

strFilter = ahtAddFilterItem(strFilter, "Text Files (*.TXT)",
"*.TXT")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
lngBefore = DCount("*", "tblSecCoords")

DoCmd.TransferText acImportFixed, "importcoords", _
strInputFIleName, "C:\import.txt", False

lngAfter = DCount("*", "tblSecCoords")

Msgbox "You imported " & (lngAfter - lngBefore) & " records."


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug for the quick response. I'm not really that up with
VBA. I
kind of understand the process.

I presume I need to place the code on the click event of the
button?
Where does the import code go?
As for number 2 you have certainly lost me there!

Andy

"Douglas J. Steele" wrote in
message
...
For 1, see http://www.mvps.org/access/api/api0001.htm at "The
Access
Web"

For 2, use DCount("*", "tblSecCoords") before and after the
import.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
I want to import data regularly (the import format will always
be the
same) and there are 2 things I wish to do as part of the
process:-

First I want to hit an "Import button" on a form which then
displays
the standard browse dialogue to allow the user to select the
file.
Secondly I want the system to display a message of
confirmation with
the number of records imported displayed in the message.

I have got it to work using an import specification (on the
click event
of the command button) :

DoCmd.TransferText acImportFixed, "importcoords",
"tblSecCoords",
"C:\import.txt", False

which works fine except for the two issues raised above. Can
anyone
help?

Andy

I'm at a loss for suggestions. I just tested an unbound text box, and had no
problems.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
If I use #.000 as the format on the text box it just displays without the
trailing zeros as it was in the table.

Does it make a difference if the text boxes are as a result of picking a
refernce from a cbo? i.e. I pick a reference from a cbo and it displays
the x&y of the start point and the x&y of the endpoint in 4 separate text
boxes. I also have an unbound txt box which calculates the distance
bewteen the two coordinates and this wont display to 3dp either
irrespective of the format used.

Andy


"Douglas J. Steele" wrote in message
...
In the absence of specific formatting in the text box, I would have
expected it to display the same as what's defined for the field in the
table. However, on testing just now, I see that isn't the case.

What happened when you used #.000 as the Format property for the text
box? I just tried it, and it worked fine for me.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks yet again Doug - Genius,

I'd actually tried this but didn't know to use the # as a wildcard. The
only problem with this is that it doesn't work on the form. In the
table if I use #.000 it works fine. I would expect the data to be
displayed identically on the form but it isn't, so I used the #.000 for
the format of the text box but it doesn't work.

Does the form not just display what the table tells it?

Andy


Regards Andy Andy Roberts BSc (Hons) Senior Consultant
www.blue-bean.co.uk M +44 (0)7970 255 156 T +44
(0)151 428 6838 240 Mackets Lane Woolton Liverpool L25 9NH United
Kingdom
"Douglas J. Steele" wrote in message
...
Set the Format property to #.000


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug

Saved the day again - your approach seems so logical now - I'll try
and work these things out with that outlook.

Managed to get the co-ordinates in as decimals by setting the field
type to Double. However, the number of decimal places differs
depending on the trailing zeros e.g.456.700 shows as 456.7 and 456.760
shows as 456.76.

I've tried setting the dec places to 3 but doesn't make any
difference. Not too worried how they are stored in the tables, but the
form displaying the values should be to 3 decimal places. I've tried
using the ROUND function as part of the control source on of the
textbox on the form, but this doesn't work. Am I on the right path?

Andy


"Douglas J. Steele" wrote in
message ...
As long as your import specification (the 2nd argument in your
TransferText: "import" in your example) is correct, the import should
work fine.

Try importing manually (through File | Get External Data) and click
on the Advanced button when the wizard appears. Click on the Specs
button and select "import". Correct it as required, and click on the
Save As button to replace the old version.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Doug,

I've changed the structure of theDB tables a little and now wish to
import the same file (see attached) into a table which has more
field than which I'm importing.

I get an error now when I use the following code:

Private Sub Command34_Click()
Dim lngAfter As Long
Dim lngBefore As Long
Dim strFilter As String
Dim strInputFileName As String

strFilter = ahtAddFilterItem(strFilter, "Text Files (*.TXT)",
"*.TXT")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
lngBefore = DCount("*", "tblSections")

DoCmd.TransferText acImportDelim, "import", "tblSections",
strInputFileName, False

lngAfter = DCount("*", "tblSections")

MsgBox "You imported " & (lngAfter - lngBefore) & " sections."
End Sub

Is this because the number of fields are different. The txtfile has
the first 5 fields which correspond to the first 5 in the table.
However the table has a further 7 fields which can't come from the
import. What I want to do is import the 5 fields into the table and
then add the remaining fields manually.

Is this possible.

(The error I get is Runtime Error 3001, Invalid Argument)

Regards Andy Andy Roberts BSc (Hons) Senior Consultant
www.blue-bean.co.uk M +44 (0)7970 255 156 T +44
(0)151 428 6838 240 Mackets Lane Woolton Liverpool L25 9NH United
Kingdom
"Andy Roberts" wrote in message
...
Thanks yet again Doug,

Thats sorted that problem.

However... then numbers are coming in as whole numbers (missing the
4 decimal places) for each value.

Each field is set as a Long Integer and the decimal places are set
to 4, but it doesn't seem to work, even if I change the type to
decimal.

Andy


"Douglas J. Steele" wrote in
message ...
"acImportFixed" says that the columns are a fixed width. You want
"acImportDelim"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug

That works a treat - well nearly!

Everything works, except the resulting data is different from
that
cointained in the incoming file. The data coming in is as
follows (they are
co-ordinates):-

0001,652.8655,349.5133,689.3182,468.6107
0002,822.5105,468.6107,861.7672,336.9030
0003,1020.1960,336.9030,1051.0406,505.0405
0004,1154.7904,505.0405,1330.0435,356.5190
0005,1531.9351,440.5878,1426.7832,573.6967
0006,1505.2966,743.2354,1799.7218,419.5706
0007,1913.2858,619.9345,1530.5330,928.1867
0008,1913.2858,619.9345,1530.5330,928.1867

It is REF, StartX, StartY, End X, EndY and saved in a txt file

On import an autonumber is assigned as a unique reference. No
problem,
however when I import the file the message I receive is correct
with the
number of entries, but the table looks like this:-

http://www.blue-bean.co.uk/images/screen.jpg

The numbers aren't formatted correctly - I thinks it must have
something to
do with number of decimal places. Any clues? It also imports
the first
comma (but the field type is text in this case)



"Douglas J. Steele" wrote in
message
...
Copy all of the shaded code (what's between Code Start and Code
End), and
paste it into a new module. When you save the module, name it
something
like "mdlFileDialog" (the name of the module isn't really
critical, other
than the fact that you cannot name the module the same as any of
the
functions or subs within it)

Rather than simply having:

DoCmd.TransferText acImportFixed, "importcoords",
"tblSecCoords",
"C:\import.txt", False

you'll want something like:

Dim lngAfter As Long
Dim lngBefore As Long
Dim strFilter As String
Dim strInputFileName as string

strFilter = ahtAddFilterItem(strFilter, "Text Files (*.TXT)",
"*.TXT")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
lngBefore = DCount("*", "tblSecCoords")

DoCmd.TransferText acImportFixed, "importcoords", _
strInputFIleName, "C:\import.txt", False

lngAfter = DCount("*", "tblSecCoords")

Msgbox "You imported " & (lngAfter - lngBefore) & " records."


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug for the quick response. I'm not really that up
with VBA. I
kind of understand the process.

I presume I need to place the code on the click event of the
button?
Where does the import code go?
As for number 2 you have certainly lost me there!

Andy

"Douglas J. Steele" wrote
in message
...
For 1, see http://www.mvps.org/access/api/api0001.htm at "The
Access
Web"

For 2, use DCount("*", "tblSecCoords") before and after the
import.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
I want to import data regularly (the import format will always
be the
same) and there are 2 things I wish to do as part of the
process:-

First I want to hit an "Import button" on a form which then
displays
the standard browse dialogue to allow the user to select the
file.
Secondly I want the system to display a message of
confirmation with
the number of records imported displayed in the message.

I have got it to work using an import specification (on the
click event
of the command button) :

DoCmd.TransferText acImportFixed, "importcoords",
"tblSecCoords",
"C:\import.txt", False

which works fine except for the two issues raised above. Can
anyone
help?

Andy

Thanks for all your help on this one - seems I have something unusual set up
somewhere. Just for your info it Access 2003 saving as Access 2000 format.

Andy

"Douglas J. Steele" wrote in message
...
I'm at a loss for suggestions. I just tested an unbound text box, and had
no problems.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
If I use #.000 as the format on the text box it just displays without the
trailing zeros as it was in the table.

Does it make a difference if the text boxes are as a result of picking a
refernce from a cbo? i.e. I pick a reference from a cbo and it displays
the x&y of the start point and the x&y of the endpoint in 4 separate text
boxes. I also have an unbound txt box which calculates the distance
bewteen the two coordinates and this wont display to 3dp either
irrespective of the format used.

Andy


"Douglas J. Steele" wrote in message
...
In the absence of specific formatting in the text box, I would have
expected it to display the same as what's defined for the field in the
table. However, on testing just now, I see that isn't the case.

What happened when you used #.000 as the Format property for the text
box? I just tried it, and it worked fine for me.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks yet again Doug - Genius,

I'd actually tried this but didn't know to use the # as a wildcard.
The only problem with this is that it doesn't work on the form. In the
table if I use #.000 it works fine. I would expect the data to be
displayed identically on the form but it isn't, so I used the #.000 for
the format of the text box but it doesn't work.

Does the form not just display what the table tells it?

Andy


Regards Andy Andy Roberts BSc (Hons) Senior Consultant
www.blue-bean.co.uk M +44 (0)7970 255 156 T +44
(0)151 428 6838 240 Mackets Lane Woolton Liverpool L25 9NH United
Kingdom
"Douglas J. Steele" wrote in
message ...
Set the Format property to #.000


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug

Saved the day again - your approach seems so logical now - I'll try
and work these things out with that outlook.

Managed to get the co-ordinates in as decimals by setting the field
type to Double. However, the number of decimal places differs
depending on the trailing zeros e.g.456.700 shows as 456.7 and
456.760 shows as 456.76.

I've tried setting the dec places to 3 but doesn't make any
difference. Not too worried how they are stored in the tables, but
the form displaying the values should be to 3 decimal places. I've
tried using the ROUND function as part of the control source on of
the textbox on the form, but this doesn't work. Am I on the right
path?

Andy


"Douglas J. Steele" wrote in
message ...
As long as your import specification (the 2nd argument in your
TransferText: "import" in your example) is correct, the import
should work fine.

Try importing manually (through File | Get External Data) and click
on the Advanced button when the wizard appears. Click on the Specs
button and select "import". Correct it as required, and click on the
Save As button to replace the old version.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Doug,

I've changed the structure of theDB tables a little and now wish to
import the same file (see attached) into a table which has more
field than which I'm importing.

I get an error now when I use the following code:

Private Sub Command34_Click()
Dim lngAfter As Long
Dim lngBefore As Long
Dim strFilter As String
Dim strInputFileName As String

strFilter = ahtAddFilterItem(strFilter, "Text Files (*.TXT)",
"*.TXT")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
lngBefore = DCount("*", "tblSections")

DoCmd.TransferText acImportDelim, "import", "tblSections",
strInputFileName, False

lngAfter = DCount("*", "tblSections")

MsgBox "You imported " & (lngAfter - lngBefore) & " sections."
End Sub

Is this because the number of fields are different. The txtfile
has the first 5 fields which correspond to the first 5 in the
table. However the table has a further 7 fields which can't come
from the import. What I want to do is import the 5 fields into the
table and then add the remaining fields manually.

Is this possible.

(The error I get is Runtime Error 3001, Invalid Argument)

Regards Andy Andy Roberts BSc (Hons) Senior Consultant
www.blue-bean.co.uk M +44 (0)7970 255 156 T
+44 (0)151 428 6838 240 Mackets Lane Woolton Liverpool L25 9NH
United Kingdom
"Andy Roberts" wrote in message
...
Thanks yet again Doug,

Thats sorted that problem.

However... then numbers are coming in as whole numbers (missing
the 4 decimal places) for each value.

Each field is set as a Long Integer and the decimal places are set
to 4, but it doesn't seem to work, even if I change the type to
decimal.

Andy


"Douglas J. Steele" wrote in
message ...
"acImportFixed" says that the columns are a fixed width. You want
"acImportDelim"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug

That works a treat - well nearly!

Everything works, except the resulting data is different from
that
cointained in the incoming file. The data coming in is as
follows (they are
co-ordinates):-

0001,652.8655,349.5133,689.3182,468.6107
0002,822.5105,468.6107,861.7672,336.9030
0003,1020.1960,336.9030,1051.0406,505.0405
0004,1154.7904,505.0405,1330.0435,356.5190
0005,1531.9351,440.5878,1426.7832,573.6967
0006,1505.2966,743.2354,1799.7218,419.5706
0007,1913.2858,619.9345,1530.5330,928.1867
0008,1913.2858,619.9345,1530.5330,928.1867

It is REF, StartX, StartY, End X, EndY and saved in a txt file

On import an autonumber is assigned as a unique reference. No
problem,
however when I import the file the message I receive is correct
with the
number of entries, but the table looks like this:-

http://www.blue-bean.co.uk/images/screen.jpg

The numbers aren't formatted correctly - I thinks it must have
something to
do with number of decimal places. Any clues? It also imports
the first
comma (but the field type is text in this case)



"Douglas J. Steele" wrote in
message
...
Copy all of the shaded code (what's between Code Start and Code
End), and
paste it into a new module. When you save the module, name it
something
like "mdlFileDialog" (the name of the module isn't really
critical, other
than the fact that you cannot name the module the same as any
of the
functions or subs within it)

Rather than simply having:

DoCmd.TransferText acImportFixed, "importcoords",
"tblSecCoords",
"C:\import.txt", False

you'll want something like:

Dim lngAfter As Long
Dim lngBefore As Long
Dim strFilter As String
Dim strInputFileName as string

strFilter = ahtAddFilterItem(strFilter, "Text Files (*.TXT)",
"*.TXT")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
lngBefore = DCount("*", "tblSecCoords")

DoCmd.TransferText acImportFixed, "importcoords", _
strInputFIleName, "C:\import.txt", False

lngAfter = DCount("*", "tblSecCoords")

Msgbox "You imported " & (lngAfter - lngBefore) & " records."


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug for the quick response. I'm not really that up
with VBA. I
kind of understand the process.

I presume I need to place the code on the click event of the
button?
Where does the import code go?
As for number 2 you have certainly lost me there!

Andy

"Douglas J. Steele" wrote
in message
...
For 1, see http://www.mvps.org/access/api/api0001.htm at "The
Access
Web"

For 2, use DCount("*", "tblSecCoords") before and after the
import.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
I want to import data regularly (the import format will
always be the
same) and there are 2 things I wish to do as part of the
process:-

First I want to hit an "Import button" on a form which then
displays
the standard browse dialogue to allow the user to select the
file.
Secondly I want the system to display a message of
confirmation with
the number of records imported displayed in the message.

I have got it to work using an import specification (on the
click event
of the command button) :

DoCmd.TransferText acImportFixed, "importcoords",
"tblSecCoords",
"C:\import.txt", False

which works fine except for the two issues raised above.
Can anyone
help?

Andy

I only have Access 97 installed here, so it'll have to wait until I get home
to see whether Access 2003 changed anything.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks for all your help on this one - seems I have something unusual set
up somewhere. Just for your info it Access 2003 saving as Access 2000
format.

Andy

"Douglas J. Steele" wrote in message
...
I'm at a loss for suggestions. I just tested an unbound text box, and had
no problems.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
If I use #.000 as the format on the text box it just displays without
the trailing zeros as it was in the table.

Does it make a difference if the text boxes are as a result of picking a
refernce from a cbo? i.e. I pick a reference from a cbo and it displays
the x&y of the start point and the x&y of the endpoint in 4 separate
text boxes. I also have an unbound txt box which calculates the distance
bewteen the two coordinates and this wont display to 3dp either
irrespective of the format used.

Andy


"Douglas J. Steele" wrote in message
...
In the absence of specific formatting in the text box, I would have
expected it to display the same as what's defined for the field in the
table. However, on testing just now, I see that isn't the case.

What happened when you used #.000 as the Format property for the text
box? I just tried it, and it worked fine for me.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks yet again Doug - Genius,

I'd actually tried this but didn't know to use the # as a wildcard.
The only problem with this is that it doesn't work on the form. In
the table if I use #.000 it works fine. I would expect the data to
be displayed identically on the form but it isn't, so I used the #.000
for the format of the text box but it doesn't work.

Does the form not just display what the table tells it?

Andy


Regards Andy Andy Roberts BSc (Hons) Senior Consultant
www.blue-bean.co.uk M +44 (0)7970 255 156 T +44
(0)151 428 6838 240 Mackets Lane Woolton Liverpool L25 9NH United
Kingdom
"Douglas J. Steele" wrote in
message ...
Set the Format property to #.000


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug

Saved the day again - your approach seems so logical now - I'll try
and work these things out with that outlook.

Managed to get the co-ordinates in as decimals by setting the field
type to Double. However, the number of decimal places differs
depending on the trailing zeros e.g.456.700 shows as 456.7 and
456.760 shows as 456.76.

I've tried setting the dec places to 3 but doesn't make any
difference. Not too worried how they are stored in the tables, but
the form displaying the values should be to 3 decimal places. I've
tried using the ROUND function as part of the control source on of
the textbox on the form, but this doesn't work. Am I on the right
path?

Andy


"Douglas J. Steele" wrote in
message ...
As long as your import specification (the 2nd argument in your
TransferText: "import" in your example) is correct, the import
should work fine.

Try importing manually (through File | Get External Data) and click
on the Advanced button when the wizard appears. Click on the Specs
button and select "import". Correct it as required, and click on
the Save As button to replace the old version.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Doug,

I've changed the structure of theDB tables a little and now wish
to import the same file (see attached) into a table which has more
field than which I'm importing.

I get an error now when I use the following code:

Private Sub Command34_Click()
Dim lngAfter As Long
Dim lngBefore As Long
Dim strFilter As String
Dim strInputFileName As String

strFilter = ahtAddFilterItem(strFilter, "Text Files (*.TXT)",
"*.TXT")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
lngBefore = DCount("*", "tblSections")

DoCmd.TransferText acImportDelim, "import", "tblSections",
strInputFileName, False

lngAfter = DCount("*", "tblSections")

MsgBox "You imported " & (lngAfter - lngBefore) & " sections."
End Sub

Is this because the number of fields are different. The txtfile
has the first 5 fields which correspond to the first 5 in the
table. However the table has a further 7 fields which can't come
from the import. What I want to do is import the 5 fields into the
table and then add the remaining fields manually.

Is this possible.

(The error I get is Runtime Error 3001, Invalid Argument)

Regards Andy Andy Roberts BSc (Hons) Senior Consultant
www.blue-bean.co.uk M +44 (0)7970 255 156 T
+44 (0)151 428 6838 240 Mackets Lane Woolton Liverpool L25 9NH
United Kingdom
"Andy Roberts" wrote in message
...
Thanks yet again Doug,

Thats sorted that problem.

However... then numbers are coming in as whole numbers (missing
the 4 decimal places) for each value.

Each field is set as a Long Integer and the decimal places are
set to 4, but it doesn't seem to work, even if I change the type
to decimal.

Andy


"Douglas J. Steele" wrote in
message ...
"acImportFixed" says that the columns are a fixed width. You
want "acImportDelim"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug

That works a treat - well nearly!

Everything works, except the resulting data is different from
that
cointained in the incoming file. The data coming in is as
follows (they are
co-ordinates):-

0001,652.8655,349.5133,689.3182,468.6107
0002,822.5105,468.6107,861.7672,336.9030
0003,1020.1960,336.9030,1051.0406,505.0405
0004,1154.7904,505.0405,1330.0435,356.5190
0005,1531.9351,440.5878,1426.7832,573.6967
0006,1505.2966,743.2354,1799.7218,419.5706
0007,1913.2858,619.9345,1530.5330,928.1867
0008,1913.2858,619.9345,1530.5330,928.1867

It is REF, StartX, StartY, End X, EndY and saved in a txt file

On import an autonumber is assigned as a unique reference. No
problem,
however when I import the file the message I receive is correct
with the
number of entries, but the table looks like this:-

http://www.blue-bean.co.uk/images/screen.jpg

The numbers aren't formatted correctly - I thinks it must have
something to
do with number of decimal places. Any clues? It also imports
the first
comma (but the field type is text in this case)



"Douglas J. Steele" wrote
in message
...
Copy all of the shaded code (what's between Code Start and
Code End), and
paste it into a new module. When you save the module, name it
something
like "mdlFileDialog" (the name of the module isn't really
critical, other
than the fact that you cannot name the module the same as any
of the
functions or subs within it)

Rather than simply having:

DoCmd.TransferText acImportFixed, "importcoords",
"tblSecCoords",
"C:\import.txt", False

you'll want something like:

Dim lngAfter As Long
Dim lngBefore As Long
Dim strFilter As String
Dim strInputFileName as string

strFilter = ahtAddFilterItem(strFilter, "Text Files (*.TXT)",
"*.TXT")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...",
_
Flags:=ahtOFN_HIDEREADONLY)
lngBefore = DCount("*", "tblSecCoords")

DoCmd.TransferText acImportFixed, "importcoords", _
strInputFIleName, "C:\import.txt", False

lngAfter = DCount("*", "tblSecCoords")

Msgbox "You imported " & (lngAfter - lngBefore) & " records."


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
Thanks Doug for the quick response. I'm not really that up
with VBA. I
kind of understand the process.

I presume I need to place the code on the click event of the
button?
Where does the import code go?
As for number 2 you have certainly lost me there!

Andy

"Douglas J. Steele" wrote
in message
...
For 1, see http://www.mvps.org/access/api/api0001.htm at
"The Access
Web"

For 2, use DCount("*", "tblSecCoords") before and after the
import.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Andy Roberts" wrote in message
...
I want to import data regularly (the import format will
always be the
same) and there are 2 things I wish to do as part of the
process:-

First I want to hit an "Import button" on a form which then
displays
the standard browse dialogue to allow the user to select
the file.
Secondly I want the system to display a message of
confirmation with
the number of records imported displayed in the message.

I have got it to work using an import specification (on the
click event
of the command button) :

DoCmd.TransferText acImportFixed, "importcoords",
"tblSecCoords",
"C:\import.txt", False

which works fine except for the two issues raised above.
Can anyone
help?

Andy