Append import data to existing record

[Bonnie]

Hi there everyone! Using A02 on XP. Not a programmer but love learning all I
can.

I have a MainForm with contract info/address/names/etc. Subform links on
field [PlanNum]. A unique field [Key] is created on BeforeInsert event on
the subform which is four digit contract number [PlanNum] plus the four digit
year from another field [PlanYearEnd] (example: 26422005) will allow me to
create a record each year for each contract and use [Key] to link to my
report.

Rather then type in all the financial data in the subform, I would like to
import a .txt file using Import Specifications that would populate some
fields in the table that is my control source on the subform.

Would like to be sure I approach this correctly rather than flounder around
for the best way to do it.

My import .txt file does not contain an identifier (no [PlanNum] field). The
..txt file is generated by the user from a government forms software. It
contains financial data only.

Is it possible to have a button on my form that will import the .txt file
data into the open record? If not, how can I tell the import macro which
record to append to? By append, I mean there will be an existing record and
the data needs to populate about 8-10 of the 25 fields and many of those
other fields will contain data relating to the imported financial data.

I'm pretty sure append will only add a new record rather than populate
fields in an existing record so I'm off on this already. That being the case,
should I append in the data to a new record, adding [PlanNum], then open the
main form, locate the newly appended record and edit further from there? That
sounds more like what I think I need to do.

Thanks in advance for any help or advice on this issue!!!

 

[John Nurick]

Hi Bonnie,

I'm not sure I understand the situation. If I've got the right end of
the stick, you have:

1. one table of Contracts (one record per contract) with a primary key
[PlanNum].
2. a related table of year-by-year information about the contracts,
joined to the Contracts table on [PlanNum] and with one record per
contract per year
3. a text file that contains year by year financial information about a
single contract (one record per year). The text file doesn't contain the
contract's PlanNum, but does have a field that shows which year each
record refers to.
4. a form bound to the Contracts table, with a subform bound to the
related table, linked on [PlanNum]
5. the related table already contains (and the subform displays) records
relating to the contract being displayed on the main form. You want to
update some fields in these records with data from the text file.

If that's the situation, things would be fairly simple except for the
[Key] field.

Question 1: Can you replace this with a field that just stores the year?
Then, the primary key of the related table would include the two fields
[PlanNum] and [Year], and a fairly simple update query would do the
trick. Any time you need to display the eight digit "Key", you can do it
by using
[PlanNum] & [TheYear]
or
Format([PlanNum],"0000") & Format([TheYear],"0000")

Question 2: What sort of text file is it? (CSV, tab-delimited, fixed
width...)

Hi there everyone! Using A02 on XP. Not a programmer but love learning all I
can.

I have a MainForm with contract info/address/names/etc. Subform links on
field [PlanNum]. A unique field [Key] is created on BeforeInsert event on
the subform which is four digit contract number [PlanNum] plus the four digit
year from another field [PlanYearEnd] (example: 26422005) will allow me to
create a record each year for each contract and use [Key] to link to my
report.

Rather then type in all the financial data in the subform, I would like to
import a .txt file using Import Specifications that would populate some
fields in the table that is my control source on the subform.

Would like to be sure I approach this correctly rather than flounder around
for the best way to do it.

My import .txt file does not contain an identifier (no [PlanNum] field). The
.txt file is generated by the user from a government forms software. It
contains financial data only.

Is it possible to have a button on my form that will import the .txt file
data into the open record? If not, how can I tell the import macro which
record to append to? By append, I mean there will be an existing record and
the data needs to populate about 8-10 of the 25 fields and many of those
other fields will contain data relating to the imported financial data.

I'm pretty sure append will only add a new record rather than populate
fields in an existing record so I'm off on this already. That being the case,
should I append in the data to a new record, adding [PlanNum], then open the
main form, locate the newly appended record and edit further from there? That
sounds more like what I think I need to do.

Thanks in advance for any help or advice on this issue!!!

 

[Bonnie]

Hi John,

Thanks for the reply. I actually understood most of it. Thank you for
writing with explanations.

I will rethink the [Key] identifier along the lines you suggest. It makes
better sense to use a double primary key.

Update query seems to be the way to go. However, the financial data I want
to import has NO linking field. It's just dollar fields. Also, there's just
one year (the current year) of data to import. Can I import the data into an
open record as an update query? In other words, can I/how do I tell it which
record to update? Can the record be open? Am I better off importing to a
temporary table and putting it on my subform in a subsubform, then, if a
button is clicked to indicate approval of the data, run a SetValue macro to
update the import data into my open record?

Thanks again for such a prompt, helpful response. Hope to hear from you one
more time on this...

--
Bonnie

Hi Bonnie,

I'm not sure I understand the situation. If I've got the right end of
the stick, you have:

1. one table of Contracts (one record per contract) with a primary key
[PlanNum].
2. a related table of year-by-year information about the contracts,
joined to the Contracts table on [PlanNum] and with one record per
contract per year
3. a text file that contains year by year financial information about a
single contract (one record per year). The text file doesn't contain the
contract's PlanNum, but does have a field that shows which year each
record refers to.
4. a form bound to the Contracts table, with a subform bound to the
related table, linked on [PlanNum]
5. the related table already contains (and the subform displays) records
relating to the contract being displayed on the main form. You want to
update some fields in these records with data from the text file.

