Save only 3 of 4 fields

[prosfora via AccessMonster.com]

I have a form with 4 combo boxes. Box 1 is linked to a unique indexed field
of my main table and does not allow duplicates.

Is there a way I can enter selections in the other 3 boxes and save only
those 3? (these do not add data, but edit an existing row in the main table)

Experimenting, I found that if I didn't make a selection in Box 1, I could
still make selections in the other 3, close (save) the form, and it would
make those 3 edits in the table row belonging to the value that was in Box 1.

This is kind of what I want, except that there are many selections in Box 1,
and if I select one of them and close (save) the form, I get an error message
that a duplicate entry will be made?

Would a possible solution be to have Box 1 on a main form and the other 3 on
a subform? If so, how do I set this up? Or is there another way to relate the
other 3 boxes to Box 1, and only save the 3.

 

[John Vinson]

This is kind of what I want, except that there are many selections in Box 1,
and if I select one of them and close (save) the form, I get an error message
that a duplicate entry will be made?

Would a possible solution be to have Box 1 on a main form and the other 3 on
a subform? If so, how do I set this up? Or is there another way to relate the
other 3 boxes to Box 1, and only save the 3.

Either the Subform, or - more simply - make the first combo box an
Unbound combo which just *finds* an existing record, rather than
attempting to update an existing one.

Delete the combo box and use the Toolbox Wizard to add a new one;
choose the option "I want this combo to find an existing record".

John W. Vinson[MVP]

 

[prosfora via AccessMonster.com]

John,

I went with the combo box wizard and it works like a champ!

Have been scouring Access Monster constantly for answers lately, and I gotta
say your responses are always accurate, concise, and on-point. Thanks loads!

This is kind of what I want, except that there are many selections in Box 1,
and if I select one of them and close (save) the form, I get an error message

[quoted text clipped - 3 lines]

a subform? If so, how do I set this up? Or is there another way to relate the
other 3 boxes to Box 1, and only save the 3.

Either the Subform, or - more simply - make the first combo box an
Unbound combo which just *finds* an existing record, rather than
attempting to update an existing one.

Delete the combo box and use the Toolbox Wizard to add a new one;
choose the option "I want this combo to find an existing record".

John W. Vinson[MVP]

 

[prosfora via AccessMonster.com]

John,

Couple additional questions about this. I put code similar to the following,
in the After Update event of each of the combo boxes and the On Open event of
the form:

Me.cboEngineer.DefaultValue = cboEngineer.ItemData(18)

This restores the defaults that I set for the 3 boxes after saving the edits.

However, the unbound box for finding records remains as what was selected.
Also, when I open the form the next time, the unbound box is blank (which is
what I'd love since there are no blank values in the main table), and the 3
other boxes show the last selections that were made.

I should note that I open the form from a cmd btn on another form so it can
be opened in edit mode, so am not sure if that is affecting the defaults. (my
code on the On Click of the button is):

Private Sub cboEdit_Click()
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmTarget"
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormEdit
End Sub

Is there a way I can get all the defaults to restore after both the form
being opened and form being saved? (If the boxes could be blank that would
be great, but not essential, especially since there are no blank record
values).

Thanks again!

John,

I went with the combo box wizard and it works like a champ!

Have been scouring Access Monster constantly for answers lately, and I gotta
say your responses are always accurate, concise, and on-point. Thanks loads!
[quoted text clipped - 10 lines]

John W. Vinson[MVP]

 

[John Vinson]

John,

Couple additional questions about this. I put code similar to the following,
in the After Update event of each of the combo boxes and the On Open event of
the form:

Me.cboEngineer.DefaultValue = cboEngineer.ItemData(18)

Hardcoding the 18 may get you in trouble down the line - might you not
want to use a literal value?

This restores the defaults that I set for the 3 boxes after saving the edits.

But why should the defaults be changing AT ALL??

However, the unbound box for finding records remains as what was selected.
Also, when I open the form the next time, the unbound box is blank (which is
what I'd love since there are no blank values in the main table), and the 3
other boxes show the last selections that were made.

I should note that I open the form from a cmd btn on another form so it can
be opened in edit mode, so am not sure if that is affecting the defaults. (my
code on the On Click of the button is):

Private Sub cboEdit_Click()
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmTarget"
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormEdit
End Sub

Is there a way I can get all the defaults to restore after both the form
being opened and form being saved? (If the boxes could be blank that would
be great, but not essential, especially since there are no blank record
values).

You can use the Form's Current event (which fires when the form opens,
or when it moves to a different record) to set the lookup combo box to
NULL. Again - you shouldn't need to "restore" the Default values
unless you are deliberately changing the Default values (which I
haven't seen you doing and am not sure why you would want to!)

John W. Vinson[MVP]

 

[prosfora via AccessMonster.com]

Thanks John,

A few questions/points:

1. I should have said "returned" instead of "restored" the default values of
the combo boxes.

2. Since I'm still a novice, could you give me an example of the code I would
use to set the lookup cbo to NULL? (I think I might have tried to do this
already, but used IS NULL instead, but maybe there were additional mistakes
as well)

3. Could you briefly explain what problem(s) could be created be hardcoding
the 18?

Thanks again---

John,

[quoted text clipped - 3 lines]

Me.cboEngineer.DefaultValue = cboEngineer.ItemData(18)

Hardcoding the 18 may get you in trouble down the line - might you not
want to use a literal value?

This restores the defaults that I set for the 3 boxes after saving the edits.

But why should the defaults be changing AT ALL??

However, the unbound box for finding records remains as what was selected.
Also, when I open the form the next time, the unbound box is blank (which is

[quoted text clipped - 17 lines]

be great, but not essential, especially since there are no blank record
values).

You can use the Form's Current event (which fires when the form opens,
or when it moves to a different record) to set the lookup combo box to
NULL. Again - you shouldn't need to "restore" the Default values
unless you are deliberately changing the Default values (which I
haven't seen you doing and am not sure why you would want to!)

John W. Vinson[MVP]

 

[John Vinson]

Thanks John,

A few questions/points:

1. I should have said "returned" instead of "restored" the default values of
the combo boxes.

You would expect to see the default value in the combo box when you're
on the "new record" - that's what the default value is FOR.

2. Since I'm still a novice, could you give me an example of the code I would
use to set the lookup cbo to NULL? (I think I might have tried to do this
already, but used IS NULL instead, but maybe there were additional mistakes
as well)

Me.comboboxname = NULL

in the VBA code. IS NULL is used in SQL (a different language!) as a
criterion to detect whether a field or control contains a NULL.

John W. Vinson[MVP]

 

[prosfora via AccessMonster.com]

Thanks as always John---

You would expect to see the default value in the combo box when you're
on the "new record" - that's what the default value is FOR.


Me.comboboxname = NULL

in the VBA code. IS NULL is used in SQL (a different language!) as a
criterion to detect whether a field or control contains a NULL.

John W. Vinson[MVP]