By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,390 Members | 1,584 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,390 IT Pros & Developers. It's quick & easy.

How to requery another object on another form?

blyxx86
100+
P: 256
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...

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command15_Click()
  2. Dim cboSales As Control
  3. cboSales = Forms!Main.Sales
  4. cboSales.Requery
  5. End Sub
  6.  
Run time error '2465'
That doesn't work.

I tried...
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command15_Click()
  2. Dim Forms!Main.Sales As Control
  3. Forms!Main.Sales.Requery
  4.  
  5. End Sub
  6.  
That one is just filled with errors.

I tried to do a:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command15_Click()
  2. Dim testReqSale As String
  3. testReqSale = Forms!Main.Sales
  4. DoCmd.Requery(testReqSale)
  5.  
  6. End Sub
  7.  
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.
Dec 6 '06 #1
Share this Question
Share on Google+
11 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command15_Click()
  2.  
  3.    Forms!Sales!cboSales.Requery
  4.  
  5. End Sub
  6.  
Dec 6 '06 #2

blyxx86
100+
P: 256
Try this ...

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command15_Click()
  2.  
  3.    Forms!Sales!cboSales.Requery
  4.  
  5. End Sub
  6.  
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.
Dec 6 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. DoCmd.OpenForm "Sales"
  3. Forms!Sales!cboSales.Requery
  4. DoCmd.Close acForm, "Sales"
  5.  
Dec 6 '06 #4

nico5038
Expert 2.5K+
P: 3,072
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)
Dec 6 '06 #5

NeoPa
Expert Mod 15k+
P: 31,661
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.
Dec 7 '06 #6

NeoPa
Expert Mod 15k+
P: 31,661
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.
Dec 7 '06 #7

blyxx86
100+
P: 256
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]
Dec 7 '06 #8

NeoPa
Expert Mod 15k+
P: 31,661
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.
Dec 7 '06 #9

blyxx86
100+
P: 256
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.
Dec 7 '06 #10

MSeda
Expert 100+
P: 159
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
Dec 7 '06 #11

NeoPa
Expert Mod 15k+
P: 31,661
I just place that in front of the other code, correct? I appreciate the help on this.
Yes.
But you should really have an
Expand|Select|Wrap|Line Numbers
  1. 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).
Dec 7 '06 #12

Post your reply

Sign in to post your reply or Sign up for a free account.