Updating a form after adding to a list?

[ctB]

Okay,

I've got a form for filling in data. One of the fields in
the form refers to a table of colors. I've restricted the
field so the user can ONLY select a value from that field
and I have a number of colors already entered into that table.

However, there may be new colors later, so I have command
button right on the form that open a small 'colors' form
where the user can add a color. The colors form works without
a problem, but when the user closes that form, the new color
is not immediately available in the new form. I need to close
the main form and then reopen it. I'd really rather not do
that as the user may have already entered a lot of info for
the record. It would be a pain to have to close and reopen
and then readd all that info.

Is there a way to have changes made to the color table auto-
matically update the form?

Thanks,

Chris

 

[Bob Quintal]

Okay,

I've got a form for filling in data. One of the fields in
the form refers to a table of colors. I've restricted the
field so the user can ONLY select a value from that field
and I have a number of colors already entered into that table.

However, there may be new colors later, so I have command
button right on the form that open a small 'colors' form
where the user can add a color. The colors form works without
a problem, but when the user closes that form, the new color
is not immediately available in the new form. I need to close
the main form and then reopen it. I'd really rather not do
that as the user may have already entered a lot of info for
the record. It would be a pain to have to close and reopen
and then readd all that info.

Is there a way to have changes made to the color table auto-
matically update the form?

Thanks,

Chris

Yes. Make sure that the code in the command button click event
opens the form using the acDialog WindowMode
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog
In the colors form, in the close event, put a statement that
issues a requery of the combobox or listbox which contains the
list of colors.

Forms!mainform!cboColors.requery. ' <- change names as required.

 

[ctB]

Yes. Make sure that the code in the command button click event

opens the form using the acDialog WindowMode
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog
In the colors form, in the close event, put a statement that
issues a requery of the combobox or listbox which contains the
list of colors.

Forms!mainform!cboColors.requery. ' <- change names as required.

Okay, I'm a bit of a novice Access user here, so you're talking
just a bit over my head.

The line:
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog

Is this Visual Basic code that needs to be put on the command
button or is this part of the 'On Click' command? Right now
I have the 'On Click' pointing to a macro that opens 'frmColors'.

Then, the line:
Forms!mainform!cboColors.requery

Again, is this Visual Basic code? The Main form is called
frmKnifeList, and the name of the combo box is simply Color,
so should that command be:

Forms!frmKnifeList!Color.requery ?

And is that VB, or part of the 'On Click' command?

Thanks very much for your help.

Chris

 

[John W. Vinson]

The colors form works without
a problem, but when the user closes that form, the new color
is not immediately available in the new form.

Requery the combo box in the AfterUpdate event of the colors form:

Private Sub Form_AfterUpdate()
Forms!YourMainForm!YourComboBox.Requery
End Sub

John W. Vinson [MVP]

 

[ctB]

Requery the combo box in the AfterUpdate event of the colors form:

Private Sub Form_AfterUpdate()
Forms!YourMainForm!YourComboBox.Requery
End Sub

John W. Vinson [MVP]

That worked perfectly!! Thanks very much.

 

[Bob Quintal]

Okay, I'm a bit of a novice Access user here, so you're
talking just a bit over my head.

The line:
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog

Is this Visual Basic code that needs to be put on the command
button or is this part of the 'On Click' command? Right now
I have the 'On Click' pointing to a macro that opens
'frmColors'.

Yes it's Visual Basic. If you are competent in creating a macro,
you should be easily capable of a little bit of code.

However, you can simply change the macro. The openform action
allows setting the Window mode. Open you macro in design mode
and change it.

Then, the line:
Forms!mainform!cboColors.requery

Again, is this Visual Basic code? The Main form is called
frmKnifeList, and the name of the combo box is simply Color,
so should that command be:

Forms!frmKnifeList!Color.requery ?

Absolutely Correct!!!

You could modify the macro to add a requery action and put the
control name there. You wont need the formname nor the .requery.

And is that VB, or part of the 'On Click' command?

Both... Once you've saved the macro, do save as...module.
It will create a module that contains the VB equivalent to the
macro. Examine that in design view to understand what different
macro actions translate to VB