Modifying templates and linking to new tables

[gshock]

I'm using the Sales funnel template in Access, and I want to add a new field "Company". I already have a Client_List database. So instead of duplicating, I want to link to that Company field in the existing list. I have already linked it in my current Sales funnel database, and it shows up as an Unassigned Object. I created a new row in the Opportunities Details form, but I can't figure out how to link the new Combo Box to the Company field.

If I have the same company name show up for more than one client, do I have to run a query on the Company field in the Client_List table to remove the duplicates?

I guess, maybe a better question is, can I create a combo box list from the values in another field, in another table?

 

[gshock]

I realize that my level of understanding with Access is very basic, ala Noob or Novice, at best. But I'm a fairly proficient (not Advanced, by any means) user of SQL language and Excel. So I understand how to make some things work in Excel, but I can't duplicate that in MS Access. After fighting with this a bit, I have figured out that I can populate a dropdown list with the names of the companies by using a SELECT DISTINCT

 

[gshock]

I realize that my level of understanding with Access is very basic, ala Noob or Novice, at best. But I'm a fairly proficient (not Advanced, by any means) user of SQL language and Excel. So I understand how to make some things work in Excel, but I can't duplicate that in MS Access. After fighting with this a bit, I have figured out that I can populate a dropdown list with the names of the companies by using a SELECT DISTINCT query to populate the list. Then using that query as the Control Source on the form. Two bugs have come up with this, or I screwed something up and don't know how to fix it.

1. The dropdown does not let me select a name from the list when creating a new contact entry. I can click the dropdown and see the company names, but I can't select one of them
2. When I opened the Contact List, all of the Company Names were missing. I had to remove the field and re-add it to the form. Once I did, everything populated correctly. I wasn't able to get this corrected simply by refreshing the Contact List form.

 

[Micron]

1. likely that you based the form on a query that is not updatable, assuming the combo is bound to a query field. That would be useful information in your post.
If the form is based on a query and the combo is bound to a query field, open the query directly and see what you can and cannot manually update by editing in the query fields.

 

[gshock]

1. likely that you based the form on a query that is not updatable, assuming the combo is bound to a query field. That would be useful information in your post.
If the form is based on a query and the combo is bound to a query field, open the query directly and see what you can and cannot manually update by editing in the query fields.

Yes, the query is bound to a query field ... not because I felt that was the best way to do it, more because I tried to create a list of unique values, and this accomplished that. But I had no idea what the effect was going to be when linking it to the combo box.

I messed around with the query Design and the SQL view, but I didn't know what else to change to troubleshoot this. I know I can use the Criteria and Or boxes, but I don't have a clue what commands will accomplish what I'm looking to do.

 

[Micron]

I was answering this issue:

The dropdown does not let me select a name from the list when creating a new contact entry. I can click the dropdown and see the company names, but I can't select one of them

which is why I wrote

open the query directly and see what you can and cannot manually update by editing in the query fields.

I'm not seeing a connection between that issue and your last post. I can't assist further with that issue without knowing the answer to the suggestion.
One thing to be aware of is that selecting a different value in a bound combo will edit the field it is bound to. OK if intentional, not so good if a user is playing around and thinks that they'll just cancel their changes by closing the form and not clicking save on the ribbon. It doesn't work that way.

 

[Micron]

BTW, I can't recall ever using a template, preferring to just roll my own. IIRC, you can't modify their structure - you have to save as a new db project and modify that. Perhaps that is what your issue is? Not sure exactly what you're trying to do.

 

[gshock]

I was answering this issue:

which is why I wrote

I'm not seeing a connection between that issue and your last post. I can't assist further with that issue without knowing the answer to the suggestion.
One thing to be aware of is that selecting a different value in a bound combo will edit the field it is bound to. OK if intentional, not so good if a user is playing around and thinks that they'll just cancel their changes by closing the form and not clicking save on the ribbon. It doesn't work that way.

I misunderstood what you meant by "open the query directly". I thought you meant open the query to edit it. Now that I understand your suggestion, my feedback will probably be a lot more helpful ... at least I hope so.

When I place my cursor in the entries for Company name, I can select, move the cursor forward and back, but I cannot edit the values in the field.

BTW, I'm not tied to using templates. I have very little experience with Access, so I just started somewhere looking for examples and "go-by" methods to use for my own needs. I'm also trying to break things and figure out how to fix them, so I can become more proficient.

 

[Micron]

I cannot edit the values in the field.

See if any of these possibilities are the cause:

Microsoft Access tips: Why is my query read-only?

List of reasons why a Microsoft Access query will yield non-editable results.

allenbrowne.com

I want to link to that Company field in the existing list

You linked that table to your new db then? As I mentioned, don't try to modify the template. Create accdb file from it and modify the accdb.

 

[gshock]

See if any of these possibilities are the cause:

Microsoft Access tips: Why is my query read-only?

List of reasons why a Microsoft Access query will yield non-editable results.

allenbrowne.com

You linked that table to your new db then? As I mentioned, don't try to modify the template. Create accdb file from it and modify the accdb.

It appears to be caused by the fact that I used a "DISTINCT" in the query, because my original table has the same company name repeated multiple times, but different contact names at the same company. For example, Company A is listed three times, because John Smith, Jane Doe, and Joe Blitzflick all work for the same company, but each has different contact information.

What I was trying to do is populate the Company Name field on the New Contact Form with the list of possible choices of "Company Name" already in the table. Suppose that I already have an entry for Company A, but I have a new contact person at that company. I was trying to avoid user-error by having someone re-type an entry that's already there. So they could select it from a list. I did figure out how to populate the list in the Combo Box manually, but I couldn't find a way to populate the list with the Company Names. If I point to the Company Name field in the Contact List Table, then it puts every entry into the Combo Box.

Forgive the ignorance, but don't know what you mean by this:

Create accdb file from it and modify the accdb.

I just opened the template and did a Save As. Personally, I don't have any heartburn starting from scratch with nothing more than a table imported from Excel.

Back to one of your earlier questions about what I'm trying to do. I am trying to take the Excel contact list that someone else created and build both a Contact List and an Opportunity List in Access (I can start a new thread on this topic), but this is what got me here. I figured I would start with the MS Access Sales Funnel, and bastardize it from there. But it's far less practical than I had anticipated. Now that I know a little bit about making and braking forms, I think it's probably faster to make my own.