If that's the situation, things would be fairly simple except for the
[Key] field.

Question 1: Can you replace this with a field that just stores the year?
Then, the primary key of the related table would include the two fields
[PlanNum] and [Year], and a fairly simple update query would do the
trick. Any time you need to display the eight digit "Key", you can do it
by using
[PlanNum] & [TheYear]
or
Format([PlanNum],"0000") & Format([TheYear],"0000")

Question 2: What sort of text file is it? (CSV, tab-delimited, fixed
width...)

Hi there everyone! Using A02 on XP. Not a programmer but love learning all I
can.

I have a MainForm with contract info/address/names/etc. Subform links on
field [PlanNum]. A unique field [Key] is created on BeforeInsert event on
the subform which is four digit contract number [PlanNum] plus the four digit
year from another field [PlanYearEnd] (example: 26422005) will allow me to
create a record each year for each contract and use [Key] to link to my
report.

Rather then type in all the financial data in the subform, I would like to
import a .txt file using Import Specifications that would populate some
fields in the table that is my control source on the subform.

Would like to be sure I approach this correctly rather than flounder around
for the best way to do it.

My import .txt file does not contain an identifier (no [PlanNum] field). The
.txt file is generated by the user from a government forms software. It
contains financial data only.

Is it possible to have a button on my form that will import the .txt file
data into the open record? If not, how can I tell the import macro which
record to append to? By append, I mean there will be an existing record and
the data needs to populate about 8-10 of the 25 fields and many of those
other fields will contain data relating to the imported financial data.

I'm pretty sure append will only add a new record rather than populate
fields in an existing record so I'm off on this already. That being the case,
should I append in the data to a new record, adding [PlanNum], then open the
main form, locate the newly appended record and edit further from there? That
sounds more like what I think I need to do.

Thanks in advance for any help or advice on this issue!!!

 

[John Nurick]

Hi Bonnie,

It sounds as if the text file is just one row of data, e.g.

12500.00, 330.36, 12.99, 4543200.00

Is that right? If so, you can work along these lines for the update
query. Make certain that you have a backup copy of the database in case
anything goes wrong.

First, open a new query. Don't select any table, but switch to SQL view,
then type in something like this, adjusting for your actual folder,
filename and fields. The default field names are F1, F2... so you need
to alias them to your actual field names as shown. Note how the folder,
filename and the .txt extension have to be split up.

SELECT
F1 AS FirstDollarField,
F2 AS SecondDollarField,
F3 AS Third
FROM [Text;HDR=No;Database=C:\MyFolder\;].MyFile#txt
;

