Query Help

[Briandr]

Hi All,

I am looking for what I hope to be multiple opinions on this. I'll be up front. I was over at Utteraccess and someone was trying to help me. I didn't quite follow the examples he was giving. Probably my fault and not his

Here is what is going on. I have a form and on it is a comb box. I want to be able select a employee name either from a local or a linked table. Then after name is selected it will query out a list of accidents this person was involved in and then open a form with the list shown. How hard is this? I know I could query on departments or dates, why not a name? I thought if I used the linked table it would be better because the employee list is updated regularly in the other program. If there is someone who wants to take a shot at helping me out and if you want to read in more on the other conversation I'll post a link. I just wanted to start the ball rolling.

Finally I am sure folks here visit Utteraccess and vice versa. If you seen the thread I am talking about over there, please reply here if you only if disagree with the opinion given over there. I am just looking for fresh thoughts and ideas. I am not saying the person who was helping me was wrong. I just wanted to see if there was another approach to this.

Many thanks in advance.

 

[mhartman]

Hello:

Basically....

In the Criteria section of your accidents query for the Field of Name or whatever your calling it, put:

Forms![YourFormName]![YourComboBoxName]

Then save the query and open your form, select a name and run your query.

Regards
Mark

 

[Briandr]

Hi Mark,

Thats what he said too. This is what I had.

=[Forms]![frm_MenuSwitchboard]![ComboBySelectedEmployee]

Then when I select a name from the combo box list it asks me to enter a parameter value.

Its getting late, but I'll do my best to respond to this and other posts from home. Also, if you think it will help I'll link to the other post and you can read up on what he talked about. Thanks for replying.

 

[mhartman]

Hello:

Copy that: Please see my enclosed example database.

Open the database and select a state such as CO, or CA, then press the query button.

Notice that the query returns only those records

Regards
Mark

 

[Briandr]

Hi Mark,

Sorry for the delay in getting back to this topic. I looked at your example and I see that it uses a 'Value List'. Before I had 'Row/Source Type' set to 'Table/Query'. Then for the 'Row Source' I was using a Select statement. Is it better to use a 'Value List' or can I somehow fix the way I was doing it before? If your more inclined to say 'Value List' for simplicity, is there a easier way to edit the value list or can you spell out the 'Value List' in code? The reason why I went the route I did initially was that table was being updated by someone else and I figured it would be easier. Let me know your thoughts and if you want to see my code example. Thanks

 

[boblarson]

Rowsource of Table/Query would be the most common as it is dynamic based on the data in the table/query. I'm attaching a sample which might help you. The form that opens has a combo box on it which, if you select a name, opens a report for that person only.

 

[mhartman]

Hello
"Value Lists" are good from small unchanging data. Use the Table/Query option for more dynamic data types. In any case the solution would be the same. Bob above is a good example to study.

Regards
Mark

 

[Briandr]

Hi Guys,

I wanted to get back to this alot sooner, but other things pushed this aside. I looked at Bob's example. Please tell me if I am putting more into this than I need to. For starters I think a query would work best because new data would be added in continuously. So let's go that route. I am going to break this down to the combo box on the form and the query itself. My apologies if I repeat myself from the original post.

On the combo box

Row Source Type = table/query

Row Source = SELECT EmployeeMaster.EmployeeID, Lastname & ", " & Firstname AS Fullname FROM EmployeeMaster
ORDER BY EmployeeMaster.LastName, EmployeeMaster.FirstName;

That works good. I see all the names as I would expect to see them.

Query

SELECT tblAccidentLog.AccNumber, tblAccidentLog.AccDate, tblAccidentLog.ClassType, tblAccidentLog.DeptName, [Lastname] & ", " & [Firstname] AS Fullname
FROM EmployeeMaster, tblAccidentLog
WHERE ((([Lastname] & ", " & [Firstname])=[Forms]![YourFormName]![YourComboBoxName]))
ORDER BY EmployeeMaster.LastName;


Then when I select a name from the combo box I get a pop up window asking me to enter a parameter value.

=[Forms]![frm_MenuSwitchboard]![ComboBySelectedEmployee]

I type in a name of a person who I know has been involved in accident I get nothing on the form.

So maybe I am trying to do too much here. Maybe the code can be simplifed.