Results 1 to 6 of 6

Thread: VB Help (2000)

  1. #1
    Lounger
    Join Date
    Sep 2001
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VB Help (2000)

    I am trying to write my first piece of code. I have been reading through help and Special Edition but I can't figure what I'm missing. I have a form with an unbound text box [OrderNumber], the user is to enter the 5 digit, numeric order number and then check any one of 20 or so check boxes. Then hit a Print All Reports button that is tied to the following code (I'm only trying one form until I get it to work, the rest should be replicating the same format):

    Private Sub PrintButton_Click()
    'Print all of the reports selected on the Master Print form

    Dim intOrderNumber As Integer
    Set intOrderNumber = Forms!frmMasterPrint!OrderNumber

    If CheckPartChecklist Then
    DoCmd.OpenReport Reports![Partner Checklist Report], , , [Order Number] = intOrderNumber
    DoCmd.PrintOut , , , , 1
    DoCmd.Close
    Else
    'Do nothing if not checked
    End If

    End Sub

    I'm expecting to get a report for the box checked only for the record equal to the unbound text box value. But I get a compile error: Object required when it gets to my Set intOrderNumber statement. I can't figure out what I'm missing, can anyone please help?

  2. #2
    Lounger
    Join Date
    Sep 2001
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Help (2000)

    OK, check that question, 10 minutes after posting that message I removed the word Set and got past that error, but now I get an error saying Access can't find the field '|' in my expression which I've changed to the following.

    DoCmd.OpenReport [Partner Checklist Report], , , [Shop Orders].[Order Number] = intOrderNumber

    I'm getting bleary eyed so I'll try some more in the morning.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VB Help (2000)

    The WHERE condition is a string. Try it this way:

    DoCmd.OpenReport [Partner Checklist Report], , , "[Shop Orders].[Order Number] = " & intOrderNumber
    Charlotte

  4. #4
    Lounger
    Join Date
    Sep 2001
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Help (2000)

    Ok, thanks. I tried that and it didn't fix it completely, but it definitely pointed me in the right direction. I also had to remove the brackets around the report name and replace them with double quotes. Then it worked great, I also got rid of the DoCmd.PrintOut statement because that seemed to hang the printer after printing the page I wanted. Thanks again for the help, now I just have to replicate the code 20 more times for the other check boxes on the form.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VB Help (2000)

    Sorry, I was tired when I posted that. I totally overlooked the brackets around the report name, as well as the fact that you don't reference the table/query object in the Where condition, only the field itself, at least not unless you have two fields with the same name from two different tables in your query

    Instead of replicating the code 20 time, write a single routine that does that part of it, but use the Screen.ActiveControl to retrieve a reference to the control just clicked. Then you would call the routine from the event procedure for each of the checkboxes/optionbuttons or whatever. You may need some additional lines of code to select the appropriate report or build the appropriate Where condition based on the button clicked, but that will save you building virtually the same routine over and over.
    Charlotte

  6. #6
    Lounger
    Join Date
    Sep 2001
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Help (2000)

    I knew there would be a better way to do it. I guess that is the great thing about coding, there is more than one way to do it. Some are just longer than others. I had already replicated the sections by the time I got back to reading your last post. It was easy, just copy and paste and change the references. It all works now, and I finally have a solution to a problem I have been thinking about how to solve for a long time. Thanks again. <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

Posting Permissions

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