calling a public function correctly

I can’t figure out how to run this for the life of me.

Trying to run a function that updates the combo boxes for all open forms.
Here is what I have with asterisks marked on what I think is the problem line.
Have tried many permutations and gotten all sorts of new and exciting errors
but none that has worked right yet.   

Sub AllForms()
    Dim obj As AccessObject, dbs As Object
    Set dbs = Application.CurrentProject
    ' Search for open AccessObject objects in AllForms collection.
    For Each obj In dbs.AllForms
        If obj.IsLoaded = True Then
*******What goes here to call the requeryCombos function?***********
        End If
    Next obj
End Sub

Public Function RequeryCombos(frm As Form)
   ' Requery all combo and list boxes on
   'the argument form
   ' and on any subforms it may contain, and any of their
   ' subforms, and so on.
   Dim ctl As Control
   With frm
       For Each ctl In .Controls
           Select Case ctl.ControlType
               Case acListBox, acComboBox
                   ctl.Requery
               Case acSubform
                   If Len(ctl.SourceObject) > 0 Then
                       RequeryCombos ctl.Form
                   End If
           End Select
       Next ctl
   End With

End Function

Any insights would be much appreciated!
Shauna

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200801/1

0
lambertsm
1/12/2008 8:06:43 AM
access.formscoding 7493 articles. 0 followers. Follow

5 Replies
857 Views

Similar Articles

[PageSpeed] 58

Try something like this:
    Set frm = obj
    Call RequeryCombos(frm)

That will require another declaration at the top:
    Dim frm As Form

This approach won't requery your subforms. To do that you need a recursive 
call for each form. Here's an example of a recursive call for all forms in 
subforms of the form under consideration:
    http://allenbrowne.com/ser-56.html

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"lambertsm via AccessMonster.com" <u39653@uwe> wrote in message
news:7e16c475dcd6e@uwe...
>I can’t figure out how to run this for the life of me.
>
> Trying to run a function that updates the combo boxes for all open forms.
> Here is what I have with asterisks marked on what I think is the problem 
> line.
> Have tried many permutations and gotten all sorts of new and exciting 
> errors
> but none that has worked right yet.
>
> Sub AllForms()
>    Dim obj As AccessObject, dbs As Object
>    Set dbs = Application.CurrentProject
>    ' Search for open AccessObject objects in AllForms collection.
>    For Each obj In dbs.AllForms
>        If obj.IsLoaded = True Then
> *******What goes here to call the requeryCombos function?***********
>        End If
>    Next obj
> End Sub
>
> Public Function RequeryCombos(frm As Form)
>   ' Requery all combo and list boxes on
>   'the argument form
>   ' and on any subforms it may contain, and any of their
>   ' subforms, and so on.
>   Dim ctl As Control
>   With frm
>       For Each ctl In .Controls
>           Select Case ctl.ControlType
>               Case acListBox, acComboBox
>                   ctl.Requery
>               Case acSubform
>                   If Len(ctl.SourceObject) > 0 Then
>                       RequeryCombos ctl.Form
>                   End If
>           End Select
>       Next ctl
>   End With
>
> End Function
>
> Any insights would be much appreciated!
> Shauna
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200801/1
> 

0
Allen
1/12/2008 8:25:27 AM
"lambertsm via AccessMonster.com" <u39653@uwe> wrote in message 
news:7e16c475dcd6e@uwe...
>I can’t figure out how to run this for the life of me.
>
> Trying to run a function that updates the combo boxes for all open forms.
> Here is what I have with asterisks marked on what I think is the problem 
> line.
> Have tried many permutations and gotten all sorts of new and exciting 
> errors
> but none that has worked right yet.
>
> Sub AllForms()
>    Dim obj As AccessObject, dbs As Object
>    Set dbs = Application.CurrentProject
>    ' Search for open AccessObject objects in AllForms collection.
>    For Each obj In dbs.AllForms
>        If obj.IsLoaded = True Then
> *******What goes here to call the requeryCombos function?***********
>        End If
>    Next obj
> End Sub
>
> Public Function RequeryCombos(frm As Form)
>   ' Requery all combo and list boxes on
>   'the argument form
>   ' and on any subforms it may contain, and any of their
>   ' subforms, and so on.
>   Dim ctl As Control
>   With frm
>       For Each ctl In .Controls
>           Select Case ctl.ControlType
>               Case acListBox, acComboBox
>                   ctl.Requery
>               Case acSubform
>                   If Len(ctl.SourceObject) > 0 Then
>                       RequeryCombos ctl.Form
>                   End If
>           End Select
>       Next ctl
>   End With
>
> End Function
>
> Any insights would be much appreciated!
> Shauna


