Results 1 to 7 of 7

Thread: VBCode problems

  1. #1
    Join Date
    Dec 2002
    Location
    Columbus, GA
    Posts
    81

    Unanswered: VBCode problems

    Let me start by saying we have a database built in 97 by someone else and the compuyer is pretty old. The database is stored on the network but we can only run it "error-free" on the computer it was built on. All of the other PC's have 2 versions of access on them (97 and 2000).

    This database was built in 97. We tried upgrading it but it didnt work. What I have done noe is import all of the information into a new database. Everything works so far except fot the following code. I am not big on the code part of Access so I need some help telling me what needs to be changed. Take in mind, this code is for 97, I need it converted to 2000.

    What this code does is Outputs Data in a Query to multiple files. We import fed-ex package data everyday into this dtabase and then export the data into seperate file names based on teh customer name. So, the query shows all dfed-ex packages we sent out, the reports are outputed based on custoemr name. If we sent 100 packages out (10 each for 10 clients). we would have 10 reports, 1 for each client, the report shows only the packages we sent out for them. For security reasons, I cannot go to much into detail about this. Here is the code.

    Option Compare Database
    Option Explicit

    Public Function ReportEachClient()

    Dim db As Database
    Dim tblClients As Recordset 'A table with one record for each client
    Dim tblClientCriteria As Recordset 'A table of clients to be reported used during loop process

    DoCmd.SetWarnings False 'Do not prompt user during dummy record processing
    DoCmd.RunSQL "delete from [tblClientCriteria]" 'empty the criteria table
    DoCmd.RunSQL "INSERT INTO [tblClientCriteria] ( [client] ) SELECT 'xyz' AS Expr1;" 'add one dummy record
    DoCmd.SetWarnings True 'Turn warnings back on

    Set db = CurrentDb
    Set tblClients = db.OpenRecordset("tblClients")
    Set tblClientCriteria = db.OpenRecordset("tblClientCriteria")

    Do Until tblClients.EOF
    tblClientCriteria.Edit
    tblClientCriteria![Client] = tblClients![ClientName] 'write the current client to the clients criteria table
    tblClientCriteria.Update
    Forms![Start].[STARTsubGenerateReports].Requery

    DoCmd.OutputTo acOutputReport, "*AllClientsMain", acFormatRTF, [Forms]![Start]![ExportTo] & tblClients![ClientName] & " " & [Forms]![Start]![ShipDateUnbound] & ".rtf"

    tblClients.MoveNext
    Loop

    DoCmd.SetWarnings False 'Do not prompt user during dummy record processing
    DoCmd.RunSQL "delete from [tblClientCriteria]" 'empty the criteria table
    Forms![Start].[STARTsubGenerateReports].Requery 'empty vindow on START
    DoCmd.SetWarnings True 'Turn warnings back on
    tblClientCriteria.Close
    tblClients.Close
    db.Close

    End Function

  2. #2
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    Access 2000 uses ADO as default, Access97 uses DAO as default. You can use both at the same time, but you have to strictly call it.

    In your case, open your access2000 module (any module), go to menu tools-references and REMOVE 'Microsoft ActiveX Data Objects 2.1' and ADD 'Microsoft DAO 3.6' you can add DAO 3.51 instead of DAO 3.6, but 3.6 is better and shoud work (you don't have any complicated code based on 3.51).


    it should work then....


    jiri

  3. #3
    Join Date
    Feb 2002
    Posts
    29
    Change these two lines:

    Dim tblClients As Recordset 'A table with one record for each client
    Dim tblClientCriteria As Recordset 'A table of clients to be reported used

    to be:

    Dim tblClients As DAO.Recordset 'A table with one record for each client
    Dim tblClientCriteria As DAO.Recordset 'A table of clients to be reported used

    Then, while you are in the VB editor go to Tools on the menu bar, then References. Look down the list until you find "Microsoft DAO 3.6 Object Library" and put a check in the box on the left. Then click OK.

    Try running your code again. If it still doesn't work tell us what the error message says and which line of code it is stopping on (it will be highlighted in yellow when you click on the "Debug" button on the error message box).

  4. #4
    Join Date
    Dec 2002
    Location
    Columbus, GA
    Posts
    81
    Ok, I have done it all and get another error that wasnt there before.

    I get: Comile Error - Invalid Use of Property

    It comes on this "EditMode is highlighted"

    Do Until tblClients.EOF
    tblClientCriteria.EditMode
    tblClientCriteria![Client] = tblClients![ClientName] 'write the current client to the clients criteria table
    tblClientCriteria.Update
    Forms![Start].[STARTsubGenerateReports].Requery

    Here is the latest UPDATED CODE (ALL)

    Option Compare Database
    Option Explicit

    Public Function ReportEachClient()
    'The structure of this function was donated by James Rombough in a post at
    'www.zdjournals.com/bbs

    Dim db As DAO.Database
    Dim tblClients As DAO.Recordset 'A table with one record for each client
    Dim tblClientCriteria As DAO.Recordset 'A table of clients to be reported used during loop process

    DoCmd.SetWarnings False 'Do not prompt user during dummy record processing
    DoCmd.RunSQL "delete from [tblClientCriteria]" 'empty the criteria table
    DoCmd.RunSQL "INSERT INTO [tblClientCriteria] ( [client] ) SELECT 'xyz' AS Expr1;" 'add one dummy record
    DoCmd.SetWarnings True 'Turn warnings back on

    Set db = CurrentDb
    Set tblClients = db.OpenRecordset("tblClients")
    Set tblClientCriteria = db.OpenRecordset("tblClientCriteria")

    Do Until tblClients.EOF
    tblClientCriteria.EditMode
    tblClientCriteria![Client] = tblClients![ClientName] 'write the current client to the clients criteria table
    tblClientCriteria.Update
    Forms![Start].[STARTsubGenerateReports].Requery

    DoCmd.OutputTo acOutputReport, "*AllClientsMain", acFormatRTF, [Forms]![Start]![ExportTo] & tblClients![ClientName] & " " & [Forms]![Start]![ShipDateUnbound] & ".rtf"

    tblClients.MoveNext
    Loop

    DoCmd.SetWarnings False 'Do not prompt user during dummy record processing
    DoCmd.RunSQL "delete from [tblClientCriteria]" 'empty the criteria table
    Forms![Start].[STARTsubGenerateReports].Requery 'empty vindow on START
    DoCmd.SetWarnings True 'Turn warnings back on
    tblClientCriteria.Close
    tblClients.Close
    db.Close

    End Function

  5. #5
    Join Date
    Feb 2002
    Posts
    29
    Change:

    tblClientCriteria.EditMode

    To:

    tblClientCriteria.Edit

  6. #6
    Join Date
    Dec 2002
    Location
    Columbus, GA
    Posts
    81

    Cool

    That worked, thanks for all of your help. If you let me know your name, i will give you credit in the code

  7. #7
    Join Date
    Dec 2002
    Location
    Columbus, GA
    Posts
    81
    Think you can help me with this one?

    http://dbforums.com/showthread.php?threadid=641437

Posting Permissions

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