code to update list box with results of text box 2

[THWatson]

Using Access 2016
Form frmEditNewGivings (for editing donations made by donors, or deleting entry made in error
Has subform fsubNewGivings

On the Form, there is a List box containing all the Envelope numbers assigned.
User can select a number from the List box. This opens the subform to donations matching the selected envelope number.
The following code works.

Private Sub lstEnvelopes_AfterUpdate()
Dim sql As String
   On Error GoTo lstEnvelopes_AfterUpdate_Error

sql = "SELECT m.EnvNbr, m.[Date Given], m.Local, m.[M and S], m.Building, m.Memorial, m.Other, m.InMemoryOf, m.ToFund " _
& "FROM tblNewGivings as m " _
& "WHERE m.EnvNbr = Forms!frmEditNewGivings!lstEnvelopes " _
& "ORDER BY m.EnvNbr, m.[Date Given]"

Me.fsubNewGivings.Visible = True
Me.lblEdit.Visible = True
'Me.cmdDelete.Visible = True
'Me.Box8.Visible = True
Me.fsubNewGivings.Form.RecordSource = sql

   On Error GoTo 0
   Exit Sub

lstEnvelopes_AfterUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure lstEnvelopes_AfterUpdate of VBA Document Form_frmEditNewGivings"

What I want also to do is include a text box above the List Box, where the user can type in the desired envelope number, removing the necessity to scroll down through the List Box.

Here's what I have tried:

Private Sub txtEnvNbr_AfterUpdate()
    On Error GoTo Err_txtEnvNbr_AfterUpdate_Error

Dim sql1 As String

sql1 = "SELECT m.EnvNbr, m.[Date Given], m.Local, m.[M and S], m.Building, m.Memorial, m.Other, m.InMemoryOf, m.ToFund " _
& "FROM tblNewGivings as m " _
& "WHERE m.EnvNbr = Forms!frmEditNewGivings!txtEnvNbr " _
& "ORDER BY m.EnvNbr, m.[Date Given]"

Me.fsubNewGivings.Visible = True
Me.lblEdit.Visible = True
Me.txtEnvNbr = Null

On Error GoTo 0
   Exit Sub

Err_txtEnvNbr_AfterUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure txtEnvNbr_AfterUpdate of VBA Document Form_frmEditNewGivings"

End Sub

This works okay for one entry. But if the editing is completed, and the user enters a new envelope number the subform doesn't change to reflect that entry.
It's as if once the user plugs an entry in that text box, things come to a halt.

What do I have to do to modify the code?

Thanks.
Tom

 

[dhookom]

[pre]Private Sub txtEnvNbr_AfterUpdate()[/pre] doesn't have any code that updates any recordsource. You build an SQL statement but ignore it. Also, I would change the code like:

[COLOR=#4E9A06]' assuming EnvNbr is numeric[/color]
sql1 = "SELECT m.EnvNbr, m.[Date Given], m.Local, m.[M and S], m.Building, m.Memorial, m.Other, m.InMemoryOf, m.ToFund " _
& "FROM tblNewGivings as m " _
& "WHERE m.EnvNbr = [highlight #FCE94F]" & Forms!frmEditNewGivings!txtEnvNbr & "[/highlight] " _
& "ORDER BY m.EnvNbr, m.[Date Given]"

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Duane
I see what you mean by building an SQL statement that updates the record source.
I have added...to follow the sql1 line, the following code

Me.fsubNewGivings.Form.RecordSource = sql1

But it doesn't do the trick.

I guess I'm not clear on what I have to fix in the part you highlighted:

" & Forms!frmEditNewGivings!txtEnvNbr & "

Tom

 

[THWatson]

I tried

 & "WHERE m.EnvNbr = 'txtEnvNbr' " _

but that didn't fix anything.

 

[Andrzejek]

But it doesn't do the trick." What does it do? Crashes? Errors?

Try:

Private Sub txtEnvNbr_AfterUpdate()
On Error GoTo Err_txtEnvNbr_AfterUpdate_Error

Dim sql1 As String

sql1 = "SELECT EnvNbr, [Date Given], Local, [M and S], Building, Memorial, Other, InMemoryOf, ToFund " _
& " FROM tblNewGivings " _
& " WHERE EnvNbr = & " Forms!frmEditNewGivings!txtEnvNbr _
& " ORDER BY EnvNbr, [Date Given]"
[blue]
Debug.Print sql1
[/blue]
Me.fsubNewGivings.Visible = True
Me.lblEdit.Visible = True
Me.txtEnvNbr = Null
[blue]
Me.fsubNewGivings.Form.RecordSource = sql1
[/blue]
On Error GoTo 0
   Exit Sub

Err_txtEnvNbr_AfterUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure txtEnvNbr_AfterUpdate of VBA Document Form_frmEditNewGivings"

End Sub

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[THWatson]

Nothing happens. Just doesn't work!

Works fine from the List Box, but not the text box.

I'll try the code you supplied. Thanks.

 

[dhookom]

Tom,
One of your issues was setting your subform's record source to a filter using a reference to Forms!frmEditNewGivings!txtEnvNbr and then setting the Forms!frmEditNewGivings!txtEnvNbr to Null. That's why both Andy and I suggested using the value of Forms!frmEditNewGivings!txtEnvNbr rather than the name of the control in your record source.


Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Andy
Your code errors out on the line:

& " WHERE EnvNbr = & " Forms!frmEditNewGivings!txtEnvNbr

Highlights the word Forms

Compile error. Expected end of Statement.

I have tried various things to fix it. No luck so far.

 

[MajP]

That is a typo the & is inside the parentheses
& " WHERE EnvNbr = " & Forms!frmEditNewGivings!txtEnvNbr

 

[MajP]

A couple of things
If you want the listbox to filter a subform you can simply do this by linking the subform control to the listbox
Link MasterFields:[lstEnvelopes]
link childfields:[envNBR]
done, no code

If you simply want to filter the subform you can also use the filter property

Private Sub txtEnvNbr_AfterUpdate()

Dim strFilter as string
if not isnull(Forms!frmEditNewGivings!txtEnvNbr)
  strFilter = "EnvNbr = " & Forms!frmEditNewGivings!txtEnvNbr
  with Me.fsubNewGivings.Form
    .filter = strFilter
    .filterOn = true
  end with
end if  
end sub

However, the previously provided code should work as well, but as you can see it is often more difficult to create a proper sql string.

 

[MajP]

Here is a demo of filtering a subform without code using a link

 

[Andrzejek]

Yes, that was a typo (sorry about that) and MayP is right.
I also included Debug.Print so you can see if you get a valid SQL

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

Tom,

Andy's code contained a typo. This is the type of error that you should be able to resolve quite quickly by looking at the code and moving the & to the right of the double-quote.
from

& " WHERE EnvNbr = & " Forms!frmEditNewGivings!txtEnvNbr

to

& " WHERE EnvNbr = " & Forms!frmEditNewGivings!txtEnvNbr

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Well, I certainly appreciate all the help...and I have corrected the typo in Andy's code (which I should have caught myself)...
BUT

Truth is whether I use Andy's Code or the string filter supplied by MajP, the results are the same. I plug the value in the text box, press the <Enter> key, but the subform does not show any results. Not only an incorrect results, no results! The subform simply doesn't populate.

Clicking on a value in the List Box works like a charm. Always has.
I was only trying to add a usable feature to the form, so that rather than the user having to scroll down through a large list in the List Box she would be able to enter the desired envelope number in the text box and press <Enter>.

Somehow, now, after worrying it to death, I have lost sight of not only the trees but the whole dadblasted forest.
Tom

 

[dhookom]

Andy suggested you use Debug.Print in your code. What are you seeing in the debug window after running the code?

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Nothing.

 

[dhookom]

So the code isn't even running?

If you add a first line of code like:

Msgbox "Test"

Do you see the message box? Does your code compile?

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Yes, I see the message box.
And yes, the code compiles.
Tom

 

[dhookom]

Please share your code.

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Duane
Here is code from the List Box...and this works fine.

Private Sub lstEnvelopes_AfterUpdate()
Dim sql As String
   On Error GoTo lstEnvelopes_AfterUpdate_Error

sql = "SELECT m.EnvNbr, m.[Date Given], m.Local, m.[M and S], m.Building, m.Memorial, m.Other, m.InMemoryOf, m.ToFund " _
& "FROM tblNewGivings as m " _
& "WHERE m.EnvNbr = Forms!frmEditNewGivings!lstEnvelopes " _
& "ORDER BY m.EnvNbr, m.[Date Given]"

Me.fsubNewGivings.Visible = True
Me.lblEdit.Visible = True

Me.fsubNewGivings.Form.RecordSource = sql

   On Error GoTo 0
   Exit Sub

lstEnvelopes_AfterUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure lstEnvelopes_AfterUpdate of VBA Document Form_frmEditNewGivings"

Here is the code from the Text Box...and it doesn't work at all, except for the Message Box you suggested I stick in there.

Private Sub txtEnvNbr_AfterUpdate()
On Error GoTo Err_txtEnvNbr_AfterUpdate_Error
MsgBox "Test"

Dim sql1 As String

sql1 = "SELECT EnvNbr, [Date Given], Local, [M and S], Building, Memorial, Other, InMemoryOf, ToFund " _
& " FROM tblNewGivings " _
& " WHERE EnvNbr = " & Forms!frmEditNewGivings!txtEnvNbr _
& " ORDER BY EnvNbr, [Date Given]"
Debug.Print sql1


Me.fsubNewGivings.Visible = True
Me.fsubNewGivings.Form.RecordSource = sql1

On Error GoTo 0
   Exit Sub

Err_txtEnvNbr_AfterUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure txtEnvNbr_AfterUpdate of VBA Document Form_frmEditNewGivings"
End Sub

I also tried using the code formulation that is used in the List Box except using "n" rather than "m" as the pseudo table. Doesn't change anything.

To see whether or not the code ran that far, after the line that sets the record source, I put

MsgBox "Go to bed"

And that runs.