Need to fill a txtbox from an unbound cbo

[Slaxer13]

Hi people,
I have a combo box named cboType which has an invisible txtbox, called txtTypeID, in which is supposed to show the ID of the selected value in the cbo. Now lets see if i can explain myself clearly. I want something like this:

If it is in a new record then the txtbox is going to be populated after updating the cboType so that it shows the ID. If i'm editing a old record then the cboType would be populated according to the ID on the txtbox.

So the real problem is that i need to populate the txtbox and when i create a new record i don't want to overwrite the previous records when i choose something from the cbo. It seems that when i save a new record, the old ones have the value of the cboType overwritten with the new record value.

Any help solving this much appreciated.
Cheers, Slaxer13

[jimatqsi]

How is the value getting from the combo box to the records? Why not just make the combo box bound instead of unbound?

Rather than an invisible textbox you could make your combo box have multiple columns. Set the width of a column to zero to hide the column from view.

If you continue with the unbound textbox plan, you could set the value of the textbox in the form's OnCurrent event. Using that event, when ever you select a row or create a new row the OnCurrent event is fired. Also, you may find the form's BeforeInsert and AfterInsert event to be useful. That fires when a new record is written.

Jim

[Slaxer13]

Thanks. I will look into that and post the results here.
Cheers, Slaxer13

[Slaxer13]

Oh and i unbound it because, i'm not sure of the reason, while it was bound it created new fields inside it everytime i created a new record. But i will search into that to see what i had done wrong.

Cheers, Slaxer13

[Slaxer13]

Ok so my cbo is unbound and i manage to put it filling the txtbox but it is still overwriting all the previous records when i create a new or edit a record... Anybody knows why this is happening?

[Seth Schrock]

Is your form in continuous view or datasheet view? I can't think of any other reason that you would be seeing multiple records getting overwritten.

I would also like to add my support to Jim's idea of doing away with the hidden textbox and use a combination of hidden columns and concatenated columns in the Row Source to display the data that you want while having the combobox bound to the proper field.

[Slaxer13]

Its continuous view. Yes i'm starting to see that maybe i have to try that way. I'll see if i find the problem today. If not then i'll do it jim's way.

[Seth Schrock]

If you have an unbound control on a continuous form, you cannot change one textbox without changing the others. Because it is unbound, there is no way for Access to tie it to a record. In order to do this, the control must be bound.

[Slaxer13]

So i have to change all the textboxes and bound them right? text and combo...

[jimatqsi]

No. It is certainly reasonable to have unbound objects on a continuous form. Normally, however, you put them in the heading area, or the footer. If you, by some means, plug data from a record in the form's recordset into an unbound object, you just have to know that you are seeing data from the current.selected record only. That depends, of course, on how you populate the unbound object.

What Seth is saying is that if you have an unbound object in the detail area, when you populate that unbound object, every row will reflect the same value.

Jim

[Seth Schrock]

Sorry for the confusion, I should have clarified the detail section. Jim is totally correct.

[Slaxer13]

Hope i got this right... So what i have to do is make it save the current record only, otherwise he will overwrite all the records. Am i correct? If i'm correct can i do that with the insert statement? Sorry for the bother but basically this is chinese for me xD

Thanks for the help, Slaxer13

[Seth Schrock]

It isn't overwriting the other records. If you save the record, it will only save the current record. If you want to continue to use the unbound control, then I would move it out of the Detail section of the form into either the header or footer. Personally, I think it would be much simpler and less confusing for the user to use a bound combobox, and once you know how to use it, you will be using it all the time.

[Slaxer13]

Ok i am going to change to bound cbo. With the bound i don't need to move them from the detail section do i?

[Slaxer13]

I had a thought... Can't i lock previous records so that they don't change? Or would they still change when i use the cbo?

[Slaxer13]

I bound the cbo and fixed one but now the other one is cleaning the previous record. I will attach a pic for you people to see. Need to finish this today plz.

Cheers, Slaxer13

[Slaxer13]

I think a light bulb pop out of my brain. If the record is continuous and i have an event that is triggered afterupdate cbo it will update all the previous records whenever a new record is created and i use that cbo right? And if they have different values they would appear blank right (the previous ones)?

