Re: Append import data to existing record

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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!!!
--
Bonnie


"John Nurick" wrote:

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

Relevant Pages

  • Re: Is it possible to link two text boxes...
    ... The only thing I can think of would be to divide the text in the query. ... SELECT MyTextField AS Field1, MyTextField AS Field2 ... question is that I have two images on the page and one is justified left ...
    (microsoft.public.access.reports)
  • Re: Group By, Max and Min
    ... SELECT field1, field2, field3, LAST, MIN, MAX ... I have (from a previous saved query) these fields: ...
    (microsoft.public.access.queries)
  • Re: query or script
    ... I want to select the whole record if the value in field1 is ... selects only the two important fields, with the distinct property ... Convert that into an Append query, ... If you need one value from field2, we'll need to know how to ...
    (comp.databases.ms-access)
  • Re: Create Weekly and Monthly Flag in database/table
    ... WHERE field1 In; ... the weekday(datefield [,optional first day of week)) returns a ... In the query designer if you put your field2, ... small table with the holidays that fall on a friday. ...
    (comp.databases.ms-access)
  • Re: Suggestions Please - Subform Results Export to Excel
    ... --the name of the subform control ... --the query or table that is the record source of the main form ... now a parameter box appears requesting the ProjectID to base the query. ... Dim strSQL As String ...
    (microsoft.public.access.formscoding)