SQL Question

[dannybeaver]

Hi All, I know the title is a bit vague, however I didn't really know how to title my issue.



I am using Access 2003, and SQL Server 2008. (Just as background info)

I have a form in one of my .mdb databases, which contains a subform. This subform is populated with information from a Pass through query to an SQL stored procedure. This basically just fetches all records and displays them in the subform.

When you double click on a value in the serial number field, it should open a new form, in which you can edit the information for the particular record.

What I would like to happen, is when the value is double clicked, the form is populated with the record information, based on the serial number value that was clicked on.

For example, if I were to click on 000002, then information would be displayed for that record; whereas if i clicked on 000010, I would be presented with this record's information.

Currently, when the double click occurs, it runs another pass through query, similar to the first. It opens the form fine, but it does not pull in the correct record. The code is currently: Exec EPMachinesQueryDetail @SerialNumberValue=NULL now I know that the NULL value will return all records, but I was hoping that someone would know what parameter I am supposed to enter instead of null, in order for the form to be populated with information, based on the record that was clicked on.

Apologies for any confusion regarding my posts, and if anyone has any questions about providing me with an answer, I will do my best to respond.

Thanks all,

D

[pbaldy]

To stay with what you're doing, either of these depending on your data.

Exec EPMachinesQueryDetail @SerialNumberValue=2

Exec EPMachinesQueryDetail @SerialNumberValue='000002'

typically I use a function to change the SQL of the pass through query. I pass it the SQL and name of the query, it uses a QueryDef to change the SQL.

[dannybeaver]

Hi,

Thanks for the response.

Doesn't that mean that I would just be displayed with the information for serial number 000002?

I'm sorry, I probably did not explain my issue in the fullest way. I need the form, that opens on double click, to be filled with information specific to the record that was clicked on. That means the SQL query would need to find the information about the record, based on the serial number value field that was clicked on.

I just don't know what it is that I need to enter as parameters??

[pbaldy]

Normally I would have suggested this:

http://www.baldyweb.com/wherecondition.htm

but if your source is a pass through query, you need to change the SQL of it. One of the 2 syntax's I posted should return just that record. I typically don't specify the parameter name, just:

EXEC procWhatever 2

for a numeric value or

EXEC procWhatever '000002'

for a text value that includes the leading zeros.

[dannybeaver]

Sorry, I think we are getting our wires crossed.

I don't want to just return the record 000002. I only want that record information displayed when I double click on 000002.

If I were to double click on the record 000003, I would want information displayed for the record 000003, not 000002.

If I specify the value in the SQL, then no matter what record I click on, I would be displayed with record 000002, if I were to use your suggestion.

Thanks again for the response.

[pbaldy]

Sorry, I wasn't clear enough with this:

typically I use a function to change the SQL of the pass through query. I pass it the SQL and name of the query, it uses a QueryDef to change the SQL.

The intent is to adjust the SQL of the second pass through query to refer to whatever record was clicked on. When you change the SQL you do something like:

strSQL = "EXEC procWhatever " & Me.TextboxName

[dannybeaver]

Hi again, and thanks again for the reply.

So am I right in thinking that I have a pass-through query that runs when I double click a record, then a second pass through query to filter the records based on the one that was double clicked on?

Apologies for the lack of basic knowledge that I have; I'm not too bad in Access, but i am still learning the ropes in SQL.

Thanks,
Danny

[pbaldy]

If I have the structure right, the second form you're trying to open to display a particular record is based on a pass through query. That pass through currently returns all records. You could simply use the technique I linked to above to return the single record from that pass through. The more efficient method is to change the SQL of that pass through on the fly so it only returns the desired record. So in the event where the user is click on a serial number, you would change the SQL then open the second form. You'd use a DAO QueryDef to do that. I would post the code, but I use a function I got from a book and I don't want to violate copyright laws by posting it here. It isn't that complicated though, if you check out the QueryDef object.

[dannybeaver]

The intent is to adjust the SQL of the second pass through query to refer to whatever record was clicked on. When you change the SQL you do something like:

strSQL = "EXEC procWhatever " & Me.TextboxName

So with what you said here, where would i put this code in order to get it to open the second form, based on the selected record?

There is that much stuff to learn with all this, and my knowledge at the minute is quite basic.

Thanks for all the help.

[pbaldy]

I use a function that accepts the SQL and query name as inputs, but you could do it in the code to open the second form. The basics of the code are in post 4 here:

https://www.accessforums.net/queries...sql-39811.html

in case needed:

http://www.baldyweb.com/FirstVBA.htm

http://www.baldyweb.com/Function.htm

[dannybeaver]

Right,

Complete change of direction (sorry for the confusion)

I am using a linked table to retrieve my information. Which is stored in a subform.

I would like to do the same thing, as in double clicking on a record in that subform, and have the information presented in another form.

I have the basis of it working, but when I double click, it keeps asking me to enter the serial number, rather than just picking it up from the record that I clicked on.

Here is the code that is on the Double Click Event.

DoCmd.OpenForm "EPMachines",,,"[SerialNumber] = " & Me!SerialNumber

The record source for this form that is being opened is a query. And this query has a parameter on the SerialNumber field of [Please Enter Serial Number]

I have tried removing the parameter from the query and just letting the code work on the double click, but I keep getting the Open form action was cancelled error.

I am stuck as to where to tackle this from next.

Again, apologies for the complete switch in direction.

Thanks

[NoellaG]

Hi,

you can chase the parameter in the query from [Please Enter Serial Number] to the field name of the control which contains the serial number on the subform. As it is on a subform, the name will be something like Forms![Name Form]![name subform control].Form.[fieldname]. You can use the builder in the query design view to get the correct name of the field.

success
NG

[pbaldy]

I would take the criteria out of the query and use the wherecondition. What is the data type of the SerialNumber field?

[dannybeaver]

Hi,

Noella, Where would I enter this to achieve my result?

pbaldy, the serial number field is a primary key in the linked table: dbo_EPMachine and it is a Text field.

Thanks guys for your time!

[pbaldy]

See my link in post 4 for the syntax adjustment required for a text field.