User Profile

Collapse

Profile Sidebar

Collapse
AndyB2
AndyB2
Last Activity: Oct 28 '13, 05:26 PM
Joined: Dec 28 '11
Location:
  •  
  • Time
  • Show
  • Source
Clear All
new posts
  • AndyB2
    started a topic Run-time error 3020; Update or CancelUpdate without AddNew or Edit
    in Access

    Run-time error 3020; Update or CancelUpdate without AddNew or Edit

    Using Access 2010
    Windows 7

    I'm sure this is a simple VBA Gramer problem.

    I am attempting to update a table.

    Below is the code and I'm getting the error on the line BOM("Level") = 1, Line 21. I'm sure lines 22, 25, and 27 will have the same issue.


    Code:
    Private Sub CB_RunQueryPrintReport_Click()

Dim stDocName As String
Dim Filename As String
    ...
    See more | Go to post
    • AndyB2
      replied to Spaces between fields on CSV output table
      in Access
      I stumbled onto a different answer that works, using concatenation with “&” vs. a comma to separate the fields takes care of the problem still using the print statement.

      New Print code:
      Code:
      If REQ("WorkOrder") = UDI("WorkOrder") Then
            Print #1, REQ!rowt & "," & REQ!fld2 & "," & i & "," & REQ!part & "," & REQ!fld5
      ...
      See more | Go to post

        Leave a comment:

      • AndyB2
        started a topic Spaces between fields on CSV output table
        in Access

        Spaces between fields on CSV output table

        Below is my code that appears to be working as expected. But the output has spaces between each of the fields. Two of the table created are attached as examples.

        I did try Trim() on a couple if fields with no luck.

        Code:
        Private Sub CB_RunQueryPrintReport_Click()

Dim stDocName As String
Dim Filename As String
Dim i, RC, X As Integer
Dim MyDB As DAO.Database
Dim REQ As DAO.Recordset
        ...
        See more | Go to post
        • AndyB2
          started a topic Writing out a CSV file getting Run-time error ‘75’: Path/File access error
          in Access

          Writing out a CSV file getting Run-time error ‘75’: Path/File access error

          Using Access 2010

          I need to write a CSV file where the number of fields on each record is not the same, requirement of the program we are feeding.

          Below is the code I am having I am getting the error: Run-time error 75: Path/File access error.

          Error is happening at “Open fileopen for Output as #1” (line 30)

          Code:
          Private Sub CB_RunQueryPrintReport_Click()
Dim stDocName As String
          ...
          See more | Go to post
          • AndyB2
            started a topic MS Access and SharePoint Error running Report
            in Access

            MS Access and SharePoint Error running Report

            Using MS/Access 2010

            When attempting to run a report in ShairPoint I am getting this error:

            "An error occurred during local report processing.
            An error has occurred during report processing.
            Query execution failed for dataset 'Default'.
            For more information about this error navigate to the report server on the local server machine, or enable remote errors"
            ...
            See more | Go to post
            Last edited by NeoPa; May 9 '12, 11:55 PM. Reason: Made pic viewable.
            • AndyB2
              started a topic How can I calc a percentage of an hour?
              in Access

              How can I calc a percentage of an hour?

              Access 2010:

              I need to calc the difference between two times then calc a percentage of an hour?

              Code:
              !StartTimeStamp1 = StartTime
