Question on Comboboxes (3 Viewers)

[Technics]

I have a customer form with source customer table. The table was originally setup with first and last name but I've found I need a company name and sometimes just the company name because some companies do not want a persons name as part of the invoice address. I have added CompanyName to the table and already have a combobox for choosing the first and last name. How or can I add an additional combobox if I only want to choose the company name to add to the address field. In other words choose one or the other to populate the invoice address.

 

[MajP]

Are the chosen values saved in the same field in the table? What is the control source of your current combobox and if you showed the company name where would that get saved?

 

[MajP]

There are probably a few ways to do this.
You could union the values and in one combobox see both people names and company names if people names do not exist.
You could have a radio button over the combobox (Pick Person Name, Pick Company Name) and change the rowsource of the combobox

 

[Gasman]

Use the company name, and if the table record also has the first and last name, use that in the invoice.
Now first and last name controls can be read only.

 

[Technics]

Are the chosen values saved in the same field in the table? What is the control source of your current combobox and if you showed the company name where would that get saved?

No, a field for each. I have a customer form that saves first, last, address, city, state and zip. The combobox is on the order form. In using you choose the name, first/last from the combobox and all other info is auto into the order form. I have a query using customerID with LF: [LastName] & ", " & [FirstName] that fills current combobox. I am trying how to best fit the company name into this setup either with the same combobox or one just for company name.

 

[theDBguy]

sometimes just the company name because some companies do not want a persons name as part of the invoice address

It depends on what you mean by that statement. If you have both the person's and company names, are you saying the invoice coould potentially display both or just the person or just the company name based on the company's preference? Also, if a company does not want a person's name to show in the invoice, do they still provide a person's name for your customer table or do the first and last name columns remain blank for them?

 

[Technics]

It depends on what you mean by that statement. If you have both the person's and company names, are you saying the invoice coould potentially display both or just the person or just the company name based on the company's preference? Also, if a company does not want a person's name to show in the invoice, do they still provide a person's name for your customer table or do the first and last name columns remain blank for them?

In writing my last comment, sometimes you write your own answer, I think I have figured it out. Seemed to simple, but hey. I believe the answer is [CompanyName] & "," & [LastName] & ", " & [FirstName]. To answer your question, if the company does not want a persons name the first and last name fields will remain empty and vise versa. If they want all three, all three fields will be full and show on the invoice. If you have an idea of having all three but choosing which goes to the invoice let me know. Right now if there is a persons name but not for the invoice, it will go in the notes field.

 

[theDBguy]

To answer your question, if the company does not want a persons name the first and last name fields will remain empty and vise versa. If they want all three, all three fields will be full and show on the invoice. If you have an idea of having all three but choosing which goes to the invoice let me know.

One approach I was thinking, if you want to allow all three info to display in the invoice, is to add a Yes/No field to indicate "CompanyOnly." Also, to avoid having extra commas in your solution, you could try something like:

[CompanyName] & ("," + [LastName]) & ("," + [FirstName])

Are you sure you don't want any spaces after the commas? Just curious...

 

[Gasman]

In writing my last comment, sometimes you write your own answer, I think I have figured it out. Seemed to simple, but hey. I believe the answer is [CompanyName] & "," & [LastName] & ", " & [FirstName]. To answer your question, if the company does not want a persons name the first and last name fields will remain empty and vise versa. If they want all three, all three fields will be full and show on the invoice. If you have an idea of having all three but choosing which goes to the invoice let me know. Right now if there is a persons name but not for the invoice, it will go in the notes field.

I was thinking
FirstName & " " & LastName
CompanyName
AdressLine1
AddressLine2
etc

on the invoice, so if no person's name that would be blank?

 

[Technics]

One approach I was thinking, if you want to allow all three info to display in the invoice, is to add a Yes/No field to indicate "CompanyOnly." Also, to avoid having extra commas in your solution, you could try something like:

[CompanyName] & ("," + [LastName]) & ("," + [FirstName])

Are you sure you don't want any spaces after the commas? Just curious...

Right now with what I am using I have only spaces. Using dash between Co name and persons name.

 

[Technics]

I was thinking
FirstName & " " & LastName
CompanyName
AdressLine1
AddressLine2
etc

on the invoice, so if no person's name that would be blank?

This is what I am thinking about now. Only problem is if I don't use Co name there is a blank space between persons name and address. Is there a way to get around that.

 

[Technics]

Right now with what I am using I have only spaces. Using dash between Co name and persons name.View attachment 121627

Only Co name shows if no person and vise versa. The above is using all three.

 

[Gasman]

This is what I am thinking about now. Only problem is if I don't use Co name there is a blank space between persons name and address. Is there a way to get around that.

A simple piece of code should sort that out?
Just have a control on the invoice that holds the concatenated name and address, not individual fields.
You *might* be able to use + and not & to concatenated. You would need to experiment, I would go with my first option.

 

[MajP]

In VBA there is null propagation, that was the recommendations for a "+"
Null + String = null
Null & string = string

Public Sub Test()
  Dim CompanyName As Variant
  Dim lastName As Variant
  Dim firstName As Variant
  firstName = "Eddie"
  lastName = "Money"
  CompanyName = "Acme"
  Debug.Print CompanyName + " - " + firstName & " " & lastName
 
  CompanyName = Null
  Debug.Print CompanyName + " - " & firstName & " " & lastName
  Debug.Print CompanyName & " - " & firstName & " " & lastName

End Sub

Results

Acme - Eddie Money
Eddie Money
 - Eddie Money

Use a plus before the " - " because if the company name is null then
CompanyName + " - " = null
but
Null & FirstName = FirstName

 

[Technics]

A simple piece of code should sort that out?
Just have a control on the invoice that holds the concatenated name and address, not individual fields.
You *might* be able to use + and not & to concatenated. You would need to experiment, I would go with my first option.

Got it figured out. Your comment got me started on the right path. I tried not using individual fields but with the way I have the query configured using a definite source for company/name, I couldn't get it to work. So I tried using concatenation for just that field but couldn't get it to work. I design websites using html so it dawned on me that there was "rich text" and I could try an html break. The following is what I came up with for the one field and it works.
[CompanyName] & "<br>" & [FirstName] & " " & [LastName]

 

[The_Doc_Man]

Here is a different viewpoint, more for consideration that immediate implementation. There is a basic rule in database design that if you need to make a decision that potentially differs from one entry to another, the OPTIMUM way to get this right is to have a field - perhaps a Y/N field - to remember which way you want to go. Therefore, some combination of Y/N fields might tell you "wants company name" and "wants contact name" so that you can decide behind the scenes in the form whether to show the company and/or personal name fields. You can always have them in the record, perhaps, but you can decide whether you want to make a particular control visible or not in the record's OnCurrent event.

The point is, if you want to make a potentially different decision per record, you need to carry along the answer to the implied question in each record or at least in a related record. If you have a company table, you would remember the company's preferences in that table, regardless of which table you are showing at the moment (as long as you can get to the company record). The point being, you must always be sensitive to the question, "How will I decide whether to do some particular action?" The answer to that question is up to you. But you must ALWAYS be sure you have what you need to get the effect you want.