Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    Dec 2006
    Location
    Arab, AL
    Posts
    13

    Unanswered: VB set statement

    The following code (part of the code) give gives me an error (Compile Error: Method or data member not found:

    Dim rst As Recordset
    Dim rst2 As Recordset
    Dim dbs As Database
    Set dbs = CurrentDb
    dbs.Execute ("DELETE * FROM Groupfile") 'empty table for new data

    Set rst = dbs.OpenRecordset("Select * from old where groupname = " & Me.groupname)
    Set rst2 = dbs.OpenRecordset("select * from groupfile where groupname = " & rst!groupname)
    rst2.AddNew
    rst2!groupno = tst!groupno
    rst2!groupname = rst!groupname
    rst2!new = rst!new
    ......

    The error defines me.groupname as the error field. This field is defined in both old anf GroupFile tables. It must be the format, but I cannot detect what is wrong.

    Please help.

    Thanks in advance.

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    assuming groupname is a name then it is string data so it needs single quotes

    Set rst = dbs.OpenRecordset("Select * from old where groupname = '" & Me.groupname & "'")

    izy

    LATER
    ditto the second rst
    Set rst2 = dbs.OpenRecordset("select * from groupfile where groupname = '" & rst!groupname & "'")

    LATER STILL
    rst2!groupno = tst!groupno ???
    currently using SS 2008R2

  3. #3
    Join Date
    Dec 2006
    Location
    Arab, AL
    Posts
    13
    I tried you code and still get the same error message. I just copied the 2 lines and corrected the tst to rst.

    I know the problem is simple but don't know what to correct. There must still be a format problem.

    Help!!!
    Last edited by don white; 01-01-07 at 12:20.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    do you have a field groupname on the form where this code is running? is groupname text or numeric or ??
    do you have a reference set to DAO ? (in the code window, menu: tools/references and see if Microsoft DAO 3.6 is ticked)
    currently using SS 2008R2

  5. #5
    Join Date
    Dec 2006
    Location
    Arab, AL
    Posts
    13
    Yes, I have the reference module.

    Groupname is a field define in both the two tables as text.

    I am deleting the new table groupfile and add the old fields to the groupfile table.

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    great, but is groupname the name of a field on the form where the code is running.
    me.blahblah takes the value of a field blahblah on the form where the code is running and it doesn't care what you have in your tables

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Dec 2006
    Location
    Arab, AL
    Posts
    13
    No, the form only calls the code to add new data to the groupfile table.

    Both groupname is the name of one of the fields in each of the tables

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    simple experiment
    add a couple of temporary lines
    dbs.Execute ("DELETE * FROM Groupfile") 'empty table for new data
    debug.print me.groupname
    stop

    it will stop at the stop. hit Ctrl-G to get the immediate window and what do you see?
    if you don't see the groupname you are trying to use in your SQL, you have your explanation.

    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    crossed posts.

    you need to tell the SQL the value of groupname
    millions of ways to do this, but need some info from you: how are you expecting your programme to 'know' the groupname of interest?

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Dec 2006
    Location
    Arab, AL
    Posts
    13
    I did not see anything in the immediate.

    What code do I need to use in order to update groupfile table with old table?

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    how many groupnames are there in the table(s)?
    1 or N ?

    izy
    currently using SS 2008R2

  12. #12
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    and if N groupnames
    is it one record per groupname or N records per groupname

    izy
    currently using SS 2008R2

  13. #13
    Join Date
    Dec 2006
    Location
    Arab, AL
    Posts
    13
    Can be N number of groupnames

  14. #14
    Join Date
    Dec 2006
    Location
    Arab, AL
    Posts
    13
    one record per groupname

  15. #15
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ok. lets summarise.
    you have two tables 'old' and 'groupfile'
    both tables have N groupnames
    both tables have identical field structure
    you want to get all the records from 'old' into 'groupfile'

    so have a go with:

    dim strSQL as string
    strSQL = "DELETE * FROM groupfile"
    currentdb.execute strSQL
    strSQL = "INSERT INTO groupfile SELECT * FROM old"

    that's all you need.
    ...it will run a few dozen times faster than the recordset approach.

    izy

    LATER (i forgot the last line - sorry)
    dim strSQL as string
    strSQL = "DELETE * FROM groupfile"
    currentdb.execute strSQL
    strSQL = "INSERT INTO groupfile SELECT * FROM old"
    currentdb.execute strSQL
    Last edited by izyrider; 01-01-07 at 12:54.
    currently using SS 2008R2

Posting Permissions

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