Pass Through Query Linked to FORM 1

[rwn]

I had to use a Pass Through Query to obtain data (Parent Part, Component Part, Qty Per), otherwise a typical query exhibits the ''#Delete'' in the fields. The Pass Through works great for this.

However, I need to have this pass through query added to a FORM and from a field on the form called Part Number have it linked to the Pass Through Query's field called Parent Part Number and then show the field called Qty Per.

 

[dhookom]

Consider writing a little DAO code to change the SQL property of a saved pass-through query. How, when, and where you would do this depends on your form, control, etc.

Duane
Hook'D on Access
MS Access MVP

 

[rwn]

Any where to go to find out how and what a DAO is?

 

[dhookom]

You could search these fora thread181-1509333.

Come back if you have questions.

Duane
Hook'D on Access
MS Access MVP

 

[rwn]

Thanks. But I'm not sure where to begin.

 

[dhookom]

My posting from 26 Oct 08 19:53 suggests how to change the SQL property of a pass-through query.

Duane
Hook'D on Access
MS Access MVP

 

[rwn]

Thanks, I have read and started with this below.
Here is what I have so far. Hopefully, I'm going down the right path. I have a FORM that uses an Order Master query that shows the PartMaster.PRTNUM_01 field for each Order. Then for each Order it needs to show the ProductStructure.QTYPER_02 field when a match occurs from the PartMaster.PRTNUM_01 to the ProductStructure.PARPRT_02 fields.

In the FORM I added a text field and entered this expression

Private Sub Text3_BeforeUpdate(Cancel As Integer)

Dim Capacity4102010 As DAO.Database
'Capacity4102010 is the name of the database
Dim ProductStructure As DAO.Recordset
Dim Capacity4102010 As CurrentDb
Set ProductStructure = Capacity4102010.OpenRecordset("qry_ProductStructure")
ProductStructure.FindFirst "ProductStructure.PARPRT_02 = PartMaster.PRTNUM_01"
'find ProductStructure.PARPRT_02 match from PartMaster.PRTNUM_01
'if match then display Product Structure"."QTYPER_02

End Sub

 

[rwn]

UPDATE: I have tried to write the code similiar to the Oct 26,08. Below is the code.


Public Function ChangeSQL(strQueryName As String, strSQL As String) As String
Dim qd As DAO.QueryDef
Dim db As DAO.Database
Set db = CurrentDb
Set qd = db.QueryDefs(strQueryName)
ChangeSQL = qd.SQL
qd.SQL = strSQL
Set qd = Nothing
Set db = Nothing

Private Sub Text3_BeforeUpdate(Cancel As Integer)

Dim strOldSQL As String
Dim strNewSQL As String
Dim strQueryName As String
strQueryName = "qry_ProductStructure"
strNewSQL = "SELECT ProductStructure.QTYPER_02, ProductStructure WHERE ID =" & Me.Text3
'text box name is Text3.
' I need to show ProductStructure.QTYPER_02 field when PartMaster.PRTNUM_01 = ProductStructure_02.
strOldSQL = ChangeSQL(strQueryName, strNewSQL)

End Sub

 

[dhookom]

I'm not sure I understand your problem description.

Your code Dim's Capacity4102010 twice which is a no-no. You second Dim should probably a "SET Capa... = CurrentDb"

If qry_ProductStructure is a pass-through, you might want to use some code to change its SQL property so it returns only one record.

Duane
Hook'D on Access
MS Access MVP

 

[rwn]

Thank you for the quick & kind reply. I did rewrite the code as below. Yes, it is a Pass Through Query. I know that
that below code has to be correct first, an you are correct, I do need to have its SQL property so it returns only one record - but now sure how yet..


Public Function ChangeSQL(strQueryName As String, strSQL As String) As String
Dim qd As DAO.QueryDef
Dim db As DAO.Database
Set db = CurrentDb
Set qd = db.QueryDefs(strQueryName)
ChangeSQL = qd.SQL
qd.SQL = strSQL
Set qd = Nothing
Set db = Nothing

Private Sub Text3_BeforeUpdate(Cancel As Integer)

Dim strOldSQL As String
Dim strNewSQL As String
Dim strQueryName As String
strQueryName = "qry_ProductStructure"
strNewSQL = "SELECT ProductStructure.QTYPER_02, ProductStructure WHERE ID =" & Me.Text3
'text box name is Text3.
' I need to show ProductStructure.QTYPER_02 field when PartMaster.PRTNUM_01 = ProductStructure_02.
strOldSQL = ChangeSQL(strQueryName, strNewSQL)

End Sub

 

[dhookom]

You are missing an End Function line in the ChangeSQL function.

Also, you would use the after update of the Text3. You also should do us all a favor and rename Text3 to something like:
txtPRTNUM_01

Public Function ChangeSQL(strQueryName As String, strSQL As String) As String
    Dim qd As DAO.QueryDef
    Dim db As DAO.Database
    Set db = CurrentDb
    Set qd = db.QueryDefs(strQueryName)
    ChangeSQL = qd.SQL
    qd.SQL = strSQL
    Set qd = Nothing
    Set db = Nothing
