combo box on form to update different table

Access 2000, Windows2000

Several tables:
ratings has fields: TrxID*, name, id, game, and shift, plus 20 others 
customers has fields: Cname,Cid, StAdd, City, and so on  
GAmes includes fields: name*
Shift includes fields: shiftname*

I have created a form to update the ratings table.
problem fields on this form are lookups 
I want the name field to allow selection form those entries in the customer 
table and display the selected name and ID in two separate fields on the 
form, the ID should then be stored in the ratings table. The name( on the 
form is for user input selection only) since it is already stored in the 
customer table.

My name field on the form is defined as a combo box, with its control; 
source = [customers]![Cname].  The rowsource = SELECT [Customers].[Cname], 
[Customers].[Cid] FROM Customers.
This currently displays the fields availble( from the table) but will not 
allow me to select anything. The status row display the message " Control 
can't be edited, it's bound to the expression [customers]![name]"  

I have verifed that the enabled properties is set to yes, and teh locked 
property is set to no.

How can I get this field to allow selection of a record form the displayed 
table and also fill in the CID value on the form. This CID value must be 
saved as part of the ratings table. 

Thanks to anyone who can point me in the right direction.


0
Utf
4/23/2007 7:12:01 PM
access 16762 articles. 3 followers. Follow

5 Replies
1421 Views

Similar Articles

[PageSpeed] 6

I may not be following, but it sounds like you have the combo box source set 
up opposite of the way I usually see it.

My comboboxes that allow selection of a "customer" do so by putting the 
CustomerID in the first column and the CustomerName (actually, [LastName] & 
", " & [FirstName]) in the second column.  I then hide the first column 
(folks don't want to have to remember IDs, they're much better with names, 
anyway!) by setting the width to "0" (zero).  That way, the user selects a 
name from the combo box, and the control stores the ID.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"JR Hester" <JRHester@discussions.microsoft.com> wrote in message 
news:37E1449B-BEEC-4E95-A425-CCFA640058D9@microsoft.com...
> Access 2000, Windows2000
>
> Several tables:
> ratings has fields: TrxID*, name, id, game, and shift, plus 20 others
> customers has fields: Cname,Cid, StAdd, City, and so on
> GAmes includes fields: name*
> Shift includes fields: shiftname*
>
> I have created a form to update the ratings table.
> problem fields on this form are lookups
> I want the name field to allow selection form those entries in the 
> customer
> table and display the selected name and ID in two separate fields on the
> form, the ID should then be stored in the ratings table. The name( on the
> form is for user input selection only) since it is already stored in the
> customer table.
>
> My name field on the form is defined as a combo box, with its control;
> source = [customers]![Cname].  The rowsource = SELECT [Customers].[Cname],
> [Customers].[Cid] FROM Customers.
> This currently displays the fields availble( from the table) but will not
> allow me to select anything. The status row display the message " Control
> can't be edited, it's bound to the expression [customers]![name]"
>
> I have verifed that the enabled properties is set to yes, and teh locked
> property is set to no.
>
> How can I get this field to allow selection of a record form the displayed
> table and also fill in the CID value on the form. This CID value must be
> saved as part of the ratings table.
>
> Thanks to anyone who can point me in the right direction.
>
> 


0
Jeff
4/23/2007 8:19:33 PM
Oh yes, and I set the bound column to 1 (the ID).

Regards

Jeff Boyce
Microsoft Office/Access MVP

"JR Hester" <JRHester@discussions.microsoft.com> wrote in message 
news:37E1449B-BEEC-4E95-A425-CCFA640058D9@microsoft.com...
> Access 2000, Windows2000
>
> Several tables:
> ratings has fields: TrxID*, name, id, game, and shift, plus 20 others
> customers has fields: Cname,Cid, StAdd, City, and so on
> GAmes includes fields: name*
> Shift includes fields: shiftname*
>
> I have created a form to update the ratings table.
> problem fields on this form are lookups
> I want the name field to allow selection form those entries in the 
> customer
> table and display the selected name and ID in two separate fields on the
> form, the ID should then be stored in the ratings table. The name( on the
> form is for user input selection only) since it is already stored in the
> customer table.
>
> My name field on the form is defined as a combo box, with its control;
> source = [customers]![Cname].  The rowsource = SELECT [Customers].[Cname],
> [Customers].[Cid] FROM Customers.
> This currently displays the fields availble( from the table) but will not
> allow me to select anything. The status row display the message " Control
> can't be edited, it's bound to the expression [customers]![name]"
>
> I have verifed that the enabled properties is set to yes, and teh locked
> property is set to no.
>
> How can I get this field to allow selection of a record form the displayed
> table and also fill in the CID value on the form. This CID value must be
> saved as part of the ratings table.
>
> Thanks to anyone who can point me in the right direction.
>
> 


