Solved

best way to combine 2 controls to one

Posted on 2015-01-20
7
122 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 34

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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 34

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now