End Function

Private Sub txtPRTNUM_01_BeforeUpdate(Cancel As Integer)

  Dim strOldSQL As String
  Dim strNewSQL As String
  Dim strQueryName As String
  strQueryName = "qry_ProductStructure"
  strNewSQL = "SELECT QTYPER_02 " & _
    "FROM ProductStructure WHERE PARPRT_02 =" & _
      Me.txtPRTNUM_01
  strOldSQL = ChangeSQL(strQueryName, strNewSQL)

End Sub

Duane
Hook'D on Access
MS Access MVP

 

[rwn]

Thank you! I will make your recommended changes and update status later today.

 

[rwn]

I have incorporated the changes as below. How do I now display the QTYPER_02 data on the FROM?
Private Sub Form_Load()

End Sub

Public Function ChangeSQL(strQueryName As String, strSQL As String) As String
Dim qd As DAO.QueryDef
Dim db As DAO.Database
Set db = CurrentDb
Set qd = db.QueryDefs(strQueryName)
ChangeSQL = qd.SQL
qd.SQL = strSQL
Set qd = Nothing
Set db = Nothing
End Function

Private Sub txtPRTNUM_01_BeforeUpdate(Cancel As Integer)

Dim strOldSQL As String
Dim strNewSQL As String
Dim strQueryName As String
strQueryName = "qry_ProductStructure"
strNewSQL = "SELECT QTYPER_02 " & _
"FROM Product Structure WHERE PARPRT_02 =" & _
Me.txtPRTNUM_01
strOldSQL = ChangeSQL(strQueryName, strNewSQL)

End Sub

 

[dhookom]

There are a number of methods for displaying data from a query/table on a form. You can use a subform bound to the query/table, code to set the value of a text box, or a DLookup() function.

Duane
Hook'D on Access
MS Access MVP

 

[rwn]

I would prefer to use code to set the value of a text box. The value returned will be the QTYPER_02 of the strNewSQL above.

The name of the text box will be txtQTYPER_02. I entered this in the Control Source of the txtQTYPER_02.
=IIf([qry_ProductStructure]![PARPRT_02]=[OrderMasterQuery]![PRTNUM_01],[qry_ProductStructure]![QTYPER_02],0)

Obviously, it is wrong for it returns #Name.

 

[dhookom]

Try a text box with a control source of:

=DLookup("QTYPER_02","qry_ProductStructure")

Duane
Hook'D on Access
MS Access MVP

 

[rwn]

It needs to show the unique QTYPER_02 for the selected txtPRTNUM_01 = PARPRT_02.

The... =DLookup("QTYPER_02","qry_ProductStructure")...shows the first record not specific to the txtPRTNUM_01 field.

 

[rwn]

I was able to get it to work with this.=DLookUp("QTYPER_02","qry_ProductStructure","txtPRTNUM_01 = PARPRT_02 ")

A very numbie question. What role does this code below do, when the code above doesn't reference it?.

Public Function ChangeSQL(strQueryName As String, strSQL As String) As String
Dim qd As DAO.QueryDef
Dim db As DAO.Database
Set db = CurrentDb
Set qd = db.QueryDefs(strQueryName)
ChangeSQL = qd.SQL
qd.SQL = strSQL
Set qd = Nothing
Set db = Nothing
End Function

Private Sub txtPRTNUM_01_BeforeUpdate(Cancel As Integer)

Dim strOldSQL As String
Dim strNewSQL As String
Dim strQueryName As String
strQueryName = "qry_ProductStructure"
strNewSQL = "SELECT QTYPER_02 " & _
"FROM Product Structure WHERE PARPRT_02 =" & _
Me.txtPRTNUM_01
strOldSQL = ChangeSQL(strQueryName, strNewSQL)

End Sub

 

[dhookom]

I thought you wanted to take advantage of the processing speed of a pass-through by dynamically changing its SQL to reference a specific record.

I don't understand how this would provide any type of filter in the pass-through.
=DLookUp("QTYPER_02","qry_ProductStructure","txtPRTNUM_01 = PARPRT_02 ")
I am at a loss as to the exact SQL view of qry_ProductStructure.


Duane
Hook'D on Access
MS Access MVP

 

[rwn]

Yes, I did need to take advantage of the processing speed. For a normal query with this table Product Structure can show #Deleted at time.

Then I needed to show the QTYPER_02 field as below.


The filter comes from: txtPRTNUM_01 = PARPRT_02 ")...in the =DLookUp("QTYPER_02","qry_ProductStructure","txtPRTNUM_01 = PARPRT_02 ").


Query for Product Structure.
SELECT [Part Master].PRTNUM_01, [Product Structure].PARPRT_02, [Product Structure].COMPRT_02, [Product Structure].QTYPER_02
FROM [Part Master] INNER JOIN [Product Structure] ON [Part Master].PRTNUM_01 = [Product Structure].PARPRT_02
WHERE ((([Part Master].PRTNUM_01) Like "9*") AND (([Product Structure].COMPRT_02) Like "01*"));