0
Jeff
4/23/2007 8:20:02 PM
Have solved the initial question regarding the "control can't be edited..."

But am still struggling with getting the CID field to be populated based on 
the customer selected in the customer name field combo box. 

"JR Hester" wrote:

> Access 2000, Windows2000
> 
> Several tables:
> ratings has fields: TrxID*, name, id, game, and shift, plus 20 others 
> customers has fields: Cname,Cid, StAdd, City, and so on  
> GAmes includes fields: name*
> Shift includes fields: shiftname*
> 
> I have created a form to update the ratings table.
> problem fields on this form are lookups 
> I want the name field to allow selection form those entries in the customer 
> table and display the selected name and ID in two separate fields on the 
> form, the ID should then be stored in the ratings table. The name( on the 
> form is for user input selection only) since it is already stored in the 
> customer table.
> 
> My name field on the form is defined as a combo box, with its control; 
> source = [customers]![Cname].  The rowsource = SELECT [Customers].[Cname], 
> [Customers].[Cid] FROM Customers.
> This currently displays the fields availble( from the table) but will not 
> allow me to select anything. The status row display the message " Control 
> can't be edited, it's bound to the expression [customers]![name]"  
> 
> I have verifed that the enabled properties is set to yes, and teh locked 
> property is set to no.
> 
> How can I get this field to allow selection of a record form the displayed 
> table and also fill in the CID value on the form. This CID value must be 
> saved as part of the ratings table. 
> 
> Thanks to anyone who can point me in the right direction.
> 
> 
0
Utf
4/23/2007 8:20:06 PM
If you are using a combo for searching, it should not also be bound.  Use a 
separate bound field that will appear only when the form is on a new record. 
Put the following in the form's Current event:

If Me.NewRecord then
    Me.SomeCombo.Visible = True
Else
    Me.SomeCombo.Visible = False
End If

"JR Hester" <JRHester@discussions.microsoft.com> wrote in message 
news:37E1449B-BEEC-4E95-A425-CCFA640058D9@microsoft.com...
> Access 2000, Windows2000
>
> Several tables:
> ratings has fields: TrxID*, name, id, game, and shift, plus 20 others
> customers has fields: Cname,Cid, StAdd, City, and so on
> GAmes includes fields: name*
> Shift includes fields: shiftname*
>
> I have created a form to update the ratings table.
> problem fields on this form are lookups
> I want the name field to allow selection form those entries in the 
> customer
> table and display the selected name and ID in two separate fields on the
> form, the ID should then be stored in the ratings table. The name( on the
> form is for user input selection only) since it is already stored in the
> customer table.
>
> My name field on the form is defined as a combo box, with its control;
> source = [customers]![Cname].  The rowsource = SELECT [Customers].[Cname],
> [Customers].[Cid] FROM Customers.
> This currently displays the fields availble( from the table) but will not
> allow me to select anything. The status row display the message " Control
> can't be edited, it's bound to the expression [customers]![name]"
>
> I have verifed that the enabled properties is set to yes, and teh locked
> property is set to no.
>
> How can I get this field to allow selection of a record form the displayed
> table and also fill in the CID value on the form. This CID value must be
> saved as part of the ratings table.
>
> Thanks to anyone who can point me in the right direction.
>
> 


0
Pat
4/23/2007 8:40:42 PM
Thanks to both Pat and Jeff, and I must also acknowledge Ken Snell and his 
post at http://www.mvps.org/access/forms/frm0058.htm. Between all three 
resources, my issue has been satisfactorily resolved.

This is a great forum for all of us.

"Pat Hartman (MVP)" wrote:

