By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,573 Members | 3,008 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,573 IT Pros & Developers. It's quick & easy.

Can't load combo box with table value and then use list to edit

P: 7
I am using a form to display/edit some records from a table query. I can get the value I want to display, but I want to use a combo box that has a list loaded from another table to edit the field, if necessary. I can get the value to display from the table query, and I can load the list from the other table, but when I try to change the value it won't allow me to do it. I fought with this all day yesterday, I tried not using a control source but using a default from the table to load an unbound object (this gave me #NAME?), I tried setting up recordsets and bumping through based on the form data (this gave me an error stating the field from the table wasn't defined). I even tried to determine how to make a recordset public so I could load it with the same query and bump through it to obtain values needed to run other queries.

All of this aside, does anyone know how to put a value in a combo box from one table, and load the dropdown from another table that will allow you to override the value from the dropbox? Based on your response I will be able to update my table either programatically or have it occur from the form.

Thanks for your help,

A. Brian Cogan, CPB
Certified QuickBooks ProAdvisor
Apr 1 '10 #1
Share this Question
Share on Google+
12 Replies


ADezii
Expert 5K+
P: 8,638
Brian, we will need much more information and details other than that which you have provided:
  1. Properties of the Combo Box such as: Name, Column Count, Control Source, Column Widths, Bound Column, etc...
  2. How is the Combo Box populated: Table name, Query, SQL Statement, etc...
  3. Code that draws the Value from another Table.
  4. All other relevant code.
  5. Display the Record Source for the Form.
  6. Are you sure the Record Source for the Form is Updatable?
  7. etc., etc., etc...
Apr 1 '10 #2

P: 7
Properties of the Combo Box such as: Name, Column Count, Control Source, Column Widths, Bound Column, etc...

Name: Variety
Column Count: 1
Control Source: This is one of the questions. Do I use one? Or is this where the problem lies? The field I want to populate it initially is [Scale Tickets].Variety
Column Widths: is blank
Column Heads: No
Row Source: This is another part of the question. What I really want here are the values from a query called [ActivePrices], which contains the different varieties and there associated prices. All I need in the list is the varieties.
Source Type:Query/Table
Bound Column::1
Limit to List: No
Allow Value List Edits:No

How is the Combo Box populated? I would like to use the query ActivePrices.

Code that draws the Value from another Table. As of this point there is no code. Nothing work, so I scapped it all. I am looking to start fresh.

Display the Record Source for the Form. I'm not sure what you mean by this. Do you mean the record layout for the table that is populating the form? The record source for the form is called ScaleTicketsQuery.

Are you sure the Record Source for the Form is Updatable? I checked some of the other fields and it won't let me update any of them. How do I make the recordset updateable? Can I do it without writing any code?

Pardon the stupid question, but I haven't written any VBA code since, I'm embarassed to say, 1988.... and I am trying to get back into it writing interfaces for QuickBooks.

I didn't think there was anything complicated in what I am trying to accomplish here. I am sure there has to be a simple answer. I just want to be able to populate a combo box when it loads and be able to update based on a list of values. Any help would be appreciated.
Apr 1 '10 #3

ADezii
Expert 5K+
P: 8,638
I checked some of the other fields and it won't let me update any of them
Here is where the problem seems to exist. Is there any chance that you can Upload the Database, or a subset of it? At this point, I need to actually see what is going on and why the Record Source is not Updateable.
Apr 1 '10 #4

P: 7
There doesn't seem to be a way to upload in a reply. I have placed a zip file containing the database at http://www.assetbooksandtax.com/driscoll.zip. The form I am having issues with is "Scale Tickets Edit".

I am also trying to figure out how to calculate the NetCwtValue by using the Price from the Active Prices query times the Net Cwt, obtaining the price from the query based on the variety in the current record.

Thanks for your help,

Brian
Apr 1 '10 #5

ADezii
Expert 5K+
P: 8,638
Sorry Brian, but I am not running Access 2007 at this time. Any chance of converting it to Access 2002? I'm afraid that I cannot do much without the actual DB in front of me.
Apr 1 '10 #6

P: 7
I am actually using Access 2010 Beta. I downloaded a trial version of Access 2007, but it says I can't save it as a 2002-2003 because it is using features that aren't available. I don't for the life of my know what that is. SO, I guess I am stuck.
Apr 1 '10 #7

ADezii
Expert 5K+
P: 8,638
What is the Record Source for the Form? Kindly post the SQL if it is a Query or SQL Staqtement.
Apr 1 '10 #8

P: 7
I have determined that it is because I have two tables in the Query, but I am not sure how to get around it.

Here is the SQL for the Query:

SELECT [Scale Tickets].[Haul Date], [Scale Tickets].Lot, [Scale Tickets].Grower, [Scale Tickets].Variety, [Scale Tickets].[Net Cwt], [Scale Tickets].[Total Process $]
FROM [Scale Tickets], SettingsTable
WHERE ((([Scale Tickets].[Haul Date])>[SettingsTable].[LastDateProcessed]))
ORDER BY [Scale Tickets].[Haul Date], [Scale Tickets].Lot, [Scale Tickets].Grower, [Scale Tickets].Variety;

LastDateProcessed is the only field in the SettingsTable
Apr 1 '10 #9

ADezii
Expert 5K+
P: 8,638
Can you establish a Relationship between the Scale Tickets and SettingsTable Tables, or are they totally independent?
Apr 1 '10 #10

P: 7
They are totally independent. The thought behind the SettingsTable was to maintain various tidbits of useful information not directly related to any of the other information, like the last date processed.
Apr 1 '10 #11

P: 7
Just to let you know, I used a different method to accomplish what I wanted. Added a field that contained a Yes/No called "Processed". Created a one table query where Processed = No to pull the records I want to load. Now I can update any field in the table.
Apr 2 '10 #12

ADezii
Expert 5K+
P: 8,638
The only other Option would probably be to convert your Select Query into a Make Table Query and make that the Record Source for the Form.
Apr 2 '10 #13

Post your reply

Sign in to post your reply or Sign up for a free account.