[twinnyfo]

Slaxer,

I have read through this entire thread, and I am still confused as to what you are trying to do. In your first post, you say you want to have an invisible(?) Text box that is somehow linked to display the ID of the combo box. To start, that is exactly what you combo box is doing anyway. If you combo box is set up properly, its value is based on the ID of the recordset you assign to its row source. There should never be a need for you to have a separate text box with the combo box's ID.

Second, based on your attachments, your form is not in continuous view. It is clearly in Single Form view. In this case, if you control is unbound, you have to tell the control to do something when you assign a value to it, most likely through VBA.

If you have now bound your control, as you say you have, if I look at your attachments, it "appears" that you have a default value for either the field bound to your control or for the control itself, so that any new record will have that value.

Aside from the strange functionality you are seeking, I still have to ask, what is the purpose of this arrangement? AS the others have said, why not just a bound control that the use selects the value for?

Please explain in greater detail the need for this type of set up.

[Slaxer13]

This thread is a bit confusing i know and it's my fault i didn't specify well what i need and to be honest the initial question is a bit awkward. My form has two bound combo boxes and one of them has its value defined according to the other combo value. The problem is that the limited combo box, when i create a new record, after i save the record and look throught the previous records, that combo has a value of null. Even though the primary combo has the correct value, the secondary one hasn't got any value at all. That is the problem i am trying to solve.

PS: Maybe i am getting this a bit wrong but might the post #17 that i wrote be correct?

Sorry for the confusion created by the first question in the topic, seems i have started one thing and talked about another.

Cheers, Slaxer13

[twinnyfo]

Your form is not continuous (not according to your pictures).

But, you last post did not "unconfuse" anything.

Why are you using unbound combo boxes, when the values of those combo boxes are too update the fields in a table? The more we try to figure this out, the more confusing it becomes.

To me, based on how your form is built, it is impossible that a combo box on one record, being updated to a value, can update all previous records to null. Do you have VBA behind the AfterUpdate events of these combo boxes?

And, although I know you are trying to explain, you are not explaining WHY you are trying to do this. Please provide an example (words are fine) of what this is supposed to do. Please don't just restate what you have said, tell us, "When I open the form, these values are present in these fields. When I update this combo box, this value changes and that is supposed to update these three other fields. When I go to a new record, these are the values I expect and when I update these controls in this way this is what I expect. But, these are the values that appear."

Right now, we have little concrete to go on, and I appreciate your patience with us as we try to work through this with you.

[Slaxer13]

The combos as i said in post #19 are bound.
Example: When i select the value "car" in the first combo box the other combo box only has, as values, car names, like: "Dodge, Honda, Subaru". Imagine that in the first record i created i choose "car" for the first combo and "dodge" for the second one. I save the first record. Then i create a second record, in which now for the value of the first combo box i choose "food" and for the second one i choose "pizza". I save the second record. After saving the second record, if i go look to the combo boxes on the first record they are like this: first combo box value = "car" and the second combo box value = "".

It's just like i showed in the attachment.

As for the afterupdate, yes i have code in it.

First combo code:

Private Sub cboArea_AfterUpdate()
 
    'Limita os campos da combobox cbotipo consoante o valor da combobox cboArea
    'Limits the cboTipo fields according to the cboArea chosen field
    Me.cboTipo.RowSource = "SELECT Cod_Tipo_Atendimento, TipoAtendimento " & _
                           "FROM tblTipoAtendimento " & _
                           "WHERE Cod_Area_Atendimento = " & Nz(Me.cboArea)
    txtAbreviatura.Value = cboArea.Column(2)
 
End Sub

The second combo code:

txtTipoAtendimento.Value = cboTipo.Column(0)

[twinnyfo]

What is the purpose of your text boxes txtAbreviatura and txtTipoAtendimento? Are they bound to your table?

Also, add this between lines 7 and 8:

Me.cboTipo.Requery

This will force the combo box to reset its row source.

Additionally, your text box txtAbreviatura will always be null, because you have told it to be so. In your query, you are pulling two fields: Cod_Tipo_Atendimento and TipoAtendimento. These are referenced in the combo box as Column 0 and column 1. You are asking for column 2, which does not exist.