When this is working properly save it (let's call it qryFromTxt). Then
add two calculated fields to get the year and PlanNum from the current
record on the subform. They will look like this, although again you need
to substitute the actual names of the form and controls:

SELECT
Forms!MainForm!SubformControl!PlanNum AS PlanNum,
Forms!MainForm!SubformControl!TheYear AS TheYear,
F1 AS FirstDollarField,
F2 AS SecondDollarField,
F3 AS Third
FROM [Text;HDR=No;Database=C:\MyFolder\;].MyFile#txt
;

Obviously this will only work if when the form is open. When it's
working properly, save the query again. (NB: I called the "year" field
TheYear to avoid confusion with the VBA Year() function; for the same
reason you should avoid giving fields names like Name, Date, etc.)

Then create another query that uses as its source (a) the table to which
the subform is bound and (b) qryFromTxt. Join them on both the PlanNum
and TheYear fields, and turn it into an Update query that updates the
fields you need in the subform's table.

After this, it will take a bit of VBA to let the user select the file to
be imported. Post back here when you're ready.

Hi John,

Thanks for the reply. I actually understood most of it. Thank you for
writing with explanations.

I will rethink the [Key] identifier along the lines you suggest. It makes
better sense to use a double primary key.

Update query seems to be the way to go. However, the financial data I want
to import has NO linking field. It's just dollar fields. Also, there's just
one year (the current year) of data to import. Can I import the data into an
open record as an update query? In other words, can I/how do I tell it which
record to update? Can the record be open? Am I better off importing to a
temporary table and putting it on my subform in a subsubform, then, if a
button is clicked to indicate approval of the data, run a SetValue macro to
update the import data into my open record?

Thanks again for such a prompt, helpful response. Hope to hear from you one
more time on this...

 

[Bonnie]

Good morning John!

Thanks very much for taking me through this project. I'm struggling a little
with the wordage but it makes sense so I plan to give it a go later this
morning. Just wanted to verify one item: I do have a header row. Do I just
change HDR=No; to HDR=Yes; and continue with your instructions? Or will that
affect the F1, F2, etc? Do I then use the column header name as my field name
in the data file? There is just one row of data under the header row.

Thanks BUNCHES!

--
Bonnie

Hi Bonnie,

It sounds as if the text file is just one row of data, e.g.

12500.00, 330.36, 12.99, 4543200.00

Is that right? If so, you can work along these lines for the update
query. Make certain that you have a backup copy of the database in case
anything goes wrong.

First, open a new query. Don't select any table, but switch to SQL view,
then type in something like this, adjusting for your actual folder,
filename and fields. The default field names are F1, F2... so you need
to alias them to your actual field names as shown. Note how the folder,
filename and the .txt extension have to be split up.

SELECT
F1 AS FirstDollarField,
F2 AS SecondDollarField,
F3 AS Third
FROM [Text;HDR=No;Database=C:\MyFolder\;].MyFile#txt
;

When this is working properly save it (let's call it qryFromTxt). Then
add two calculated fields to get the year and PlanNum from the current
record on the subform. They will look like this, although again you need
to substitute the actual names of the form and controls:

SELECT
Forms!MainForm!SubformControl!PlanNum AS PlanNum,
Forms!MainForm!SubformControl!TheYear AS TheYear,
F1 AS FirstDollarField,
F2 AS SecondDollarField,
F3 AS Third
FROM [Text;HDR=No;Database=C:\MyFolder\;].MyFile#txt
;

Obviously this will only work if when the form is open. When it's
working properly, save the query again. (NB: I called the "year" field
TheYear to avoid confusion with the VBA Year() function; for the same
reason you should avoid giving fields names like Name, Date, etc.)

Then create another query that uses as its source (a) the table to which
the subform is bound and (b) qryFromTxt. Join them on both the PlanNum
and TheYear fields, and turn it into an Update query that updates the
fields you need in the subform's table.

After this, it will take a bit of VBA to let the user select the file to
be imported. Post back here when you're ready.

Hi John,

Thanks for the reply. I actually understood most of it. Thank you for
writing with explanations.

I will rethink the [Key] identifier along the lines you suggest. It makes
better sense to use a double primary key.

Update query seems to be the way to go. However, the financial data I want
to import has NO linking field. It's just dollar fields. Also, there's just
one year (the current year) of data to import. Can I import the data into an
open record as an update query? In other words, can I/how do I tell it which
record to update? Can the record be open? Am I better off importing to a
temporary table and putting it on my subform in a subsubform, then, if a
button is clicked to indicate approval of the data, run a SetValue macro to
update the import data into my open record?

Thanks again for such a prompt, helpful response. Hope to hear from you one
more time on this...

 

[John Nurick]

Use HDR=Yes, and then instead of using
F1 AS FieldName,
F2 AS OtherFieldName
etc., just list the exact field names from the header row in the file, e.g.
FirstField,
SecondField,
etc.

Good morning John!

Thanks very much for taking me through this project. I'm struggling a
little
with the wordage but it makes sense so I plan to give it a go later this
morning. Just wanted to verify one item: I do have a header row. Do I just
change HDR=No; to HDR=Yes; and continue with your instructions? Or will
that
affect the F1, F2, etc? Do I then use the column header name as my field
name
in the data file? There is just one row of data under the header row.

Thanks BUNCHES!

--
Bonnie

Hi Bonnie,

It sounds as if the text file is just one row of data, e.g.

12500.00, 330.36, 12.99, 4543200.00

Is that right? If so, you can work along these lines for the update
query. Make certain that you have a backup copy of the database in case
anything goes wrong.

First, open a new query. Don't select any table, but switch to SQL view,
then type in something like this, adjusting for your actual folder,
filename and fields. The default field names are F1, F2... so you need
to alias them to your actual field names as shown. Note how the folder,
filename and the .txt extension have to be split up.

SELECT
F1 AS FirstDollarField,
F2 AS SecondDollarField,
F3 AS Third
FROM [Text;HDR=No;Database=C:\MyFolder\;].MyFile#txt
;

When this is working properly save it (let's call it qryFromTxt). Then
add two calculated fields to get the year and PlanNum from the current
record on the subform. They will look like this, although again you need
to substitute the actual names of the form and controls:

SELECT
Forms!MainForm!SubformControl!PlanNum AS PlanNum,
Forms!MainForm!SubformControl!TheYear AS TheYear,
F1 AS FirstDollarField,
F2 AS SecondDollarField,
F3 AS Third
FROM [Text;HDR=No;Database=C:\MyFolder\;].MyFile#txt
;

Obviously this will only work if when the form is open. When it's
working properly, save the query again. (NB: I called the "year" field
TheYear to avoid confusion with the VBA Year() function; for the same
reason you should avoid giving fields names like Name, Date, etc.)

Then create another query that uses as its source (a) the table to which
the subform is bound and (b) qryFromTxt. Join them on both the PlanNum
and TheYear fields, and turn it into an Update query that updates the
fields you need in the subform's table.

After this, it will take a bit of VBA to let the user select the file to
be imported. Post back here when you're ready.

Hi John,

Thanks for the reply. I actually understood most of it. Thank you for
writing with explanations.

I will rethink the [Key] identifier along the lines you suggest. It
makes
better sense to use a double primary key.

Update query seems to be the way to go. However, the financial data I
want
to import has NO linking field. It's just dollar fields. Also, there's
just
one year (the current year) of data to import. Can I import the data
into an
open record as an update query? In other words, can I/how do I tell it
which
record to update? Can the record be open? Am I better off importing to
a
temporary table and putting it on my subform in a subsubform, then, if a
button is clicked to indicate approval of the data, run a SetValue macro
to
update the import data into my open record?

Thanks again for such a prompt, helpful response. Hope to hear from you
one
more time on this...

 

[Bonnie]

Hi John!

I finally had time to work on this again. I have done the following:

1. Created a field [PlanYear] that gets populated with the 4 digit year from
a date field [PYE] and set [PlanYear] and [PlanNum] both as primary keys.

2. Created the first query (named it qSARFromTxtI)(was so excited that it
works!):

SELECT Forms!fSARMain!fSAR!PlanNum AS PlanNum, Forms!fSARMain!fSAR!PlanYear
AS PlanYear, BEGINNING, ENDING, ERCONTR, PARTCONTR, OTHERCNTR, NONCASH,
OTHERINCOME, TOTALINC, BENEFITS, CORRECTVE, DEEMED, OTHEREXP, TOTALEXP,
NETINCOME, NETTRNSFR
FROM [TEXT;HDR=YES;DATABASE=S:\rps\PTS\SARData\].SAR_I.TXT;

3. Created the update query, added the 2 queries and joined on both
[PlanNum] and [PlanYear]. However, I don't understand how to get the update
to work. Do I put the field from my subform query (qSARData) into the grid
and the qSARFromTxtI field into the update line? Not sure...

Question: You mentioned "After this, it will take a bit of VBA to let the
user select the file to be imported." Haven't I already named the file with
it's path in item 2 above? I would love to not have to be rigid about how the
users must name the files. Or do you refer to the file location? Not sure...

I really appreciate your help with this, I am really learning some new stuff
here!
--
Bonnie

Use HDR=Yes, and then instead of using
F1 AS FieldName,
F2 AS OtherFieldName
etc., just list the exact field names from the header row in the file, e.g.
FirstField,
SecondField,
etc.

Good morning John!

Thanks very much for taking me through this project. I'm struggling a
little
with the wordage but it makes sense so I plan to give it a go later this
morning. Just wanted to verify one item: I do have a header row. Do I just
change HDR=No; to HDR=Yes; and continue with your instructions? Or will
that
affect the F1, F2, etc? Do I then use the column header name as my field
name
in the data file? There is just one row of data under the header row.

Thanks BUNCHES!

--
Bonnie

Hi Bonnie,

It sounds as if the text file is just one row of data, e.g.

12500.00, 330.36, 12.99, 4543200.00

Is that right? If so, you can work along these lines for the update
query. Make certain that you have a backup copy of the database in case
anything goes wrong.

First, open a new query. Don't select any table, but switch to SQL view,
then type in something like this, adjusting for your actual folder,
filename and fields. The default field names are F1, F2... so you need
to alias them to your actual field names as shown. Note how the folder,
filename and the .txt extension have to be split up.

SELECT
F1 AS FirstDollarField,
F2 AS SecondDollarField,
F3 AS Third
FROM [Text;HDR=No;Database=C:\MyFolder\;].MyFile#txt
;

When this is working properly save it (let's call it qryFromTxt). Then
add two calculated fields to get the year and PlanNum from the current
record on the subform. They will look like this, although again you need
to substitute the actual names of the form and controls:

SELECT
Forms!MainForm!SubformControl!PlanNum AS PlanNum,
Forms!MainForm!SubformControl!TheYear AS TheYear,
F1 AS FirstDollarField,
F2 AS SecondDollarField,
F3 AS Third
FROM [Text;HDR=No;Database=C:\MyFolder\;].MyFile#txt
;

Obviously this will only work if when the form is open. When it's
working properly, save the query again. (NB: I called the "year" field
TheYear to avoid confusion with the VBA Year() function; for the same
reason you should avoid giving fields names like Name, Date, etc.)

Then create another query that uses as its source (a) the table to which
the subform is bound and (b) qryFromTxt. Join them on both the PlanNum
and TheYear fields, and turn it into an Update query that updates the
fields you need in the subform's table.

After this, it will take a bit of VBA to let the user select the file to
be imported. Post back here when you're ready.



On Wed, 11 May 2005 12:31:09 -0700, Bonnie

Hi John,

Thanks for the reply. I actually understood most of it. Thank you for
writing with explanations.

I will rethink the [Key] identifier along the lines you suggest. It
makes
better sense to use a double primary key.

Update query seems to be the way to go. However, the financial data I
want
to import has NO linking field. It's just dollar fields. Also, there's
just
one year (the current year) of data to import. Can I import the data
into an
open record as an update query? In other words, can I/how do I tell it
which
record to update? Can the record be open? Am I better off importing to
a
temporary table and putting it on my subform in a subsubform, then, if a
button is clicked to indicate approval of the data, run a SetValue macro
to
update the import data into my open record?

Thanks again for such a prompt, helpful response. Hope to hear from you
one
more time on this...

 

[John Nurick]

Hi Bonnie,

I'm glad you've got the key query working.

With the update query open in Design View:

- in the "Fields" row, select the fields from qSARData that you want to
update with data from qSARFromTxtI (i.e. with data from the text file).
Don't select any other fields. Each occupied cell in the "Table" row
will show "qSARData".

- in the "Update To" row, type the corresponding fields from
qSARFromTextI, like this:
[qSARFromTextI].[PlanYear]


As for your final question: yes, you specified the file name in step 2,
but it's hard-coded into the SQL statement and it's not very easy to
alter so as to import another file.

One possibility is just to tell the users that when they want to import
a file they should copy it to a particular folder and give it a standard
name. But if you want to let the user specify which file to import
you'll have to write VBA code. Typically one would

1) Pop up the standard File Open dialog (there's code to do this at
www.mvps.org/access/api/api0001.htm) and get the filespec (the path and
name of the file).

2) Split the filespec apart and reassemble it into a
[TEXT;HDR=YES;DATABASE...]
clause. I've pasted a function to do this at the end of this message.

3) Assemble this into a SQL SELECT statement that's the same as
qSARFromTxtI except for its source, and modify qSARFromTextI
accordingly.

4) Finally, execute the update query.

