Command Button to List Box? 1

[hgg21]

Hi,

I need some help here. I m doing a shift schedule database. I had a form here. With command buttons linking to some queries. When I click on the button, it will prompt me to enter the query name of the shifters or date of the shifts. Issit possible that I change these command buttons to list box so that I dun have to key in the names or dates and straight away select it from the list??

PLs kindly advise. Thanks.
Jerry

 

[dcurtis]

You could use an unbound form with combo boxes. Use the combo boxes to look up the names or dates from the appropriate tables. Set the underlying query's critera to the combo box.

For example:

Table1
WorkDate
EmpName

Form1
WorkDate (combo box looking up from table 1)
EmpName (combo box looking up from table 1)

Query1 (based on table 1)
WorkDate (critera is [Forms]![Form1]![WorkDate]
EmpName (criteria is [Forms![Form1]![EmpName]

 

[hgg21]

Hi dcurtis,

I understand what u mean. But I cant get the criteria correct. What do u mean by (critera is [Forms]![Form1]![WorkDate)?? Issit the expression builder??

How do u make the combo box looking up from the table??

Pls kindly advice. Thks.

Jerry

 

[dcurtis]

I am not sure I understood your original question. What happens when you click the command button then enter the name to query?

 

[benbakelaar]

i am new here, and not the best access programmer, but if you are in design mode in the form with the command buttons that are linked to queries, you should be able to turn on the Field List (menu View - select Field List) and then drag-n-drop the fields you are referring to, and they will automatically be combo boxes if they are already defined as lookup fields.

if those fields are not lookups yet, you can just create the combo box, type in the values you want, and then reference the combo box field in the query criteria as dcurtis outlined.

hope that helps, maybe... this is my first response post.

 

[hgg21]

Hi,

Well, I forgot to let u know that those command buttons in the first place are being formed using marcos. I used marcos to open up the queries. In my queries, it actually requires user to enter the name of shifters or date of shift. This is troublesome as we need to know the exact name of the shifters.

What I require now is that I can have the name of shifters or the date of shifts directly in the combo or list box. So that i do not have to input them. Is that possible?

Sorry, please bear with me.

Thank you
Jerry

 

[dcurtis]

OK, if it were me I would start by using an unbound form to collect the data for the query. On the form I would use combo boxes to look up the information from the tables holding the shift information. Then use a command button to call the query. In the query that is called set the criteria equal to the combo box.

So, if your unbound Form is called Form1 and the combo box is cboBox1 (this will get the names), and your table is called Table1 it would look something like this:

Combo Box Source:

SELECT PersID, PersName
FROM Table1

If you use a PK in Table1 for personnel you should use that in the look up also and use that as the criteria in the next step.

Then in the design view for the criteria add what fields you want to display. In the PersName (or ID if you are using that field) set the criteria to

=[Forms]![Form1]![cboBox1].

Let me know if that helps or if you need further assistance.

 

[hgg21]

Hi Dcurtis,

Thanks a lot for ur help! I got it now.

Regards
Jerry

 

[hgg21]

Hi Dcurtis,

I have a new problem here.

Let's say I have 2 tables. 1 is for storing the names and particulars of the employees. Another 1 for the shifts.

Using the table meant for shifts, I am able to show it in the combo box and run a query. However, I want to show the names of the employees in the combo box. And using this name able to show the activities from the 2nd table. Seems now that I can only use 1 table for combo and query. If I try to use 2 tables it doesnt work. Pls kindly advise. Thank you.

Regards
Jerry

 

[dcurtis]

Are the tables related? Please send a brief description of the table structure and fields.