Here's how to do it using the code framework you started with (though I have 
an alternate suggestion which I'll post afterward):

'----- start of code snippet 1 -----
Sub RequeryAllOpenCombos()

    Dim obj As AccessObject, dbs As Object

    Set dbs = Application.CurrentProject

    ' Search for open AccessObject objects in AllForms collection.
    For Each obj In dbs.AllForms
        If obj.IsLoaded = True Then
            RequeryCombos Forms(obj.Name)
        End If
    Next obj

End Sub
'----- end of code snippet 1 -----

But it seems to me that, since you only care about those forms that are 
open, you could more easily use the application's Forms collection, which 
holds all open forms.  Here's how to do it that way:

'----- start of code snippet 2 -----
Sub RequeryAllOpenCombos()

    Dim frm As Access.Form

    For Each frm In Application.Forms
        RequeryCombos frm
    Next frm

End Sub
'----- end of code snippet 1 -----

That's all air code, but it should be something pretty close to that.

-- 
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

0
Dirk
1/12/2008 8:35:32 AM
"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message 
news:%23xQ0ySPVIHA.4440@TK2MSFTNGP06.phx.gbl...
> Try something like this:
>    Set frm = obj
>    Call RequeryCombos(frm)

Allen, I don't think that will work.  "obj" is an AccessObject, not a Form.

> This approach won't requery your subforms. To do that you need a recursive 
> call for each form.

But the code Shauna posted *does* have a recursive call to handle subforms:

>>               Case acSubform
>>                   If Len(ctl.SourceObject) > 0 Then
>>                       RequeryCombos ctl.Form

-- 
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

0
Dirk
1/12/2008 8:40:38 AM
Thanks to both of you!  I went with Dirk's code snippet 2, but thanks for
posting the other solutions as it helped me figure out what I was doing wrong.
This form continues to be a sanity saver for me.  It's all so simple when you
know the answer . . .

Dirk Goldgar wrote:
>>I can’t figure out how to run this for the life of me.
>>
>[quoted text clipped - 39 lines]
>> Any insights would be much appreciated!
>> Shauna
>
>Here's how to do it using the code framework you started with (though I have 
>an alternate suggestion which I'll post afterward):
>
>'----- start of code snippet 1 -----
>Sub RequeryAllOpenCombos()
>
>    Dim obj As AccessObject, dbs As Object
>
>    Set dbs = Application.CurrentProject
>
>    ' Search for open AccessObject objects in AllForms collection.
>    For Each obj In dbs.AllForms
>        If obj.IsLoaded = True Then
>            RequeryCombos Forms(obj.Name)
>        End If
>    Next obj
>
>End Sub
>'----- end of code snippet 1 -----
>
>But it seems to me that, since you only care about those forms that are 
>open, you could more easily use the application's Forms collection, which 
>holds all open forms.  Here's how to do it that way:
>
>'----- start of code snippet 2 -----
>Sub RequeryAllOpenCombos()
>
>    Dim frm As Access.Form
>
>    For Each frm In Application.Forms
>        RequeryCombos frm
>    Next frm
>
>End Sub
>'----- end of code snippet 1 -----
>
>That's all air code, but it should be something pretty close to that.
>

-- 
Message posted via http://www.accessmonster.com

