Requery method for not in list

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am new to VBA and could really use some help. I have a main form called
'Issues'. It has a bound control with a combo box and drop down list with
'Limit to List' set to Yes. When the user tries to enter information that
isn't in the list, the error message tells them to enter something from the
list. I have created a macro that causes a second form 'Lots' to pop up when
this occurs, so they can add the new infromation to the underlying list.
When the second form is closed and saved, the combo box on the first form
doesn't requery to include the newly added information. Does anyone know how
or what I need to do to cause the requery to happen when I close the second
form 'Lots'?

Thanks for any and all help.
 
Yes,
If you're opening the pop-up form in Dialog mode, then you need only to set
the response to acDataErrAdded after your DoCmd.OpenForm statement.

DoCmd.OpenForm .....
Response = acDataErrAdded

Brian
 
First, dump the macro and do it in VBA. You can convert the Macro to VBA and
paste the resulting code in your routine.
Now, the trick is that the code you are running to open the Lots form does
not stop to wait for it to complete, it keeps on trucking, so if you put a
requery right after you open the Lots form, it would not know about the new
entry, so what you can do in the Close event of the Lots form is requery the
combo.

Forms!Issues!MyCombo.Requery

There is one thing you need to consider. If the form Lots can be opened from
anywhere else or if there is a chance your user could close the Issues Form
without first closing the Lots form, you need to avoid that error. Here is
how you would do that:
If CurrentProject.AllForms("Issues").IsLoaded Then
Forms!Issues!MyCombo.Requery
End If
 
Should add that if you ARE doing it this way, then you do NOT want to issue
an additional requery command. The acDataErrAdded is all that is needed.

Brian
 
Thanks for your help. I understand the code you wrote, but like I said I'm
still learning. Is that all I need to put in, or does there need to be an
opening like Sub Private...

Shawn
 
Open your Lots form in design view select Properties and select the form.
One the events tab, select Close. Select Code from the Builder dialog. It
will open the VB editor with the cursor positioned in the sub for the form
close event. Paste the code in there. Be sure you modify the code to use
your form name and the name of your combo.
 
Thanks. I'll give it a try.

Klatuu said:
Open your Lots form in design view select Properties and select the form.
One the events tab, select Close. Select Code from the Builder dialog. It
will open the VB editor with the cursor positioned in the sub for the form
close event. Paste the code in there. Be sure you modify the code to use
your form name and the name of your combo.
Click to expand...
 
Hi Klatuu,
I tried what you suggested, and I'm not sure it's working or not because I'm
getting a run-time error 2118--You must save the field before you run the
requery method. I've tried various things including the DoCmd.save and
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70, and
using a Save Record button. Can you help me any further?

Thanks for all your help.
SVE
 
Sorry for butting in, but it is not at all clear to me why you would not be
using the combobox's Not In List event procedure. There is a Response
argument. If you are, you'll open the called form in Dialog mode(6th
argument); and the way to requery the combo so it shows the added item is
setting its response to acDataErrAdded. No coding in the Close event of the
'called' form.

Brian
 
I appreciate all input. I am using the Not In List procedure which opens the
other form. But When I close that form, it doesn't requery the first form so
the new information is available in the list. Thanks for the suggestion, I'll
try that when I get back from my meetings.
 
Here's a basic approach (without error handling):

Private Sub MyCombo_NotInList(NewData As String, Response As Integer)

If MsgBox("Would you like to add this Issue?", vbYesNo) = vbYes Then

DoCmd.OpenForm "Issues", , , , acFormAdd, acDialog
'requery combobox
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me.Undo
End If

End Sub

Obviously, you'll have to change 'MyCombo' to the name of your combobox
control.

HTH,
Brian
 
Thank you so much. It worked beautifully. Now will my user have any issues
if they open the forms independently like Klatuu mentioned earlier in the
post?
 
With the code below, you are opening the form independently. The acFormAdd
only specifies that in that particular form instance, it will open in Data
Entry mode.

The rest of the code has to do with the calling form, after you've closed
the "Issues" form.

Brian
 
How are you at Queries and Reports?

Brian Bastl said:
Here's a basic approach (without error handling):

Private Sub MyCombo_NotInList(NewData As String, Response As Integer)

If MsgBox("Would you like to add this Issue?", vbYesNo) = vbYes Then

DoCmd.OpenForm "Issues", , , , acFormAdd, acDialog
'requery combobox
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me.Undo
End If

End Sub

Obviously, you'll have to change 'MyCombo' to the name of your combobox
control.

HTH,
Brian
Click to expand...
 
How are you at Queries and Reports?

Depends on your POV! That said, there are appropriate newsgroups for each of
those subjects.

Brian
 
Back
Top