Modifying templates and linking to new tables

G

gshock

New Member
Joined
Jan 26, 2013
Messages
33
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
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
 
Upvote 0
gshock said:
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.
Click to expand...
 
Upvote 0
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.
 
Upvote 0
Micron said:
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.
Click to expand...
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.
1696418461139.png


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.

1696418572578.png
 
Upvote 0
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
Click to expand...
which is why I wrote
open the query directly and see what you can and cannot manually update by editing in the query fields.
Click to expand...
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.
 
Upvote 0
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.
 
Upvote 0
Micron said:
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.
Click to expand...
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.
 
Upvote 0
Upvote 0
Solution
Micron said:
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.
Click to expand...
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.
Click to expand...
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Primary Key versus Foreign Key
2
Replies
14
Views
757
jackd
J
C
  • Question
Linked Excel table in Access - field values change as I navigate?
Replies
5
Views
180
welshgasman
welshgasman
H
  • Solved
ACCESS suitability for multiple column "TEMPLATE" concept
Replies
4
Views
441
heathball
H
K
  • Solved
Automaticaly Sum multiple fields in a form
Replies
9
Views
618
welshgasman
welshgasman
K
  • Question
Query that can filter using field name
Replies
1
Views
512
Micron
M

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top