!EndTimeStamp1 = MDrs("TimeStamp1")
!Total_time = MDrs("TimeStamp1") - StartTime
              First problem, the above code is giving the following result, adding 12 hours when the difference is less than one hour. See attachment.

              [imgnothumb]http://bytes.com/attachments/attachment/6233d1331908530/time-example.jpg[/imgnothumb]...
              See more | Go to post
              Last edited by NeoPa; Mar 17 '12, 11:03 PM. Reason: Made pic viewable.
              • AndyB2
                replied to Looking for a good book / learning tool for MS VB 2010
                in Visual Basic
                Well just using MS online help http://msdn.microsoft.com/en-us/vstudio/hh388573 for now.
                See more | Go to post

                  Leave a comment:

                • AndyB2
                  started a topic Looking for a good book / learning tool for MS VB 2010
                  in Visual Basic

                  Looking for a good book / learning tool for MS VB 2010

                  I feel comfortable with my for/Do while/if then/case/call sub/etc… logic. What I need help with is taking the logic I have and putting it into the correct syntax.

                  The first book I purchased looks out of date, “Visual Basic.NET Second Edition by Deitel”. MS VB Studio 2010 looks like the forms design is point and click vs. the book still calls it out in the code.

                  I am sure everyone has their favorite book. But...
                  See more | Go to post
                  • AndyB2
                    replied to Need to run code every minute, thinking I might have to use SQL Trigger?
                    in Access
                    Thanks

                    1) A quick search of SSIS looks like it is available on 2008 and not 2005. At lease when I search MS/SQL 2005 SSIS, it sends me to 2008.

                    2) I need to see if I can find an example of a VBS script to run an Access update query.

                    3) As far as doing a calculation with the timestamp, that could happen. But I am building the backend to a website where multiple people will be running the efficiency reports...
                    See more | Go to post

                      Leave a comment:

                    • AndyB2
                      replied to Need to run code every minute, thinking I might have to use SQL Trigger?
                      in Access
                      We track downtime by machine/operator. If we are producing product with a downtime code not zero (0) it makes it hard to figure productivity.

                      Example 1: Operator 1 is scheduled to produce 100 widgets an hour. His actual downtime for the first hour is 15 min’s (8:30 > 8:45) and he produced 80 widgets, 80/75 (75% of a std. hour) = 106.6% eff.

                      Example 2: Same operator now forgets to reset his downtime and...
                      See more | Go to post

                        Leave a comment:

                      • AndyB2
                        replied to Need to run code every minute, thinking I might have to use SQL Trigger?
                        in Access
                        Realizing the above code needs to run in its own instance of access. Not the best solution, but could work if there isn't a better one.
                        See more | Go to post

                          Leave a comment:

                        • AndyB2
                          replied to Need to run code every minute, thinking I might have to use SQL Trigger?
                          in Access
                          How the data is created; data logger is polling any change to “Tags” from PLCS on the floor. So if we produce a wigged it is logged. Downtime reason codes entered by the operator are also logged. Just the operator sometimes forgets to change their codes back to zero(0) when they startup production.

                          This code grabs all data logged in the last 2 minutes with a downtime code not zero(0) then check to see if the production...
                          See more | Go to post

                            Leave a comment:

                          • AndyB2
                            started a topic Need to run code every minute, thinking I might have to use SQL Trigger?
                            in Access

                            Need to run code every minute, thinking I might have to use SQL Trigger?

                            Access 2010
                            SQL Sever 2005 express

                            Here is my code, works fine as is. But I need to run it once a minute, if not more often. From what I have found out by surfing the web I have one of three choices:

                            1) Use the timer in the forms property.
                            2) Use the PC scheduler and call access then autoexec.
                            3) Rewrite the code below into SQL and apply it to a trigger.

                            I do not like any of the...
                            See more | Go to post
                            • AndyB2
                              replied to 3086 error could not delete from specified tables
                              in Access
                              Thanks for the reply, last question.

                              Is
                              Code:
                              Dim cdb As DAO.Database
 
Set cdb = CurrentDb()
Call cdb.QueryDefs("[Truncate_dbo_SupperSummary]").Execute
                              technically better than:
                              Code:
                              DoCmd.OpenQuery "Truncate_dbo_SupperSummary"
                              The above code is working, but I would like to know the "correct way"?

                              Thanks,

                              ...
                              See more | Go to post

                                Leave a comment:

                              • AndyB2
                                replied to 3086 error could not delete from specified tables
                                in Access
                                Found answer:

                                Pass through query named "Truncate_dbo_S upperSummary"

                                with code:
                                Code:
                                truncate table SupperSummary
                                vba code:
                                Code:
                                DoCmd.OpenQuery "Truncate_dbo_SupperSummary"
                                Problem: Had different error with this setup.

                                Solution in pass through query had to turn option: Returns Records to "NO".
                                ...
                                See more | Go to post

                                  Leave a comment:

                                • AndyB2
                                  replied to 3086 error could not delete from specified tables
                                  in Access
                                  Current state:

                                  I have a standalone pass-through query called "Truncate_dbo_S upperSummary", it's code is:
                                  Code:
                                  truncate table SupperSummary
                                  In this query proprety: the ODBC Connect Str is pointing at the SQL Server

                                  The above works like a charm when I double click it.


                                  The current code that is NOT working in my VBA with the same 3086 error code:

                                  ...
                                  See more | Go to post

                                    Leave a comment:

                                  • AndyB2
                                    replied to 3086 error could not delete from specified tables
                                    in Access
                                    Looking back I can see were I was using dbo. where I should of been using dbo_ and I appoligize.

                                    When I was talking about testing in the SQL server I was not using the dbo_ prefix, and I can see where that can be confusing also. I should state more clearer what enviroment I am taking my examples from.
                                    See more | Go to post

                                      Leave a comment:

                                    • AndyB2
                                      replied to 3086 error could not delete from specified tables
                                      in Access
                                      dbo_SupperSumma ry is the table name Access created when I linked to table SupperSummary in the SQL server. I have deleted the link and recreated it, same name is created.

                                      I attemped the above options 1 and 2 with no luck. Both of the options seem to want to write the SQL in the statement. Is there a way to evoke the passthrough query I created in Access?
                                      See more | Go to post

                                        Leave a comment:

                                      • AndyB2
                                        replied to 3086 error could not delete from specified tables
                                        in Access
                                        No luck on "DELETE * FROM dbo.SupperSumma ry", that's what I started with.

                                        I did take your idea on a pass-through query:

                                        Query Name: Truncate_dbo_Su pperSummary
                                        Query: truncate table SupperSummary;

                                        That works when I fire it off inside Access, but get hung up in my code when I call it:

                                        Code:
                                        DoCmd.OpenQuery "Truncate_dbo_SupperSummary"
                                        ...
                                        See more | Go to post

                                          Leave a comment:

                                        • AndyB2
                                          replied to 3086 error could not delete from specified tables
                                          in Access
                                          dbo.SupperSumma ry is the name of the linked table inside of Access. It is linked through ODBC.
                                          See more | Go to post

                                            Leave a comment:

                                          No activity results to display
                                          Show More
                                          Working...