Report shows part numbers in part description field 1

[mirgss]

Okay, I'm going to do my best to explain this.

I have a form "Estimate entry form" into which my users will enter data for an estimate - part number, description, and quantity. I have part number and part description in a combo box, and if the user selects a part number it will update the description and vice versa. This works perfectly (big thanks to those who helped!). However, I created a report which opens at the click of a button on the form with all the same data...and even though I have the control source set to

SELECT =[Forms]![Estimate entry form]![PartDescription1]

it returns the part number instead of the part description.

Does this make sense? Does anyone know how to make the part description show up in the part description box?

Thanks!

 

[Gammachaser]

The control source of what? That is not a control source. I would think you would get a syntax error from that statement (or maybe the equivalent of False.)

If you want your report to have the same information as your form you could use

= Forms![Estimate Entry Form]![PartDescription1]

without the 'Select' part and repeat that format for the other textboxes for the appropriate fields. Alternately, (and IMHO preferably) you could use the same record source for the report as you do for the form (make sure the record is saved) and pass the specific record in the 'WhereCondition' parameter when you open the report

Docmd.OpenReport "MyReport",acViewPreview,,"MyRecordID = " & ID

But, you can't use 'Select =' for anything.

 

[dhookom]

Do you really have "SELECT" in your Control Source?

Are you attempting to display a value from your form in your report output?

Is PartDescription1 a Text Box or Combo Box or other?

Does your report have a Record Source that includes the field that actually stores the part description?

Duane
Hook'D on Access
MS Access MVP

 

[mirgss]

No, there is not a SELECT statement in the control source. I am an idiot - sorry!

Dhookm: yes, I am trying to display a part description field (combo box) in my form in the part description field (text box with previously noted control source).

To generate the report, I have a button on the form that performs the following:

Private Sub GetReport_Click()

    Dim strDocName As String
    Dim strWhere As String
    strDocName = "Estimate"
    strWhere = "[ID] = " & Me.ID
    DoCmd.OpenReport strDocName, acPreview, , strWhere
End Sub

Should I change my control source (on the report) to the table instead of the form?

 

[dhookom]

I would expect the value you want to display to be in the Record Source of the report. I try not to have a control source on a report depend on a control name on a form name.

Duane
Hook'D on Access
MS Access MVP

 

[Gammachaser]

You can't use a form as a Control Source -- and a report does not have a Control Source... it has a Record Source.

A Record Source is a Table or query (either a saved query or a 'Select' statement) from which the report draws its records. A text box or combo box on a report (or form) has a Control Source which is either a field from the table or query, an equation (such as '= varible * constant') or a function (such as '= Date()' or '=CalcMarkup(cost, percent)')

You are mixing up you terms and in the process mixing up your self and the rest of us.

You don't have to have a Record Source for your form or report (but I usually recommend that you do) but you do need to have a Control Source for your controls, if you want them to have any data.

My first post has a recommended Control Source based on what I think it is you are trying to do.

 

[mirgss]

Sorry; I'm not trying to be annoying. In the property sheet of the text box in the report it says "control source" so I figured that's what it was. I have the text box set to what you suggested in your first post; but it just comes up with the part number. If I try setting it to the table that the form is bound to, that doesn't work either.

I'm really sorry if I'm being confusing. I'm pretty lost.

 

[dhookom]

mirgss,
Can you answer two questions you didn't before?

Is PartDescription1 a Text Box or Combo Box or other?

Does your report have a Record Source that includes the field that actually stores the part description?
Duane



Duane
Hook'D on Access
MS Access MVP

 

[mirgss]

PartDescription1 is a combo box that's linked to the PartNumber1 box in the form, but it is a text box in the report.

I have the record source as

SELECT Estimates.ID, Estimates.Date, Estimates.Representative, Estimates.Customer FROM Estimates;

Does that help?

 

[PHV]

What about this as ControlSource in the report ?
=[Forms]![Estimate entry form]![PartDescription1].Column(1)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

If the column doesn't work as PH has suggested, you can place a invisible text box on the form and set these properties:

Name: txtPartDescription
Visible: No
Control Source: PartDescription1.Column(1)

Then, in your report, reference txtPartDescription rather than the combo box.

Duane
Hook'D on Access
MS Access MVP

 

[mirgss]

I guess I should have realized I needed the Column(1) in there.

Thank you so much everyone for your help!