How do I bind a table in Access 2013 to a form

BLACK THANOS
BLACK THANOS used Ask the Experts™
on
Good evening Experts,

It has been a few years since I have dabbled in access databases and forms  used as front ends. What I am trying to do is to populate fields on the form from a drop down menu. I will show you an example below to show you what I am attempting.

Here is the form view:
!Here is the design view
!
As you can see I have a table  but it is not completely linked to the form. I know that I should have created the table then highlighted it to create an automatic form , but I didn't do it that way. I put the egg before the chicken so to speak, meaning that I created the form from scratch based upon a word document: see picture below:

!
From the form I am attempting to populate the address, city, state and zip fields from the drop down menu that has all of the contract numbers (unique). However when I try to choose a contract from the drop down menu it gives the following error:

Control cant be edited. It is bound to the expression  [tsgCustomerTbl]![CustomerName]

I thought that I had bound the table to the form when I formated the properties sheet as follows the appropriate control source, row source and row source type

!
!
!
!
You will notice I tried to bind customer name:


!
!
!

So now you know as much as I do. I am hoping someone can help me find a way  to help me achieve  the population of the aforementioned fields from the table to the form.
Thank you in advance for your support.

Regards,
BlackThanos
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer
Commented:
I think you overcomplicating things ...just set the Record Source of the Form to the Table you want and then set each's control Control Source to the corresponding table field..
BLACK THANOSSemi - Retired

Author

Commented:
Hi John,
I know I am over complicating things , but dont really want to start over. Is it possible with what I have now to , as you say, " Just set the Record Source of the form to the Table you want..." I clicked on the details of the form itself and found  the  property to set the Record Source, but it doesn't accomplish what I want , and that is to use a drop down menu to bring of records and not  the  previous and next records.
Most Valuable Expert 2012
Top Expert 2014
Commented:
If you bind a control in the manner you're currently doing, you cannot edit. That's the way Access behaves, and you can't get around that.

As John suggests, bind the form directly to the table, and set the ControlSource of each control to table field that will store that data. If you want to include search features, you can do that as well, but it's best to set things up the correct way now, before you get any deeper.

To populate your fields, AFTER binding the table to the form, you can do something like this:

Me.Filter = "SomeField=" & Me.YourSearchCombo
Me.FilterOn = True
Distinguished Expert 2017
Commented:
The "=" preceding the column name tells Access that this is a calculated value and therefore the control is Unbound and not updateable.

Once you select the table as the RecordSource for the form, each control's ControlSource property will show you a list of available columns from the form's recordsource.  Select the appropriate column as the ControlSource and copy that name and paste it into the Name property for the same control.  You do NOT want to end up with controls named text145.  You want the controls to either have names that match the bound column name exactly or use a naming convention that uses a prefix.  So the control's ControlSource is StartDate but the  control's Name property is EITHER StartDate or if you use a prefix convention then it would be txtStartDate.

If you need data from more than one table to fill the form, create a query that joins the tables and use the query as the RecordSource for the form.

Once the form is properly bound, you can add a combo to the form's header that you can use for searching.  This combo will be unbound and you should let the wizard build it so delete any combo you currently have for this purpose.  You need a bound control in the detail section of the form to display/update the actual field but the search is controls from a separate control that is unbound.  The wizard will give you three options, one of which is find a record on this form.  Choose that one and it will build a macro for you.  you can use the convert macro tool if you want to convert the macro to VBA so you can see what it does.

PS, you should probably rethink using Long Text (memo) as the data type for the name and address.  Short Text goes up to 255 characters which is plenty long enough for both fields.
BLACK THANOSSemi - Retired

Author

Commented:
Thank you all for your input.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial