UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Add Where Clause To Form Record Source, Access 2016    
 
   
chalupabatman
post Feb 27 2020, 02:43 PM
Post#1



Posts: 88
Joined: 12-April 17



I have my form loading with a query as the recordsource.

I need to filter the form based off a combo box selection by the user. Which means I want the record source of the form to when it loads be null - but when a employee is selected from the combo box I want the record source to be changed to
CODE
SELECT UserID empName, empAddress, empCity, empPhone FROM tblUserSocialInfo WHERE UserID = =[cboUserSelect].[Column](0);


How would I set this up? Either straight from the form or using VBA is okay as well...
Go to the top of the page
 
Larry Larsen
post Feb 27 2020, 02:51 PM
Post#2


UA Editor + Utterly Certified
Posts: 24,527
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi

You could use the [Filter Event ] of the form to fool it..
CODE
Private Sub Form_Load()
    Me.Filter = "(False)"
    Me.FilterOn = True
End Sub


Then formulate you instruction to [Filter] the form..
CODE
Me.Form.Filter = "[UserID] = " & Me.cboUserSelect.Column(0)
Me.FilterOn = True


HTh's
thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
chalupabatman
post Feb 27 2020, 02:52 PM
Post#3



Posts: 88
Joined: 12-April 17



Do the filter conditions also go in the Form_Load() event?
Go to the top of the page
 
Larry Larsen
post Feb 27 2020, 02:55 PM
Post#4


UA Editor + Utterly Certified
Posts: 24,527
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi

As my example above, you can then place the Filter instruction in the combo's [Click Event]..
thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
chalupabatman
post Feb 27 2020, 03:00 PM
Post#5



Posts: 88
Joined: 12-April 17



Something is not working for me...I have this in the Form_Load() event and this in my comboboxclick() event
CODE
Private Sub cboUserSelect_Click()
    Me.Form.Filter = "[UserID] = " & Me.cboUserSelect.Column(0)
    Me.FilterOn = True
End Sub

Private Sub Form_Load()
    Me.Filter = "(False)"
    Me.FilterOn = True
End Sub


but when the form loads, and a user selects a name from the combo box the fields do not filter to pull that users data from the table...even though they are bound fields.
Go to the top of the page
 
chalupabatman
post Feb 27 2020, 03:04 PM
Post#6



Posts: 88
Joined: 12-April 17



Should I edit the record source of the form through the vba and not a filter param?
Go to the top of the page
 
Larry Larsen
post Feb 27 2020, 03:19 PM
Post#7


UA Editor + Utterly Certified
Posts: 24,527
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi

I am assuming the form has a query as it's source, set up without any criteria..

It may be worth placing a [Code/Toggle Stop] on { Me.Form.Filter = "[UserID] = " & Me.cboUserSelect.Column(0) }

And see what the combo value is returning..??
thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
chalupabatman
post Feb 27 2020, 03:21 PM
Post#8



Posts: 88
Joined: 12-April 17



The filter criteria is getting the appropriate userID to filter on. If I look at the table that the fields are bound to that userID exists in the database but the form is not being populated with the desired values from the table.

Linkn to database giving me trouble -
https://easyupload.io/z0un03
This post has been edited by chalupabatman: Feb 27 2020, 03:28 PM
Go to the top of the page
 
Larry Larsen
post Feb 27 2020, 03:30 PM
Post#9


UA Editor + Utterly Certified
Posts: 24,527
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi

If you look at the form source query and put a UserID into the criteria column, does it return a record..??

Tell a little about the form setup, Single / Continuous / sub forms..??

SQL for the combo may help us..
thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
chalupabatman
post Feb 27 2020, 03:31 PM
Post#10



Posts: 88
Joined: 12-April 17



i edited my previous post and provided a link to the DB i'm using. Here it is agian smile.gif

Go to the top of the page
 
Larry Larsen
post Feb 27 2020, 03:33 PM
Post#11


UA Editor + Utterly Certified
Posts: 24,527
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi OK..

Will have a look, but have to say external links are a "No No" here on UA..

Check out how to upload db's /examples here at UA...

I'm removing the link..
thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
chalupabatman
post Feb 27 2020, 03:35 PM
Post#12



Posts: 88
Joined: 12-April 17



ah, i did nto realize that.

I keep trying to upload to the site, by selecting Choose File - then upload, but when i cselect my file and click okay, I get an error of You did not select a file to upload
Go to the top of the page
 
Larry Larsen
post Feb 27 2020, 03:38 PM
Post#13


UA Editor + Utterly Certified
Posts: 24,527
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi
Sent you personal mail..
compute.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
chalupabatman
post Feb 27 2020, 03:40 PM
Post#14



Posts: 88
Joined: 12-April 17



got it and sent. Thank you so much! smile.gif
Go to the top of the page
 
Larry Larsen
post Feb 28 2020, 05:54 AM
Post#15


UA Editor + Utterly Certified
Posts: 24,527
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi Guy's

Sorry had to take the discussion outside of the forum to resolve, out come was to use the [Filter] method and to simple address some reference issue..
thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    5th April 2020 - 06:31 PM