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]

Does your subform have anything in its Link Master and Link Child properties?

Confirming, when you press Ctrl+G after the update code runs, you don't see something like this?

SELECT EnvNbr, [Date Given], Local, [M and S], Building, Memorial, Other, InMemoryOf, ToFund ...

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Duane
Here's what I see when I press Ctrl + G

SELECT EnvNbr, [Date Given], Local, [M and S], Building, Memorial, Other, InMemoryOf, ToFund  FROM tblNewGivings  WHERE EnvNbr = 719 ORDER BY EnvNbr, [Date Given]
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]
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]
SELECT EnvNbr, [Date Given], Local, [M and S], Building, Memorial, Other, InMemoryOf, ToFund  FROM tblNewGivings  WHERE EnvNbr = 719 ORDER BY EnvNbr, [Date Given]
SELECT n.EnvNbr, n.[Date Given], n.Local, n.[M and S], n.Building, n.Memorial, n.Other, n.InMemoryOf, n.ToFund  FROM tblNewGivings as n  WHERE n.EnvNbr = 719 ORDER BY n.EnvNbr, n.[Date Given]
SELECT n.EnvNbr, n.[Date Given], n.Local, n.[M and S], n.Building, n.Memorial, n.Other, n.InMemoryOf, n.ToFund  FROM tblNewGivings as n  WHERE n.EnvNbr = 719 ORDER BY n.EnvNbr, n.[Date Given]
SELECT n.EnvNbr, n.[Date Given], n.Local, n.[M and S], n.Building, n.Memorial, n.Other, n.InMemoryOf, n.ToFund  FROM tblNewGivings as n  WHERE n.EnvNbr = 12 ORDER BY n.EnvNbr, n.[Date Given]
SELECT EnvNbr, [Date Given], Local, [M and S], Building, Memorial, Other, InMemoryOf, ToFund  FROM tblNewGivings as  WHERE EnvNbr = 719 ORDER BY EnvNbr, [Date Given]
SELECT EnvNbr, [Date Given], Local, [M and S], Building, Memorial, Other, InMemoryOf, ToFund  FROM tblNewGivings  WHERE EnvNbr = 719 ORDER BY EnvNbr, [Date Given]
SELECT EnvNbr, [Date Given], Local, [M and S], Building, Memorial, Other, InMemoryOf, ToFund  FROM tblNewGivings  WHERE EnvNbr = 719 ORDER BY EnvNbr, [Date Given]
SELECT EnvNbr, [Date Given], Local, [M and S], Building, Memorial, Other, InMemoryOf, ToFund  FROM tblNewGivings  WHERE EnvNbr = 719 ORDER BY EnvNbr, [Date Given]
SELECT EnvNbr, [Date Given], Local, [M and S], Building, Memorial, Other, InMemoryOf, ToFund  FROM tblNewGivings  WHERE EnvNbr = 12 ORDER BY EnvNbr, [Date Given]

Somehow old stuff is sticking in there.

 

[THWatson]

I tried adding this line to the beginning of the After Update code for txtEnvNbr

Me.lstEnvelopes.Value = Null

Doesn't change anything.
Tom

 

[dhookom]

I think there are at least two unanswered questions:
[ul]
[li]Does your subform have anything in its Link Master and Link Child properties?[/li]
[li]confirm ' assuming EnvNbr is numeric[/li]
[/ul]

Apparently when I asked

What are you seeing in the debug window after running the code?

you weren't aware of this feature. All of your debug.print statements will stick until you delete them or quit Access.

Open a new query and go to the SQL view and paste this statement that I pulled from your debug window.

SELECT EnvNbr, [Date Given], Local, [M and S], Building, Memorial, Other, InMemoryOf, ToFund  
FROM tblNewGivings  
WHERE EnvNbr = 12 
ORDER BY EnvNbr, [Date Given]

Does your query return any records?



Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Duane
I apologize. I didn't answer your question about the linking of child and master fields in the subform fsub.NewGivings
I have looked all through the subform's properites and I can't even find a line for linking child and master fields.

Yes, EnvNbr is numeric.

As for being aware of Ctrl + G, it's been a while since I built any databases, even though I still maintain a few (they were both built and now maintained by me on a strictly volunteer basis), so my memory had forgotten that.

Running the SQL in a new query works perfectly.

So I reopened the form, deleted the lines in the Immediate (Ctrol G) window
On the main form, in the txtEnvNbr box, I entered 719
That did not populate the subform but here's what shows in the Immediate window

