changing a record source

  • Thread starter Breecy via AccessMonster.com
  • Start date
B

Breecy via AccessMonster.com

I have a button on my form to allow a user to search for a portfolio number.
The user enters the number and then clicks O.K. on the form. What I want to
happen is the form displays with the results. What currently happens is the
screen goes blank. Here is my code:

Form buttons:

Private Sub cmdCancel_Click()
DoCmd.Close acForm, frmSearchPortfolio
End Sub

Private Sub cmdOK_Click()
If txtPortSearch = "" Then
MsgBox " A portfolio number has not been entered." & vbCrLf & _
"Please enter a portfolio number or click cancel", vbOKOnly +
vbInformation
Else: txtPortSearch = strPortSearch
Call Search_Rec
DoCmd.Close acForm, "frmSearchPortfolio"
End If
End Sub

Sub Code:



Sub Search_Rec()
Dim strRsp As String
Dim strSQL As String


strSQL = "SELECT MASTER.PORTFOLIO_ID, MASTER.ACCOUNT_NAME, " & _
"MASTER.PORT_SHORT_NAME,MASTER.PRY_MGR_ID, CrossRef.Sg, " & _
"CrossRef.[Sg Dummy], tblxRefERS.ERS, tblxRefCID.Client_ID " & _
"FROM tblxRefCID RIGHT JOIN (tblxRefERS RIGHT JOIN (CrossRef INNER JOIN
MASTER ON CrossRef.P = MASTER.PORTFOLIO_ID) " & _
"ON tblxRefERS.FUND = CrossRef.FUND) ON tblxRefCID.FUND = CrossRef.FUND " & _
"WHERE(((MASTER.PORTFOLIO_ID)='" & strPortSearch & "'));"

Forms!frmMain.RecordSource = strSQL
Forms!frmMain.Recalc
Forms!frmMain.Refresh

End Sub

strPortSearch is a global variable. Any help is greatly appriciated.
 
B

Breecy via AccessMonster.com

I should say the form goes blank.
I have a button on my form to allow a user to search for a portfolio number.
The user enters the number and then clicks O.K. on the form. What I want to
happen is the form displays with the results. What currently happens is the
screen goes blank. Here is my code:

Form buttons:

Private Sub cmdCancel_Click()
DoCmd.Close acForm, frmSearchPortfolio
End Sub

Private Sub cmdOK_Click()
If txtPortSearch = "" Then
MsgBox " A portfolio number has not been entered." & vbCrLf & _
"Please enter a portfolio number or click cancel", vbOKOnly +
vbInformation
Else: txtPortSearch = strPortSearch
Call Search_Rec
DoCmd.Close acForm, "frmSearchPortfolio"
End If
End Sub

Sub Code:

Sub Search_Rec()
Dim strRsp As String
Dim strSQL As String

strSQL = "SELECT MASTER.PORTFOLIO_ID, MASTER.ACCOUNT_NAME, " & _
"MASTER.PORT_SHORT_NAME,MASTER.PRY_MGR_ID, CrossRef.Sg, " & _
"CrossRef.[Sg Dummy], tblxRefERS.ERS, tblxRefCID.Client_ID " & _
"FROM tblxRefCID RIGHT JOIN (tblxRefERS RIGHT JOIN (CrossRef INNER JOIN
MASTER ON CrossRef.P = MASTER.PORTFOLIO_ID) " & _
"ON tblxRefERS.FUND = CrossRef.FUND) ON tblxRefCID.FUND = CrossRef.FUND " & _
"WHERE(((MASTER.PORTFOLIO_ID)='" & strPortSearch & "'));"

Forms!frmMain.RecordSource = strSQL
Forms!frmMain.Recalc
Forms!frmMain.Refresh

End Sub

strPortSearch is a global variable. Any help is greatly appriciated.
Click to expand...
 
D

Dirk Goldgar

Breecy via AccessMonster.com said:
I have a button on my form to allow a user to search for a portfolio
number.
The user enters the number and then clicks O.K. on the form. What I want
to
happen is the form displays with the results. What currently happens is
the
screen goes blank. Here is my code:

Form buttons:

Private Sub cmdCancel_Click()
DoCmd.Close acForm, frmSearchPortfolio
End Sub

Private Sub cmdOK_Click()
If txtPortSearch = "" Then
MsgBox " A portfolio number has not been entered." & vbCrLf & _
"Please enter a portfolio number or click cancel", vbOKOnly +
vbInformation
Else: txtPortSearch = strPortSearch
Call Search_Rec
DoCmd.Close acForm, "frmSearchPortfolio"
End If
End Sub

