How to make unbound combobox bound in subform

[johnboytweed]

I have cascading comboboxes in a form I created. The combo boxes on the main form work fine, but I don't know how to edit the control source for the combo boxes on the subform. Right now the combo boxes are unbound, but I need them to be bound to a table in the subform. Does anyone know how to do this? I have researched a very long time, but I haven't come to any resolution for this.

Thank you for any help provided!


:banghead:

 

[mdlueck]

but I need them to be bound to a table in the subform.

You can not bind controls on one form to another form (it's data source) even if it is associated with the form via a subform control relationship.

What are you trying to accomplish? Perhaps if you explain the context, a suggestion of how to accomplish your end result will come to mind.

 

[johnboytweed]

I am trying to create a form that will bring several tables together so that information can be added to all tables at one time. When I use the Form Wizard it allows for me to pick one table and then to have all the other tables below it in a subform. Is there a better way to accomplish this?

 

[mdlueck]

so that information can be added to all tables at one time.

Yikes!!! What a multi-user locking nightmare! Here is my recommendation / style:

Client/Server Architecture
http://www.access-programmers.co.uk/forums/showpost.php?p=1110794&postcount=5

 

[johnboytweed]

I have no idea what I just read. I was trying to make a database for me and a few other people in order to get us off of the ever growing Excel sheet currently being used. I have had no other experience with Access other than this. I've spent a lot of time trying to make this work, but I'm not sure what else to do. I want this to work so badly because I have spent a very long time trying to make it happen.
I don't understand why I can't have combo boxes from different tables all function on the same form?
I just want to make it easier to fill in some of the repetitive information. There would only be maybe three or four people using this DB.
Is there something that I can follow out there that I can actually understand after I have read it?
I can attach my database if that helps, although I am sure I've done it all wrong now and need to start over. :banghead:

 

[mdlueck]

Having one form editing more than one record / more than one table at a time will result in multi-user locking issues. Sounds like you want a multi-spreadsheet form in Access which would allow free-wheeling group editing.

It is all right if you make a read-only dashboard form displaying many types of records each from the various tables. That is all right as that is read-only.

When Adding / Editing records, then you need to be working with only the one table / record, start with a blank form for Adding records, INSERT to the table to add when finished / or for Edit start with a SELECT to check out the one record, then to check the changes back in issue an UPDATE to commit the changes back to the table.

 

[johnboytweed]

So I need to house all the different tables into one table in order to have a form that will allow multiple users to edit records?
A "read-only" form doesn't help me at all because I need the form view to allow edits as well.

 

[johnboytweed]

Would a tabbed form or navigation form allow me to do have a form that can be edited without creating a multi-user locking issue?

 

[mdlueck]

Using fancy UI controls still has all of the tables open for read/write all of the time the form is open. That will not help you avoid problems.

 

[johnboytweed]

I understand what you are saying, but I thought the Access just locked the row of the table being edited. I didn't know it would lock the whole table.
The issue I deal with now in using Excel is similar. Excel wants to lock the whole spreadsheet and I was under the impression that Access only locked the row. There are only two or three people at most using the current spreadsheet, but many times there are lines being added or deleted so Excel asks which entry should be kept and which one should be ignored.
Because of that and because the information is getting so long I thought that Access might help us to keep the information more organized.
I apologize for the ignorance but I can assure you that I have been trying to tackle this for quite sometime. I guess I'm learning all the things that I can't do at least.

 

[johnboytweed]

Where can I find a "multi-spreadsheet" form? Is that the same as datasheet form?
Can this multi-spreadsheet form be created with the Form Wizard?

 

[mdlueck]

Likewise... and I do not mean to rain on your pretty thoughts, rather trying to assist you in avoiding Access pitfalls.

Desktop database programs such as Access more use page locks than row locks. An entire "page" of records end up getting locked. (Pages are internally tracked - you have no control over which records end up in which page.) That us why I took care to come up with a very transactional design. The application I have built for a client works extremely well even over remote VPN connections. (The BE DB is a SQL box, so simple Access database are not where the real data resides.)

With a little bit more up-front work, you can totally avoid so much common nonsense Access puts people through.

 

[mdlueck]

Where can I find a "multi-spreadsheet" form?

In Access, one form gets bound to one table.

To get views of multiple tables, you need to build / save those "one form per table" forms, then combine all of those forms onto one dashboard form by using subform controls to be able to display each of those individual forms.

For that dashboard form, just start with a completely blank form, then select your color theme, then switch to Edit more and place your first subform control / configure the control to display the form you want to show up in that area of the dashboard form.

 

[johnboytweed]

Okay. I have all the tables created to each individual form and I opened up a blank subform. When you say to "edit" are you speaking of the icon you select to add a subform? This is where you drag and drop the control/icon onto the form itself and the subform wizard creates this by asking a couple questions?
If that is correct, then I just do this for every form that I want to appear on this subform? After that is completed are you saying that I will be able to have a form that I can edit? Because what I need is what I will be calling an enrollment form. Will this get me there?
Thank you for your patience.

 

[mdlueck]

When you say to "edit" are you speaking of the icon you select to add a subform?

The mode of the form... By "edit" in Access lingo they call that Design View. When you create a new form it starts in Layout View, which is the mode you need to be in to pick your color scheme theme. That is the only thing I use Layout View more for.

 

[johnboytweed]

Okay. So when inserting my forms into the subform do I just drag and drop or should I use the wizard (or does it matter?) I understand the designing aspects of Access because I have tried this so many times already, ugh..but..the way you are saying to do this, it will allow for multiple users? We are only talking maybe three at most, but two users consistently.

 

[mdlueck]

So when inserting my forms into the subform do I just drag and drop or should I use the wizard (or does it matter?)

No, you should be in Design Mode of the Form, and place a Subform control on the form. Then configure the Subform control to select the form you desire to occupy that subform control.

the way you are saying to do this, it will allow for multiple users? We are only talking maybe three at most, but two users consistently.

There is no way to safely have a Multiple Items form in Edit/Updatable mode in a multi-user environment, period. There is no magic shell game of using tabs or subform controls to make that completely safe and reliable.

You asked about being able to display multiple different tables. I answered that you need to use Subform controls so that each form can view records from different tables.

 

[johnboytweed]

So then why does the form view get used in the Northwinds Database?
What is the best way to allow multiple users to enter information into a database then?

 

[mdlueck]

So then why does the form view get used in the Northwinds Database?

Perhaps it was written to illustrated what Access as a stand-alone DB is able to do.

It does come from a company not passionate about quality.

What is the best way to allow multiple users to enter information into a database then?

I will again point you back to my post:

Client/Server Architecture
http://www.access-programmers.co.uk/forums/showpost.php?p=1110794&postcount=5

Something less, perhaps it will generally work, with a little annoying * next to the text leading to lots of exceptions and clauses. I do not spell quality with a K.

 

[johnboytweed]

The example of the Northwinds database opens with a pop up form that asks for the name of the person using the db. There are several names to choose from in this form so I can only assume that they are designing this as a multiple user interface. Obviously I can be wrong about this.
Thank you for redirecting me back to your link. Unfortunately I still don't know what the abbreviations mean throughout the information.
Thank you for your insight, but at the end of the day I might be further away from my goal of have a functioning database than I was at the beginning of the day. I deleted the forms that I had previously created and tried to follow your direction. I don't understand why this seems to be so complicated.
If anyone can assist me with a way to have a multi-user database layout that allows for a few people to simultaneously enter data please let me know.
I thought I could do this with a form, but I guess that isn't possible. I guess I just need to know what is possible with Access.

Thank you for your time.