Results 1 to 10 of 10

Thread: Help with VBA

  1. #1
    Join Date
    Dec 2002
    Posts
    6

    Unanswered: Help with VBA

    Hey guys,

    I am trying to write a VBA procedure that parses certain records from a table and transfers them to a new table. The trouble is that I want to transfer all records containing quotation marks and I don't know how to do this. :0{

    In essence, I want the VBA code to search the entire table, find all the records with text enclosed in quotation marks and then transfer these records to a new table.

    Any help on this would be greatly appreciated.

    Cheers,
    David

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    here's an outline (DAO):

    private sub doit()
    dim dabs as dao.database
    dim reci as dao.recordset
    dim reco as dao.recordset
    dim strKey as string
    dim intRet as integer (...as long if thisOne is HUGE)


    strKey = chr$(34) 'the double-quote

    set dabs = currentdb

    set reci = dabs.openrecordset("nameOfTheSourceTable")
    ' source recordset, field thisOne might have quotes

    set reco = dabs.openrecordset("nameOfTheSinkTable")
    ' destination recordset

    with reci
    do while not .eof

    intRet = instr(1, !thisOne, strKey)
    if not isnull(intRet) then
    if intRet > 0 then 'a double-quote was found
    reco.addnew
    reco!thisOne = !thisOne
    reco.update
    endif
    endif

    .movenext

    loop
    end with

    set reci = nothing
    set reco = nothing
    set dabs = nothing

    msgbox "all done"

    end sub

    izy

  3. #3
    Join Date
    Dec 2002
    Posts
    6
    Izy,

    Thanks for your response.

    When I try and run the module I get a compile error on the line:
    intRet = InStr(1, !thisOne, strKey)

    telling me that the "item is not found in this collection".

    What is the function of thisOne, indeed what is thisOne?

    Regards,
    David

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    sorry - you (must) have two tables:

    your source table which contains a field that you suspect of containing embedded ".
    replace nameOfTheSourceTable in my example with the real name of your source table.

    i have used thisOne as the name of the field that contains suspected ".
    replace thisOne with the real name of the field you are investigating

    i have user nameOfTheSinkTable to represent the name of the destination table - replace with the real name. (easiest way to generate this table (which MUST exists before you run the code) is to copy/paste nameOfTheSourceTable ...structure only).

    if you have other fields of interest in nameOfTheSourceTable that you want to copy over, it goes like this:

    reco.addnew
    reco!thisOne = !thisOne
    reco!thisTwo = !thisTwo
    reco!thisThree = !thisThree
    reco!thisFour = !thisFour

    reco.update

    ...again, i don't know your field names so i used dummy names thisTwo etc: you use the real names.

    izy

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ...and you did ask for a VBA solution so that's what you got.

    there is probably a SQL make-table that would do the job, but i'm not an SQL expert. maybe someone else will propose SQL

    izy

  6. #6
    Join Date
    Dec 2002
    Posts
    6
    Izy,

    Thanks for all your help.

    I am also looking to create some VBA code for use in a macro.

    So far in the macro, I have selected certain records I want copied from one table to another. I now need some function that I can call to cut and copy the currently selected record and transfer it to a new table.

    Any ideas on some VBA code that would do this? I am having trouble selecting the currently active record for copying.

    Regards,
    David

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    sorry: sub-zero knowledge of macros.

    maybe if you can explain where the code can look to "understand" which records are selected by your macro...

    izy

  8. #8
    Join Date
    Dec 2002
    Posts
    6
    Izy,

    I have modified the VBA code you submitted slightly to find instances of 2001. It now looks like the following:


    Private Sub doit()
    Dim dabs As DAO.Database
    Dim reci As DAO.Recordset
    Dim reco As DAO.Recordset
    Dim strKey As String
    Dim intRet As Integer
    Dim strSQL As String
    Dim rcs As Recordset


    strKey = "2001" 'the year 2001

    Set dabs = CurrentDb

    Set reci = dabs.OpenRecordset("Aaasmit")
    ' source recordset,

    Set reco = dabs.OpenRecordset("Aaasmit")
    ' destination recordset

    With reci
    Do While Not .EOF

    strSQL = "Select * from Aaasmit"
    Set rcs = dabs.OpenRecordset(strSQL)


    intRet = InStr(1, !Details, strKey) 'Aaasmit only contains 1 field called Details

    If Not IsNull(intRet) Then
    If intRet > 0 Then '2001 was found
    reco.AddNew
    reco!Details = !Details
    reco.Update
    End If
    End If

    .MoveNext

    Loop
    End With

    Set reci = Nothing
    Set reco = Nothing
    Set dabs = Nothing

    MsgBox "all done"

    End Sub

    However when I try to run the code I get the following error:
    Run-time error '94': Invalid use of null
    ,on the line: intRet = InStr(1, !Details, strKey)

    Any ideas on what is going wrong?

    Once again, many thanks.

    David

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    first: you have reci & reco & rcs looking at the same stuff (all are * FROM Aaasmit) ...this seems to be overkill , and positively dangerous as you are appending from reci to reco (which are the same table).

    second, depending on the order of your library references, rcs may or may not be a failed ADO recordset: best to explicitly declare it as DAO.Recordset (or convert the whole lot to ADO)

    third, rcs is completely ignored by your code

    fourth:
    intRet = InStr(1, !Details, strKey)
    ...will be unhappy if either
    strKey=Null (it is explicitly NOT null from your code)
    or
    !Details ( = reci!Details thanks to the "with reci") =Null
    ...so this last one is the prime candidate.

    add a
    msgbox !Details, vbokonly, "Current value for !Details"
    just before line intRet = InStr(1, !Details, strKey) and see what you see.

    the next question is WHY !Details is null: either you have Null values in the Aaasmit.Details (always possible!), or the recordset is not collecting records as it should (baffling!!!!!!)

    after your msgbox experiment above, delete the msgbox line and surround the intRet line like so:

    if not isnull(!Details) then
    intRet = InStr(1, !Details, strKey)
    end if


    best of luck, izy

  10. #10
    Join Date
    Dec 2002
    Posts
    6
    Finally have it working!

    Thanks Izy, I did have some null values further down the database.

    Any ideas on a VBA function that would remove any unneeded text from a parsed record.

    i.e. if I want to copy all records containing 'xyz' from one table to another, pretty easy just 'LIKE "*xyz*"

    However, when records are copied across, the full record is copied from one table to the other, eg: '...... ..... ..... xyz'

    I want to copy the record to a new table, but I just want it to appear as 'xyz'

    Maybe I could case for all instances of "*xyz*", then transfer them to new table as just 'xyz'. Can this be done? Any ideas??

    David

Posting Permissions

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