> If you are using a combo for searching, it should not also be bound.  Use a 
> separate bound field that will appear only when the form is on a new record. 
> Put the following in the form's Current event:
> 
> If Me.NewRecord then
>     Me.SomeCombo.Visible = True
> Else
>     Me.SomeCombo.Visible = False
> End If
> 
> "JR Hester" <JRHester@discussions.microsoft.com> wrote in message 
> news:37E1449B-BEEC-4E95-A425-CCFA640058D9@microsoft.com...
> > Access 2000, Windows2000
> >
> > Several tables:
> > ratings has fields: TrxID*, name, id, game, and shift, plus 20 others
> > customers has fields: Cname,Cid, StAdd, City, and so on
> > GAmes includes fields: name*
> > Shift includes fields: shiftname*
> >
> > I have created a form to update the ratings table.
> > problem fields on this form are lookups
> > I want the name field to allow selection form those entries in the 
> > customer
> > table and display the selected name and ID in two separate fields on the
> > form, the ID should then be stored in the ratings table. The name( on the
> > form is for user input selection only) since it is already stored in the
> > customer table.
> >
> > My name field on the form is defined as a combo box, with its control;
> > source = [customers]![Cname].  The rowsource = SELECT [Customers].[Cname],
> > [Customers].[Cid] FROM Customers.
> > This currently displays the fields availble( from the table) but will not
> > allow me to select anything. The status row display the message " Control
> > can't be edited, it's bound to the expression [customers]![name]"
> >
> > I have verifed that the enabled properties is set to yes, and teh locked
> > property is set to no.
> >
> > How can I get this field to allow selection of a record form the displayed
> > table and also fill in the CID value on the form. This CID value must be
> > saved as part of the ratings table.
> >
> > Thanks to anyone who can point me in the right direction.
> >
> > 
> 
> 
> 
0
Utf
4/23/2007 9:26:01 PM
Reply:

Similar Artilces:

Excel 97 dropdown function box
The dropdown function box on the Formula Bar show formulas but will no longer show "More Functions". How can I get this item back? ...

String Table (VC6 IDE)
I have strings in English language in the "String Table" of my project (myProject.rc). I'm loading them using: CString msg; msg.LoadString(150); Now, I need to internationalise my app. How can I do that? How can I add support for multiple languages? Which is the best way to do that? >I have strings in English language in the "String Table" of my project >(myProject.rc). I'm loading them using: > >CString msg; >msg.LoadString(150); > >Now, I need to internationalise my app. How can I do that? How can I add >support for multiple lang...

Controlling printed records when report bound to multiple tables
I created a report that uses the control from a form to generate a report based on that record's primary key. This form also has a subform which has relationships tied to the primary key for record identification and is linked to the main table. When preview the report the data from the subform either does not show up in the preview when using the filter [control]=[form]![control].[value] or makes multiple copies of the report equal to the number of entries in the subform's table. Is there any way around this? I have tried queries but have not found a way to use a f...

2007
Project desktop expert, new to Project Server 2007 and working with a sandbox implementation currently. When I use the PWA Build Team...Replace to replace a generic resource on a project with an actual named resource after first publishing a plan, my updates don't appear to be reflected in Project Server. If I check out and open the Project Plan in MSP Pro, however, the updates were made, and then if I publish the schedule, the updates appear in server. I thought when you made resource updates using PWA that they should be reflected immediately (or as soon as the request...

pivot table %
I have 2 columns in a pivot table - decription and amount. I need to calc a % of each value of the total. I don't know how to do that. ...

Enable Canadian Tax Detail option should update cost on receipt la
Would like the Enable Canadian Tax Detail option in the Company Setup Options window to work like how Landed Costs work. For example, if I select to post my tax detail to the Inventory account, I would like it to not only update my Inventory account in the General Ledger, but also to update the cost on the Receipt Layer in Inventory so when the Item is sold, it will sell at the Item Unit Cost plus the tax amount. The voucher created in Payables Management would just include the Item Cost without the tax amount. ---------------- This post is a suggestion for Microsoft, and Microsoft re...

