User Input in SQL query 1

[bgroce]

I am trying to run a query based on the value of a user input in one field of a form and display the results as a subform. The query and subform work, but when I try to go into the form I get two prompts for the value. Is there any way to have the SQL query use the input in the form and not display a pop-up dialog asking for the input. Also, and this is a stupid question, I am wanting this same form to take two numerical values that are entered by a user and muliply them for display in a third field and I can't get it to work.

 

[Blorf]

I do this quite often. If the sub form is based on a query, and the query is based on tables with critera on the main form, it should work.

Example:

Select Bla bla bla where blah = forms!Mainform.Textbox

I see though that you are having troubles. Send the SQL, and give the form and field names of the criteria fields on the form.

Will try to help.

ChaZ

Ascii dumb question, get a dumb Ansi

 

[bgroce]

Blorf,

Here is my SQL:

SELECT DISTINCT JobJacket.CustContact, JobJacket.CustAddr, JobJacket.CustCity, JobJacket.CustState, JobJacket.CustZip, JobJacket.CustPhone, JobJacket.CustFax, JobJacket.CustEmail
FROM JobJacket
WHERE (((JobJacket.Customer)=[Please Enter Customer]));


I am trying to use a form to create a new Job Jacket and to look and see if the customer entered already has information in our database. Essentially, the query should look in the JobJacket table and see if the entered customer value equals a JobJacket.Customer value in the table. The form name is JobJacketTEST and the field is customer. Thank you so much.

 

[bgroce]

One more thing:

The [Please Enter Customer] is there because it was prompting me using whatever I had in the brackets and so I changed it to this where the prompt didn't look like code.

 

[Blorf]

I see.

Try this:

SELECT DISTINCT JobJacket.CustContact, JobJacket.CustAddr, JobJacket.CustCity, JobJacket.CustState, JobJacket.CustZip, JobJacket.CustPhone, JobJacket.CustFax, JobJacket.CustEmail
FROM JobJacket
WHERE (((JobJacket.Customer)=Forms!JobJacketTEST.Customer));

ChaZ


Ascii dumb question, get a dumb Ansi

 

[dcurtis]

The SQL you wrote is looking for an exact match. So if the customer name is Jim's Gym Equipment, and the user just enters Jim or Jims Gym Equipment, nothing will come back.

Try blah blah blah = Like "*" & [Enter Customer] & "*"

So if you enter Jim, Gym, or Equip it will show up. The only problem is if you also have a company Paul's Gym that will return also.

 

[Blorf]

Good point.

This assumes that Customer refers to a customer name, and not a key value, but either way, using the like "*" will work rather nicely. Will also return all items if the field is left blank.

ChaZ

Ascii dumb question, get a dumb Ansi

 

[bgroce]

dcurtis,
Were you referring to me or Blorf? Either way I see your point. Thanks

Blorf,
I tried the query and my form doesn't prompt me, but it also doesn't give me the results upon entering a customer. I have the query running as the source object for a subform i have in my form. Any ideas either of you? Thanks

Bgroce

 

[Blorf]

Ok. You are opening your main form, with the subform attached. You enter a value, and wallah, nothing happens.

Ok, you need to write a requery thingie for the after update even for the field customer.

If you right click the field in design view, select properties, go to events, and select after update, click the ..., and select Macro builder.

Now for the Macro.

Put requery and hit tab. Then on the item to refresh option box down at the bottom, put the name of the subform. Do not reference it any wierd way, just put the name of the sub form.

This will make it requery the data each time you change the value in customer.

Hope that works.

ChaZ

Ascii dumb question, get a dumb Ansi

 

[bgroce]

Ok, chaz, that seemed to work great. Now, Is there a way, instead of pulling up the data in a table like form, to pull the data into fields that could be typed in if the customer didn't match?

 

[Blorf]

Certainly.

If I understand what you are asking.

Say you have a form with a field called Address. And you have a field called Customer, and you want to fill address with what ever it is in the Customer table.

You can say in address =dlookup ("Address", "Customers", "Customer=" & forms!formname.Customer).

Give that a try.

ChaZ

Ascii dumb question, get a dumb Ansi

 

[bgroce]

Two Questions:
1.)Where do I put the dlookup thingie
2.)Everytime I open my form I get a "Query Must Have At Least One Destination Field" Message and my CustomerContact query that you helped me with earlier is blank and I have to go back in and enter the query again. Is there anyway to avoid this?

 

[Blorf]

I don't understand what may cause that. The query the form is based on is saved? Or the Query is typed directly into the forms Datasource?

With regard to the dlookup thingie, probably in an after update proceedure for the customer on your form.

ChaZ

Ascii dumb question, get a dumb Ansi

 

[bgroce]

The query is saved as a query and not typed directly into the form. Can you put more than one command in the after update procedure?

 

[Blorf]

Yes. if you use a macro, you can have multiple lines. If you use VB code, you can write large proceedures.

Poste the SQL of your query. Maybe I will see something.

ChaZ

Ascii dumb question, get a dumb Ansi

 

[bgroce]

My SQL is:

SELECT DISTINCT JobJacket.CustContact, JobJacket.CustAddr, JobJacket.CustCity, JobJacket.CustState, JobJacket.CustZip, JobJacket.CustPhone, JobJacket.CustFax, JobJacket.CustEmail
FROM JobJacket
WHERE (((JobJacket.Customer)=Forms!JobJacketTEST.Customer));

I have the query listed as the Source Object for my subreport and also have a macro to requery the subreport in the After Update of my Customer Field.

 

[Blorf]

I do not understand what would make the query go away or give that error.

Is there any more information you can give?

Ascii dumb question, get a dumb Ansi

 

[bgroce]

Not really any more info. I don't understand why either. Should I try entering the SQL into the field in the form?

 

[bgroce]

This is very strange. It seems to be working now. Do you have any ideas on the multiplying two entered fields and displaying them in a third field?

 

[Blorf]

Certainly. Make the third text box say this in the Control source field

=forms!Formname.Field1 * forms!Formname.Field2.

ChaZ

Ascii dumb question, get a dumb Ansi