text box in report must be populated from combobox in the form

[sessionone]

Hi all,

I'm a newbie in the access world, so sorry if this appears to be a simple solution.

My simple database at the moment consists of two tables: one for customer information and the other for department names and fax numbers of those departments.

The first table was converted into a form. The form has a command button that runs a macro to open a report and a combo box that takes the values from the department field of the second table.

So basically, I'd like to have a textbox in the report that is populated with fax numbers from the departments combo box.

Please help, as I've been surfing for answers now for too long.

Thanks

 

[dhookom]

It would really help if you provided the significant table and field names as well as the relationships between the tables and maybe the field types.

It sounds like your report is based on the department table. The fax number should be in this table so you can add a text box and set its control source to the fax field.

Duane
Hook'D on Access
MS Access MVP

 

[sessionone]

Thanks,

Ok, a bit more info. For Customers table I have the following fields: first name, last name, customer number (a unique number, but it's not generated automatically), and department. The Departments table has two fields: Department Name and Fax No. All fields are text, but perhaps I should change the fax number field to numbers.

The Departments table only stores information, nothing will be added to it once it's completed. Only the customers table will be constantly updated.

As of now, the form consists first name, last name, customer number, and department. Department is a combo box linked to the Departments table.

On the report I have the customer names and the department name. So I once I select the department from the combo box in the form, I would like the relevant fax no. to appear on the report.

Hope this helps....

 

[dhookom]

Add the Departments table to the report's record source and join the Department and Department Name fields. Add the Fax No field to the query grid so you can display it in your report.

Duane
Hook'D on Access
MS Access MVP

 

[sessionone]

Thanks,

It's either I don't understand something or it's not working. It's probably the first one.....

 

[dhookom]

A report has a record source which is generally a table, query, or SQL statement. What is the record source of your report? Do you understand how to change it? The record source is the same as designing a query.

Duane
Hook'D on Access
MS Access MVP

 

[sessionone]

I do understand how to change it, but the auto report doesn't do what I want it to do. So I've decided to build it from scratch, but now the selection from the combo box appears only as an ID no. on the report. But I suppose it's a totally different question, so I'll have to study a bit more.

Thanks anyway for your help.

 

[dhookom]

You could just tell us what your report's record source is? Bottom line, if you want to display a value in a report, the table containing that value should be in the report's record source. I assume you used a lookup field which is causing the confusion. I recommend never using lookup fields for this and other reasons the evils of lookup fields.

Duane
Hook'D on Access
MS Access MVP

 

[sessionone]

ok,

the record source is the customers table. and, yes, while creating the customers table, i did use the lookup field that takes department names from the department table.

 

[dhookom]

Include the department table in the report's record source. Do you understand how to do this? It seems I have suggested this (or something similar) 4 times in this thread ;-)

Duane
Hook'D on Access
MS Access MVP

 

[sessionone]

Thanks,

No, it seems I don't understand how to do this...sorry

 

[dhookom]

Open your report in design view and display the report property dialog. You should see a property on the data tab of "Record Source". This needs to include both tables. You can click the builder button [...] on the right to go into a query designer. Make sure both tables are included and there is a join line between the common fields of the tables. Add all of the desired fields to the grid. Exit the query design and save the report. Then you can add the fields to your report from both tables.

Duane
Hook'D on Access
MS Access MVP