multiple lines in a controll.

[Ankarn]

How do i get an adress into a textbox. Lets say i want to write the adress over 4 lines, which is pretty usual, how do i make that happen in a controll? And also in a report.

 

[neileg]

Why one text box? Just use 4 controls side by side.

You could concatenate the data if you wanted including carriage returns.

 

[missinglinq]

In Design View, select the textbox then goto Properties - Other and set Enter Key Behavior to New Line In Field. But I have to tell you that this is poor database design! Data should be "atomic," which is to say any given field should only have one peice of data. Each component of the address should have its own field.

 

[Ankarn]

I agree with you on that one. I use several controls for it.

But in the same database i have a field where i want the user to be able to write a little text, like a comment, with no limitation in length. What is the smartest way to do this? A textbox? i want this comment to be shown in the bottom of the report as well. A textbox there as well? it would make sense that this went over several lines.

 

[missinglinq]

You'll need a textbox on the form and report, but in your underlying table I suspect you'll need to make the field a Memo field, which is approximately 65k in size. A text field is limited to 255 characters. If a size limit of 255 will suit your needs, it's preferable to the memo field.

 

[Ankarn]

Could i store the adress in one field of the table? and seperate the lines by some sign? are there a way that i can seperate the text by these signs when i want to display it in 4 different controls? if i have name, street and city in 3 different lines. Could i store it like name;street;city ? and then get the values out in 3 different lines by splitting it again?

Is this doable?

 

[neileg]

This violates the rules of normalisation. You can always combine fields if you need to.

 

[Ankarn]

What is the rules of normalisation? I understand that i can put all the values in the controls into one field, but how do i evt. split them?

How would you do this? it is just that there is two adresses for every primary key, and it would add another 8-10 fields. I think that would be a bit messy.

 

[neileg]

If you are developing a relational database, you need to know about normalisation. Look it up in Wikipedia for a start.

Tables need to be structured for efficient use, not to look pretty!

 

[Ankarn]

Ok, i red as much as i need now to understand what is ment by normalization, and i can say that my database is as normalized as it can be. But the adress-fields could potentially be different for every primary key, that is not likely to be the case, but i have to make the database able to put two new adresses for every primary key.

Maybe use an autonumber-table for each adress and use the autonumber in the main table?

 

[neileg]

Maybe use an autonumber-table for each adress and use the autonumber in the main table?

You could, but you need to think what advantages that would give you. If it is likely that the same address will be associated with different subjects, yes. If the address is unique, more or less, then I wouldn't. You need to understand normalisation, but you can choose how far you go. I would consider it perfectly acceptable to embed the address in the subject record, but not all in one field.

If you are storing two addresses, then that prompts a different set of questions. Will you want to retrieve data that relates to either address? For example to search for either address in a particular town. Will the second address be the same as the first on many occasions? If for example you have a delivery address and an invoice address, these will often duplicate and you would only want to store it once. If the answers to these are yes, then a separate address table would make sense.

 

[Ankarn]

Yes.. it would be likely that up to 100 records would have the same adresses. So i think an autonumber-table would make sense. I dont need the ability to search for any of these, just want it to display in a certain form. The invoice and delivery-adress would often be the same, yes. But not allways, so i guess i DO need two seperate tables?
When this is stored it should be an ability to search for earlier used adresses.

Is there an ability in access to make a full name pup up when they begin typing something that is allready in the database? like intellisense..

 

[Ankarn]

Can i make 4 =DLookup in ONE texbox with 4 lines.

I want each lookup in seperate line.

i reckon i dont use vbCrLf in a controll?

 

[neileg]

Yes.. it would be likely that up to 100 records would have the same adresses. So i think an autonumber-table would make sense. I dont need the ability to search for any of these, just want it to display in a certain form. The invoice and delivery-adress would often be the same, yes. But not allways, so i guess i DO need two seperate tables?
When this is stored it should be an ability to search for earlier used adresses.

One table. You need two fields in your main table to hold the PK of the invoice address and the PK of the delivery address. This tells you which is which.

Is there an ability in access to make a full name pup up when they begin typing something that is allready in the database? like intellisense..

This is standard behaviour of a combo box.

 

[Ankarn]

PK?

And wouldnt i need 4 fields for each adress?

 

[neileg]

Can i make 4 =DLookup in ONE texbox with 4 lines.

I want each lookup in seperate line.

Sorry I don't understand

i reckon i dont use vbCrLf in a controll?

Depends what you want to do. You might want to concatenate your address lines but separate them with vbCrLf. But that's for display, not storage or data entry.

 

[Ankarn]

This is for display-purpose. I have a table i can read from, and i want to use DLookup to display 4 of those values in one textbox, but with 4 lines.

Only for display.

 

[Ankarn]

i'm typing this into a control

=[LevNavn] & vbCrLf & [LevAtt] & vbCrLf & [LevAdr] & vbCrLf & [LevSted]

This doesnt work.

There gotta be a way to use more than one line in a textbox.

Any suggestions?

 

[neileg]

I think you can only use vbCrLF in VBA. If you want to use it in a query you'll need to use ASCII codes. It might be Chr$(13) but I can't honestly remember.

 

[Ankarn]

its not in a query, its in a controll. i need to change lines in controll.