Transferring Field from Existing Table/limitations and change of d
Thank you in advance for your help! I have two Excel spreadsheets that I successfully imported into Access 2003 and created tables for. I need to add the field from one table to the other, but there is not a direct match in the relationship. The large table uses the Employee ID as the primary key. The smaller table contains one field that lists a subset of these Employee ID numbers (a selection of certain employees). I need to transfer this field to the larger table, but I do not know how to tell Access to match up the corresponding numbers (i.e., the large table lists all employees, bu...

Line Graph with two different data points
Hello, I am trying to create a line chart with numbers from my product Vs a competitor. The problem i am having is how can i make a chart that will have four data points comparing to each other? For example Product A- 1.5 1.2 1.6 1.8 Product B- 2.0 1.1 1.2 1.3 Product A- 70% 20 % 30% 67% Product B- 65% 30% 65% 55% How do i get all of these numbers on one chart? Is a line chart correct. I want to see these numbers compared?? Thank you so much Hi, Since the number don't appear related to the percentages you might plot them on the same chart but two different axes. If they were relate...

Query cross two table
Hi, I have two tables, tbAdmission and tbCode. In my tbAdmission, I have Code1, Code2 and Code3. In my tbCode, I have Code, Description1 and Description2. In my Form, frmAdmission, I have txtCode1, txtCode2 and txtCode3 that are all bounded to tbAdmission. And txtDescription1Code1, txtDescription2Code1, txtDescription1Code2, txtDescription2Code2, txtDescription1Code3 and txtDescription2Code3 that are unbounded and only for displaying the descriptions. txtCode1, txtCode2 and txtCode3 all refer to Code in tbCode to retrieve Description1 and Description2 for displaying in the unbou...

Conversion Errors Table
Hello, I'm new to working with Access, I just converted an Access 97 databas into Access 2002. It tells me there were errors, and to look at th Conversion Errors Table. But nowhere in the message or in the MS Hel is there anything telling me where to find this table. Can someon help? Thanks Patric -- psha ----------------------------------------------------------------------- pshaw's Profile: http://www.officehelp.in/member.php?userid=493 View this thread: http://www.officehelp.in/showthread.php?t=125029 Posted from - http://www.officehelp.i I'd expect to find it in the new...

validation list or combo box dependant on cell value
Am i able to determine the values shown in either a validation list or combo box being dependant on a value in another cell? ie: Cell A1 = BOB then validation or combo box would then base it's list from the named range (or whatever the solution may be) based on Bob. if i was to change A1 to ROY then it would also change the underlying list? I have tried everything that i think SHOULD work but that it pretty limited... thanks in advance rich I'd start with Debra Dalgleish's site: http://contextures.com/xlDataVal02.html Richard Edwards wrote: > > Am i able to deter...

Radio Button on opening the form
Hello: I have know how to open the form using command button, but now I want to use Radio Option button to open the form consists of: 1. Show all supplier invoices 2. Show only outstanding invoices 3. Show only paid invoices I have created 3 types of Form using 3 types of query, and now I want to open it by using radio button with the button OK and cancel to open the form. Is there any website providing the sample of Radio button to opent the form. I want to studdy how to write the VBA for that. Thanks in advance. Frank You sure you want the form to open when the radio button is sel...

need help Combo Box with duplicate entry.
I have a combo box with unique and non-unique entries. (search field) 00010 | john | smith | 12345 | 00002 00196 | jane | doe | 0120 | 00001 00196 | Jone| wood| 0220 | 00005 I would like the following to happen. 1) user types the number needed ( 10 ) 2) the combo box zero fills the field (00010) 3) then selects an entry from the combo box. (12345) if the select is incorrect ( one of the non-unique numbers was selected - 00196) the user will open the combo box and select the correct entry. (jone wood) add the info will be put on the form. the following code works if the user ente...

if value not found in table ?
Hello all I need to display a msgbox if a value is not found in a table. Something like: If value not_in table.field then msgbix end if I know that code wont work is just an ilustration of what i am looking for Thanks in advance Use DLookup() to see if the value is in the table. If it's not found, the result will be Null. So, use IsNull() to test the result. Here's how to get your Dlookup() expression working: Getting a value from a table: DLookup() at: http://allenbrowne.com/casu-07.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access us...

Pop-Up Subform not linking to Main Form
I have a subform - frmLawEnforcement - that is accessed on the main form - frmCorporateSecurity - by clicking on a command button from the main form. The two forms are linked by the CaseIDNumber field. If I place the subform as just an entry from with the main form, the information shows as linking by the CaseIDNumber. When I enter the information into the subform using the command button, the information does not link to the case number. I am not sure what I could be doing wrong. I am an intermediate Access user but am fairly limited on VBA. ABradley, It would help...

Linked Table Manager Doesn't Work in Access 2003
I recently upgraded to Access 2003 and have found that the menu option: Tools/Database Utilities/Linked Table Manager no longer works correctly for re-linking my front end database to the backend. No tables show in the table list. If I click “Select All” and then fill in the path to the backend database I get the following error message: Method ‘List’ of object ‘IfieldListWnd’ failed. As a work around I have to delete all the table links from the front-end and then File/Get External Data/Link Tables. Is anybody else having this problem? Thanks in advance for your help. "...

