INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

code to update list box with results of text box

code to update list box with results of text box

(OP)
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.

CODE

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:

CODE

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

RE: code to update list box with results of text box

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

CODE --> vba

' assuming EnvNbr is numeric
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]" 

Duane
Hook'D on Access
MS Access MVP

RE: code to update list box with results of text box

(OP)
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

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:

CODE

" & Forms!frmEditNewGivings!txtEnvNbr & " 

Tom

RE: code to update list box with results of text box

(OP)
I tried

CODE

& "WHERE m.EnvNbr = 'txtEnvNbr' " _ 
but that didn't fix anything.

RE: code to update list box with results of text box

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

Try:

CODE

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]"

Debug.Print sql1

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

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 

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: code to update list box with results of text box

(OP)
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.

RE: code to update list box with results of text box

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

RE: code to update list box with results of text box

(OP)
Andy
Your code errors out on the line:

CODE

& " 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.

RE: code to update list box with results of text box

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

RE: code to update list box with results of text box

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

CODE -->

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.

RE: code to update list box with results of text box

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.

RE: code to update list box with results of text box

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

CODE --> vba

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

to

CODE --> vba

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

Duane
Hook'D on Access
MS Access MVP

RE: code to update list box with results of text box

(OP)
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

RE: code to update list box with results of text box

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

RE: code to update list box with results of text box

(OP)
Nothing.

RE: code to update list box with results of text box

So the code isn't even running?

If you add a first line of code like:

CODE --> vba

Msgbox "Test" 

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

Duane
Hook'D on Access
MS Access MVP

RE: code to update list box with results of text box

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

RE: code to update list box with results of text box

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

CODE

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.

CODE

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

CODE

MsgBox "Go to bed" 
And that runs.

RE: code to update list box with results of text box

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?

CODE --> Immediate

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

Duane
Hook'D on Access
MS Access MVP

RE: code to update list box with results of text box

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

CODE

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.

RE: code to update list box with results of text box

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

CODE

Me.lstEnvelopes.Value = Null 
Doesn't change anything.
Tom

RE: code to update list box with results of text box

I think there are at least two unanswered questions:
  • Does your subform have anything in its Link Master and Link Child properties?
  • confirm ' assuming EnvNbr is numeric
Apparently when I asked

Quote (dhookom)

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.

CODE --> sql

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

RE: code to update list box with results of text box

(OP)
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

CODE

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

RE: code to update list box with results of text box

(OP)
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

RE: code to update list box with results of text box

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 winky smile

Duane
Hook'D on Access
MS Access MVP

RE: code to update list box with results of text box

(OP)
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:

CODE

Forms!frmEditNewGivings!lstEnvelopes 

The Link Child Fields line shows:

CODE

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.

RE: code to update list box with results of text box

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

RE: code to update list box with results of text box

(OP)
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.

RE: code to update list box with results of text box

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 Great Post 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:

CODE

Private Sub ShowTheForm(ByRef lngEnvNbr 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 = " & lngEnvNbr  _
& "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:

CODE

Private Sub lstEnvelopes_AfterUpdate()
Dim sql As String
   On Error GoTo lstEnvelopes_AfterUpdate_Error
Call ShowTheForm(Forms!frmEditNewGivings!lstEnvelopes)

   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

Private Sub txtEnvNbr_AfterUpdate()
On Error GoTo Err_txtEnvNbr_AfterUpdate_Error

Call ShowTheForm(Forms!frmEditNewGivings!txtEnvNbr)

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.

RE: code to update list box with results of text box

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close