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

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
BLACK THANOSAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
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..
1
BLACK THANOSAuthor 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.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
0
PatHartmanCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BLACK THANOSAuthor Commented:
Thank you all for your input.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.