Hi John!

I finally had time to work on this again. I have done the following:

1. Created a field [PlanYear] that gets populated with the 4 digit year from
a date field [PYE] and set [PlanYear] and [PlanNum] both as primary keys.

2. Created the first query (named it qSARFromTxtI)(was so excited that it
works!):

SELECT Forms!fSARMain!fSAR!PlanNum AS PlanNum, Forms!fSARMain!fSAR!PlanYear
AS PlanYear, BEGINNING, ENDING, ERCONTR, PARTCONTR, OTHERCNTR, NONCASH,
OTHERINCOME, TOTALINC, BENEFITS, CORRECTVE, DEEMED, OTHEREXP, TOTALEXP,
NETINCOME, NETTRNSFR
FROM [TEXT;HDR=YES;DATABASE=S:\rps\PTS\SARData\].SAR_I.TXT;

3. Created the update query, added the 2 queries and joined on both
[PlanNum] and [PlanYear]. However, I don't understand how to get the update
to work. Do I put the field from my subform query (qSARData) into the grid
and the qSARFromTxtI field into the update line? Not sure...

Question: You mentioned "After this, it will take a bit of VBA to let the
user select the file to be imported." Haven't I already named the file with
it's path in item 2 above? I would love to not have to be rigid about how the
users must name the files. Or do you refer to the file location? Not sure...

