Hello everyone, I'm a newbie to access programming. I'm trying to create a form, in that I need to write a query where by just selecting the user we can get all the data of voucher numbers he's using. Looking for help. Thank you.
Looking for a Query
- Thread starter sai pavan
- Start date
Ranman256
Well-known member
- Local time
- Today, 17:46
- Joined
- Apr 9, 2015
- Messages
- 4,354
The form will have a combo box with all users names.
The query SQL will look at this box for its criteria...qs1UserVouchers
Select * from table where [user]= forms!frmQry!cboUsers
Select the user, click a button, the button will open the query,
Docmd.openquery "qs1UserVouchers"
The query SQL will look at this box for its criteria...qs1UserVouchers
Select * from table where [user]= forms!frmQry!cboUsers
Select the user, click a button, the button will open the query,
Docmd.openquery "qs1UserVouchers"
GohDiamond
"Access- Imagineer that!"
- Local time
- Today, 17:46
- Joined
- Nov 1, 2006
- Messages
- 550
Are you trying to use a Dropdown List as your selection? Does the table with all the Voucher information have a Key that can be related to the Dropdown List?
For example the Dropdown list would contain UserIDs and UserNames with the UserID as the Key. The Voucher Table would also have the UserID with each record and be related to the Dropdown List selection by the UserID.
If the Form is based on a query of the Voucher Table then you can use the Dropdown List selected value as Criteria for the Query's UserID, requerying the form after the selection is made. That will effectively "Filter" the query to show only the Vouchers related to that UserID. IF you are using Access 2007 or later you can set Split Form to show records on the bottom of the form.
Here is a Link to creating a Split Form:https://support.office.com/en-us/article/Create-a-split-form-e8eb0efb-2fa6-4315-9d4b-86e79a1fbe1e
This is not the only way to handle your question, but it is one way to get you started.
Cheers!
Goh
For example the Dropdown list would contain UserIDs and UserNames with the UserID as the Key. The Voucher Table would also have the UserID with each record and be related to the Dropdown List selection by the UserID.
If the Form is based on a query of the Voucher Table then you can use the Dropdown List selected value as Criteria for the Query's UserID, requerying the form after the selection is made. That will effectively "Filter" the query to show only the Vouchers related to that UserID. IF you are using Access 2007 or later you can set Split Form to show records on the bottom of the form.
Here is a Link to creating a Split Form:https://support.office.com/en-us/article/Create-a-split-form-e8eb0efb-2fa6-4315-9d4b-86e79a1fbe1e
This is not the only way to handle your question, but it is one way to get you started.
Cheers!
Goh
Create the query from your table putting in all the fields you want displayed. In the query user name field criteria put:
In the above code, forms is calling your group of forms.
Form1 is the name of the search form that you'll create. (I would name the form1 something more meaningful.)
Finally, text0 is your unbound textbox that you'll put your username into for the search, you can do this via a list box or a normal textbox. (I would name text0 something more meaningful as well.)
All you need to do from there is create a button in form1 with a macro that runs your query.
As a bit of advice, I would create a report with a record source as the query and have the button run the report. It will look much cleaner to the end user.
Any questions, please don't hesitate.
Code:
Like "*" & [forms]![form1]![text0] & "*"In the above code, forms is calling your group of forms.
Form1 is the name of the search form that you'll create. (I would name the form1 something more meaningful.)
Finally, text0 is your unbound textbox that you'll put your username into for the search, you can do this via a list box or a normal textbox. (I would name text0 something more meaningful as well.)
All you need to do from there is create a button in form1 with a macro that runs your query.
As a bit of advice, I would create a report with a record source as the query and have the button run the report. It will look much cleaner to the end user.
Any questions, please don't hesitate.
Thanks a lot for your reply, but I actually can't work on it. May be because of lack of skills in access. Can you help me in detail. In my table the user name is "Traveler" and voucher is "Voucher Number".
I have created a combo box, It got many problems
1) The Traveler doesn't show up in the dropdown list.
2) Sometimes it show up but it gives some integer value instead of actual data
