lstBox Record Set won't change

[RobUCSD via AccessMonster.com]

With the code below I'm attempting to change the row source for lstRNnotesLU
(a list box) based on a value entered on the frmVisitNewEdit field called
ReasonForVisitTest. For some reason it is not working. Then record set is not
changed by the code. It continues to use the underlying query for the
rowsource rather than the rowsource based on the code below. Your help is
greatly appreciated. Thanks Rob
******************************************************************************************************************
Dim T_Visit As String

T_Visit = Nz(Forms!frmPtDemographicNew!frmVisitNewEdit.Form!.
ReasonForVisitTest, "")

If T_Visit = "Ablation" Then

Me.lstRNnotesLU.RowSource = "SELECT * FROM tblRNnotesLU " & _
"WHERE fldRNnotesCode = 'A'" & "ORDER BY [fld.order]"

End If
************************************************************************************************************

 

[Douglas J. Steele]

Try issuing a Requery:

Dim T_Visit As String

T_Visit =
Nz(Forms!frmPtDemographicNew!frmVisitNewEdit.Form!ReasonForVisitTest, "")

If T_Visit = "Ablation" Then
Me.lstRNnotesLU.RowSource = "SELECT * FROM tblRNnotesLU " & _
"WHERE fldRNnotesCode = 'A' ORDER BY [fld.order]"
Me.lstRNnotesLU.Requery
End If

(You were (still) missing the space between the WHERE clause and the ORDER
BY clause, so I changed your SQL slightly)

If that doesn't work, make sure that the code is actually running: sometimes
procedures get detached from their events.

 

[RobUCSD via AccessMonster.com]

Thank You Doug, I think your right re: the code being detached from the
procedure as there is no change in the rowsource. How can I re-attach it?
Thanks, Rob

Try issuing a Requery:

Dim T_Visit As String

T_Visit =
Nz(Forms!frmPtDemographicNew!frmVisitNewEdit.Form!ReasonForVisitTest, "")

If T_Visit = "Ablation" Then
Me.lstRNnotesLU.RowSource = "SELECT * FROM tblRNnotesLU " & _
"WHERE fldRNnotesCode = 'A' ORDER BY [fld.order]"
Me.lstRNnotesLU.Requery
End If

(You were (still) missing the space between the WHERE clause and the ORDER
BY clause, so I changed your SQL slightly)

If that doesn't work, make sure that the code is actually running: sometimes
procedures get detached from their events.

With the code below I'm attempting to change the row source for
lstRNnotesLU

[quoted text clipped - 18 lines]

End If
************************************************************************************************************

 

[Douglas J. Steele]

Check my March, 2004 "Access Answers" column in Pinnacle Publication's
"Smart Access".

