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

updating tables with forms

P: 15
Hi all.

I am a complete newbie to Access. I've created a set of forms with nested subforms which while lovely (Yay me), are completely non-functional (boo).

There seem to be 2 problems, but I can't be completely sure that I'm not seeing 2 manifestations of the same problem.

1. My bound combo and list boxes will not allow me to select any of the options in them. (of note, my values are being drawn from a different table than they are being written to.)
2. No data is being written to the tables.

I've checked the "locked" and "enabled" fields. I'm guessing that I should have an update statement somewhere, but I don't know where.

Thanks.
Elaina
Dec 11 '06 #1
Share this Question
Share on Google+
11 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
In the properties of your combo and list boxes under the data tab. Check what the control source is set to (I'm guessing nothing ;) ).

If I'm right just select in each case the field in which you want to store the value.

Mary

Hi all.

I am a complete newbie to Access. I've created a set of forms with nested subforms which while lovely (Yay me), are completely non-functional (boo).

There seem to be 2 problems, but I can't be completely sure that I'm not seeing 2 manifestations of the same problem.

1. My bound combo and list boxes will not allow me to select any of the options in them. (of note, my values are being drawn from a different table than they are being written to.)
2. No data is being written to the tables.

I've checked the "locked" and "enabled" fields. I'm guessing that I should have an update statement somewhere, but I don't know where.

Thanks.
Elaina
Dec 11 '06 #2

P: 15
In the properties of your combo and list boxes under the data tab. Check what the control source is set to (I'm guessing nothing ;) ).

If I'm right just select in each case the field in which you want to store the value.

Mary

Ah... you don't know me, but I'm wayyy trickier than that. (insert evil laugh.) The Control sources are set correctly already. Sorry. I wish it were that easy. There is another person who had a similar problem, but he solved it on his own without describing in enough detail for idiot me. I replied to that one as well, http://www.thescripts.com/forum/thread537788.html

Thanks.
Elaina
Dec 12 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Questions:

What is your forms record source bound to? If it's a query it's possible it's not updateable. Post the full sql code for the query.

What are the Row Sources and Row Source Types for your combo and list boxes?

Mary
Dec 12 '06 #4

NeoPa
Expert Mod 15k+
P: 31,307
If your form is bound to table A and you want to select an option from table B then :
Add an unbound ComboBox control to your form and populate it with the data from table B.
In the AfterUpdate event of your unbound ComboBox put in some code to assign the value to the Bound TextBox for the item in Table A.
Does that help?
Dec 12 '06 #5

P: 15
If your form is bound to table A and you want to select an option from table B then :
Add an unbound ComboBox control to your form and populate it with the data from table B.
In the AfterUpdate event of your unbound ComboBox put in some code to assign the value to the Bound TextBox for the item in Table A.
Does that help?

hmmm that might be it. Do I do this as a Macro with an Update SQL statement? And if I do this, should it matter if the combobox is bound or not? I'm still fuzzy on the purpose of combo boxes. I'm gathering that it is mostly for search capabilities, is this a fair assessment?

Thank you for your help.

Elaina
Dec 12 '06 #6

P: 15
Questions:

What is your forms record source bound to? If it's a query it's possible it's not updateable. Post the full sql code for the query.

What are the Row Sources and Row Source Types for your combo and list boxes?

Mary

Control Source: br_rec (of Incidents)
Row Source type: Table/Query
Row Source: SELECT decode.option, decode.[opt description], decode.Table, decode.Variable FROM decode WHERE (((decode.Table)="Incidents") AND ((decode.Variable)="br_rec"));


decode is my table which provides the text descriptions for the numeric values that are recorded into incidents.br_rec.

if this is not the appropriate way to do this, do you have a recommendation? I'm trying to create a data entry form for two very non-technical data entry people. They need to be able to add new records, delete, scroll through, etc. I thought this was the appropriate tool, but perhaps it's not.

Thanks.
Elaina
Dec 12 '06 #7

NeoPa
Expert Mod 15k+
P: 31,307
hmmm that might be it. Do I do this as a Macro with an Update SQL statement? And if I do this, should it matter if the combobox is bound or not? I'm still fuzzy on the purpose of combo boxes. I'm gathering that it is mostly for search capabilities, is this a fair assessment?

Thank you for your help.

Elaina
No, you put some code like this (You will need to change various names in here) :
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboTableB_AfterUpdate()
  2.     Me.txtTableA = Me.cboTableB
  3. End Sub
I don't think the ComboBox can be bound if getting data from another table (Mary will correct me if I'm wrong I suspect - she's more familiar with this stuff).
Dec 12 '06 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
Control Source: br_rec (of Incidents)
Row Source type: Table/Query
Row Source: SELECT decode.option, decode.[opt description], decode.Table, decode.Variable FROM decode WHERE (((decode.Table)="Incidents") AND ((decode.Variable)="br_rec"));


decode is my table which provides the text descriptions for the numeric values that are recorded into incidents.br_rec.

if this is not the appropriate way to do this, do you have a recommendation? I'm trying to create a data entry form for two very non-technical data entry people. They need to be able to add new records, delete, scroll through, etc. I thought this was the appropriate tool, but perhaps it's not.

Thanks.
Elaina
You can bind the combo box to a field in the table or query that the form is bound to. However, what is not clear above is whether the bound field of the combo box corresponds to the control source.

Also you haven't said what the Record Source of the form is. There is still the possibility that it is not updateable.

Mary
Dec 12 '06 #9

P: 15
You can bind the combo box to a field in the table or query that the form is bound to. However, what is not clear above is whether the bound field of the combo box corresponds to the control source.

Also you haven't said what the Record Source of the form is. There is still the possibility that it is not updateable.

Mary

Ahhh sorry- attention to detail is inversely proportional to the number of hours I bang my head against this... the Recordsource is set to "incidents"- the name of the table.

does that help?
Dec 13 '06 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
Ahhh sorry- attention to detail is inversely proportional to the number of hours I bang my head against this... the Recordsource is set to "incidents"- the name of the table.

does that help?
Yes it means the records should be updateable.

Now check one of the combo boxes as follows:

Control Source is set to Field1.

Row Source is set to SELECT FieldA, FieldB, FieldC From ...

Bound Column is set to 1.

Is the value in FieldA from the combo box the same value that corresponds to Field1 in the control source? Also, is it of the same data type?

Mary
Dec 13 '06 #11

P: 15
Yes it means the records should be updateable.

Now check one of the combo boxes as follows:

Control Source is set to Field1.

Row Source is set to SELECT FieldA, FieldB, FieldC From ...

Bound Column is set to 1.

Is the value in FieldA from the combo box the same value that corresponds to Field1 in the control source? Also, is it of the same data type?

Mary
Thank you for all of your assistance. In all of my poking around and answering your questions, I stumbled upon the solution. In the interest in documenting for other users of your site... I had nested subforms and the top level subforms was still set to "Locked= yes", so this manifested itself as basically everything looking locked from that point down.

Sorry for wasting your time.

Elaina
Dec 14 '06 #12

Post your reply

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