Adding field to a form that is not on the Field List 1

[gbuchman1]

Hi,
I created a form using Form Designer and am now trying to add a field to the form. I know that if fields are in the field list, they can be added to the form easily, and I have done this. However, when I created the form, I must have forgotten to include one field, because it is not in the Field List. Therefore I cannot see how to add it to the form. Is there a way to still add it to the form? I havent been able to do it. The closest I got was when I thought I had added the field, but the value in the field said "#Name?". I could not get it to display the proper name of the field or the value. I do not want to have to scrap my form to add this field. Please assist. Thanks.

 

[dhookom]

You need to check or modify the form's record source property. This might be a query or select statement that doesn't include the field. You may need to add it.

Duane
Hook'D on Access
MS Access MVP

 

[gbuchman1]

I am working with an Invoice Entry form. I see from the form properties that the form is based on the InvoiceMaster table, which does not include the field that I want to display. Basically, the table contains Location, but I also want to see Facility. Location is the number of the facility, whereas Facility is the name of the facility. Such as 26 New Brunswick. 26 is Location and New Brunswick is Facility. There is a table called Facilities which contains a list of all of them, the locations and the corresponding facilities. Can I just display the Facility from the Facilities table on the form, even though just the Location will entered in the InvoiceMaster table?

 

[PHV]

Why not bind your form to a query joining the relevant tables ?

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

 

[gbuchman1]

Sounds good, but Im not sure how to do that. Also, I dont want to populate the second table (the Facilities table). I only want to populate the InvoiceMaster and InvoiceDetail tables as invoices are entered by the user. Would it be easier to just enter a List box? I did this and it looks ok.

 

[randy700]

When you add the new text box, set the Control Source to:
=Dlookup("Facility", "Facilities", "Location = " & txtLocation)

Replace txtLocation with the name of the text box you all ready have that displays the location number.

Randy

 

[dhookom]

I would not use DLookup(). PH's solution of linking the table is more efficient and will work well if the tables can be joined on primary and foreign key fields. You can lock the text boxes you don't want updated by the users.

My second option would be to use a combo box to display the field value from the related table.

Duane
Hook'D on Access
MS Access MVP

 

[gbuchman1]

Hi Randy, When I set the Control Source on the new text box, it says #Error. I get this no matter what record I scroll to, although the Location does show in my earlier field.

 

[gbuchman1]

Dhookam, the tables are already linked in Relationships. Is that enough? If not, then Im not sure how to bind a form to a query.

 

[dhookom]

If I understand the structures of your tables, you should be able to set the Record Source of your form to:

SELECT InvoiceMaster.*, Facilities.Facility
FROM InvoiceMaster INNER JOIN FACILITIES ON InvoiceMaster.Location = Facilities.Location;

You may need to change the table or field names to match your names.

Duane
Hook'D on Access
MS Access MVP

 

[gbuchman1]

Thanks Duane, that worked great! Now the facility shows up nicely next to the location!

 

[dhookom]

As I stated earlier, you might want to set the Locked property of the Facility to Yes. I would also set the Enabled to No.

Duane
Hook'D on Access
MS Access MVP

 

[gbuchman1]

Will do. Thanks again!