Combo Box limits (1 Viewer)

[saintsman]

I have a form with a combo box where the 'Row Source Type' is a Table/query and the 'Row Source' is a Table (Table 2) which is used purely to provide the information for the Combo Box.

This works fine but I have one entry that does not fill in all the information. Is there a limit on the number of characters you can use in a Combo box?

The Row Source in the Table (Table 2) is a memo field, as is the Control Source in the Table I want to store the information (Table 1).

Thanks in advance for any info.

 

[Pat Hartman]

You are not using the combo properly. The combo should be used to look up some user friendly value but store, the KEY to retrieving that data. So for example, your combo should show customer name but be bound to (ie store) customerID.

It sounds like you are duplicating data.

 

[saintsman]

Thank you Pat. Maybe I haven't explained clear enough (always a problem).

The information in my main table is used to print a form which details a series of operations in order to manufacture an item. These operations are different depending on what is being made, however there are three or four operations that are common to every manufacturing process. The operating process is stored in a second table linked to the main table.

The information for these common operations are accessed via the combo box. The Combo box control source is my manufacturing detail field, the row source type is a table / query and the row source is a table. The table holds only the four common manufacturing operations.

It is when I use one of these common operations that I don't get all the information displayed on my form, hence the question is there a limit to the number of characters in a combo box.

I believe I am using the combo box correctly but please enlighten me if not as I'm always willing to learn.

regards,

Saintsman

 

[Fizzio]

I assume that the long text is stored in the operations table and you are using a textbox to show the long text. I'm also assuming that you have the textbox's controlsource as Forms!nameofForm!NameofCombo.column(x) where x is the column where the long text is stored.

I dont know about the amount of data allowed in a combo but if you are using the combo to purely select an operation, could you not use a parameter query to populate the form and use the combo as the criteria?

 

[saintsman]

Fizzio,

Due to the length of the text both are stored in memo fields. (althought the text is not huge it is more than 255 characters).

I'm not sure on the parameter query. There are common operations (at the start and end of the processes) but the manufacturing fields are different in between.

For example. The manufacturing process will commence with an "inspect the components before you start" operation and end with a "final inspection" operation. What is in between is dependent on the individual requirement of the item being made. It could be several different processes or just one (depending on how complex it is). We tend to make one-offs to order and not the same sort of things all the time otherwise the whole process would be much simpler.

My database works fine, apart from the combox box entry (and its only one entry in the four items stored in the table that does not fill in all the data).

 

[Fizzio]

I think I understand what you are saying. I'm just giving an alternative way of displaying the data without experiencing the limitations you have currently encountered. Was I correct in the way that you are displaying the info from the combo or are you displaying it in the combo box itself?

 

[Rich]

Use a report to print the operation

 

[saintsman]

Fizzio,

Yes I think the way you described it is correct.

Rich,

I have printed it as a report but it does clip the last sentence.

This is what I want:

1) Ensure all relevant Health & Safety requirements are read prior to work being carried out.
2) Ensure all relevant Documents are at the latest Issue iaw FRCA-XXXX.
3) Ensure all Serial & Batch Numbers are recorded on Form FRCA-XXXX.
4) Works Query Notes to be added to Form FRCA-XXX as and when answered and signed by Design.


And this is what I get:

1) Ensure all relevant Health & Safety requirements are read prior to work being carried out.
2) Ensure all relevant Documents are at the latest Issue iaw FRCA-XXXX.
3) Ensure all Serial & Batch Numbers are recorded on Form FRCA-XXXX.
4) Works Query No

MS Word says the latter is 249 characters (with spaces)

 

[Rich]

I have printed it as a report but it does clip the last sentence.

Are you using GroupBy in the Reports underlying query, UCase or such?

 

[saintsman]

No, there is nothing unusual.

The report prints exactly what is stored in the memo field. ( I have just done it printing the report direct from the table). It still clips the last sentence.

If I do the same from the table where my combo gets it's reference from it prints everything.

Therefore it must be using the combo box that causes the problem???

 

[Rich]

As Pat has already pointed out, you only store the PK from the combo, and then use a query to return the actual field associated with the PK, is that what you're doing?
If that's the case and you still can't get the text box on the report to fill correctly then you can use DLookUp as a last resort to return the field contents

 

[saintsman]

No I don't think I am doing it that way.

I am using the combox box to take the data straight from one table and put it in another. The table my combo box refers to could have 4 entries in it saying A,B,C & D, instead they have 4 sentences in it. Is this not the correct way to do it as it seems to be the simplest way?

I realise that using A,B,C & D as examples might be misleading because you would use a value list for that. I have used a query / table because of the size of the data required.

Pat indicated that I was duplicating data but is that really the case?

 

[Fizzio]

If you are copying data from one table to another then yes, you are duplicating data.
Your text table should Ideally contain an unique ID, usually an autonumber and a field for the text. You(as Pat and Rich have indicated) use the combo to display the text BUT, you actually want to select the ID corresponding to this text from the table. You then use the ID in the report. If you are still stuck, it maybe worthwhile posting your Db or asking for example if you are unsure how to use the combo correctly.

 

[saintsman]

Maybe I am splitting hairs but it seems to me that even if you are just using the ID you are duplicating data albeit not so much.

However, it seems I am doing it wrong so I will have a play (usually the best way to learn anyway) and I will see if I can sort it.

Thank you all for taking the time to look at my problem.

 

[Fizzio]

You Ideally should not need to duplicate the data, whatever it is. If you need to show a particular subset of results, then you use a query to select the relevant results to show on the form/report, rather than copying the data to a new/existing table and showing this tables data.

 

[Pat Hartman]

Maybe I am splitting hairs but it seems to me that even if you are just using the ID you are duplicating data albeit not so much.

This is a common misconception of people new to relational database theory. You must store the "key" so that you have a means to find the related data. "keys" do not change (or at least they shouldn't). Let's use a very simple everyday example. You decide to place an order with a mail-order house. If this is your first purchase, you are entered into their customer table and assigned a customer ID. The clerk then proceeds to take your order. Should the order details be added to the customer record? No, there is no way to predict how many orders you will place nor what items you will order. Therefore, a second table needs to be created to hold the Order information. This would include the date, the ship-to address, the credit card information. Should it also repeat the customer information? No, because the customer name and billing address can be retrieved any time we need them as long as we store the CustomerID in our Order table. That way if the customer moves, we don't have to change his address in possibly dozens of records. We only need to change it once. When we move a step lower in the hierarchy down to the item level, the potential for change grows to thousands of records if we store the customer information. At this level, we would store only a reference to the Order. So we would join the OrderDetails to the Order to obtain information relating to the order and join the Order to the Customer to obtain Customer information.

This is the power of a relational database. You separate your data into descrete sets to eliminate duplication and then join it back together with queries to produce user-friendly recordsets to populate forms and reports.