Thanks to AWF (1 Viewer)

[Dick7Access]

Thanks to everyone for all the help I get from a great bunch of people. Access for me has two venues. One I can keep excellent records of my ministry, appointments, and medical records. My doctor at the VA says I have the best records of any patient she has ever had. I have the biggest DB of churches of anybody in my circle. Access is also my hobby. It’s a hobby that I can play with while I wait for my flight or in a motel. People that are not into computing think I am crazy. One friend said why go through all that trouble. The VA will give you a little notebook, just go buy a pencil.
You folks understand. I said all that to say this. Some of the question I ask is for bells and whistles, and to play with my hobby.
Thanks again to all.
That said here is my next whistle. On my vitals Db I have a field where I record what I ate. I got this bright idea. I thought since I put coffee and other stuff in every day why not build and put on the form a combo box. I was on an ego trip. That was until I had the combo box put in ham & eggs and then I put in a comma and had combo babe put in coffee.
(stop laughing)
Of course it replaces the ham & eggs. (deflated ego)
[ Is there any way to accomplish what I though was a cute whistle? ] putting in multiple items without having to type repetitive stuff over and over again

ALSO: On Nov. 20th I will be in Guadalajara, Mexico for a week. Anybody on AWF in Guadalajara, Mexico? Give me a shout

 

[theDBguy]

Hi Dick. Did you create a lookup table for the combobox?

 

[Dick7Access]

Hi Dick. Did you create a lookup table for the combobox?

I created a table with one field that has records of coffee, eggs, etc. for the combo box to reference. Is that a lookup table?

 

[Dick7Access]

SunnySandyEggo? Is that where the Navy wanted to send me once?

 

[theDBguy]

I created a table with one field that has records of coffee, eggs, etc. for the combo box to reference. Is that a lookup table?

Hi. That's correct! So, I am not sure I understand what you mean by "replacing" what was there before. Are you trying to select from a combo and then create a series of comma separated list?


Re: San Diego. Yes, the Navy is still here!

 

[Dick7Access]

Hi. That's correct! So, I am not sure I understand what you mean by "replacing" what was there before. Are you trying to select from a combo and then create a series of comma separated list?


Re: San Diego. Yes, the Navy is still here!

I selected ham & eggs from the combo box. It put it in the field ok. I then typed a comma after the ham & eggs and picked coffee from the combo expecting it to put coffee after the comma. The combo didn't put coffee after the ham & eggs, instead the coffee replaced the ham & eggs.

 

[theDBguy]

I selected ham & eggs from the combo box. It put it in the field ok. I then typed a comma after the ham & eggs and picked coffee from the combo expecting it to put coffee after the comma. The combo didn't put coffee after the ham & eggs, instead the coffee replaced the ham & eggs.

Okay, if you're using code in the AfterUpdate event of the Combo, then try it this way:

If Me.Textbox & "" = "" Then
    Me.Textbox = Me.Combobox

Else
    Me.Textbox = Me.Textbox & ", " & Me.Combobox

End If

Then, all you have to do is keep selecting an item from the combo. You don't have to touch the textbox in between items just to add a comma.

 

[AccessBlaster]

Dick, the NotInList event is a great way to build your lookup table, for those repetitive data entries. https://docs.microsoft.com/en-us/office/vba/api/access.combobox.notinlist

 

[Dick7Access]

Okay, if you're using code in the AfterUpdate event of the Combo, then try it this way:

If Me.Textbox & "" = "" Then
    Me.Textbox = Me.Combobox

Else
    Me.Textbox = Me.Textbox & ", " & Me.Combobox

End If

Then, all you have to do is keep selecting an item from the combo. You don't have to touch the textbox in between items just to add a comma.

not working, still replace previous entry

please check my code.

Private Sub ComboFood_AfterUpdate()
If Me.food & "" = "" Then
   Me.food = Me.ComboFood
   Else
   Me.food = Me.food & "' " & Me.ComboFood
End If
End Sub

 

[theDBguy]

Hi. How come your code is using a single quote instead of a comma like the one I used? Just curious...


PS. Are there any other code at play here as well, maybe?

 

[theDBguy]

Here's what I mean...

 

[Dick7Access]

Hi. How come your code is using a single quote instead of a comma like the one I used? Just curious...


PS. Are there any other code at play here as well, maybe?

Just missed that I had put in single quote. I have changed it. Good catch on your part.

I have to get off line right now, as I am on my way out. I will check for any other code when I get back.

 

[Micron]

sounds to me like if you want to bind the combo to a field you ought to have it on a continuous form. What you're doing now is populating one field with multiple values - aka a multi value field. Only once in about 20 years have I ever found it necessary to do that. To continue on your present course will require a work around. The simplest is probably a listbox with multi select option. Then in vba you loop through the selected and create a comma separated values (csv) string that you write to the field.

 

[theDBguy]

Actually, Micron brings up a good point. If you want to record multiple food intake per incident, you might consider using a child table and a subform for it instead of comma delimited string. Just a thought...

 

[The_Doc_Man]

Particularly if you are like me and sometimes like to go back for seconds. But I have given up thirds completely. Probably accounts for my recent weight loss.

 

[Dick7Access]

Actually, Micron brings up a good point. If you want to record multiple food intake per incident, you might consider using a child table and a subform for it instead of comma delimited string. Just a thought...

actually I did do that but I felt it was over kill. As I said even a combo box is a little over the top. Really how much time does it take to type this

2 pancakes, 2 eggs, 2 link sausage, 4 cups coffee w/ half&half, sweet and low

but it's fun figuring out how to do it.

 

[theDBguy]

Hi. So, did you get a chance to look at the demo I posted earlier?

 

[Dick7Access]

Hi. So, did you get a chance to look at the demo I posted earlier?

I put your code into my db and changed my cbo name to yours. What it does now is replace the original with the new one, and not only puts the new one in, but puts the new one double such as coffee, coffee. Hunting around looking for some difference between you demo and my DB I could not find a table in yours. Where does you cbo get its data? Do you think this is where the problem lies?

 

[theDBguy]

I put your code into my db and changed my cbo name to yours. What it does now is replace the original with the new one, and not only puts the new one in, but puts the new one double such as coffee, coffee. Hunting around looking for some difference between you demo and my DB I could not find a table in yours. Where does you cbo get its data? Do you think this is where the problem lies?

Hi Dick. I didn't use a table in the demo (because I was too lazy). Instead, I just typed all the food choices in the Combo's Row Source. Are you sure there aren't any other codes running on the form? If you want me to take a look, maybe we can do a screenshare.

 

[Dick7Access]

Hi Dick. I didn't use a table in the demo (because I was too lazy). Instead, I just typed all the food choices in the Combo's Row Source. Are you sure there aren't any other codes running on the form? If you want me to take a look, maybe we can do a screenshare.

Thank you so much. As I said previously lots of this is for learning. I couldn't figure out how you could have any data without a table. Now I know (row source) I am going to put items in row source and see what happens. I let you know what happens.


Do you have TeamViewer?
First, however I want to try and find the cause of error myself. The best teacher is always a little bit of tears and cussing.