Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    Lounger
    Join Date
    Nov 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating Forms (2000)

    I currently have two tables the first one being a table with an ID and a part number.
    The second table (Certs) has; a cert ID (autonumber, primary Key), Attention (text), and PartNumber (used the lookup wizard and linked it to the part number table).

    I created a qry off of the Certs table which has two fields; Attention and PartNumber (the part number looks correct has the drop down box).

    Created a Form from the qry again with Attention and PartNumber (which is also correct on the form).

    Then when I created the Report based off of the query, but instead of getting the actual part number I am getting the ID# for the part number. How can I create the report off of the Query and have it show the actual part number?

    Also I want to be able to link the report to the current form that is up. Everytime I run the report I get all of the records, but I just want to be able to print the current record that is shown on the form.

    Any help would be greatly appreciated.

    Thanks,
    Melanie

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creating Forms (2000)

    Look at the properties of the combo box (dropdown list) on the form to see how it works. In particular, note the following properties:
    - Column Count
    - Column Widths
    - Row Source Type
    - Row Source

    Now, open the report in design view and right-click the text box bound to the ID field. Select Change To > Combo Box.
    Then set the properties of the combo box as they were on the form.

    Put a command button on your form, with caption "Preview Report" or something similar.
    Name it for example cmdReport.
    Activate the Event tab of the Properties window and click in the On Click event.
    Select [Event Procedure] from the dropdown list, then click the ... to the right of the dropdown arrow.
    The Visual Basic Editor will open to the code module for the form, with the first and last line of the event procedure already created for you.
    Make the code look like this, with the appropriate names substituted:
    <code>
    Private Sub cmdReport_Click()
    On Error GoTo ErrHandler
    DoCmd.OpenReport "rptCerts", acViewPreview, , "[ID]=" & Me.[ID]
    Exit Sub

    ErrHandler:
    If Err = 2501 Then
    ' Canceled, ignore
    Else
    MsgBox Err.Description, vbExclamation
    End If
    End Sub
    </code>
    In Me.[ID], ID is the name of the combo box (dropdown list) from which you select a part number.

  3. #3
    Lounger
    Join Date
    Nov 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating Forms (2000)

    Ok i was able to follow the first set of instructions to get the report to work, and it did.
    Thank you very much, this was driving me crazy.

    But to my second question, when I ran the print preview button i received the following message: " The database engine could not lock 'TblCerts' because it is in use by another person or process'. I double checked and i had nothing else open.

    I thing I ran in to trouble with the "[ID]=" & Me.[ID], what was I supposed to put here again. I copied the code below so you could see what I did.

    Private Sub cmdReport_Click()
    On Error GoTo ErrHandler
    DoCmd.OpenReport "RptCerts", acViewPreview, , "[ID]=" & PartNumber
    Exit Sub

    ErrHandler:
    If Err = 2501 Then
    ' Canceled,ignore
    Else
    MsgBox Err.Description, vbExclamation
    End If
    End Sub


    Any help would be greatly appreciated.

    thanks again!!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creating Forms (2000)

    The error message indicates that the table may have been open in design view. Try closing and reopening the database. Then open the form, and click the button.

  5. #5
    Lounger
    Join Date
    Nov 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating Forms (2000)

    I am still getting the error message, even after closing the whole database and only opening the one form?!

    DoCmd.OpenReport "RptCerts", acViewPreview, , "[ID]=" & PartNumber

    In this string of text what does the "[ID]=" & PartNumber stand for, I think that this is maybe where the problem lies.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creating Forms (2000)

    The 4th argument of DoCmd.OpenReport is the WhereCondition argument, it provides the criteria to filter the report.

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  7. #7
    Lounger
    Join Date
    Nov 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating Forms (2000)

    I'm trying to post a copy of the database but I am having problems zipping the program. Any suggestions?

    Thanks,
    Melanie

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creating Forms (2000)

    Do you mean that you don't have a zip utility, or that you do, but the zip file is too big?

  9. #9
    Lounger
    Join Date
    Nov 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating Forms (2000)

    I don't have a zip utility.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creating Forms (2000)

    If you have Windows XP, zipping is built in: right-click the database in Windows Explorer, and select Send To | Compressed Folder.
    Otherwise, you can download a free evaluation copy of WinZip from WinZip (click Try Now).

  11. #11
    Lounger
    Join Date
    Nov 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating Forms (2000)

    Ok, it should be attached

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creating Forms (2000)

    Open the report in design view.
    Activate the Other tab of the Properties window.
    Set Record Locking to None instead of All Records.
    That should do it...

  13. #13
    Lounger
    Join Date
    Nov 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating Forms (2000)

    Thanks!! That took care of the error message I was receiving, but now when I press the "PrintPreview" button on the form it is asking me to "Enter Parameter Value" ID?

    Any suggestions would be helpful!!

    Thanks Melanie

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creating Forms (2000)

    Sorry, forgot to mention that. The field is named PartNumber in both the form and the report, so the WhereCondition argument of OpenReport should be

    "PartNumber = " & Me.PartNumber

  15. #15
    Lounger
    Join Date
    Nov 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating Forms (2000)

    Ok, well that worked sort of. It is showing all of the reports that match the one part number that I bring up on the form, but not always with the attention I type in. Is there a way to have the report linked to the direct form that I have up? I am also creating many other reports based off of forms that are going to have a lot more fields than just (2).

    Thanks,
    Melanie

Page 1 of 2 12 LastLast

Posting Permissions

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