UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Syntax For Passing And Using Variables In Subroutines    
 
   
muse43
post Aug 15 2016, 02:14 PM
Post#1



Posts: 30
Joined: 21-January 13



Hi!

I am working on programming my first form that will point to a sub in an outside module using variables. (i.e. instead of writing the code on the form's VBA sheet - or object- I am writing it in a new module and calling it from the form's code).

I use the code below a lot so I thought it would be a good one to start with (for learning). In this case, it is used to open a Clientdetails Form from a main Form but once opened, I want the user to still be able to navigate to other clients while staying on the client details form. The code itself works fine but when trying to put it in a separate module and use variables, I am having trouble.

Here is the code so far:

On the form's page :

Private Sub GoToClientDetails_Click()

OpenToBookmarkM.OpenToBookmark "ClientDetailsF", Me.ID

End Sub

On the module page (the actual routine):

Sub OpenToBookmark(FormName As String, CurrentID As Integer)

DoCmd.OpenForm FormName, , , "[ID]=" & CurrentID

Dim intID As Integer
Dim rs As Object
intID = Forms!ClientDetailsF.ID
Forms!ClientDetailsF.FilterOn = False
Set rs = Forms!ClientDetailsF.RecordsetClone
With rs
.FindFirst "[ID]=" & intID
Forms!ClientDetailsF.Bookmark = .Bookmark
End With
rs.Close

End Sub


As it stands it works. So far I've only used the variables to open the new form (DoCmd.OpenForm FormName, , , "[ID]=" & CurrentID).

Next, I want to replace intID = Forms!ClientDetailsF.ID with intID = Forms!FormName.ID

This doesn't work though. I believe it is because FormName is a string and I can't just stick it in there. I've tried a few other things but nothing seems to work and I don't know where to go next. How to replace all instances of "ClientDetailsF" in my routine so that I can use it for any other form as well in the future?

Any help on this would be very much appreciated.

Thanks!
Go to the top of the page
 
theDBguy
post Aug 15 2016, 02:20 PM
Post#2


Access Wiki and Forums Moderator
Posts: 71,220
Joined: 19-June 07
From: SunnySandyEggo


Hi. When you use the WhereCondition argument of the DoCmd.OpenForm method, you are indeed setting a filter. But then, you turn around and set the Filter off (FilterOn = False), which defeats the purpose of using the WhereCondition argument. If you want the users to have the ability to navigate through other records on the details form, then I would suggest not using the WhereCondition argument, so you won't have to turn Filter off.

And to answer your question, you can use the following syntax:

Forms(FormName)

Hope it helps...
Go to the top of the page
 
muse43
post Aug 15 2016, 02:42 PM
Post#3



Posts: 30
Joined: 21-January 13



Thanks for your quick reply!

I use this snippet in many places. Sometimes, I'm opening a form and cannot use the ID for some reason and so I use a field name instead. In these cases the where condition just gets me to the right client details page so that I can grab the ID and put it in IntID. Once I have that I can turn off my filter and then use the bookmark to get back to where I was but now can also navigate from there.

In this case though I'm not using a field name, I'm using the ID directly. So I guess like you say, I can shortcut and just use the following code... which I just tried and it works.

Sub OpenToBookmark(FormName As String, CurrentID As Integer)

DoCmd.OpenForm FormName

Dim intID As Integer
Dim rs As Object
intID = CurrentID
Forms!ClientDetailsF.FilterOn = False
Set rs = Forms!ClientDetailsF.RecordsetClone
With rs
.FindFirst "[ID]=" & intID
Forms!ClientDetailsF.Bookmark = .Bookmark
End With
rs.Close

End Sub


Now to substitute the other places where I use "ClientDetailsF" you say I can replace with Forms(FormName). So for example, I would replace

Forms!ClientDetailsF.FilterOn = False

with

Forms(FormName).FilterOn = False

Is that correct?

thanks again!
Go to the top of the page
 
muse43
post Aug 15 2016, 02:45 PM
Post#4



Posts: 30
Joined: 21-January 13



OK tried it and it works! Thank you!

Here's the new working code:

Sub OpenToBookmark(FormName As String, CurrentID As Integer)

DoCmd.OpenForm FormName

Dim intID As Integer
Dim rs As Object
intID = CurrentID
Forms(FormName).FilterOn = False
Set rs = Forms(FormName).RecordsetClone
With rs
.FindFirst "[ID]=" & intID
Forms(FormName).Bookmark = .Bookmark
End With
rs.Close

End Sub
Go to the top of the page
 
theDBguy
post Aug 15 2016, 02:59 PM
Post#5


Access Wiki and Forums Moderator
Posts: 71,220
Joined: 19-June 07
From: SunnySandyEggo


Hi. Congratulations! Glad to hear you got it working. Good luck with your project.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 10:21 PM