duplicating data entries

[Noreago]

New Question

Lets say I have Three fields... 'A' 'B' and 'C'

I would like field A(Name) to duplicate what is entered into field B(Company), but if there is no entery in Field B to use entry from field C(Contact)



A = B or if B is Null... A = C

Hope this makes sence

 

[jwhite]

Re: button created to copy and paste a field

Rather than have duplicate data, when the Name is needed and it is null, then pull the value from field B, but if B is null also, then pull it from field C. Not sure why you would have incomplete data though...

 

[Noreago]

Re: button created to copy and paste a field

Rather than have duplicate data, when the Name is needed and it is null, then pull the value from field B, but if B is null also, then pull it from field C. Not sure why you would have incomplete data though...

The reason I would have incomplete data is becuase there may not always be a company name. So in my Header there is a field "Name" which would display the Company Name, but in the cases where there is no company name i would like it to display the Contact Name.

The problem is i need to know how to make a macro or VBA to perform this action or something to make this work.

 

[HiTechCoach]

Re: Related Fields

I have Three fields... 'A' 'B' and 'C'

I would like field A(Name) to duplicate what is entered into field B(Company), but if there is no entery in Field B to use entry from field C(Contact)

'A' is the same as 'B' or if 'B' is Null 'A' is the same as 'C'

The reason I would have incomplete data is becuase there may not always be a company name. So in my Header there is a field "Name" which would display the Company Name, but in the cases where there is no company name i would like it to display the Contact Name.

The problem is i need help to make a macro or VBA to perform this action or something to make this work

DisplayName = IIF(IsNull([Company]),[Contact],[Company])

Note: Since Name is a a property of most objects, it is best to avoid naming any objects "Name". It will cause you grief and just confuse Access.

 

[wazz]

Re: Related Fields

If Nz([Company], "") <> "" Then
    Me.txtHeader = [Company]
Else
    Me.txtHeader = [Contact]
End If

you should not have an extra table-field for name. use an extra unbound textbox control on your form/report to "calculate" the name. do not store the data twice.

 

[jwhite]

Re: button created to copy and paste a field

In that you are saying "Header", I guess you are talking about a report? The simplest solution is to evaluate for this in the Query which is the RecordSource of the report.

Let's 'assume' you have two fields in your table: CompanyName & ContactName.
Include both fields in your query.
Add a NEW field with FieldRow set to: CoName: Iif(Nz([CompanyName],"")="", [ContactName], [CompanyName]).
Then in your Control Source for Company Name, set it to: CoName
If CompanyName is empty, you will get ContactName.

If you describe your issue with the details of your issue rather than a "suppose I had...", we will be more able to assist you without asking questions and provide a concrete response.

 

[Noreago]

Re: button created to copy and paste a field

In that you are saying "Header", I guess you are talking about a report? The simplest solution is to evaluate for this in the Query which is the RecordSource of the report.

Let's 'assume' you have two fields in your table: CompanyName & ContactName.
Include both fields in your query.
Add a NEW field with FieldRow set to: CoName: Iif(Nz([CompanyName],"")="", [ContactName], [CompanyName]).
Then in your Control Source for Company Name, set it to: CoName
If CompanyName is empty, you will get ContactName.

If you describe your issue with the details of your issue rather than a "suppose I had...", we will be more able to assist you without asking questions and provide a concrete response.

I have attached the file i am working on. The Header being the header of the Form. I hope this explains a little better of what I hope to accomplish.
I can design very well and set it up with the intent of how i would like it to work. I fail in the knowledge of how to actually program it to perform the way i want it to.

 

[Noreago]

Re: Related Fields

I have tried both ideas, and still unsuccesfull. I have attached the file i am working on so you can see first hand what I am trying to do. Hope this will be enough to help me

 

[jwhite]

Re: button created to copy and paste a field

Had a peek. No Data? Some of the things I noticed...

1. "Name" is a reserved word, changed Control to txtName.
2. "Mr/Mrs/etc" is an improper field/control name, changed to NameTitle.
3. Modified your Copy Address Command - proper Control Name, and data validation.
4. For filling txtName, need to check OnCurrent Event AND Afterupdate of Company & Contact.
5. On cmdCopyAddress, merged validation into (1) message to display all missing Control Names.
6. Modified form Cycle Property to Current Record, so Tabbing doesn't go to next record.
7. Not sure what the OK/Cancel buttons are supposed to be doing -- no code behind them.

Okay, that gets you past your current issue, But... While the forms does work for want you (may) want, it is not logically designed, nor is your data Normalized.

You should using Naming Conventions for object names (Search online for Reddic Naming Conventions).

Your data should not be in just one table. You should break that up into separate entities. As a start, some of these might be:

tblCustomers
------------
Customerid, AN PK
CustomerName, Text
WebPage, Text
EmailAddress, Text (Main email)
...
DateEntered, Date/Time
IsActive, Yes/No
...

tblCustomerCategories
---------------------
CustomerCategoryID, AN PK
CustomerID, Number FK
CategoryID, Number FK

tblCategories
------------------
CategoryID, AN PK
Category, Text

tblCustomerAddresses
--------------------
CustomerAddressID, AN PK
CustomerID, Number FK
AddressTypeID, Number FK
Address1, Text
Address2, Text
City, Text
Province, Text
PostalCode, Text
Country, Text
DateEntered, Date/Time
IsActive, Yes/No

tblAddressTypes
---------------
AddressTypeID, AN PK
AddressType, Text (Default, BillTo, ShipTo, etc.)

tblCustomerContacts
-------------------
CustomerContactID, AN PK
CustomerID, Number FK
NameTitle, Text
NameFirst, Text
NameLast, Text
NameMiddle, Text
DateEntered, Date/Time
IsActive, Yes/No

tblCustomerComments
---------------------
CustomerCommentID, AN PK
CustomerID, Number FK
Comments, Memo
DateEntered, Date/Time

tblContactComments
---------------------
ContactCommentID, AN PK
ContactID, Number FK
Comments, Memo
DateEntered, Date/Time

You may also want to provide "Type" tables for City/Province/PostalCode/Country. This is just a start... Your requirements may (likely) dictate changes. If you get to a point where you are unsure about the Table Structures, post what you have up to that point in the Database Design forum and other AWF members will gladly review it for you.

MS Access works very well when the information is separated to their own entities and then correctly related to each other (Relational Integrity). It may look like alot of work right now -- and it SHOULD be! Database Planning/Designing usually takes MOST of the project time. If done right, the rest (User Interface, reports, code) comes easy. Have a search here for "Normalization" or on the Web. Plenty of information available.

I realize the above is alot, but you will be happier to have gone the route above than continuing to create code to work around flaws in database design.

Good luck!