Update table with values on the form

[ch1]

Hi,
How do I update a table with values on the form.I can do this if the form has only text boxes with the statement

sSQL= " UPDATE Tabename SET columnname = " & Me.Formfieldname & "
DoCmd.RunSQL(sSQL)
But what if the form has combo boxes and option buttons and I want to update the tabel with the values selected.
I am using Access2000,appreciate any help,
Thanks,
C

 

[gusbrunston]

Hi, C:

The combo box wizard would do this for you if it's turned on when you drag the control to the form.

However, you can do this:

Bind your combo box to the field you want to save. Select the property sheet for the combo box and select the appropriate control from the box on the first row (Control source) of the data page.

In Row source type you can use "Table/Query" or you can type in a "Value list".

If your combo box has three columns, myField1, myField2, and myField3, then in the property sheet for your combo box, Format > Number of Columns, put 3. Format > Column widths, put "0.5;0.5;0.5". That will give you 1/2 inch for each column. Adjust to what you want. in Format > Total column widths put 1.5" or whatever; or leave it at auto (I never do, don't know why not).

In Properties > Data > Bound column, put the number of the column you want to bind to your table or query: it might be column 1, 2 or 3, depending upon which column holds the data for the bound field. (Remember, if you move this to code in a form module, column count starts at 0, not 1.)

You can also populate other controls on your form with columns in your combo box. If you want to do this, I'll post some code here you can put in the form module.

I hope this has been helpful. If I've missed the point, let me know. If I haven't been explicit enough let me know. I'm really busy on weekends, but by late Sunday afternoon I can answer any other questions. Access 2000 Help is really good for things like this.

Cheers.



Gus Brunston An old PICKer, using Access2000
padregus@attbi.com

 

[ch1]

Hello Gus,
Thanks for replying.
I have made all the fields in the forms Unbound.Because when I used save button to save all the fields from the form to the table when the fields were bound, I used to get error since some of the fields were all ready filled with data from other forms.So I am trying to do it using Update statement, to selectively chose fileds and update only those fields.And it is working if the form has just text boxes,but I want to use option buttons and combo boxes(unbound) and save the selected options from the form to the table.If there are other ways of doing this please let me now,
Thanks again,
C

 

[new9]

Hi Ch1:
If I Understand your question these might help:

Depending on which field you are trying to update:
**All Fields that is already defined on the update query
1)Create an update query to update what ever fields you want on your Table.
2)On Update to fileds, put the correct reference to the fields from your Form which hold the requered values.
3)on your Form After update, create a macro or write on the event code some code to run your update query.
4)Or create a command button to run the update query.

**If you want to update some field of your choice
You migh need to :
1)Create a CheckBox for each field.
2)Create a command botton or chose the Form after update event
3)Write some code to create update query to update each field on your table which has been checked on your form.

PS:this just a queck thought Hope it help

 

[gusbrunston]

Hi, C:

Some second thoughts from an idle fellow...

I think you're trying to solve the wrong problem. I don't understand why you would get errors from trying to edit data in fields that had been edited by other forms. Such editing is commonplace in Access applications. In my opinion, you should try to solve the problem of the errors. Access relies on bound controls in forms and reports, and to try to work around this with VBA code is going to be a prodigious undertaking if you are going to have more than a very small collection of tables, queries, forms and reports.

Gus Brunston An old PICKer, using Access2000
padregus@attbi.com

 

[ch1]

Hello Gus and everybody,
I would like to explain my problem in detail now.I have been trying to solve this from so many days and yet I haven't, since I don't know access at all.I am also thinking of doing this entirely from scratch again.So I will state my problem, please give my any ideas to solve this.

I have an Access2000 database which has around 20 tables in it.I am supposed to create data entry forms to fill these tables with data.All the tables have one common field ie COMPID in them or MGTID.So I linked all the tables using relationships and I also made this COMPID and MGTID as primary key.

Also I have been given the paper format of the forms.And the forms have a design entirely different than the structure of the tables.For example I have a table called COMP(for company) which has fields for maximum rent, minimum rent etc.But on the COMP form I don't have options to enter those, because I have a different form called UNIT which has these fileds.
So what I did was I designed the forms based on the paper forms.I have a main form and several buttons on it to take me to different forms.Also on the main form I have some fields which enter data to table called COMP.And also I have other forms which should enter data to the same tabel.Now is the problem the tabels are not accepting data from the other forms since they have COMPID from the main form.So I am trying to use UPDATE or INSERT to selctively enter data rather than all the data from the forms.
Any ideas ?????
please let me know.Also if it is possible to do this in other ways, help me,
Thanks,
C

 

[gusbrunston]

Hi C:

I've been building a property management program for almost a year, and it has just now gone on line, and I'm busy working out the bugs. Wish I could help, but it seems like you're facing a pretty steep learning curve. Keep at it, it will all come clear someday. You surely need some good Access reference books. There's one that's called The Access 2000 Bible which I refer to more than any other, and I've got a couple!

Good luck. Gus Brunston An old PICKer, using Access2000
padregus@attbi.com

 

[new9]

Hi Ch1:
"I have a main form and several buttons on it to take me to different forms"
Have you concedered using SubForms?
COMP (main form)& UNIT (subform) (linked fileds : based on your structure)(Assumeng they have One-To-Many relationship).

"Now is the problem the tabels are not accepting data from the other forms since they have COMPID from the main form"

If you opended form A which has a botton to open form B,
and your primary key for the source of your form B is also part of your form A.You might use the value from form A as the defualt value for the primary key on B (useing the correct reference. You, could see the linked criteria for the button used for opening formB)

Hope, this is not confusing