SELECT EnvNbr, [Date Given], Local, [M and S], Building, Memorial, Other, InMemoryOf, ToFund  FROM tblNewGivings  WHERE EnvNbr = 719 ORDER BY EnvNbr, [Date Given]

That is what it should be. So why didn't it populate the subform?
Tom

 

[THWatson]

I'm wondering where the Link master and child field lines went in Access 2016? I have only been using Office 2016 for two weeks, and some stuff has changed.

Searching on the internet, I can only find references to Access 2013. My version is Office 365, so maybe 2016 and 2013 are the same.
Tom

 

[dhookom]

I'm fairly certain they haven't moved from the property box when the subform control is selected. Every control on your main form has properties. You have a subform control that contains the fsubNewGivings form. These are the properties you need to find. I expect there might be something there that needs to be removed.

BTW: do you stay up at night working on Access ;-)

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Well, I finally found it.
On the property sheet for the main form, frmEditNewGivings, click to see the properties for fsubNewGivings.

The Link Master Fields line shows:

Forms!frmEditNewGivings!lstEnvelopes

The Link Child Fields line shows:

EnvNbr

That is what has been blocking the text box from working. I removed those links and, although I have to test a bit more, that seems to be the resolution to the problem.

Are there any safeguards I need to build in if I remove those links?

- - - - - - - -

As for your question: Do you stay up at night working on Access?
No, but it has been proccupying me for the last few days. My other...main...preoccupation has been my wife who has been ill.
I went to bed last night at 11 EST and was up this morning at 5:30.
As you may or may not remember, I am a church minister, now retired, but still have stuff to do. In an hour I have to leave and go to a town about 50 miles away and see a man who has been ill for a couple of years with Parkinson's Disease...and then on to another city to see a good friend who suffered a concussion in August and that caused agitated delirium. Had to be restrained most days for a few weeks, then ended up in a care home, and the poor guy might never come home again.
Story of my life.

 

[dhookom]

Glad to hear you found the answer. Also glad to hear you are serving so many people but sorry to hear about your wife. I think you can't refer to yourself as "retired".

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Duane
Thanks so much for sticking with me through this. I don't know what I would do without expert help.
It looks as if I have everything working fine now, and can return updates to the user.

As for the difference between being "retired" and not, it is that I don't get paid for anything anymore!

Quite an emotional day yesterday, seeing the people I went to see.

Thanks for your thoughts about my wife. She turned 80 in September, has suffered from periodic depression for years, and is just now coming out of a close to 3 week spell of it, the fourth lengthy one this year.

All the best to you in all that you do.

 

[Andrzejek]

Congratulations! You have solved your issue. And it was not the 'coding' problem, it was a 'properties' problem. Give Duallne a star by clicking on [blue]Great Post[/blue] link in his post.

But now you have 2 pieces of code that are practically the same. A nightmare to maintain, in my opinion.
What you may want to do is this:

Private Sub ShowTheForm(ByRef [blue]lngEnvNbr[/blue] As Long)
Dim sql As String

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 = " & [blue]lngEnvNbr[/blue]  _
& "ORDER BY m.EnvNbr, m.[Date Given]"

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

Me.fsubNewGivings.Form.RecordSource = sql

End Sub

and have just this code in your SfterUpdate events:

[b]Private Sub lstEnvelopes_AfterUpdate()[/b]
Dim sql As String
   On Error GoTo lstEnvelopes_AfterUpdate_Error[blue]
Call ShowTheForm(Forms!frmEditNewGivings!lstEnvelopes)[/blue]

   On Error GoTo 0
   Exit Sub

lstEnvelopes_AfterUpdate_Error:

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

[b]Private Sub txtEnvNbr_AfterUpdate()[/b]
On Error GoTo Err_txtEnvNbr_AfterUpdate_Error
[blue]
Call ShowTheForm(Forms!frmEditNewGivings!txtEnvNbr)
[/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]

Andy
Thanks. I must apologize, as I thought for certain I had given Duane a star. Anyway, I have done so now.
I also gave you one just now as you have persisted in trying to help, and appreciate your suggestions.

Actually, in the end, after I got the Text Box all worked out, I decided - upon suggestion from Duane Hookum - to change the List Box to a Combo Box. That way I don't need the Text Box as well.

All's well that ends well.

You have fun too, Andy.

Tom