By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,945 Members | 1,703 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,945 IT Pros & Developers. It's quick & easy.

Need to fill a txtbox from an unbound cbo

Slaxer13
100+
P: 106
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
Jul 1 '14 #1
Share this Question
Share on Google+
34 Replies


Expert 100+
P: 1,240
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
Jul 1 '14 #2

Slaxer13
100+
P: 106
Thanks. I will look into that and post the results here.
Cheers, Slaxer13
Jul 1 '14 #3

Slaxer13
100+
P: 106
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
Jul 1 '14 #4

Slaxer13
100+
P: 106
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?
Jul 1 '14 #5

Seth Schrock
Expert 2.5K+
P: 2,951
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.
Jul 1 '14 #6

Slaxer13
100+
P: 106
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.
Jul 1 '14 #7

Seth Schrock
Expert 2.5K+
P: 2,951
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.
Jul 1 '14 #8

Slaxer13
100+
P: 106
So i have to change all the textboxes and bound them right? text and combo...
Jul 1 '14 #9

Expert 100+
P: 1,240
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
Jul 1 '14 #10

Seth Schrock
Expert 2.5K+
P: 2,951
Sorry for the confusion, I should have clarified the detail section. Jim is totally correct.
Jul 1 '14 #11

Slaxer13
100+
P: 106
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
Jul 1 '14 #12

Seth Schrock
Expert 2.5K+
P: 2,951
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.
Jul 1 '14 #13

Slaxer13
100+
P: 106
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?
Jul 2 '14 #14

Slaxer13
100+
P: 106
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?
Jul 2 '14 #15

Slaxer13
100+
P: 106
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

Attached Images
File Type: jpg New Record.jpg (29.9 KB, 178 views)
File Type: jpg Previous Record.jpg (29.7 KB, 176 views)
Jul 2 '14 #16

Slaxer13
100+
P: 106
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)?
Jul 2 '14 #17

twinnyfo
Expert Mod 2.5K+
P: 3,487
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.
Jul 2 '14 #18

Slaxer13
100+
P: 106
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
Jul 2 '14 #19

twinnyfo
Expert Mod 2.5K+
P: 3,487
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.
Jul 2 '14 #20

Slaxer13
100+
P: 106
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboArea_AfterUpdate()
  2.  
  3.     'Limita os campos da combobox cbotipo consoante o valor da combobox cboArea
  4.     'Limits the cboTipo fields according to the cboArea chosen field
  5.     Me.cboTipo.RowSource = "SELECT Cod_Tipo_Atendimento, TipoAtendimento " & _
  6.                            "FROM tblTipoAtendimento " & _
  7.                            "WHERE Cod_Area_Atendimento = " & Nz(Me.cboArea)
  8.     txtAbreviatura.Value = cboArea.Column(2)
  9.  
  10. End Sub
The second combo code:
Expand|Select|Wrap|Line Numbers
  1. txtTipoAtendimento.Value = cboTipo.Column(0)
Jul 2 '14 #21

twinnyfo
Expert Mod 2.5K+
P: 3,487
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:

Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. 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.
Jul 2 '14 #22

twinnyfo
Expert Mod 2.5K+
P: 3,487
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.
Jul 2 '14 #23

Slaxer13
100+
P: 106
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
Jul 2 '14 #24

twinnyfo
Expert Mod 2.5K+
P: 3,487
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.
Jul 2 '14 #25

Slaxer13
100+
P: 106
txtTipoAtendimento doesn't exist. Its cboTipo the one who retrieves the data for that field in the table. txtAbreviatura is bound.
Jul 2 '14 #26

twinnyfo
Expert Mod 2.5K+
P: 3,487
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.
Jul 2 '14 #27

Slaxer13
100+
P: 106
The cboarea is bound and the cbotipo too.
Jul 2 '14 #28

twinnyfo
Expert Mod 2.5K+
P: 3,487
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.
Jul 2 '14 #29

Slaxer13
100+
P: 106
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 :)
Jul 3 '14 #30

twinnyfo
Expert Mod 2.5K+
P: 3,487
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:

Expand|Select|Wrap|Line Numbers
  1. StateID StAbbr StText      StCapital
  2.    1      AL   Alabama     Montgomery
  3.    2      AK   Alaska      Juneau
  4.    3      AZ   Arizona     Phoenix
  5.    4      AR   Arkansas    Little Rock
  6.    5      CA   California  Sacramento
  7. ....
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.
Jul 3 '14 #31

Slaxer13
100+
P: 106
In the reunion i had few minutes ago when i was presenting the database they talked about that. I have to change that
Jul 3 '14 #32

twinnyfo
Expert Mod 2.5K+
P: 3,487
We'll make an expert out of you yet! I hope this moves you in the right direction....
Jul 3 '14 #33

Slaxer13
100+
P: 106
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:
Expand|Select|Wrap|Line Numbers
  1. me.txtabreviatura = cboarea.column (2)
As we cannot put ourselves as best answer i'll leave this topic unanswered.

Cheers, Slaxer13
Jul 3 '14 #34

twinnyfo
Expert Mod 2.5K+
P: 3,487
Hope I helped at least a bit in this process...
Jul 3 '14 #35

Post your reply

Sign in to post your reply or Sign up for a free account.