The line:

txtTipoAtendimento.Value = cboTipo.Column(0)

is really superfluous, as cboTipo already is that value, so unless there is a specific need to have a text box with the same value, just use the value of cboTipo.

[twinnyfo]

Additionally,

I just thought of something. Your form "appears" to be in single form view, but you say it is in continuous form view. Please change it to single form view, because there is no value in using continuous form view, if you are only displaying one record at a time. This is why you are updating all your records to Null.

Second, why is there a separate text box for txtAbreviatura, if you already know the index for cboTipo? Once you have that value, you can use that value throughout, rather than having a field for Tipo and a field for Abreviatura. This is just a good DB principle.

[Slaxer13]

Its in portuguese so i will translate. The txtAbreviatura is (in english) txtAbbreviation. It is suposed (and does so) to, after the cboArea is updated it will see, in the table which is the abbreviation of that area and put the value in the txt. And you were right my form is single view, my mistake in saying it was continuous. Me asking the column 2 was just me testing something and forgot to update again to 0.

I thought the reason behind the null values was because of the afterupdate event in the cboArea, because everytime i use that combo it triggers that event and changes the previous records but maybe this is wrong.

I will add the line you said and see how it goes.

Thanks for the help and patience.

Cheers, Slaxer13

[twinnyfo]

However, I still need to know if txtAbreviatura and txtTipoAtendimento are bound to the table or not. This can help us determine why your previous records are being set to Null.

[Slaxer13]

txtTipoAtendimento doesn't exist. Its cboTipo the one who retrieves the data for that field in the table. txtAbreviatura is bound.

[twinnyfo]

On your form, cboArea should be bound to your Table, as txtAbreviatura is based off that control. Likewise, cboTipo should be bound. Both of the other controls are redundant.

[Slaxer13]

The cboarea is bound and the cbotipo too.

[twinnyfo]

So, then, my friend, I absolutely must ask, "Why do you have the text box txtAbreviatura if cboArea is already bound. These are duplicating data in your table, with one (cboArea) being the index and the other (txtAbreviatura) a text field. Again, there should be no need to duplicate this value.

Sorry for my late responses. I have been away most of the day.

Thank you for your patience.

[Slaxer13]

cboarea has inside it the customer service areas inside it. txtAbreviatura has the abreviation of the areas.
No problem for the late response, I got out just bout an hour after i posted :)

[twinnyfo]

Slaxer,

Yes, I understand that the two fields have different data, but to reinforce good DB principles, it is still "duplicate" data. I will demonstrate:

I have a Table tblStates. My Table will look something like this:

StateID StAbbr StText      StCapital
   1      AL   Alabama     Montgomery
   2      AK   Alaska      Juneau
   3      AZ   Arizona     Phoenix
   4      AR   Arkansas    Little Rock
   5      CA   California  Sacramento
....

Based on this Table, If I have another Table that uses the index from the States Table, what must I know in order to get the State? The State Abbreviation? The State Capital? The only thing I will ever need is the index to that record. So If I want to know the state Arizona, I need the index (3). Knowing the index, I will automatically be able to find the Abbreviation and the Capital, but I have no need to keep a separate field in my table for all of these values, because finding these values is always based on the index. And, in this case, because the index is only an integer, I am only using up two bytes of space, rather than a byte for each character of text.

This is a proven DB principle, and one I hope you will learn to appreciate.

[Slaxer13]

In the reunion i had few minutes ago when i was presenting the database they talked about that. I have to change that

[twinnyfo]

We'll make an expert out of you yet! I hope this moves you in the right direction....

[Slaxer13]

This thread is a bit messy so i think this one will stay without answer since the cbo problem is being treated in http://bytes.com/topic/access/answer...ds#post3775817. Since the answer to the txt part was to do the following in the afterupdate of my cboarea:

me.txtabreviatura = cboarea.column (2)

As we cannot put ourselves as best answer i'll leave this topic unanswered.

Cheers, Slaxer13

[twinnyfo]

Hope I helped at least a bit in this process...