replace field of all records based on combo box value

[xg]

I have limited VBA experience and need your help.

I have table called course with 2 fields

sem field contain text 0, 1, 2 or 3 represent Winter, Spring, Summer and
Fall semester respetively.

sect field contains text.

This small course table contain 0-25 records.

I want to use a continuse form to display sect only. user can add or delete
records. I want a combo box to read sem but display Winter, Spring, Summer
or Fall instead. If it's empty table, it does not matter what semester it
displays.

When user select a new semester from the combo box and close the form, I
want sem field be replaced with new code (0, 1, 2 or 3) that user selected
for all the records in the table.

Thanks for help.

 

[Guest]

At first you say you want the form to display Sect ONLY. you also want the
user to select Sem from a combobox. Seems to me that you want the form to
display both Sem and Sect. Actually, I think you have or need a main form
with a Sem combobox, which displays a subform in continuous-form view with
Sect, perhaps in a textbox. If that is the case, and you dont already have it
setup:
Create a form based on the table. Set the form's Default View to Continuous
Forms. Add the Sect field.

Create another form (the main form), not based on any table.
Add a SubForm control. Set its SourceObject property to the name of the form
previously created.
Add a combobox. Set the following properties for the combobox:
RowsourceType : Value List
Rowsource : 1;Winter;2;Spring;3;Summer;4Fall
ColumnCount : 2
ColumnWidths : "0;1"

In the After_Update event of the combobox, type the following code:
Docmd.SetWarnings 0
DoCmd.RunSql "UPDATE [TableName] SET Sem = '" & ComboboxName & "'"
DoCmd.SetWarnings -1
SubFormControlName.Requery

In the Load event of the main form, place the code:
If DCount("*","TableName") > 0 Then
ComboboxName.Value = DLookup("Sem","TableName")
Endif

NOTE: TableName is the name of your table as saved in your database;
ComboboxName is the name you choose for the combobox on the form
SubFormControlName is the name of the subform control you added to the main
form (and not necessarily the name of the form it is based on)

Hope that helps

 

[xg]

it works great! thanks a lot.

At first you say you want the form to display Sect ONLY. you also want the
user to select Sem from a combobox. Seems to me that you want the form to
display both Sem and Sect. Actually, I think you have or need a main form
with a Sem combobox, which displays a subform in continuous-form view with
Sect, perhaps in a textbox. If that is the case, and you dont already have
it
setup:
Create a form based on the table. Set the form's Default View to
Continuous
Forms. Add the Sect field.

Create another form (the main form), not based on any table.
Add a SubForm control. Set its SourceObject property to the name of the
form
previously created.
Add a combobox. Set the following properties for the combobox:
RowsourceType : Value List
Rowsource : 1;Winter;2;Spring;3;Summer;4Fall
ColumnCount : 2
ColumnWidths : "0;1"

In the After_Update event of the combobox, type the following code:
Docmd.SetWarnings 0
DoCmd.RunSql "UPDATE [TableName] SET Sem = '" & ComboboxName & "'"
DoCmd.SetWarnings -1
SubFormControlName.Requery

In the Load event of the main form, place the code:
If DCount("*","TableName") > 0 Then
ComboboxName.Value = DLookup("Sem","TableName")
Endif

NOTE: TableName is the name of your table as saved in your database;
ComboboxName is the name you choose for the combobox on the form
SubFormControlName is the name of the subform control you added to the
main
form (and not necessarily the name of the form it is based on)

Hope that helps

I have limited VBA experience and need your help.

I have table called course with 2 fields

sem field contain text 0, 1, 2 or 3 represent Winter, Spring, Summer
and
Fall semester respetively.

sect field contains text.

This small course table contain 0-25 records.

I want to use a continuse form to display sect only. user can add or
delete
records. I want a combo box to read sem but display Winter, Spring,
Summer
or Fall instead. If it's empty table, it does not matter what semester it
displays.

When user select a new semester from the combo box and close the form, I
want sem field be replaced with new code (0, 1, 2 or 3) that user
selected
for all the records in the table.

Thanks for help.