Manually storing Data to a field

[~~Ryan Rooney]

Ok, I have a form with a combo box, called "Location Combo" that queries a
table for business names, address, city, state, zip & phone. The combo box
only shows the business name. Once I choose a business name, six other fields
are automatically filled with the other information using...

=[Location Combo].[Column](2)
=[Location Combo].[Column](3)

etc...

Now, once I am done filling in the form I want to print that record...I
accomplished that by creating a button that opens a report using the Where
Condition...

[Jobs]![ID]=[Forms]![Edit_Job]![ID]

Now the problem is that the report won't show any of the information I
collected using

=[Location Combo].[Column](2)

it just gives me the ID number of the information I want. How do I fix that?
Thanks for any help you can give!

 

[Jeanette Cunningham]

Hi Ryan,
the report's data source would be a query based on the table that has the
business details.
You would include the necessary fields in the query, that way when the
report is opened, the values for business, address etc will show for the ID
you select to open it with.
You need to save the record in the form before you open the report.
Something like this:

Private Sub cmdReport()
If Me.Dirty = True Then
Me.Dirty = False
End If
DoCmd.OpenReport "NameOfReport",acviewpreview, ,"[JobID] = " & Me.JobID & ""


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Steve Schapel]

Ryan,

The Record Source of your report needs to be a query that includes the
table that has the location details. This will no doubt be the same
table that provides the Row Source of the Location Combo combobox on
your form. Put this table into the query, join it on the ID field to
the other applicable table, and then you will have the supplementary
Location information available to bind the controls on your report.

 

[~~Ryan Rooney]

The problem is it is two different tables. The form uses "Jobs." The table I
get the info for the "Location Combo" combo box is called "Associations."

Ryan,

The Record Source of your report needs to be a query that includes the
table that has the location details. This will no doubt be the same
table that provides the Row Source of the Location Combo combobox on
your form. Put this table into the query, join it on the ID field to
the other applicable table, and then you will have the supplementary
Location information available to bind the controls on your report.

--
Steve Schapel, Microsoft Access MVP

Ok, I have a form with a combo box, called "Location Combo" that queries a
table for business names, address, city, state, zip & phone. The combo box
only shows the business name. Once I choose a business name, six other fields
are automatically filled with the other information using...

=[Location Combo].[Column](2)
=[Location Combo].[Column](3)

etc...

Now, once I am done filling in the form I want to print that record...I
accomplished that by creating a button that opens a report using the Where
Condition...

[Jobs]![ID]=[Forms]![Edit_Job]![ID]

Now the problem is that the report won't show any of the information I
collected using

=[Location Combo].[Column](2)

it just gives me the ID number of the information I want. How do I fix that?
Thanks for any help you can give!

 

[~~Ryan Rooney]

What do you mean the report's data source would be a query based on the table
that has the business details?

To clarify...I am using a form who's control source is a table called "Jobs"
I wanted the form to automatically input the location of the job using
another table in the database called "Association" which stores all the
various locations we send entertainment to. So I created a query/combo box
and used the previously mentioned =[Location Combo] etc... to achieve that.

What I would really like to know is, how to store the information that was
auto entered for me into the "Jobs" table. Normally I would just make a new
field in the "Jobs" table and then set the Control Source to that field in
the form, however in this case the Control Source is now =[Location Combo]
etc.

Hi Ryan,
the report's data source would be a query based on the table that has the
business details.
You would include the necessary fields in the query, that way when the
report is opened, the values for business, address etc will show for the ID
you select to open it with.
You need to save the record in the form before you open the report.
Something like this:

Private Sub cmdReport()
If Me.Dirty = True Then
Me.Dirty = False
End If
DoCmd.OpenReport "NameOfReport",acviewpreview, ,"[JobID] = " & Me.JobID & ""


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Ok, I have a form with a combo box, called "Location Combo" that queries a
table for business names, address, city, state, zip & phone. The combo box
only shows the business name. Once I choose a business name, six other
fields
are automatically filled with the other information using...

=[Location Combo].[Column](2)
=[Location Combo].[Column](3)

etc...

Now, once I am done filling in the form I want to print that record...I
accomplished that by creating a button that opens a report using the Where
Condition...

[Jobs]![ID]=[Forms]![Edit_Job]![ID]

Now the problem is that the report won't show any of the information I
collected using

=[Location Combo].[Column](2)

it just gives me the ID number of the information I want. How do I fix
that?
Thanks for any help you can give!

 

[Steve Schapel]

Ryan,

Yes, I know that.

But we are not worrying about the form now. Sounds like you've got that
working satisfactorily.

But you were asking about the Report. Is that still what you want help
with? If so, this is what I was trying to say before:

Make a Query, that includes *both* tables. In the query, you will Join
the two tables on the ID field. This query will then allow you to
return all the data from the Jobs table *and* the Associations table.

Then, make this query as the Record Source of the report. Hey presto!

 

[~~Ryan Rooney]

Ok, I see what your saying, will this also work if I want to print the
current record on the form?

 

[John W. Vinson]

On Tue, 28 Oct 2008 17:51:01 -0700, ~~Ryan Rooney

Yes, if you use a reference to the appropriate control or controls on the form
as a criterion in the Query.

 

[~~Ryan Rooney]

thank you!

On Tue, 28 Oct 2008 17:51:01 -0700, ~~Ryan Rooney

Yes, if you use a reference to the appropriate control or controls on the form
as a criterion in the Query.