I really appreciate your help with this, I am really learning some new stuff
here!

Function BuildJetTextSource(ByVal FileSpec As String, ByVal HDR As
Boolean) As String
'Takes a filespec and returns a string that allows that file to
'be used as a Text data source in a Jet SQL query.
' E.g.
' C:\My Folder\MyFile.txt
' returns
' [Text;HDR=No;Database=C:\My Folder\;].[MyFile#txt]
' The HDR argument controls the HDR parameter in string returned.
'
' Returns empty string if file not found.
' By John Nurick 2005

Dim fso As Object 'FileSystemObject
Dim strFolder As String
Dim strFileName As String
Dim strFileExt As String
Dim strTemp As String

If Len(Dir(FileSpec)) = 0 Then
'File not found
strTemp = ""

Else
'Parse FileSpec
Set fso = CreateObject("Scripting.FileSystemObject")
With fso
FileSpec = .GetAbsolutePathName(FileSpec)
strFolder = .GetParentFolderName(FileSpec)
strFileName = .GetBaseName(FileSpec)
strFileExt = .GetExtensionName(FileSpec)
End With
Set fso = Nothing

'Build string
strTemp = "[Text;HDR=" _
& IIf(HDR, "Yes", "No") _
& ";Database=" _
& strFolder & "\;].[" _
& strFileName & "#" _
& strFileExt & "]"
End If

BuildJetTextSource = strTemp
End Function

 

[Bonnie]

Hello John!

I've populated the update query but when I run it, after the "you are about
the run an update....", and "you can't change data after you run this..., I
get an error: "Updating data in a linked table is not supported by this
ISAM." Looked in Access help but found nothing. ISAM rings a bell but
cannot recall and Help is no help on that topic.

Is that an add-in or a VB reference? I can have systems fix me up but need
to know what I'm asking for.

Thanks SO VERY MUCH for your help on this.

Bonnie

 

[John Nurick]

Bonnie,

I;m sorry. I've been carried away by enthusiasm and forgot one of the
little gotchas of the the Jet database engine used by Access. For some
reason it gets confused when it's asked to update one (updatable) table
or query with values from a non-updatable table or query - even though
you're not trying to change the latter at all.

Unfortunately, text files fall into the "non-updatable" category. ISAM,
by the way, stands for Indexed Sequential Access Module, which doesn't
mean much to me.

