Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

best way to combine 2 controls to one

Posted on 2015-01-20
7
Medium Priority
?
133 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 48

Expert Comment

by:Dale Fye
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 51

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 39

Accepted Solution

by:
PatHartman earned 2000 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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 51

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 39

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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

722 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