Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    REFER NOT VALID (2000 sr 1)

    i have this it import a value from txt file and insert in the sheet... i have add a line to export the same variable into a table of acces but i recieve this msg: see image, why.?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: REFER NOT VALID (2000 sr 1)

    Above the line .AddNew, insert

    With ExportRecordset

    and after the last line starting with a point . insert

    End With

    Without those lines, VBA does not know what .AddNew, .Fields and .Update refer to.

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: REFER NOT VALID (2000 sr 1)

    Hans, sorry for 2 post with the same question...
    But i dont see .insert in my macro, take a look on the txt file, please.
    Tkls as usual.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: REFER NOT VALID (2000 sr 1)

    He is not telling you to find a line ".insert" he is telling you to insert a line

    Above the line (currently in your code)
    <pre>.AddNew</pre>


    insert the line:

    <pre>With ExportRecordset</pre>


    and after the last line starting with a point/period (.), then insert the line:

    <pre>End With</pre>


    Steve

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: REFER NOT VALID (2000 sr 1)

    is it correct:

    'ExportRecordSet.Find "SERVIZIO = " & Chr(34) & Range("S" & r).Value & Chr(34)
    If ExportRecordSet.EOF = True Then
    With ExportRecordSet
    .AddNew ' create a new record
    ..............


    .............
    .Fields("MT") = ELENCO.Range("R" & Trim(Str(CONT))).Value
    .Fields("SERVIZIO") = ELENCO.Range("S" & Trim(Str(CONT))).Value
    .Update ' stores the new record
    End With

    in effect the macro insert the value into sheet but not in the table....acccccccc.....

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: REFER NOT VALID (2000 sr 1)

    That code looks OK. If it does not do what you want, set a breakpoint and single step through the code to see what happens.

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: REFER NOT VALID (2000 sr 1)

    ARGGGGGGGGGGGGGGGG..........

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: REFER NOT VALID (2000 sr 1)

    ExportRecordset is a recordset on the TOTALE table. Are you sure that TOTALE contains a field named DATA_CONT?

  9. #9
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: REFER NOT VALID (2000 sr 1)

    See here...
    But if you have another way...

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: REFER NOT VALID (2000 sr 1)

    <P ID="edit" class=small>(Edited by sdckapr on 06-Dec-04 07:59. added PS)</P>Is the datatype in the excel range the same as the datatype of the access field? If not you might have to convert it before trying to enter it.

    Steve
    PS What is the value of "CONT" at this stage?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: REFER NOT VALID (2000 sr 1)

    Your code contains a line On Error Resume Next. This is intended to work with opening the connection to the database. But you don't restore normal error handling afterwards. This means that you'll never find out where things go wrong.

    Below the lines

    On Error Resume Next
    PROVADatabase.Open "Provider=..."
    If Err <> 0 Then
    MsgBox "NON TROVATO PROVA.MDB. PERCORSO ERRATO!"
    Exit Sub
    End If

    insert this line:

    On Error GoTo 0

    You will probably find that the error occurs much earlier.

  12. #12
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: REFER NOT VALID (2000 sr 1)

    Hans, i have insert all your suggestion, but always not work.
    In effect with the macro IMPORT_L0785 (attached) i import from txt file an insert a value in the sheet. all ok!
    After this operation i export from the sheet into table TOTALE (attached)
    I would want to insert the same value from every variable in the table TOTALE during the txt import file.
    The fileds and variable are the similar the same... is useless to start 2 macro when the first macro import and charge the variable
    in effect mixed the two macro in one, have a suggestion?

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: REFER NOT VALID (2000 sr 1)

    I will repeat what I already wrote above:
    - You must insert On Error GoTo 0 in the location I indicated.
    - You must single-step through the code to see where the error occurs.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: REFER NOT VALID (2000 sr 1)

    Which image?

  15. #15
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: REFER NOT VALID (2000 sr 1)

    Hans, all solved...
    One prob:

    .Fields("C_C") = var_NUMCC
    i take this value and insert in the table.
    i want the same format i have in the column of excel (Formated General) for example see the image
    without modify the format in access...

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •