using form field values in sql queries

[shemeer]

hi,
i created a database in access with forms and querires, and it works well.
now i want this to be moved to sql, i tried using the upsizing wizard, and i could move th tables properly. the problem is with the queries. i have used the vaules of the form fileds with in the queries to itrait the number of records returned by the queries.
eg
select name from customer where(customer.name= forms![customer search].name);
when i moved this query to sql, it gives me error. it looks like i cannot use the field names of the forms within sql query statement.
please help me to solve this problem
all i want to know is how to use the vaules(eg value in a text box) that i enter in a form with in a sql query.
i am waiting for a response

 

[Pat Hartman]

You can't use Access form references or functions in queries that you want to store in your SQL database. SQL doesn't know anything about Access objects. Either leave the quer as a querydef in the Access db or create the query in VBA where the form field reference can be evaluated by VBA before the query is sent off to SQL for processing.

 

[shemeer]

hi,
thanks for your quick reply...

i request you to give me one eg for each of your suggestion
1. " leave the quer as a querydef in the Access db "- i realy do not understand what you meant by this, i will be very happy if you could through som more light on this

2."create the query in VBA where the form field reference can be evaluated by VBA before the query is sent off to SQL for processing"--- this again not clear to me for the lack of my knowledge.
do you mean to use the access forms and the script that we normaly write for diffrent event(like onclickevent). whatever it is, please give me a small eg so that i can understand it properly.

once again many thanks for your quick response, hope to c u doing it again.
thanks and regards

 

[Pat Hartman]

1. Link your SQL tables. Then your existing queries can run against the linked tables just as they did when the tables were Access tables. Your linked table may now be preceeded by something like dbo_ . If so, you can rename the tables in the query or you can rename the table after it is linked. These queries can be used as the recordsources for forms or reports.

2. To create an SQL string in code, define a variable and give it a value:

Dim strSQL as String
strSQL = "Select fld1, fld2, fld3 From YourTable Where fld2 = "
strSQL = strSQL & Forms!YourForm!YourField & ";"

or if the field is text:
strSQL = "Select fld1, fld2, fld3 From YourTable Where fld2 = '"
strSQL = strSQL & Forms!YourForm!YourField & "';"

 

[shemeer]

Thank you very much,
I liked your second suggestion, let me try that.

Please keep this opened till i come back with the result.

thanks and regards