You can download the column (and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank You Doug, I think your right re: the code being detached from the
procedure as there is no change in the rowsource. How can I re-attach it?
Thanks, Rob

Try issuing a Requery:

Dim T_Visit As String

T_Visit =
Nz(Forms!frmPtDemographicNew!frmVisitNewEdit.Form!ReasonForVisitTest, "")

If T_Visit = "Ablation" Then
Me.lstRNnotesLU.RowSource = "SELECT * FROM tblRNnotesLU " & _
"WHERE fldRNnotesCode = 'A' ORDER BY [fld.order]"
Me.lstRNnotesLU.Requery
End If

(You were (still) missing the space between the WHERE clause and the ORDER
BY clause, so I changed your SQL slightly)

If that doesn't work, make sure that the code is actually running:
sometimes
procedures get detached from their events.

With the code below I'm attempting to change the row source for
lstRNnotesLU

[quoted text clipped - 18 lines]

End If
************************************************************************************************************

 

[RobUCSD via AccessMonster.com]

I've been trying to figure out what to do with the your article, but I just
don't have the skills. I put the code into the current event and it still
did nothing, so now I don't think the procedure is detached from the code.
went back and tried it in earlier versions of the app with the same results.
I don't know if you can help me, but I sure need help. Rob

Check my March, 2004 "Access Answers" column in Pinnacle Publication's
"Smart Access".

You can download the column (and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html

Thank You Doug, I think your right re: the code being detached from the
procedure as there is no change in the rowsource. How can I re-attach it?

[quoted text clipped - 25 lines]

 

[Douglas J. Steele]

You don't put the code anywhere in your code.

Import the module mdlFormEvents into your database.

Create a module along the lines of:

Sub FixEvents()
Dim strResults As String

If CheckAllForms(strResults) = True Then
MsgBox "No errors found"
Else
MsgBox strResults
End If

End Sub

(add it to mdlFormEvents)

Go to the Immediate Window (Ctrl-G), type Run FixEvents, then hit Enter.

You'll get a message box open up, either saying "No errors found", or
listing what fixes it made.

You can then delete module mdlFormEvents if you want.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I've been trying to figure out what to do with the your article, but I
just
don't have the skills. I put the code into the current event and it still
did nothing, so now I don't think the procedure is detached from the code.
went back and tried it in earlier versions of the app with the same
results.
I don't know if you can help me, but I sure need help. Rob

Check my March, 2004 "Access Answers" column in Pinnacle Publication's
"Smart Access".

You can download the column (and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html

Thank You Doug, I think your right re: the code being detached from the
procedure as there is no change in the rowsource. How can I re-attach
it?

[quoted text clipped - 25 lines]

End If
************************************************************************************************************

 

[RobUCSD via AccessMonster.com]

Doug, I created a module from the Sub FixEvents code you posted, then I
placed the code in the mdlFormEvents and typed Run FixEvents and hit enter. I
get Compile Error: Ambiguos name detected:FixEvents.

In your note you said create a module from this code; Sub FixEvents()
*******************************************************************************************
Dim strResults As String

If CheckAllForms(strResults) = True Then
MsgBox "No errors found"
Else
MsgBox strResults
End If

End Sub
*******************************************************************************
I did create a module with this code called mdlFixEvents. I don't know how to
insert a module in a module. Thanks for your kidness and persistance. Rob
Douglas J. Steele wrote:

You don't put the code anywhere in your code.

Import the module mdlFormEvents into your database.

Create a module along the lines of:

Sub FixEvents()
Dim strResults As String

If CheckAllForms(strResults) = True Then
MsgBox "No errors found"
Else
MsgBox strResults
End If

End Sub

(add it to mdlFormEvents)

Go to the Immediate Window (Ctrl-G), type Run FixEvents, then hit Enter.

You'll get a message box open up, either saying "No errors found", or
listing what fixes it made.

You can then delete module mdlFormEvents if you want.

I've been trying to figure out what to do with the your article, but I
just

[quoted text clipped - 16 lines]

 

[Douglas J. Steele]

Sorry: typo. I meant to say "Create a sub along the lines of..."

In other words, copy the following:

Sub djsFixEvents()
Dim strResults As String

If CheckAllForms(strResults) = True Then
MsgBox "No errors found"
Else
MsgBox strResults
End If

End Sub

and paste it into mdlFormEvents (put it at the end of the module)

Note that I renamed the sub, since obviously you already have a sub named
FixEvents in your application. That means you need to type

Run djsFixEvents

in the immediate window.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Doug, I created a module from the Sub FixEvents code you posted, then I
placed the code in the mdlFormEvents and typed Run FixEvents and hit
enter. I
get Compile Error: Ambiguos name detected:FixEvents.

In your note you said create a module from this code; > I did create a
module with this code called mdlFixEvents. I don't know how to
insert a module in a module. Thanks for your kidness and persistance. Rob
Douglas J. Steele wrote:

You don't put the code anywhere in your code.

Import the module mdlFormEvents into your database.

Create a module along the lines of:

Sub FixEvents()
Dim strResults As String

If CheckAllForms(strResults) = True Then
MsgBox "No errors found"
Else
MsgBox strResults
End If

End Sub

(add it to mdlFormEvents)

Go to the Immediate Window (Ctrl-G), type Run FixEvents, then hit Enter.

You'll get a message box open up, either saying "No errors found", or
listing what fixes it made.

You can then delete module mdlFormEvents if you want.

I've been trying to figure out what to do with the your article, but I
just

[quoted text clipped - 16 lines]

End If
************************************************************************************************************

 

[RobUCSD via AccessMonster.com]

I ran it and it came up "no errors were found". I'm absolutely lost. Thanks
again for your help and patience. If you have any other suggestions I'd love
to hear them and try them. Rob

Sorry: typo. I meant to say "Create a sub along the lines of..."

In other words, copy the following:

Sub djsFixEvents()
Dim strResults As String

If CheckAllForms(strResults) = True Then
MsgBox "No errors found"
Else
MsgBox strResults
End If

End Sub

and paste it into mdlFormEvents (put it at the end of the module)

Note that I renamed the sub, since obviously you already have a sub named
FixEvents in your application. That means you need to type

Run djsFixEvents

in the immediate window.

Doug, I created a module from the Sub FixEvents code you posted, then I
placed the code in the mdlFormEvents and typed Run FixEvents and hit

[quoted text clipped - 36 lines]

 

[RobUCSD via AccessMonster.com]

Doug, I got it! It works correctly when I choos only one visit code criteria.
I just need help with the OR syntax. Here's what I've got:
**********************************************************************************************************************************
Dim T_Visit As String

T_Visit = Nz(Forms!frmPtDemographicNew!frmVisitNewEdit.Form!.
fldVisitType, "")
If T_Visit = "2" Then
Me.lstRNnotesLU.RowSource = "SELECT * FROM tblRNnotesLookUp " & _
"WHERE fldRNnotesCode = 'D' OR = 'E' ORDER BY [fldRNnotesCode]"
Me.lstRNnotesLU.Requery
End If
*********************************************************
If I take of the OR = 'E' part it works great. Probably another of my syntax
problems. Thanks for all your help Doug. Rob

Sorry: typo. I meant to say "Create a sub along the lines of..."

In other words, copy the following:

Sub djsFixEvents()
Dim strResults As String

If CheckAllForms(strResults) = True Then
MsgBox "No errors found"
Else
MsgBox strResults
End If

End Sub

and paste it into mdlFormEvents (put it at the end of the module)

Note that I renamed the sub, since obviously you already have a sub named
FixEvents in your application. That means you need to type

Run djsFixEvents

in the immediate window.

Doug, I created a module from the Sub FixEvents code you posted, then I
placed the code in the mdlFormEvents and typed Run FixEvents and hit

[quoted text clipped - 36 lines]

 

[RobUCSD via AccessMonster.com]

Doug, I got the thing to work exactly the way it is supposed to work. Oh man,
what a work out. Again, you are the best and I appreciate your help super
duper whole lot.
Rob


Dim T_Visit As String
T_Visit = Nz(Forms!frmPtDemographicNew!frmVisitNewEdit.Form!.
fldVisitType, "")
Debug.Print T_Visit
If T_Visit = "2" Then
Me.lstRNnotesLU.RowSource = "SELECT * FROM tblRNnotesLookUp " & _
"WHERE fldRNnotesCode = 'D' OR fldRNnotesCode = 'E' ORDER BY
[fldorder]"
Me.lstRNnotesLU.Requery
End If

Sorry: typo. I meant to say "Create a sub along the lines of..."

In other words, copy the following:

Sub djsFixEvents()
Dim strResults As String

If CheckAllForms(strResults) = True Then
MsgBox "No errors found"
Else
MsgBox strResults
End If

End Sub

and paste it into mdlFormEvents (put it at the end of the module)

Note that I renamed the sub, since obviously you already have a sub named
FixEvents in your application. That means you need to type

Run djsFixEvents

in the immediate window.

Doug, I created a module from the Sub FixEvents code you posted, then I
placed the code in the mdlFormEvents and typed Run FixEvents and hit

[quoted text clipped - 36 lines]