Having different gap widths among bars
Hi, I tried to 2 different gap widths among yearly bars and monthly bars. No success. Any resolution or a better way to handle this kind of presentation? Advice is greatly appreciated. MW Do you want a bunch of years to the left, and a bunch of months to the right? Put your months on the secondary axis, and include lots of blanks in the beginning of the series data range, so the months pile up toward the right. Include blanks at the end of the yearly series data range to push those bars to the left. Right click the chart, choose Chart Options, and click on the Axes tab. Check th...

Custom Forms
Hello, A fellow administrator recently installed a custom form on our 2003 exchange server. One thing we noticed is that within Outlook 2003 the new form works just great, but we don't see it when logging into exchange via the web interface. Is there a way to get the custom for to be seen there? thanks! Sean no. 6-bit or 32-bit eforms won't display in OWA That isn't to say that you can't customize OWA, nor am I saying that you can't build all sorts of powerful apps on top of the Exchange store (i.e. non-mapi top level hierarchies and custom app-specific schemas,et...

Custom Forms 11-04-04
Hi All, When customizing a form in CRM 1.2 I cant find the way to edit the part of the forms that appear when you save a newly opened case. ie. When you save a new case it goes from having just the "information" button on the left hand side to having the activites and notes buttons. I'd like to be able to customise those areas. Is this possible through the CRM interface? If not how do you do it? Thanks All Tom You can add tabs to the left part of the screen via the ISV.Config file. For more information on that, refere to the SDK online. Matt Parks MVP - Microsoft CRM ---...

Multiple Filters in a Form
Hi, I need some help. I have a form based on a query which lists wine in a cellar. I want to allow the users to filter the content on the form by using multiple filters. The following code works well. Private Sub CmdApplyFilter_Click() Dim strWhere As String strWhere = "" Dim strNonZero As String Dim strType As String Dim strYear As String strNonZero = "" strType = "" strYear = "" ' from a check box to show either only current wine in cellar or all wine included that which has been drunk - no bottles in cellar If CheckNonZero = True Then ...

6/8/2010 Microsoft Office Security Updates failing to install.
All 6 fail to install. Both of the necessary Service Packs are installed on my system. Now what? Just delete them? Kirk MI wrote: > All 6 fail to install. > > Both of the necessary Service Packs are installed on my system. > > Now what? Just delete them? Lack of given information... "Both of the necessary Service Packs" <-- what necessary service packs for what prodcut. I know your subject says "Microsoft Office", but... yeah - there is not just one version of Microsoft Office and you might not even be speaking of Microsoft O...

Deleting data from a table through a query
I have a database that is designed to update a list of credit union members from a master list so that vehicle insurance coverage can be tracked. I have been able to run an unmatched query to achieve a list of old members who have either paid off their vehicles or moved their loans to other locations. What I need to do now is delete these people from the main table. I have the cascade update and delete funtion in place to delete the vehicle information once the member is deleted, but I don't know how to take the information found in the unmatched query and delete those members fr...

Issues with MSM 04 Updating Accts
i have MS M 04 and last week when i added a new bank to online services the updating function seems to have stoped working. When i update the accounts i get the summary sheet that says Act XXX has Y items to review, but when i open the account there are no items to review, and no items have been matched either (or rather it has not automaticly gone and matched trasactions). i have watied about a week, removed all online services and re added them and i still have this issue. MSM will update the accounts but now show me what has been downloaded, or let me match transcations. In microsoft.publ...

centering text box in a table cell
Trying to do a project. I am attempting to center a text box in a row of a particular table. If I put it inline, I can kind of get it centered, but, it stays to the left side in the cell, doing about 32 of them, and they are all the same, tried adjusting, but must be doing something wrong. Select the text box, click on the dog icon on the picture toolbar, click none. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com http://officebeta.iponet.net/en-us/publisher/FX100649111033.aspx "Bruce" <notreally@idontwantoo.com> w...

FORM Calculated Field
I am trying to attempt a simple percentage calculation to provide our sales team for the Opportunity Entity. I have 3 fields 1. estimatedvalue (Est. Revenue)- money 2. closeprobability (Probability) - int 3. dsi_weightedrevenue (Weighted Revenue) - int I need the following calculation to populate my Weighted Revenue (int) field as a percent estimatedvalue X closeprobability = dsi_weightedrevenue I have tried the following script adding it to the OnLoad event of the form being sure to select the "Event is enabled" check box there as well. But after publishing I get nothing but a...