How to requery another object on another form?

[blyxx86]

I can't seem to get this figured out. I am trying to requery another form after adding new information to a table that a cboList is created from.

So, I have a combo box that has Sales personnel, and i have another form that lets me add sales personnel to the table of all the sales team. (Name, number, etc...)

But when I open up the main form (with the combo box of Sales Personnel) and then open the form to add new members, or edit the members, I can not seem to make a piece of code that will requery the main combo box.

I have tried the following with no success...

Private Sub Command15_Click()
Dim cboSales As Control
cboSales = Forms!Main.Sales
cboSales.Requery
End Sub
 

Run time error '2465'
That doesn't work.

I tried...

Private Sub Command15_Click()
Dim Forms!Main.Sales As Control
Forms!Main.Sales.Requery
 
End Sub
 

That one is just filled with errors.

I tried to do a:

Private Sub Command15_Click()
Dim testReqSale As String
testReqSale = Forms!Main.Sales
DoCmd.Requery(testReqSale)
 
End Sub
 

Again, no success and more errors.

I suppose I could put a Requery command on the link to the form to add sales personnel, but that seems ineffective and the form could be possibly opened otherwise.

[MMcCarthy]

Try this ...

Private Sub Command15_Click()
 
   Forms!Sales!cboSales.Requery
 
End Sub
 

[blyxx86]

Try this ...

Private Sub Command15_Click()
 
   Forms!Sales!cboSales.Requery
 
End Sub
 

Now I have another error.

I get an error message saying that this can not be done within the database's current view. (Because the "Sales" window is not open.)

What type of code would I put in this?

Would I place an "On Error Continue" ??

I'm not too sure what to do on this one, but that code did work, but only if that window is open. If it's not open, I get an error.

[MMcCarthy]

Now I have another error.

I get an error message saying that this can not be done within the database's current view. (Because the "Sales" window is not open.)

What type of code would I put in this?

Would I place an "On Error Continue" ??

I'm not too sure what to do on this one, but that code did work, but only if that window is open. If it's not open, I get an error.

You're right the Sales form has to be open for it to work. One option would be to open the form and close it after.

 
DoCmd.OpenForm "Sales"
Forms!Sales!cboSales.Requery
DoCmd.Close acForm, "Sales"
 

[nico5038]

A requiry in Access doesn't always give the needed result.
I personally prefer to use:
cboSales.rowsource =cboSales.rowsource
This guarantees a requiry.

When you use a "normal" form for the addition you can add the statement in the form's OnActivate event. Thus the combobox will always be refreshed when the form is activated. When you have a "popup" form, the OnActivate of the original form isn't triggered, don't ask me why <LOL>

Nic;o)

[NeoPa]

Now I have another error.

I get an error message saying that this can not be done within the database's current view. (Because the "Sales" window is not open.)

What type of code would I put in this?

Would I place an "On Error Continue" ??

I'm not too sure what to do on this one, but that code did work, but only if that window is open. If it's not open, I get an error.

Yes.
An 'On Error GoTo ...' is the best thing to do here.
If it isn't already open there's really no point in requerying. It will happen if/when it's opened.

[NeoPa]

A requiry in Access doesn't always give the needed result.
I personally prefer to use:
cboSales.rowsource =cboSales.rowsource
This guarantees a requiry.

When you use a "normal" form for the addition you can add the statement in the form's OnActivate event. Thus the combobox will always be refreshed when the form is activated. When you have a "popup" form, the OnActivate of the original form isn't triggered, don't ask me why <LOL>

Nic;o)

With a popup form, the idea is 'logically' that the calling form is not de-activated first, but that the popup form goes on top (like a MsgBox). When the Popup is closed the calling form 'stays' activated. A little strange to be sure.

[blyxx86]

Yes.
An 'On Error GoTo ...' is the best thing to do here.
If it isn't already open there's really no point in requerying. It will happen if/when it's opened.

What exactly would it say? I've never seen that before...

[code]
On Error Goto what?
[code]

[NeoPa]

What exactly would it say? I've never seen that before...

[code]
On Error Goto what?
[code]

On Error Statement


Enables an error-handling routine and specifies the location of the routine within a procedure; can also be used to disable an error-handling routine.

Syntax

On Error GoTo line

On Error Resume Next

On Error GoTo 0

The On Error statement syntax can have any of the following forms:

Statement Description
On Error GoTo line Enables the error-handling routine that starts at line specified in the required line argument. The line argument is any line label or line number. If a run-time error occurs, control branches to line, making the error handler active. The specified line must be in the same procedure as the On Error statement; otherwise, a compile-time error occurs.
On Error Resume Next Specifies that when a run-time error occurs, control goes to the statement immediately following the statement where the error occurred where execution continues. Use this form rather than On Error GoTo when accessing objects.
On Error GoTo 0 Disables any enabled error handler in the current procedure.

Actually you would use 'On Error Resume Next' and just continue as if nothing had happened.

[blyxx86]

Actually you would use 'On Error Resume Next' and just continue as if nothing had happened.

I just place that in front of the other code, correct? I appreciate the help on this.

[MSeda]

I know I'm jumping in kind of late here, but have you tried setfocus. In order to requery an object the form it's on has to have focus.

Try

Private Sub Command15_Click()

Forms![Main].SetFocus
DoCmd.Requery "Sales"

End Sub

[NeoPa]

I just place that in front of the other code, correct? I appreciate the help on this.

Yes.
But you should really have an

On Error GoTo 0

afterwards as well.
See the help on this (mainly posted above but in full from Access VBA Help) as it's very important to understand (and to use).