Results 1 to 15 of 15

Thread: Help With Code

  1. #1
    Join Date
    Mar 2004
    Posts
    361

    Unanswered: Help With Code

    I need a button that updates a table and shows a report with that same data in one click. This is what I have so far. It shows the report with the data but the error shows "You can't use the GoToRecord action or method on an objext in Design view". This also doesn't update the table either. Can someone please assist me with the code? Thank you very very much.

    Private Sub Command149_Click()
    On Error GoTo Err_Command149_Click


    Dim stDocName As String

    stDocName = "Term Worksheet Report"
    DoCmd.OpenReport stDocName, acPreview
    DoCmd.GoToRecord , , acNewRec
    DoCmd.Close


    Exit_Command149_Click:
    Exit Sub

    Err_Command149_Click:
    MsgBox Err.Description
    Resume Exit_Command149_Click


    End Sub

  2. #2
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Been a while TJarvas... I havent been working on databases much recently, but I will see what I can do to help.

    What is this function built on? A form control? Obviously I see that you have a button that you are using to bring up the report.

    I have a button that prints a report or views the report from a form. Is that what you are wanting?

    Is the form unbound? (I almost assume that it is since you say you need to append the records to a table).

    Let me know the answers to my questions and I am pretty sure I can help.

    Thanks,
    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  3. #3
    Join Date
    Mar 2004
    Posts
    361
    Yes, kinda. What i want is this. The user presses the button on the form and it shows the report with the data in the text boxes. Once the report is shown I would like the data to be updated to the table and then close the form. The form is bound to a query. In the past I have been able to press a button that writes the data from the text boxes to the control it is linked to. I don't think this should be a problem. This is the type of fuction I am using on the report. =[Forms]![Term Worksheet]![txtResidual]. Maybe this will give you a little more info.

  4. #4
    Join Date
    Aug 2004
    Posts
    40
    Try this

    Code:
    Private Sub Command149_Click()
    On Error GoTo Err_Command149_Click
    
    Dim stDocName As String, strWhere As String
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    
    stDocName = "Term Worksheet Report"
    strWhere = "[WorksheetNo]=" & Me!WorksheetNo
    '** Replace WorksheetNo with your unique identifier **
    DoCmd.OpenReport stDocName, acPreview,,strWhere
    DoCmd.Close acForm, "your form name"
    
    Exit_Command149_Click:
    Exit Sub
    
    Err_Command149_Click:
    MsgBox Err.Description
    Resume Exit_Command149_Click
    
    End Sub
    HTH

    Howard

  5. #5
    Join Date
    Mar 2004
    Posts
    361
    What is the worksheet number? Is the the name of the report. Sorry for the simple question.

  6. #6
    Join Date
    Mar 2004
    Posts
    361
    I tried primary key ideas as well as GUID's but to no avail. Any other ideas?
    Last edited by tjarvas; 08-09-04 at 15:49.

  7. #7
    Join Date
    Aug 2004
    Posts
    40
    OK... I've made an assumption here.

    I'm assuming that each term that you created has some sort of unique id, maybe an autonumber field. If it has then that is the field name you put in place instead of "worksheetno"

    What the code is doing

    Dim stDocName As String, strWhere As String
    (Declare a couple string variables)

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    (Save the current form and it's record)

    stDocName = "Term Worksheet Report"
    '(the name of your of course)

    strWhere = "[WorksheetNo]=" & Me!WorksheetNo
    '(this is where we get the curreny record id and add it to the string strWhere)

    '** Replace WorksheetNo with your unique term identifier **
    DoCmd.OpenReport stDocName, acPreview,,strWhere
    '(open the report in preview mode where the record id of the term is equal to the record id of your form record using the string strWhere to pass this info to the report)

    DoCmd.Close acForm, "your form name"
    '(close the form and keep the report open)

    Hope that explains it a bit better for you.

    If you're not using an unique identifier then we gunna have a know a little bit more about the structure of your database.

    Howard

  8. #8
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    If you have your form based on a query then you shouldnt have to 'save' your record. It automatically does it as you enter the next field.

    So either I am not quite understanding your process or something.

    I have a form that enter's a sales order into the database. It is based off of a query. I have 2 buttons at the bottom of the form (still in the detail section), one is 'View Report' (it views the report for that record) the other one is 'Print Report' (it confirms that you wanted to print and after click of okay it prints). If you want something like this or even both of those in the same button I can do that. I do have to make sure the last value I changed on a sales order is saved by changing the focus from that field to another one. I normally do that manually. I probably could build a function to do that automated.

    Let me know,
    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  9. #9
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313

    Just a thought

    Hey guys,

    In reading through this post, I couldn't tell if tjarvas wanted to restrict the report to only the record on the form...or just updated with the new data.
    If it's just the update your looking to do this code should do it...otherwise, I think hpicken had your answer.
    Hopefully this doesn't add to the confusion.

    Chris

    P.S. JS, I think you're right, however I have users that will edit a field then click the 'close' button (thereby not saving)...so I've made a habit of including the 'save' line to any 'close' button.

    Code:
    Private Sub Command149_Click()
    On Error GoTo Err_Command149_Click
    
    Dim stDocName As String
    
    ' Save the current stuff
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    
    stDocName = "Term Worksheet Report"
    
    ' Preview the report
    DoCmd.OpenReport stDocName, acPreview
    
    ' Close yourform
    DoCmd.Close acForm, "your form name"
    
    Exit_Command149_Click:
    Exit Sub
    
    Err_Command149_Click:
    MsgBox Err.Description
    Resume Exit_Command149_Click
    
    End Sub

  10. #10
    Join Date
    Mar 2004
    Posts
    361
    I am not going to be the user of this form so I have to make it dummy proof. The report will only show the information that is queried/added to the form. The table that I am using, does not have a autonumber field. I tried using the primary key name but that didn't work either. So I am still stuck I think. I need to open the report with the data that's on the form and then update the table with the data on the form. I think the code that hpicken gave me will work as long as I find a worksheetno.

  11. #11
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Quote Originally Posted by tjarvas
    I am not going to be the user of this form so I have to make it dummy proof. The report will only show the information that is queried/added to the form. The table that I am using, does not have a autonumber field. I tried using the primary key name but that didn't work either. So I am still stuck I think. I need to open the report with the data that's on the form and then update the table with the data on the form. I think the code that hpicken gave me will work as long as I find a worksheetno.

    What is the table's primary key? You should be able to use that as the 'worksheetno' and yes hpicken's code should work.

    Let me know,
    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  12. #12
    Join Date
    Mar 2004
    Posts
    361
    SWEET GEORGIA BROWN! We have a winner. It works perfectly. Thanks for all of your help. If I could buy you guys a round I would.

  13. #13
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Excellent glad it worked!

    cpgospi thanks for that tid bit... I will save that code so as to make sure I always get everything saved. That will make my application a bit more friendly.

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  14. #14
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    Quote Originally Posted by tjarvas
    I am not going to be the user of this form so I have to make it dummy proof.
    Be careful, they're always upgrading the dummies.

    Np JS...took me a while to figure out why their changes were intermittently being lost.

    Have a good one.

  15. #15
    Join Date
    Aug 2004
    Posts
    40
    Glad you got it sorted tjarvis.

    Very good quote about the dummies too cpgospi.

    Cheers

Posting Permissions

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