The official work-round, at
http://support.microsoft.com/default.aspx?scid=kb;en-us;824159 is to
import the text file into an Access table, and then to use that table in
the update query. (You can convert your qSARFromTxtI into an append
query to do the importing smoothly.)

An alternative - probably neater given that there's only the one record
in the text file - is to write VBA code that uses recordset operations
to read the data from the text file and update the subform table. The
sequence would be something like this:

'Open a recordset on qSARFromTxtI
Dim rstR As DAO.Recordset
Set rstR = CurrentDB.OpenRecordset("qSARFromTxtI")

'Update the values in the subform
Forms("MyForm").MySubForm.Form.Field1.Value = _
rstR.Field1.Value
Forms("MyForm").MySubForm.Form.Field2.Value = _
rstR.Field2.Value

rstR.Close
forms(0).frmsub1.Form.ItemName.Value

 

[Bonnie]

Hi John,

AARRRGGGGHHHHH! Import to a table was where I began this journey, but I
really liked the idea of linking to the text file. Rather than go back and
recreate the table and the import specs and the macro, I'd like to try your
code below. However, I need to verify which of the items below I need to
'customize' to my info. Such as:

Forms("MyForm").MySubForm.Form.Field1.Value = _rstR.Field1.Value

(I'm sure I need to exchange Field1 with my actual field name but what about
the ("MyForm").MySubForm.Form? Do I put my form names here anywhere? And, to
clarify, my access field name goes into Field1 for
MySubForm.Form.Field1.Value and the import text file field goes into Field1
for _rstR.Field1.Value, correct?)

forms(0).frmsub1.Form.ItemName.Value

(Do I make any changes to the above line?)

Also, where am I to put the code? Into an OnClick event procedure on a
button on my form?

Thank you again for staying with this thread, I am truly learning great
stuff on this project. I hope I'm not frustrating you or imposing too much
but getting walked through this is FANTASTIC!!!

 

[John Nurick]

Hi John,

AARRRGGGGHHHHH!
Sorry...

Import to a table was where I began this journey, but I
really liked the idea of linking to the text file. Rather than go back and
recreate the table and the import specs and the macro, I'd like to try your
code below. However, I need to verify which of the items below I need to
'customize' to my info. Such as:

Forms("MyForm").MySubForm.Form.Field1.Value = _rstR.Field1.Value

(I'm sure I need to exchange Field1 with my actual field name but what about
the ("MyForm").MySubForm.Form? Do I put my form names here anywhere?

Replace MyForm with the name of your form, and MySubForm with the name
of the subform control on the main form (this is usually but not
necessarily the same as the name of the subform).

And, to
clarify, my access field name goes into Field1 for
MySubForm.Form.Field1.Value and the import text file field goes into Field1
for _rstR.Field1.Value, correct?)

That's right. You need to repeat this
Forms("MyForm").MySubForm.Form.Field1.Value = _
rstR.Field1.Value
for each field you're updating, using the appropriate names.

forms(0).frmsub1.Form.ItemName.Value

(Do I make any changes to the above line?)

Also, where am I to put the code? Into an OnClick event procedure on a
button on my form?

That's probably the best place. (If you're new to coding, check out how
you can set breakpoints in the code by clicking in the grey left margin
of the code window; also check out the Debug toolbar that lets you step
through code a line at a time so you can see what's going on.)

One thing: I've been assuming that we're updating the current record in
the subform with data from the text file. If that's not right, say so
because the coding gets a little more complicated.

Thank you again for staying with this thread, I am truly learning great
stuff on this project. I hope I'm not frustrating you or imposing too much
but getting walked through this is FANTASTIC!!!

I'm glad you're finding it helpful (and embarrased about the detour<g>)

 

[Bonnie]

So happy to hear from you so quickly again! Thanks tons.

First, Yes, we are updating the current record on the subform.

I have the following code but get an error on Compile that highlights the
word BEGINNING. Error reads "Compile Error: Method or Data Member Not Found".

Private Sub SARIn_Click()
'Open a recordset on qSARFromTxtI
Dim rstR As DAO.Recordset
Set rstR = CurrentDb.OpenRecordset("qSARFromTxtI")

'Update the values in the subform
Forms("fSARMain").Forms.[fSAR].[BeginningAssets] = _
rstR.BEGINNING.Value
Forms("fSARMain").Forms.[fSAR].[BeginningAssets] = _
rstR.ENDING.Value

rstR.close

End Sub

Also want to clarify that I am using the select query (qSARFromTxtI) above,
not the update query (qSARUpdateI), right?

 

[John Nurick]

That's me trying to reply in a hurry.

Forms("fSARMain").Forms.[fSAR].[BeginningAssets] = _
rstR.Fields("BEGINNING").Value

So happy to hear from you so quickly again! Thanks tons.

First, Yes, we are updating the current record on the subform.

I have the following code but get an error on Compile that highlights the
word BEGINNING. Error reads "Compile Error: Method or Data Member Not Found".

Private Sub SARIn_Click()
'Open a recordset on qSARFromTxtI
Dim rstR As DAO.Recordset
Set rstR = CurrentDb.OpenRecordset("qSARFromTxtI")

