Changing Combo box Control in a table to a text box - getting read only option

[bearcub]

I have a field in my table that was initially set up as a combo box. It is currently populated and updated by using a form that was created for this purpose. There are presently over 3000 cells of data that have been selected in this field by using the combo box method via a form.

However, I now need to over write this data quickly by copying new data that I created in Excel into the table.

Other fields that are not set up as a combo box (i.e. text box) can accept copied and pasted information from Excel. However, if I'm using a check box type control (Boolean), or combo box control the fields appear to remain blank (which means I have to manually update them from the form or directly into the table).

Now I'm wondering if the new data is sitting in the cells but just not visible (this scenario isn't likely but ...)

Is there a way in Access to convert an existing combo box to text box with read/write capabilities? I just tried that in a practice db but after I converted the combo box to text box control Access saved itas read only so I couldn't add new data to this field.

Thank you for your help in advance,

Michael

 

[xenou]

I have never used a lookup in a table field - not sure what that means in this case. Possibly you are facing a case where the possible values in the field must exist in another table (i.e., a lookup table) and your new values don't meet the criteria for that referential integrity. Otherwise no idea. Tables don't really have comboboxes in them, just data. But sometimes that means by extension rules that apply to that data (what can and cannot be stored in the field based on validation rules, foreign key and primary key rules, data types rule, and referential integrity rules). For instance, a boolean checkbox is really either -1, 0, or null. There are no actual "checkmarks" stored in a data table. So I recommend you try to look at this as a data problem, not a combobox problem. What *is* the data you are trying to input? What is the data type of the field? Are there foreign keys in the table? Is the field a lookup field that is tied to another table?

You should be able to easily test this by entering data that you know should work - for instance, re-entering a row that is already there.

You have also said nothing about how you are trying to import this data so really not at all sure what you are actually doing to import data from Excel. Always break a problem down with simpler test cases. For example, import one row, not 3000 rows of data, to begin with. Start with something you can tear apart to see what is really wrong.

 

[bearcub]

Thank you. You're right. The combo box is linked to another table which is probably creating the issue. We have to choose among the different selections in the table (president, treasurer, vice president, etc) and sometimes a member has done multiple positions.

I am copying over from Excel into Access, not importing, copying and pasting data. When I tried to import the table, I got a number of paste errors.

I'll have to look at it some more and see what I can do.

Michael

 

[xenou]

I haven't used copy/paste much but you might want to create a copy of the table then paste into that. or at least make a backup and work with that while you are having a go at it.

I'd probably prefer to import into a blank table from Excel first, using the import data tab. You can work with the data better that way when it is in a regular table (and then use SQL inserts to move it when it is cleaned up).

Having a lookup table / reference table pretty much makes sense here, and remember that Excel is free form so you always need to be wary of data problems since you can pretty much put anything into an Excel cell.

 

[bearcub]

Great advice. I have been playing with the data but I there are other queries and reports based upon this field to generate a report so I think to change the structure in the table would require rebuilding the query and report as well, which is easy enough to do.

What would the SQL statements look like to move that data to a new table, just curious. I new to SQL so I'm not familiar with a lot of it's capabilities.

Michael

 

[xenou]

This type of SQL statement would be pretty easy.

Assume a table with ID (number), Product (Text), Qty (number), Price (decimal), TransDate (date). An exact copy of the table exists also.

Then to insert into Table1 from the copy, just run SQL like this:

insert into Table1 (ID, Product, Qty, Price, TransDate)
select 
	ID, Product, Qty, Price, TransDate 
from Table2

If ID is an autonumber field then you would leave it out, so that you are only inserted the other data fields and letting a new autonumber key generate in the original table:

insert into Table1 (Product, Qty, Price, TransDate)
select 
	Product, Qty, Price, TransDate 
from Table2

That's all. But we already know that it can be slightly more trouble if (for instance) related fields in other tables must exist first in order to satisfy foreign key constraints or similar data rules.


Edit: Note that your copy/paste method may also work from one access table to another (although I'd almost always write a query instead - you can use the query builder to create queries like this in a few clicks - watching some youtube videos would be best to see how to do that in the query builder since its easier "shown" than "explained" when you are using GUI tools).