Solved

best way to combine 2 controls to one

Posted on 2015-01-20
7
129 Views
Last Modified: 2015-01-20
Hello,  
What would be the best way to take 2 unbound controls and combine those to one to save this in the table (as one field not two)?
0
Comment
Question by:Ernest Grogg
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40559747
Normally, the BEST way would be to NOT combine them.

When you combine data into a single field, then you are violating the 1st normal form, and will inevitably end up splitting the data at some point.  It is better to save those two values as two separate fields in your database.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40559762
You can use the BeforeUpdate event of the form, for example:

    Me!DateTime.Value = Me!txtDate.Value + Me!txtTime.Value

/gustav
0
 
LVL 36

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40559768
For starters, I wouldn't advise this since it violates first normal form which specifies that each field be "atomic" - meaning - contain one and only one attribute.  So for example keeping first and last names in the same field, violates first normal form and as you try to use the field, you eventually come to an understanding of why this is not recommended.

Once you combine multiple fields, you run the risk of later having to separate them and depending on how clean the data entry is, that may or may not even be possible.

If you are combining them because you want a two-column unique index or primary key, you don't have to do that.  You can select up to 10 separate columns for a PK or index.  Just use cntl-click or shift-click as you select them.

And finally, the answer to the question much as I think it is wrong to do.

Me.SomeField = Me.unboundcntl1 & Me.unboundcntl2

Put the code in the FORM's BeforeUpdate event.

Of course, it doesn't end there.  If you want to show the data in two controls when people view the record, then you need to separate them and populate the two individual controls in the form's Current Event.

Bottom line - don't do it.
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:Ernest Grogg
ID: 40559946
OK.

So as anyone who has read any of my questions, knows I have made some mistakes.  Now, that on a previous question I had I realized I need to start from scratch and do it right, I have to try to fix this mess, at least temporarily.

I have a field that I actually wanted to separate from the beginning, but decided for time's sake and ease, I would have the user put the data as "one" so I could use this for searching and query use instead of using two fields.  Bad choice.

I am rebuilding this on better principles, but in the course of action data is now being entered incorrectly or inconsistently (this may be a better word).  So I wanted to change the forms (at least for now) to reflect better usage.  But since this is field is combined, I wanted to break it down with unbound controls and recombine so I don't have to do too much until the new db is done.

I can later go back and remove the first two characters in the field and move them to the new separate field.

Don't know if that would be better to just go ahead and add the field and run an update query that would move the first two characters to the new field and delete them from the other field or not...

What are your thoughts on this?
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40559973
If you have to do this, do it.
If you can manage to modify the database to "get it right", do at better sooner than later.

/gustav
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 40560054
If you are in a position to separate the data now, do it.  The sooner you do it, the less bad data you will have to correct.  Once the data is separated, it sounds like the first field should be a combo to limit the selection to a set of codes.  Combos are great for enforcing consistent data entry.  They can't prevent a user from entering "Red" when it should be "Black" but there won't be any "Green" values if "Green" isn't an option in the combo.

Always back up the database when making bulk changes.  You should only do this during off-hours.  You can't have any users in the database while you are splitting it.  To reduce the amount of work that needs to be done all at once, I would add two new columns to the table.  Then I would modify my copy of the FE to use the two new columns.  The last step would be to run the update query to split the one field into two.  You can leave the mushed field in the table until you are sure that the split worked correctly.  Then delete it.  In the meantime, rename it so incase you missed any references in the forms/code/queries, they will break.  That way you can find them and fix them immediately.
0
 

Author Closing Comment

by:Ernest Grogg
ID: 40560133
As Usual, the answers are great and explain everything...even in simple terms.

Thanks!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question