Setting Selected ComboBox From Code

[doubleJ]

Hello...


I have a form where you add a record to the table that a combobox pulls from.
Once you add that record, it updates the combobox and puts that new record into the field.
The problem is that it doesn't actually enter that record into the table that the main form uses (it just visually shows the record).

Main form (FrmEpisodes) contains ComboBox called SeriesID that lists series names

Public NewSeries As Integer

Subform that allows the addition of a new series

NewSeries = Me.SeriesID.Value
Forms!FrmEpisodes!SeriesID = NewSeries

User enters new series



New series shows in ComboBox



New series is in the table



New series isn't in the actual record



If I change records and go back to the record, it's in the table



I was under the impression that data was updated as soon as you left the field
How do I get that ComboBox value into the actual table?

Thanks...
JJ

[Gicu]

You should try to force the main form save after to update the combo:

NewSeries = Me.SeriesID.Value
Forms!FrmEpisodes!SeriesID = NewSeries
Forms!FrmEpisodes.Form.Dirty=False

Not quite sure about your setup as there is no visible subform....
Cheers,

[doubleJ]

You should try to force the main form save after to update the combo:

NewSeries = Me.SeriesID.Value
Forms!FrmEpisodes!SeriesID = NewSeries
Forms!FrmEpisodes.Form.Dirty=False

Not quite sure about your setup as there is no visible subform....
Cheers,

Thanks, I'll give that a try.
The visible subform is in the first screenshot.
It's what has...

New Series Name
Creation           Add

JJ

[doubleJ]

Well, this flies in the face of everything that I ever knew about Access.
It has nothing to do with loading a different form or automation.
If the series is already in the list, and I do something with that combobox (delete, change, etc...), none of it changes the underlying table until I change records or close the form.
I always thought that Access always updated on a field-by-field basis.
JJ

[doubleJ]

Ok, so the way that I found to make it work was...

FrmEpisodes (main form)

Public NewSeries As Integer
Option Compare Database

Private Sub Command38_Click()
DoCmd.OpenForm "FrmNewSeries" 'this was the subform, but now opens in a new window but not acDialog so that it takes the whole Access window
End Sub

Private Sub Form_Activate()
Me.Refresh
End Sub

FrmNewSeries (subform)

Private Sub Command2_Click()
NewSeries = Me.SeriesID.Value
Forms!FrmEpisodes!SeriesID.SetFocus 'this was done in the original post so I'm not sure why I didn't include it
Forms!FrmEpisodes!SeriesID = NewSeries
DoCmd.Close
End Sub

I included everything, just in case something that wasn't considered is what fixed it.
I had a refresh on the main form, already, but it was in conjunction with a click event that hid the subform after entering a new series.
Apparently, the Activate function and a separate window really sealed the deal.

I really liked having the new option integrated into the form, but I need another subform to be continuous and you can't have subforms within continuous forms.

Things are coming together, nicely...
JJ

[Micron]

I always thought that Access always updated on a field-by-field basis.

AFAIK, never has. A field can update but the entire record does not update/commit until
- the record changes from being the active record to not (it loses focus). That means selecting a different record in a continuous or datasheet form, query, table or moving between records in a single record form.
- closing the form commits changes
- any method in code that causes a record to not be the active record or forces the record to be saved
- clicking ribbon save button or Ctrl+S will save a record

The above is all dependent on all the record fields passing any sort of validation rule.

you can't have subforms within continuous forms

Only in the header or footer, which is probably not what you'd want.

[doubleJ]

Only in the header or footer, which is probably not what you'd want.

Hmmm...
When I added a subform to a continuous form, a dialog popped up saying that it could only be on a single form and it changed the form from continuous to single.
I don't think I added it to the header, but I guess anything is possible.
JJ

[Micron]

Access will balk and force default back to single. If you save, set back to continuous it will save and allow that. I only know this from playing around so I'm not sure if a subform on a cf or datasheet makes any sense. I think the purpose was to look at a single record and know that the many subform records relate to what is displayed in the sub. With a cf or ds there really is no such visual representation. Might work if the subform only showed records for the active main record, but it's not always obvious to users which one that is. Not sure I'd create such a form as it may prove to be wonky down the road.