VBA code prompts for primary key instead of auto-populating from form?

[highmeh]

I have an access form with a command button, cmdPrint. I got some source code from another access website to display a report, which is linked to a query, and only display the report number on my form (which is also my primary key)

Now when i click the cmdPrint button, it prompts me with "Print_PO_Query" but i have no constraints set in that query that it should be asking for. Here is my VBA Code which is in onclick for cmdPrint. Any help would be greatly appreciated!

Private Sub cmdPrint_Click()
Dim strWhere As String

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "[PONumber] = " & Me.[PONumber] & ""
DoCmd.OpenReport "Print PO", acViewPreview, , strWhere
End If

End Sub

 

[RuralGuy]

It is *always* best to do a Copy/Paste when posting code and it is nice to enclose the code in the code tags. The code you posted will not compile so I suspect you did not Copy/Paste. You will never see a NewRecord after the Me.Dirty = False so the first part of the following If...Else...End If will never execute. If [PONumber] is a text field then you need to surround the value with quotes, single or double.
ie: strWhere = "[PONumber] = " & Chr(34) & Me.[PONumber] & Chr(34)

 

[highmeh]

Actually this is an exact copy/paste. I have modified the code to:

Private Sub cmdPrint_Click()
    Dim strWhere As String

    strWhere = "[PO Number] = """ & Me.[PO Number] & """"
        DoCmd.OpenReport "Print PO", acViewPreview, , strWhere
        
End Sub

However, the same problem exists; i'm prompted with "Print_PO_Query"

I want it to use the information found in [PO Number]

 

[RuralGuy]

Hmmm...
The WhereCondition string only applies a filter to the query of the report. Is the report based on a query named "Print_PO_Query"? Can you run the report by itself directly? The [PO Number] field *is* a text field, right?

 

[highmeh]

Hmmm...
The WhereCondition string only applies a filter to the query of the report. Is the report based on a query named "Print_PO_Query"? Can you run the report by itself directly? The [PO Number] field *is* a text field, right?

Yes it is a text field. Print_PO_Query is the query the report is based on. If i run it by itself it populates correctly but prints out all the POs i have in the database. I just want it to print out the Purchase Order in [PO Number] on the form

 

[RuralGuy]

...it prompts me with "Print_PO_Query"...

How is it doing that? Is it a MsgBox with some buttons or an InputBox asking for a parameter? Does your "Print_PO_Query" include the [PO Number] field?

 

[highmeh]

How is it doing that? Is it a MsgBox with some buttons or an InputBox asking for a parameter? Does your "Print_PO_Query" include the [PO Number] field?

I've been playing with this for a while and i'm really not getting anywhere useful. It now prompts for "PO Number" with an input box but regardless of which number i enter, it still shows all items in the table.

Maybe you can help me re-write the code from scratch, i just want to click the button (cmdPrint) and have it look at [Forms]![PO Table]![PO Number], display the report "Print PO" for only the specified PO number, not the entire table.

Thanks.

 

[RuralGuy]

Any chance you can post your db? Strip any sensitive data but leave enough test data to demonstrate the issue.

 

[highmeh]

Any chance you can post your db? Strip any sensitive data but leave enough test data to demonstrate the issue.

Here it is stripped of sensitive data. Please excuse how terrible it looks, i had to jumble things around a bit to remove all the company info.

The test db was larger than the 393k limit this forum has so i sent it out on MegaUpload, hope that is ok.

http://www.megaupload.com/?d=JQP9Y9Y5

Thanks again

 

[RuralGuy]

If you use Tools>Database Utilities>Compact and Repair Database... you can shrink your db down quite a bit. Your field in the PO_Table was named [PONumber], not [PO Number] and it was a numeric field and not a text field. See if this is what you wanted.

 

[RuralGuy]

And your report was based on the PO_Table and not the PO_Table_Query.

 

[highmeh]

And your report was based on the PO_Table and not the PO_Table_Query.

Awesome, THANKS for the help!! Everything is working great now, thanks again!!

 

[RuralGuy]

Glad I could help.