Sub Code:



Sub Search_Rec()
Dim strRsp As String
Dim strSQL As String


strSQL = "SELECT MASTER.PORTFOLIO_ID, MASTER.ACCOUNT_NAME, " & _
"MASTER.PORT_SHORT_NAME,MASTER.PRY_MGR_ID, CrossRef.Sg, " & _
"CrossRef.[Sg Dummy], tblxRefERS.ERS, tblxRefCID.Client_ID " & _
"FROM tblxRefCID RIGHT JOIN (tblxRefERS RIGHT JOIN (CrossRef INNER JOIN
MASTER ON CrossRef.P = MASTER.PORTFOLIO_ID) " & _
"ON tblxRefERS.FUND = CrossRef.FUND) ON tblxRefCID.FUND = CrossRef.FUND "
& _
"WHERE(((MASTER.PORTFOLIO_ID)='" & strPortSearch & "'));"

Forms!frmMain.RecordSource = strSQL
Forms!frmMain.Recalc
Forms!frmMain.Refresh

End Sub

strPortSearch is a global variable. Any help is greatly appriciated.
Click to expand...


The idea is that the user enters the portfolio number in txtPortSearch, it
gets assigned to global variable strPortSearch, and then you call Search_Rec
to search for it?

If so, the fundamental problem is that you have your assignment statement
reversed. You said:
Else: txtPortSearch = strPortSearch
Click to expand...

.... when you should have written:

Else
strPortSearch = Me.txtPortSearch

(Note: I took out the colon and placed the Else statement and its first
consequence on separate lines, since this is a "block If" structure.)

There are other, more minor problems in your code. This statement:
If txtPortSearch = "" Then
Click to expand...

.... should allow for Null in the text box, which is much more likely for an
unbound text box than the zero-length string "". You can cover both
possibilities like this:

If Len(Me.txtPortSearch & "") = 0 Then

Also, after reassigning the recordsource in Search_Rec, you don't need these
lines at all and they should be removed:
Forms!frmMain.Recalc
Forms!frmMain.Refresh
Click to expand...

It isn't clear why you need the global variable strPortSearch, and don't
just pass the portfolio number to the Search_Rec procedure as an argument.
Global variables have their uses but also their weaknesses, and should
generally be avoided where possible.
 
B

Breecy via AccessMonster.com

All very good points!!! Thanks so much. I will try it and let you know.
But I am pretty sure of my success. It is great to have a second pair of
eyes. Thanks a bunch Dirk.

Dirk said:
I have a button on my form to allow a user to search for a portfolio
number.
Click to expand...
[quoted text clipped - 43 lines]
strPortSearch is a global variable. Any help is greatly appriciated.
Click to expand...

The idea is that the user enters the portfolio number in txtPortSearch, it
gets assigned to global variable strPortSearch, and then you call Search_Rec
to search for it?

If so, the fundamental problem is that you have your assignment statement
reversed. You said:
Else: txtPortSearch = strPortSearch
Click to expand...

... when you should have written:

Else
strPortSearch = Me.txtPortSearch

(Note: I took out the colon and placed the Else statement and its first
consequence on separate lines, since this is a "block If" structure.)

There are other, more minor problems in your code. This statement:
If txtPortSearch = "" Then
Click to expand...

... should allow for Null in the text box, which is much more likely for an
unbound text box than the zero-length string "". You can cover both
possibilities like this:

If Len(Me.txtPortSearch & "") = 0 Then

Also, after reassigning the recordsource in Search_Rec, you don't need these
lines at all and they should be removed:
Forms!frmMain.Recalc
Forms!frmMain.Refresh
Click to expand...

It isn't clear why you need the global variable strPortSearch, and don't
just pass the portfolio number to the Search_Rec procedure as an argument.
Global variables have their uses but also their weaknesses, and should
generally be avoided where possible.
Click to expand...
 
B

Breecy via AccessMonster.com

worked perfectly.

All very good points!!! Thanks so much. I will try it and let you know.
But I am pretty sure of my success. It is great to have a second pair of
eyes. Thanks a bunch Dirk.
[quoted text clipped - 39 lines]
Global variables have their uses but also their weaknesses, and should
generally be avoided where possible.
Click to expand...
Click to expand...
 
Top