command code

[Guest]

I am using a command button to open a form in a mainform:

Me.[dummy].SourceObject = “billshelpâ€

I would like that the “billshelp†Form is showing the data only based on the
clientnumber from the main form where it is opening in.
Can someone help me with the coding?
Thanks
Klaus

 

[Guest]

Private Sub ButtonName_Click()
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "billshelp"
stLinkCriteria = "[clientnumber]=" & Me![clientnumber]
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

 

[Guest]

Why don't you try something like:

(could be on the on acitivate of the subform)
Dim sSQL as String
Dim iClient as integer ->assuming number

iclient=me.parent.clientnumber

sSQL="Select * From [your tablename here] Where clientnumber= " & iclient
(if it's a textfield you should place quotes like this:

Where clientnumber ='" & iclient & "'"

Then append the sSQL as your sourceobject like this

Me.[dummy].SourceObject = sSQL

If you have navigationbuttons you must realize that code has to be
implemented on the on current of the form as well (hitting perfomance like
.....)

 

[Guest]

Dear Wayne
thanks for the code - but with this code my "billshelp" Form is opening
seperate and not in the main form. Somehow I have to merge your code with:
Me.[dummy].SourceObject = “billshelpâ€.
Do you have any idea how to do that?
Thanks
Klaus

Private Sub ButtonName_Click()
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "billshelp"
stLinkCriteria = "[clientnumber]=" & Me![clientnumber]
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub




--
Wayne
Manchester, England.

I am using a command button to open a form in a mainform:

Me.[dummy].SourceObject = “billshelpâ€

I would like that the “billshelp†Form is showing the data only based on the
clientnumber from the main form where it is opening in.
Can someone help me with the coding?
Thanks
Klaus

 

[Dirk Goldgar]

In

I am using a command button to open a form in a mainform:

Me.[dummy].SourceObject = "billshelp"

I would like that the "billshelp" Form is showing the data only based
on the clientnumber from the main form where it is opening in.
Can someone help me with the coding?

So this code is executing on the main form, and "dummy" is the name of a
subform control on that form? You can establish the filtering you want
via the Link Master Fields and Link Child Fields properties of the
subform control.

If the subform is always going to be filtered by clientnumber, you would
normally set those properties at design time. In that case, setting the
subform's SourceObject property at run time defers the loading of the
subform, but doesn't do anything else.

If you're using the same [dummy] subform control to display different
subforms, and they aren't all linked by clientnumber, then you have to
assign values to the Link Master and Child Fields properties at the time
you assign the SourceObject. You might do this:

With Me![dummy]
On Error Resume Next ' temporarily ignore errors
.LinkMasterFields = "clientnumber"
.LinkChildFields = "clientnumber"
' restore original error-handler
On Error Go To YourErrorHandlerCode
.SourceObject = "billshelp"
End With

The reason for temporarily disabling error-handling is that I have found
that Access raises an error if, in the process of setting the Link
Master/Child fields properties, I momentarily have a different number of
fields listed in one of the properties from the number of fields listed
in the other property. However, by the time I'm done setting both
properties, all is well again.

 

[Guest]

If form "billshelp" will only open as a subform, or when your main form is
open, you could modify its underlying record source (query) so that the
ClientNumber uses the main forms ClientNumber field. Your Record source
would look something like:

SELECT * FROM yourTable
WHERE [ClientNumber] = Forms!frm_MainForm.txt_ClientNumber

HTH
Dale