Form that Runs a Query.

[Chris]

I have set up a Form that has a combo box. When something in selected in the
combo box it automatically runs a query. The problem that I am having is
that I don't believe that the query is picking up the name correctly. The
query is designed to grab a name form a table(The name may be listed several
different times) and show a report of everything that related to that name.
However when the query is run it just has a blank report as if it doesn't
recgonize the name. I will post the SQL of the query but if there is
something else that I would need to post for help please let me know.

SELECT Users.UserName, CompleteList.DirectoryName
FROM Users INNER JOIN CompleteList ON Users.UsersID = CompleteList.UserName
WHERE (((Users.UserName)=[Forms]![PersonAccess]![cboSearch]));

 

[NG]

You could try:

SELECT Users.UserName, CompleteList.DirectoryName
FROM Users INNER JOIN CompleteList ON Users.UsersID = CompleteList.UserName
WHERE Users.UserName = """ & [Forms]![PersonAccess]![cboSearch] & """"

kind regards
NG

 

[Chris]

Yea I believe that the query is working just fine. I origionally started out
where this query, when run, would prompt you for a name, I would enter the
name and all related information would show. I wanted to change this so that
instead of prompting for me to type a name I would just be able to select a
name. I was told that in order to do that I needed to create a form with a
combo box and have it relate back to the query. I did that and that is where
I am at now. However I am having the problem that I previously mentioned.

Hi -

Taking another look at your query - are you sure your join (Users.UsersID =
CompleteList.UserName)is using the correct fields? A UserID is usually
numeric, while a UserName is more likely to be a string, so perhaps your
query is not returning any values at all anyway. Did you try it out in the
query design grid?

HTH

John

I have set up a Form that has a combo box. When something in selected in the
combo box it automatically runs a query. The problem that I am having is
that I don't believe that the query is picking up the name correctly. The
query is designed to grab a name form a table(The name may be listed several
different times) and show a report of everything that related to that name.
However when the query is run it just has a blank report as if it doesn't
recgonize the name. I will post the SQL of the query but if there is
something else that I would need to post for help please let me know.

SELECT Users.UserName, CompleteList.DirectoryName
FROM Users INNER JOIN CompleteList ON Users.UsersID = CompleteList.UserName
WHERE (((Users.UserName)=[Forms]![PersonAccess]![cboSearch]));

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com


.

 

[Chris]

Not sure what you are asking for or how to even show you what you might need
to know. Can you give me some direction on how to show you what you are
wanting to see?

What is the code you are using to open the query from the form, after you
make the selection in your combo box?

John

Yea I believe that the query is working just fine. I origionally started out
where this query, when run, would prompt you for a name, I would enter the
name and all related information would show. I wanted to change this so that
instead of prompting for me to type a name I would just be able to select a
name. I was told that in order to do that I needed to create a form with a
combo box and have it relate back to the query. I did that and that is where
I am at now. However I am having the problem that I previously mentioned.

Hi -

[quoted text clipped - 20 lines]

FROM Users INNER JOIN CompleteList ON Users.UsersID = CompleteList.UserName
WHERE (((Users.UserName)=[Forms]![PersonAccess]![cboSearch]));

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com


.

 

[Chris]

It won't run the query when I make this modification.

You could try:

SELECT Users.UserName, CompleteList.DirectoryName
FROM Users INNER JOIN CompleteList ON Users.UsersID = CompleteList.UserName
WHERE Users.UserName = """ & [Forms]![PersonAccess]![cboSearch] & """"

kind regards
NG

I have set up a Form that has a combo box. When something in selected in the
combo box it automatically runs a query. The problem that I am having is
that I don't believe that the query is picking up the name correctly. The
query is designed to grab a name form a table(The name may be listed several
different times) and show a report of everything that related to that name.
However when the query is run it just has a blank report as if it doesn't
recgonize the name. I will post the SQL of the query but if there is
something else that I would need to post for help please let me know.

SELECT Users.UserName, CompleteList.DirectoryName
FROM Users INNER JOIN CompleteList ON Users.UsersID = CompleteList.UserName
WHERE (((Users.UserName)=[Forms]![PersonAccess]![cboSearch]));

 

[Chris]

I think that this is what you were looking for. PLease let me know if you
have any idea on how it should be changed.

Option Compare Database

Private Sub cboSearch_Change()
DoCmd.OpenQuery "DirectoryAccess"
Me.cboSearch = ""

End Sub

Private Sub Detail_Click()

End Sub

What is the code you are using to open the query from the form, after you
make the selection in your combo box?

John

Yea I believe that the query is working just fine. I origionally started out
where this query, when run, would prompt you for a name, I would enter the
name and all related information would show. I wanted to change this so that
instead of prompting for me to type a name I would just be able to select a
name. I was told that in order to do that I needed to create a form with a
combo box and have it relate back to the query. I did that and that is where
I am at now. However I am having the problem that I previously mentioned.

Hi -

[quoted text clipped - 20 lines]

FROM Users INNER JOIN CompleteList ON Users.UsersID = CompleteList.UserName
WHERE (((Users.UserName)=[Forms]![PersonAccess]![cboSearch]));

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com


.