query problem

[Briandr]

I'd like to re-visit an old problem. If the problem required viewing the database I can strip out the data and e-mail it. I don't want to post it though on the forums.

On the combo box on the form

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.

On the Query that serves as the data source for the form:

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.

I'd really appreciate it if someone can help me get this working. Thanks.

 

[Rabbie]

I think you need to tell your datasource query for the form that you want to use the Fullname column of the combobox for the where clause.

At present It looks like you are trying to match lastname,firstname with the EmployeeID.

 

[Briandr]

Do you want me to

Change this :

WHERE ((([Lastname] & ", " & [Firstname])=[Forms]![YourFormName]![YourComboBoxName]))


To this :

WHERE ((([FullName])=[Forms]![frmMenuSwitchboard]![ComboBySelectedEmployee]))

Now its prompting me to put a full name in and to enter a parementer value.

 

[neileg]

When you refer to a combo box control, unless you specify the column number, it defaults to the first column. In your case this is EmployeeMaster.EmployeeID

If it was me, I would change the WHERE clause in your query to match EmployeeMaster.EmployeeID with the corresponding ID field in the dataset you are querying. By using the primary key (I assume the ID fields are the PK in each case) you guarantee a unique match. You can't guarantee that the combination of last and first names is unique so your join could produce multiple matches.

 

[Briandr]

Could you help me with the code. I am not that good of a coder and the stuff I posted originally someone helped me with that. The two id fields that have the PK set to them are Acc & EmployeeId. Many Thanks.

 

[Rabbie]

Try something like this

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

 

[Briandr]

WHERE EmployeeMaster.EmployeeID=[Forms]![frmMenuSwitchboard]![ComboBySelectedEmployee]))

It did not like the two ')'. I removed them and it still prompting me to 'Enter a Paremeter Value' when I select a name from the pull down list. Here is the code that populates that pull down list.

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

 

[Rabbie]

I cant do much more without seeing some of your data can you post a stripped down version of your DB so we can get this working for you.

 

[Briandr]

Can I e-mail it to you? Send me a private message with your e-mail address if your Ok with me e-mailing you. I am not a spammer and don't harvest e-mail addressess.

One thing I should mention if I e-mail it to you is that since one table is linked you won't be able to do much with it. You can see its structure and stuff but not the data.

 

[Briandr]

query help

Hi All,

I gave this a couple days before deciding to come back here and address it again. Is anyone willing to look at this if I sent it via e-mail?? I made a mistake before in another forum by posting a db. I really don't want to post even a stripped down version. The only way I would agree to post it for all to see is if I could delete the file after the fact and/or this whole topic. I know where you guys are coming from. I hope you can see where I am coming from. Thanks.