Capturing current record or Requery?

[skoutr1]

First, let me say that I am new to the world of coding.

I have a subform nested in a parent form. One field in the subform contains a drop-down list for the user to select an entry. The data in this field is limited to the list.

Here's what I want to do: If the user tries to input something not on the list, the subform's source opens so that the new data may be added. On close of the source, the user is returned to the parent form (to the record they were inputting data) and the new data is included in the drop-down list. The subform is sourced from a query.

Two options I can think of...code triggered from "On Not In List"...

Option 1:
1. Capture the current form (parent form) and record.
2. Close parent form.
3. Open subform's source and add data.
4. Close subform's source.
5. Open parent form and automatically go to the record that data was being entered on.

Option 2:
1. Keep parent form open.
2. Open subform's source and add new data.
3. Close subform's source.
4. Somehow "requery" the drop-down list so new data is included.

Any insight on how to perform either of these options? Which one would be easier, or is there another, simpler way to go about this?

Thanks in advance,
Rod

 

[zevw]

Try in the After Update event procedure

Me.Refresh

or

Me.Requery

It should give you all the new info entered

 

[jimmythegeek]

On the NotOnList event of the combo box, put the following code (changing values as necessary):

====================
Private Sub cbo1_NotInList(NewData As String, Response As Integer)
Dim ctrl As Control
Set ctrl = Me.cbo1

If MsgBox(NewData & " Is not on the list, would you like to add it?", vbQuestion + vbYesNo) = vbYes Then
Response = acDataErrAdded

DoCmd.OpenForm "YourPopupForm", acNormal, , , acFormAdd, acDialog
Else
Response = acDataErrContinue
ctrl.Undo
End If
End Sub
====================

Then on the Close event of your popup form, put the following:

Forms!ParentFormName!SubformName.Form!cbo1.Requery

The reason you open your form (and it should be a pop up form) in acDialog is that it suspends any code following the OpenForm Line until you close the form. Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[skoutr1]

Jim-

Thanks for the assistance. The NotInList portion of the code worked fine. However, I got the following error message with the OnClose part:

==========
Run-time error '2465':

Microsoft Access can't find the field 'SubformName' referred to in our expression.
==========

Apparently it is looking at the 'SubformName' as a field instead of a subform in your suggested code below.

Forms!ParentFormName!SubformName.Form!cbo1.Requery

One other thing, after you click "OK" on the error message the combobox is requerried anyway and I get the effect I was looking for.

 

[jimmythegeek]

Are you putting the actual name of the subform where it says:

SubformName?

Go to the properties of the subform and see what the name of the subform is. This is what has to go in the code. If the parents form name is "frmOrder", and the subform name (not the Access name, but the name in design view of the main form) is "sfrmOrderDetails", your line would read like this:

Forms!frmOrders!sfrmOrderDetails.Form!cbo1.Requery
Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[skoutr1]

forgot to include that I was quoting the generic version of your code. I did replace with the proper names. And still got that error

 

[skoutr1]

I'm now getting the following error:

==========
Run-time error '2118':

You must save the current field before you run the Requery action
==========

And again, when you click OK on the error message it completes and gives me the results I wanted anyway.

Rod

 

[WMcGregor]

Put a me!refresh before the cbo1.requery statement to write the changes to the source table before the requery is executed...

 

[WMcGregor]

Oops, make that a me.refresh... Sorry