'Update the values in the subform
Forms("fSARMain").Forms.[fSAR].[BeginningAssets] = _
rstR.BEGINNING.Value
Forms("fSARMain").Forms.[fSAR].[BeginningAssets] = _
rstR.ENDING.Value

rstR.close

End Sub

Also want to clarify that I am using the select query (qSARFromTxtI) above,
not the update query (qSARUpdateI), right?

 

[Bonnie]

YAHOO! Compile is working on the first 2 fields! Have some keying in to do
this AM. (When I tried to test the query and got the too few parameters error
so I know I'm on the right track. As soon as I get ALL the fields listed,
I'll try again. If that works, I'm golden!

You're the greatest!!!
--
Bonnie

That's me trying to reply in a hurry.

Forms("fSARMain").Forms.[fSAR].[BeginningAssets] = _
rstR.Fields("BEGINNING").Value

 

[Bonnie]

Hi John! Ran into another question---

I also need to add 2 of the fields together to update one field on my
subform. Have 2 ways to go: add fields in my code or in the SQL. Which would
be better? Here is what I've tried so far---

1. Code:

Since I can't test anything on my subform until all fields are in, not sure
if it works the way I have it written below. It compiles okay but don't know
if the fields will add or just string together (I need 243 plus 567 equals
810 rather than equals 243567).

I have this:

Forms("fSARMain").Forms.[fSAR].[BeginningAssets] = _
rstR.Fields("BEGINNING").Value
Forms("fSARMain").Forms.[fSAR].[EndingAssets] = _
rstR.Fields("ENDING").Value
Forms("fSARMain").Forms.[fSAR].[BenefitsPaid] = _
rstR.Fields("BENEFITS").Value + rstR.Fields("DEEMED").Value

2. SQL

SELECT Forms!fSARMain!fSAR!PlanNum AS PlanNum, Forms!fSARMain!fSAR!PlanYear
AS PlanYear, BEGINNING, ENDING, BENEFITS+ DEEMED AS BenefitsPd, OTHEREXP,
TOTALEXP, NETINCOME, NETTRNSFR
FROM [TEXT;HDR=YES;DATABASE=S:\rps\PTS\SARData\].SAR_I.TXT;

When I run the SQL above the field [BenefitsPd] gives me 243567 rather than
810.

Can you tell me which way is best and how to make it work? When I sent my
first excited reply, I should have known that I'd come up with a monkey
wrench.

Waiting to hear back...and lovin' this!!! Thanks again.

--
Bonnie

That's me trying to reply in a hurry.

Forms("fSARMain").Forms.[fSAR].[BeginningAssets] = _
rstR.Fields("BEGINNING").Value

 

[Bonnie]

Sorry John, but I seem to be abusing you today.

Have removed the fields that I want to add to the others as referenced in my
prior post. So I just have 12 to update and the 2 that are 'pulling' the
PlanNum and PlanYear from the subform. Have tried to run the code with and
without PlanNum and PlanYear and get 2 different errors. See below:

SQL reads: SELECT Forms!fSARMain!fSAR!PlanNum AS PlanNum,
Forms!fSARMain!fSAR!PlanYear AS PlanYear, BEGINNING, ENDING, ERCONTR,
PARTCONTR, OTHERCNTR, OTHERINCOME, TOTALINC, BENEFITS, OTHEREXP, TOTALEXP,
NETINCOME, NETTRNSFR
FROM [TEXT;HDR=YES;DATABASE=S:\rps\PTS\SARData\].SAR_I.TXT;

Run-time error ‘3061’: Too few parameters. Expected 2.

DEBUG highlighted this row: Set rstR =
CurrentDb.OpenRecordset("qSARFromTxtI")

and I also tried;

SQL reads: SELECT BEGINNING, ENDING, ERCONTR, PARTCONTR, OTHERCNTR,
OTHERINCOME, TOTALINC, BENEFITS, OTHEREXP, TOTALEXP, NETINCOME, NETTRNSFR
FROM [TEXT;HDR=YES;DATABASE=S:\rps\PTS\SARData\].SAR_I.TXT;

Run-time error ‘2465’: Application-defined or object-defined error

DEBUG highlighted this line:
Forms("fSARMain").Forms.[fSAR].[BeginningAssets] = _
rstR.Fields("BEGINNING").Value

Sorry, but I feel like it's falling apart on me. Would appreciate your help
and aplogize for being such a prolonged agony.

--
Bonnie

That's me trying to reply in a hurry.

Forms("fSARMain").Forms.[fSAR].[BeginningAssets] = _
rstR.Fields("BEGINNING").Value

So happy to hear from you so quickly again! Thanks tons.

First, Yes, we are updating the current record on the subform.

I have the following code but get an error on Compile that highlights the
word BEGINNING. Error reads "Compile Error: Method or Data Member Not Found".

Private Sub SARIn_Click()
'Open a recordset on qSARFromTxtI
Dim rstR As DAO.Recordset
Set rstR = CurrentDb.OpenRecordset("qSARFromTxtI")

'Update the values in the subform
Forms("fSARMain").Forms.[fSAR].[BeginningAssets] = _
rstR.BEGINNING.Value
Forms("fSARMain").Forms.[fSAR].[BeginningAssets] = _
rstR.ENDING.Value

rstR.close

End Sub

Also want to clarify that I am using the select query (qSARFromTxtI) above,
not the update query (qSARUpdateI), right?

 

[John Nurick]

Hi John! Ran into another question---

I also need to add 2 of the fields together to update one field on my
subform. Have 2 ways to go: add fields in my code or in the SQL. Which would
be better? Here is what I've tried so far---

1. Code:

Since I can't test anything on my subform until all fields are in, not sure
if it works the way I have it written below. It compiles okay but don't know
if the fields will add or just string together (I need 243 plus 567 equals
810 rather than equals 243567).

I have this:

Forms("fSARMain").Forms.[fSAR].[BeginningAssets] = _
rstR.Fields("BEGINNING").Value
Forms("fSARMain").Forms.[fSAR].[EndingAssets] = _
rstR.Fields("ENDING").Value
Forms("fSARMain").Forms.[fSAR].[BenefitsPaid] = _
rstR.Fields("BENEFITS").Value + rstR.Fields("DEEMED").Value

This should work. If you get 234567 it means that the fields in the
recordset from the text file are being treated as text rather than
numbers, in which case convert them, e.g.
CLng(Nz(rstR.Fields("BENEFITS").Value),0) _
+ Clng(Nz(rstR.Fields("DEEMED").Value),0)

The Nz(blah, 0) handles any situations where the fields are empty by
converting the resulting Null value to 0. (Otherwise, attepmting to do
arithmetic with Nulls would raise an error.) Then the CLng( ) converts
the value into a Long (number). Use the conversion function that matches
the field type in your subform table (e.g. CLng() for Long, CDbl() for
Double, CCur() for Currency).

2. SQL

SELECT Forms!fSARMain!fSAR!PlanNum AS PlanNum, Forms!fSARMain!fSAR!PlanYear
AS PlanYear, BEGINNING, ENDING, BENEFITS+ DEEMED AS BenefitsPd, OTHEREXP,
TOTALEXP, NETINCOME, NETTRNSFR
FROM [TEXT;HDR=YES;DATABASE=S:\rps\PTS\SARData\].SAR_I.TXT;

When I run the SQL above the field [BenefitsPd] gives me 243567 rather than
810.

I can't remember whether the CLng(Nz(FieldName),0) technique will work
in that situation. Suck it and see.

 

[John Nurick]

Bonnie,

I think my brain is going. I keep missing little traps that I know
about. My only excuse is that I'm having a busy time in my day job and
by the time I get to the newsgroups it's late and I'm finding it hard to
concentrate. This time, I forgot that (for complicated but not quite
indefensible reasons) the
Forms!FormName!ControlName
syntax doesn't work in certain circumstances, including opening a
recordset in the way you do below.

I have to prepare for a business trip now and can't give this the
attention it deserves before the weekend. If you search at
http://groups.google.com for this:

"too few parameters" forms expected 3061
group:microsoft.public.access.*

you'll find plenty of postings describing the problem and the fixes.
Please let me know how you get on: sorry I can't be more help just now.

John

Sorry John, but I seem to be abusing you today.

Have removed the fields that I want to add to the others as referenced in my
prior post. So I just have 12 to update and the 2 that are 'pulling' the
PlanNum and PlanYear from the subform. Have tried to run the code with and
without PlanNum and PlanYear and get 2 different errors. See below:

SQL reads: SELECT Forms!fSARMain!fSAR!PlanNum AS PlanNum,
Forms!fSARMain!fSAR!PlanYear AS PlanYear, BEGINNING, ENDING, ERCONTR,
PARTCONTR, OTHERCNTR, OTHERINCOME, TOTALINC, BENEFITS, OTHEREXP, TOTALEXP,
NETINCOME, NETTRNSFR
FROM [TEXT;HDR=YES;DATABASE=S:\rps\PTS\SARData\].SAR_I.TXT;

Run-time error ‘3061’: Too few parameters. Expected 2.

DEBUG highlighted this row: Set rstR =
CurrentDb.OpenRecordset("qSARFromTxtI")

and I also tried;

SQL reads: SELECT BEGINNING, ENDING, ERCONTR, PARTCONTR, OTHERCNTR,
OTHERINCOME, TOTALINC, BENEFITS, OTHEREXP, TOTALEXP, NETINCOME, NETTRNSFR
FROM [TEXT;HDR=YES;DATABASE=S:\rps\PTS\SARData\].SAR_I.TXT;

Run-time error ‘2465’: Application-defined or object-defined error

DEBUG highlighted this line:
Forms("fSARMain").Forms.[fSAR].[BeginningAssets] = _
rstR.Fields("BEGINNING").Value

Sorry, but I feel like it's falling apart on me. Would appreciate your help
and aplogize for being such a prolonged agony.

 

[John Nurick]

Hi Bonnie,

How are you getting on?