0
lambertsm
1/12/2008 3:37:04 PM
Oops I mean this Forum.  (My forms don't actually keep me very sane.) 

lambertsm wrote:
>Thanks to both of you!  I went with Dirk's code snippet 2, but thanks for
>posting the other solutions as it helped me figure out what I was doing wrong.
>This form continues to be a sanity saver for me.  It's all so simple when you
>know the answer . . .
>
>>>I can’t figure out how to run this for the life of me.
>>>
>[quoted text clipped - 39 lines]
>>
>>That's all air code, but it should be something pretty close to that.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200801/1

0
lambertsm
1/12/2008 3:38:34 PM
Reply:

Similar Artilces:

If value in cell Then perform function #2
Hi Guys Many thanks for the solutions - they both work! Emm -- emm808 ----------------------------------------------------------------------- emm8080's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1185 View this thread: http://www.excelforum.com/showthread.php?threadid=27483 ...

public folder smtp address
In 5.5 you could use Exchange Administrator, select Global Address List node in left pane, then from the view dropdown menu public folders and hidden and get a good list to work with for checking smtp addresses. I don't find a similar view in E2K3. Anybody have any ideas? In this case tree view is not good. Can you state what you are trying to accomplish? Is it to get the e-mail addresses of all PF's? This can be accomplished in Active Directory Users & Computers. Enable the Advanced view (View menu, Advanced Features), then browse to Microsoft Exchange System Objects. ...

TEXT Function: please help
I have a sheet that keeps track of MAC to IP adresses. I've created a formula that will output the required format for DHCP server import. The formula was quite long and it was suggested to use the TEXT function . Thanks to Bob Phillips and my friend LavaDude my rather legthy formula has been reduced to the folowing, but it doesnt work correctly. =IF(D2="","","host"&L2&IF(C2="","",".")&IF(OR(B2="",B2>30,),"",B2&"-")&C2&"{ hardware ethernet "&TEXT(D2,"...

Auto Archive into a Public Folder
Can Outlook 2007 be configured so that auto archiving is done into a folder within the Public Folder system? No. Autoarchive uses pst files only. I think Auto-mate (http://www.pergenex.com/auto-mate/index.shtml) would be able to move to a public folder. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT....

Public Folder Replication. Help
When I try and create a replica on exchange server EX-3 the folder shows up but not the content. If I right click the folder in the Public Folder Tree and select replicate content I get a window where I can choose source and destination. In source I choose EX-1 and in the destination I choose EX-3. This generates a Backfill Replication message. If I track this message it goes to EX-2 and stops. It never makes it to EX-3. Please reveiw details below. All Exchange servers are running 2003 w/sp1. I have 2 Sites in site "A" there is one server(EX-1) which contains the only Public Fold...

Importing data into public folders
I am trying to get my old calendar data into a public folder i can copy the items one by one but that is taking to long is there anyway to get a pst or data file into a public folder I know i can copy the calendar folder into public folder but then it has a folder inside of a folder just trying to create one calendar folder "TOG2005" wrote: > I am trying to get my old calendar data into a public folder i can copy the > items one by one but that is taking to long is there anyway to get a pst or > data file into a public folder In news:0797FD35-C581-48A4-9E15-D5AEFB...

Exchange OWA question
All, Two questions on OWA: 1. Do I need a Front End server to enable OWA from outside for my users? I only have one Exchange 2003 server running Windows 2003. 2. When I access OWA from inside my network, the public folders are view on a different screen. I am not able to use the move items option to move or copy items from my inbox to a public folder as they are not available in the Pick Folder list. Can this be changed? Thanks, Rafael 1. No; OWA can run on that server - but be cautious in allowing connections to the server. I'd be sure to use SSL connections only. 2. I thin...

custom filter does not work correctly
I have a worksheet with one column contains a list of codes in each cell. The list can be longer than 256 characters. When I tried to use the custom filter to filter this column, it does not seem to display rows containing what I was looking for correctly. In the column, the codes is listed in a comma delimited list like aaa, bbb, ccc, ... If I tried to use the custom filter to look for a code that exists on some rows beyond 256 characters, Excel will not display these rows. Only rows that the code can be found in the first 256 characters will be filtered. Is this a limitation of Excel...

Secure Call Center Tracking
I'm looking to use MS CRM to track our members and what contacts we have with them. One of our departments operates a call center for Health Insurance question. They often deal with HIPAA covered info. I was hoping to add a checkbox labeled "secure", that if checked, restricts all access to just this particular group. Is this possible? Is there a better way? Should I just create a custom entity for call tracking and restrict access to that entity to just their team? Thanks! Doug Evans IT Manager Association of Washington Cities Doug - Yes, it is possible with the ch...

Numbers won't sort correctly.
I have a column of numbers that were imported from a web site and they won't sort correctly. I have tried reformatting the cells as general numbers but that doesn't change anything. I discovered that numbers below 100 have a space or invisible data in front of the number. If I remove it, the number then shifts to right alignment in the cell and will sort as a number. Is there any way I can remove these spaces from all numbers at once instead of going down the column row by row? Hi Fern One way Type a 1 into a blank cell on your sheet. Copy the cell. Mark the range of your data...

I have a question about using a function in a form
I have used the "workingdays2" function in a query to calculate the number of working days between two days like so... WorkingDays2([SLA_Date3],[SLA_Date4]) I have tried using this in a text box in a form but the calculation won't work. Any ideas? Another stupid moment. Date4 was earlier than Date3, duh! "SteveM" wrote: > So I assume WorkingDays2() is a user-defined public function? > SLA_Date3 and SLA_Date4 are fields on your form? > > Try putting = sign first: > > =WorkingDays2([SLA_Date3],[SLA_Date4]) > > Steve > > &quo...

Public Folders Won't Replicate
I have two exchange 2003 servers running SP 2 and am trying to replicate the folder hierachy from one server to the other. I have all the folders setup to replicate to both servers (SERVER1 and SERVER2). I have turned on message tracking and diagnostic logging as specified in the troubleshooting articles in the knowledge base. The replication message is sent from SERVER1-IS@mydomain.com and received by SERVER2-IS@mydomain.com, however the diagnostic logging shows no events related to the messages. In essence, SERVER2-IS receives the message but never processes it. The original server ...

Check to see if a formula is correct
I want to do something like this Sub Macro1() If Range("A1") =(=B1 + B2) Then ' This is a formula Exit Sub Else Range("C1").Select 'Has correct formula Selection.Copy Range("A1").Select Selection.PasteSpecial Paste:=xlFormulas End If End Sub How do I do this? Hi Try Sub Macro1() If Range("A1").HasFormula Then Exit Sub Else Range("C1").Copy Range("A1").PasteSpecial Paste:=xlFormulas Application.CutCopyMode = False ...

VBA calling sp with ANSI_NULLS set ON
Hi, On Query Analyzer, I created a sp as follow: > > if exists (select * from dbo.sysobjects where id = object_id(N'dbo.PCS_SEG1') and OBJECTPROPERTY(id, N'IsProcedure')= 1) DROP PROCEDURE dbo.PCS_SEG1 GO CREATE PROCEDURE dbo.PCS_SEG1 as set ANSI_NULLS on set ANSI_WARNINGS on ** sql statement *** > > I need the to set ANSI_NULLS and ANSI_WARNINGS on as it involves another server using linked server. When executing the sp from query analyzer, it works fine too: > exec PCS_SEG1 > But, when I called from VBA, I received error: Heterogeneous queries require t...

Word 2007 Find/Highlight Function
In Word 2007, the Find function allows you to find and highlight words or phrases in a document. However, saving the document with those 'found' words highlighted does not save the highlighted text. Additionally, if searching for multiple different words in a document, when you search on a 2nd or subsequent word, the 1st or previous 'found' word highlight all disappear. This functionality was possible in Word 2003 and XP environment. Question: In Word 2007, is there a way to highlight and keep highlighted multiple word searches/highlighted words and save the do...

Combined Functions
Hello, I am trying to streamline my work. I am trying to apply two functions to the same data at once. Is this possible? 1. PROPER(A1) 2. TRIM(A1) How would I combine the above two and have the result appear in one cell? Thanks, -- fncuis ------------------------------------------------------------------------ fncuis's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26466 View this thread: http://www.excelforum.com/showthread.php?threadid=397792 Good afternoon fncuis Try this: =TRIM(PROPER(A1)) HTH DominicB -- dominicb ---------------------------...

Problem with a Function
I had posted earlier some formulas I am trying to reduce to functions and didn't get any response. I guess it was too hard or maybe can't be done. Anyway, I'm taking a small piece of it and trying things on my own. In a cell I have the following function: =QBRushYds(1, 1) The first argument is week number corresponding to a sheet name and the second arguement is the position of the player corresponding to a cell on the sheet. The function returns an error (#Value): Here is the function definition: Public Function QBRushYds(W, Q) ' Calculates Points for R...

Workflow Call Assembly to add Date or Time
I cannot get a very simple workflow to work. All I want it to do is update a datetime field to add one month! Workflow monitor says it completed, but it has never worked... Has anyone ever been able to make this work? On 26 Mar, 18:54, "DubSport" <jamie.carmich...@cmgl.ca> wrote: > I cannot get a very simple workflow to work. All I want it to do is > update a datetime field to add one month! Workflow monitor says it > completed, but it has never worked... Has anyone ever been able to > make this work? Show your workflow rule OK here it is. its a manual rule, s...

Insert row, table to table
Hi, As I cannot use INSERT INTO with values specified to WHERE. I have created an interim temporary table where the record contains two text strings.Now I want to be able to take those text strings and append to the correct table where the record is set by an ID number. First_Name = Fred Last_Name = Jones Its a new contact so it is too be added to a customer with an ID = 1375 Now I can't use the following sql statement strsql = "INSERT INTO [tblCUSTCONTACTS] ( [FIRST_NAME], [LAST_NAME] ) " _ & " VALUES (""" & strFirst & "&...

Preserving formulas when using sort function
Hi If I have a two columns of data say, A and B and the are added together in rows in Column C eg A1 + B1 = C1 A2 + B2 = C2 etc If I then move the contents of cell A1 and put it in say A23 the value of C1 does not change because the C1 become A23 + B1. However, if I sort Column A then the value of C1 changes and the formula appears to be wrong because it now takes the new value of cell A1 How can I make sure that when the sort takes place that wherever the value in A1 ends up it is still added to B1 to create a value in C1? Any help appreciated Hi Andy, Why aren't you moving th...

Lookup function doesn't work
I am using a lookup function to compare the results in sheet1 column 1 to sheet2 column 2 and return the results of sheet2 column2. The function seems to work for the first few entries, but then returns the wrong results for some of the other entries. I am using "=LOOKUP(A7,Sheet2!$A$2:$A$16,Sheet2!$B$2:$B$16)" and it is copied down a column of text/numeric entries in the first sheet. Has anyone else had problems with this? Thanks. -- Frank L Are the values in the lookup_vector i.e. "Sheet2!$A$2:$A$16" in ascending order ? -- Rgds Max xl 97 -- Please respond, in new...

VBA Function to explode string
Hi. I am wanting to know hbow I would go about getting the following 3 cells: Cells A1, A2, A3: :: 1-4,6,8 50 S1B1:: Into a form that looks more like this ::A1 A2 A3 1 8.33 S1B1 2 8.33 S1B1 3 8.33 S1B1 4 8.33 S1B1 6 8.33 S1B1 8 8.35 S1B1:: How it does the rounding (and on which one), it does not matter! Thanks, Tom -- tomjermy ------------------------------------------------------------------------ tomjermy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24666 View this thread: http://www.excelforum.com/showthread.php?threadid=382414 You haven't exp...

Change publication design
First of all, I have set up a master page for the design for a brochure, so I have all the elements that I need - address, logo, etc. Currently I am using 'Arrows'. Now having considered it again, I am not sure that this design is the right one for the brochure so I want to go through the designs individually whilst I am in not in master page view changing the brochure so that I can get a general idea what it would look like in a different design. I suspect this isnt what you should do according to the rules of Publisher, but it seems to work for me because altho' I need ...

how to forward mail to correct domains and Exch servers,
We have an NT 4 domain (call it "NT4") and a new AD domain (call it "AD"). Domain NT4 is NT 4 with Exchange 5.5. New domain AD with Windows 2003 and very soon Exchange 2003. We have one Internet domain name ("abc.com"). How can we have mail sent to user@abc.com get routed to the correct Exchange server on the correct domain? Currently our firewall passes all SMTP traffic to the NT4 Exchange 5.5 server. I'm thinking the answer is the ADC, but will this work between two different (but trusting) domains? If this is the answer, does the ADC end up "ins...

Rand() function
Excel spreadsheets using the rand() function in Excel 2000 are not behaving the same way in Excel 2003. The distributions that were "almost-normal" are now significantly non-normal. Does anyone have a pointer to how to fix this problem ? Perhaps: The RAND function returns negative numbers in Excel 2003 http://support.microsoft.com/default.aspx?scid=kb;en-us;834520 Otherwise, RAND() in XL03 is *far* more "almost-normal" than XL00 or XL02. Description of the RAND function in Excel 2003 http://support.microsoft.com/default.aspx?scid=kb;en-us;828795...