Subform problem

  • Thread starter Thread starter Joan
  • Start date Start date
J

Joan

Hi,
I am not sure how to resolve the following problem. I have an Invoice
form where when the user enters a store code on the main form, the dogs
that are to be invoiced automatically appear in a subform. This subform is
based on a query that has a limiting criteria of when [FinalStore] Is Null.
[FinalStore]
acts as a switch to indicate whether the dog has been invoiced (sold) or
available
for sale. The problem enters when the user clicks the "Record Invoice"
button. This
button runs several action queries.

The first is an append query(SalesAppendQuery) which appends the Invoice
Number from the main form and the Dog Number and Salesprice for each dog
from the subform to the Sales table.

PARAMETERS [Forms]![InvoiceForm]![StoreCode] Text ( 255 );
INSERT INTO Sales ( [Dog Number], InvSalePrice, [Invoice Number] )
SELECT qryInvoiceSubform2.[Dog Number], qryInvoiceSubform2.SalesPrice,
Invoices.[Invoice Number]
FROM Invoices, qryInvoiceSubform2
WHERE (((Invoices.[Invoice
Number])=[Forms]![InvoiceForm]![txtInvoiceNumber]) AND
((qryInvoiceSubform2.Store)=[Forms]![InvoiceForm]![StoreCode]));

The second query is an update query which is supposed to update the
FinalStore field to the value of txtStore in the subform.

UPDATE Dogs SET Dogs.FinalStore = Forms!InvoiceForm!InvoiceSubform!txtstore
WHERE (((Dogs.[Dog Number])=[Forms]![InvoiceForm]![InvoiceSubform]![Dog
Number]));


When the user clicks the "Record Invoice" button, the first append query
runs fine, but the
second update query only puts the txtstore value in one record and not the
others. For instance,
dogs with Dog Numbers: 1076, 1026 and 907 are all listed on the subform,
but the update only
works on the dog #1076 record and not the other two. FinalStore is blank for
both dog records with
Dog Number 1026 and 907. What would cause this to happen? I placed a
Me.Recalc in my code after the
first query runs. See code below.

Thanks ahead for any help.
Joan



Private Sub RecordInvoice_Click()
On Error GoTo Err_RecordInvoice_Click

Dim stDocName As String
Dim stQueryName As String
Dim FirstUpdate As String

'Run action query to append Dog Numbers of dogs on invoice, the
'Invoice Number of this invoice and the SalesPrice for each dog on the
'invoice to the Sales table.
stDocName = "SalesAppendQuery"
DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName, acViewNormal, acEdit
DoCmd.SetWarnings True
Me.Recalc

'For dogs that have been sold on this invoice, append the StoreCode
' from this form to the dog's FinalStore field.

FirstUpdate = "UpdateFinalStoreRetDog"
DoCmd.SetWarnings False
DoCmd.OpenQuery FirstUpdate, acViewNormal, acEdit
DoCmd.SetWarnings True
Me.Recalc
........
 
I think this happens because the line

[Forms]![InvoiceForm]![InvoiceSubform]![Dog Number]))

references currently active record in the subform, not all records in
the subform.
You need to replace the second query with SQL that provides the entire
recordset for the subform:

UPDATE Dogs SET Dogs.FinalStore = Forms!InvoiceForm!InvoiceSubform!txtstore
WHERE Dogs.[Dog Number] IN (SELECT [Dog Number] FROM qryInvoiceSubform2
WHERE subformPK = mainformPK)

Sorry for subformPK, mainformPK - please replace that with real
parent-child link.
Good luck,
Pavel

Hi,
I am not sure how to resolve the following problem. I have an Invoice
form where when the user enters a store code on the main form, the dogs
that are to be invoiced automatically appear in a subform. This subform is
based on a query that has a limiting criteria of when [FinalStore] Is Null.
[FinalStore]
acts as a switch to indicate whether the dog has been invoiced (sold) or
available
for sale. The problem enters when the user clicks the "Record Invoice"
button. This
button runs several action queries.

The first is an append query(SalesAppendQuery) which appends the Invoice
Number from the main form and the Dog Number and Salesprice for each dog
from the subform to the Sales table.

PARAMETERS [Forms]![InvoiceForm]![StoreCode] Text ( 255 );
INSERT INTO Sales ( [Dog Number], InvSalePrice, [Invoice Number] )
SELECT qryInvoiceSubform2.[Dog Number], qryInvoiceSubform2.SalesPrice,
Invoices.[Invoice Number]
FROM Invoices, qryInvoiceSubform2
WHERE (((Invoices.[Invoice
Number])=[Forms]![InvoiceForm]![txtInvoiceNumber]) AND
((qryInvoiceSubform2.Store)=[Forms]![InvoiceForm]![StoreCode]));

The second query is an update query which is supposed to update the
FinalStore field to the value of txtStore in the subform.

UPDATE Dogs SET Dogs.FinalStore = Forms!InvoiceForm!InvoiceSubform!txtstore
WHERE (((Dogs.[Dog Number])=[Forms]![InvoiceForm]![InvoiceSubform]![Dog
Number]));

When the user clicks the "Record Invoice" button, the first append query
runs fine, but the
second update query only puts the txtstore value in one record and not the
others. For instance,
dogs with Dog Numbers: 1076, 1026 and 907 are all listed on the subform,
but the update only
works on the dog #1076 record and not the other two. FinalStore is blank for
both dog records with
Dog Number 1026 and 907. What would cause this to happen? I placed a
Me.Recalc in my code after the
first query runs. See code below.

Thanks ahead for any help.
Joan

Private Sub RecordInvoice_Click()
On Error GoTo Err_RecordInvoice_Click

Dim stDocName As String
Dim stQueryName As String
Dim FirstUpdate As String

'Run action query to append Dog Numbers of dogs on invoice, the
'Invoice Number of this invoice and the SalesPrice for each dog on the
'invoice to the Sales table.
stDocName = "SalesAppendQuery"
DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName, acViewNormal, acEdit
DoCmd.SetWarnings True
Me.Recalc

'For dogs that have been sold on this invoice, append the StoreCode
' from this form to the dog's FinalStore field.

FirstUpdate = "UpdateFinalStoreRetDog"
DoCmd.SetWarnings False
DoCmd.OpenQuery FirstUpdate, acViewNormal, acEdit
DoCmd.SetWarnings True
Me.Recalc
........
Click to expand...
 
Pavel,

Thank you so much! The SQL you suggested worked like a charm. I am
thrilled.

Joan


Pavel Romashkin said:
I think this happens because the line

[Forms]![InvoiceForm]![InvoiceSubform]![Dog Number]))

references currently active record in the subform, not all records in
the subform.
You need to replace the second query with SQL that provides the entire
recordset for the subform:

UPDATE Dogs SET Dogs.FinalStore = Forms!InvoiceForm!InvoiceSubform!txtstore
WHERE Dogs.[Dog Number] IN (SELECT [Dog Number] FROM qryInvoiceSubform2
WHERE subformPK = mainformPK)

Sorry for subformPK, mainformPK - please replace that with real
parent-child link.
Good luck,
Pavel

Hi,
I am not sure how to resolve the following problem. I have an Invoice
form where when the user enters a store code on the main form, the dogs
that are to be invoiced automatically appear in a subform. This subform is
based on a query that has a limiting criteria of when [FinalStore] Is Null.
[FinalStore]
acts as a switch to indicate whether the dog has been invoiced (sold) or
available
for sale. The problem enters when the user clicks the "Record Invoice"
button. This
button runs several action queries.

The first is an append query(SalesAppendQuery) which appends the Invoice
Number from the main form and the Dog Number and Salesprice for each dog
from the subform to the Sales table.

PARAMETERS [Forms]![InvoiceForm]![StoreCode] Text ( 255 );
INSERT INTO Sales ( [Dog Number], InvSalePrice, [Invoice Number] )
SELECT qryInvoiceSubform2.[Dog Number], qryInvoiceSubform2.SalesPrice,
Invoices.[Invoice Number]
FROM Invoices, qryInvoiceSubform2
WHERE (((Invoices.[Invoice
Number])=[Forms]![InvoiceForm]![txtInvoiceNumber]) AND
((qryInvoiceSubform2.Store)=[Forms]![InvoiceForm]![StoreCode]));

The second query is an update query which is supposed to update the
FinalStore field to the value of txtStore in the subform.

UPDATE Dogs SET Dogs.FinalStore = Forms!InvoiceForm!InvoiceSubform!txtstore
WHERE (((Dogs.[Dog Number])=[Forms]![InvoiceForm]![InvoiceSubform]![Dog
Number]));

When the user clicks the "Record Invoice" button, the first append query
runs fine, but the
second update query only puts the txtstore value in one record and not the
others. For instance,
dogs with Dog Numbers: 1076, 1026 and 907 are all listed on the subform,
but the update only
works on the dog #1076 record and not the other two. FinalStore is blank for
both dog records with
Dog Number 1026 and 907. What would cause this to happen? I placed a
Me.Recalc in my code after the
first query runs. See code below.

Thanks ahead for any help.
Joan

Private Sub RecordInvoice_Click()
On Error GoTo Err_RecordInvoice_Click

Dim stDocName As String
Dim stQueryName As String
Dim FirstUpdate As String

'Run action query to append Dog Numbers of dogs on invoice, the
'Invoice Number of this invoice and the SalesPrice for each dog on the
'invoice to the Sales table.
stDocName = "SalesAppendQuery"
DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName, acViewNormal, acEdit
DoCmd.SetWarnings True
Me.Recalc

'For dogs that have been sold on this invoice, append the StoreCode
' from this form to the dog's FinalStore field.

FirstUpdate = "UpdateFinalStoreRetDog"
DoCmd.SetWarnings False
DoCmd.OpenQuery FirstUpdate, acViewNormal, acEdit
DoCmd.SetWarnings True
Me.Recalc
........
Click to expand...
Click to expand...
 
Pavel,
I posted you back before and said that the update query's SQL you helped
me with worked. As it turns out, it still doesn't quite work.
You posted an SQL to try like the one below. In the SELECT WHERE portion I
substituted the real parent/child link for 'subformPK = mainformPK' like you
suggested.

UPDATE Dogs SET Dogs.FinalStore = Forms!InvoiceForm!InvoiceSubform!txtstore
WHERE Dogs.[Dog Number] IN (SELECT [Dog Number] FROM qryInvoiceSubform2
WHERE subformPK = mainformPK)
Click to expand...

I made the substitution as shown below. However, this not only puts the
txtstore value in the FinalStore field for the dog on the Invoice subform
but it also puts this value in every dog in the query not just the ones on
the invoice. The store field on the main form(control is called StoreCode)
has Invoice.Store as its control source. Whereas the store field for each
dog record in the subform (control name is txtStore) has Dogs.Store as it's
control source. The two store fields serve two different purposes.
Invoices.Store is the store that the Invoice is sent to. When a salesman
sells a dog he indicates such by putting the store code of the customer in
Dogs.Store on a form that shows dogs in inventory. At invoicing time, the
query, qryInvoiceSubform2, retrieves all dogs that have the store field fill
in. Then when the user starts entering data into the InvoiceForm , they
first put in a storecode in the StoreCode control on the main InvoiceForm.
Because of the parent/child link, only the dogs with this storecode in their
Dogs.Store field appear in the subform. The update query below consequently
puts the store code on the this particular invoice into every dog's record
in inventory where a salesmen has entered something into Store. So for
instance say dog # 2 has REAL in it's Dogs.Store field and dog #3 has NEXA
in its store field. When the user invoices to REAL, not only does dog#2
have REAL in its FinalStore field but dog #3 has REAL in its FinalStore
field also. Dog #3 should get NEXA put into its FinalStore field when it is
invoice however. Is my explanation making any sense?

UPDATE Dogs SET Dogs.FinalStore = Forms!InvoiceForm!InvoiceSubform!txtstore
WHERE (((Dogs.[Dog Number]) In (SELECT [Dog Number] FROM qryInvoiceSubform2
WHERE [Store] = [Store])));

How should I alter my query to get it to do what I want? I want it to put
the store code from the Invoice into the FinalStore field of the dogs'
records that are listed in the Invoice subform, but not all of the dogs that
are to be invoiced to other stores. Do I change the second WHERE portion of
my SQL? Or do I try to limit my select query, qryInvoiceSubform2?

I would so appreciate any assistance.

Joan




Pavel Romashkin said:
I think this happens because the line

[Forms]![InvoiceForm]![InvoiceSubform]![Dog Number]))

references currently active record in the subform, not all records in
the subform.
You need to replace the second query with SQL that provides the entire
recordset for the subform:

UPDATE Dogs SET Dogs.FinalStore = Forms!InvoiceForm!InvoiceSubform!txtstore
WHERE Dogs.[Dog Number] IN (SELECT [Dog Number] FROM qryInvoiceSubform2
WHERE subformPK = mainformPK)

Sorry for subformPK, mainformPK - please replace that with real
parent-child link.
Good luck,
Pavel

Hi,
I am not sure how to resolve the following problem. I have an Invoice
form where when the user enters a store code on the main form, the dogs
that are to be invoiced automatically appear in a subform. This subform is
based on a query that has a limiting criteria of when [FinalStore] Is Null.
[FinalStore]
acts as a switch to indicate whether the dog has been invoiced (sold) or
available
for sale. The problem enters when the user clicks the "Record Invoice"
button. This
button runs several action queries.

The first is an append query(SalesAppendQuery) which appends the Invoice
Number from the main form and the Dog Number and Salesprice for each dog
from the subform to the Sales table.

PARAMETERS [Forms]![InvoiceForm]![StoreCode] Text ( 255 );
INSERT INTO Sales ( [Dog Number], InvSalePrice, [Invoice Number] )
SELECT qryInvoiceSubform2.[Dog Number], qryInvoiceSubform2.SalesPrice,
Invoices.[Invoice Number]
FROM Invoices, qryInvoiceSubform2
WHERE (((Invoices.[Invoice
Number])=[Forms]![InvoiceForm]![txtInvoiceNumber]) AND
((qryInvoiceSubform2.Store)=[Forms]![InvoiceForm]![StoreCode]));

The second query is an update query which is supposed to update the
FinalStore field to the value of txtStore in the subform.

UPDATE Dogs SET Dogs.FinalStore = Forms!InvoiceForm!InvoiceSubform!txtstore
WHERE (((Dogs.[Dog Number])=[Forms]![InvoiceForm]![InvoiceSubform]![Dog
Number]));

When the user clicks the "Record Invoice" button, the first append query
runs fine, but the
second update query only puts the txtstore value in one record and not the
others. For instance,
dogs with Dog Numbers: 1076, 1026 and 907 are all listed on the subform,
but the update only
works on the dog #1076 record and not the other two. FinalStore is blank for
both dog records with
Dog Number 1026 and 907. What would cause this to happen? I placed a
Me.Recalc in my code after the
first query runs. See code below.

Thanks ahead for any help.
Joan

Private Sub RecordInvoice_Click()
On Error GoTo Err_RecordInvoice_Click

Dim stDocName As String
Dim stQueryName As String
Dim FirstUpdate As String

'Run action query to append Dog Numbers of dogs on invoice, the
'Invoice Number of this invoice and the SalesPrice for each dog on the
'invoice to the Sales table.
stDocName = "SalesAppendQuery"
DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName, acViewNormal, acEdit
DoCmd.SetWarnings True
Me.Recalc

'For dogs that have been sold on this invoice, append the StoreCode
' from this form to the dog's FinalStore field.

FirstUpdate = "UpdateFinalStoreRetDog"
DoCmd.SetWarnings False
DoCmd.OpenQuery FirstUpdate, acViewNormal, acEdit
DoCmd.SetWarnings True
Me.Recalc
........
Click to expand...
Click to expand...
 
Hi Joan,

Not being able to see the actual DB, it is rather difficult for me to
follow the logic. However, it is clear that there is a flaw in this SQL:

UPDATE Dogs SET Dogs.FinalStore = Forms!InvoiceForm!InvoiceSubform!txtstore
WHERE (((Dogs.[Dog Number]) In (SELECT [Dog Number] FROM qryInvoiceSubform2
WHERE [Store] = [Store])));

because the last WHERE will return every single record. Try:

UPDATE Dogs SET Dogs.FinalStore = Forms!InvoiceForm!InvoiceSubform!txtstore
WHERE (((Dogs.[Dog Number]) In (SELECT [Dog Number] FROM qryInvoiceSubform2
WHERE [Store] = Invoice.Store)));

Invoice.Store should be a field in the qryInvoiceSubform2, or you can
pull it from the form. In my original suggestion, I thought you will use
the ParentID field from the main form, which would restrict the number
of records retrieved by the SQL in parenthesis.
I hope this helps. If not, it might be better to take this thread to
E-mail rather than this news group becasue it becomes very specific to
your particular database.
Good luck,
Pavel
Pavel,
I posted you back before and said that the update query's SQL you helped
me with worked. As it turns out, it still doesn't quite work.
You posted an SQL to try like the one below. In the SELECT WHERE portion I
substituted the real parent/child link for 'subformPK = mainformPK' like you
suggested.

UPDATE Dogs SET Dogs.FinalStore = Forms!InvoiceForm!InvoiceSubform!txtstore
WHERE Dogs.[Dog Number] IN (SELECT [Dog Number] FROM qryInvoiceSubform2
WHERE subformPK = mainformPK)
Click to expand...

I made the substitution as shown below. However, this not only puts the
txtstore value in the FinalStore field for the dog on the Invoice subform
but it also puts this value in every dog in the query not just the ones on
the invoice. The store field on the main form(control is called StoreCode)
has Invoice.Store as its control source. Whereas the store field for each
dog record in the subform (control name is txtStore) has Dogs.Store as it's
control source. The two store fields serve two different purposes.
Invoices.Store is the store that the Invoice is sent to. When a salesman
sells a dog he indicates such by putting the store code of the customer in
Dogs.Store on a form that shows dogs in inventory. At invoicing time, the
query, qryInvoiceSubform2, retrieves all dogs that have the store field fill
in. Then when the user starts entering data into the InvoiceForm , they
first put in a storecode in the StoreCode control on the main InvoiceForm.
Because of the parent/child link, only the dogs with this storecode in their
Dogs.Store field appear in the subform. The update query below consequently
puts the store code on the this particular invoice into every dog's record
in inventory where a salesmen has entered something into Store. So for
instance say dog # 2 has REAL in it's Dogs.Store field and dog #3 has NEXA
in its store field. When the user invoices to REAL, not only does dog#2
have REAL in its FinalStore field but dog #3 has REAL in its FinalStore
field also. Dog #3 should get NEXA put into its FinalStore field when it is
invoice however. Is my explanation making any sense?

UPDATE Dogs SET Dogs.FinalStore = Forms!InvoiceForm!InvoiceSubform!txtstore
WHERE (((Dogs.[Dog Number]) In (SELECT [Dog Number] FROM qryInvoiceSubform2
WHERE [Store] = [Store])));

How should I alter my query to get it to do what I want? I want it to put
the store code from the Invoice into the FinalStore field of the dogs'
records that are listed in the Invoice subform, but not all of the dogs that
are to be invoiced to other stores. Do I change the second WHERE portion of
my SQL? Or do I try to limit my select query, qryInvoiceSubform2?

I would so appreciate any assistance.

Joan

Pavel Romashkin said:
I think this happens because the line

[Forms]![InvoiceForm]![InvoiceSubform]![Dog Number]))

references currently active record in the subform, not all records in
the subform.
You need to replace the second query with SQL that provides the entire
recordset for the subform:

UPDATE Dogs SET Dogs.FinalStore = Forms!InvoiceForm!InvoiceSubform!txtstore
WHERE Dogs.[Dog Number] IN (SELECT [Dog Number] FROM qryInvoiceSubform2
WHERE subformPK = mainformPK)

Sorry for subformPK, mainformPK - please replace that with real
parent-child link.
Good luck,
Pavel

Hi,
I am not sure how to resolve the following problem. I have an Invoice
form where when the user enters a store code on the main form, the dogs
that are to be invoiced automatically appear in a subform. This subform is
based on a query that has a limiting criteria of when [FinalStore] Is Null.
[FinalStore]
acts as a switch to indicate whether the dog has been invoiced (sold) or
available
for sale. The problem enters when the user clicks the "Record Invoice"
button. This
button runs several action queries.

The first is an append query(SalesAppendQuery) which appends the Invoice
Number from the main form and the Dog Number and Salesprice for each dog
from the subform to the Sales table.

PARAMETERS [Forms]![InvoiceForm]![StoreCode] Text ( 255 );
INSERT INTO Sales ( [Dog Number], InvSalePrice, [Invoice Number] )
SELECT qryInvoiceSubform2.[Dog Number], qryInvoiceSubform2.SalesPrice,
Invoices.[Invoice Number]
FROM Invoices, qryInvoiceSubform2
WHERE (((Invoices.[Invoice
Number])=[Forms]![InvoiceForm]![txtInvoiceNumber]) AND
((qryInvoiceSubform2.Store)=[Forms]![InvoiceForm]![StoreCode]));

The second query is an update query which is supposed to update the
FinalStore field to the value of txtStore in the subform.

UPDATE Dogs SET Dogs.FinalStore = Forms!InvoiceForm!InvoiceSubform!txtstore
WHERE (((Dogs.[Dog Number])=[Forms]![InvoiceForm]![InvoiceSubform]![Dog
Number]));

When the user clicks the "Record Invoice" button, the first append query
runs fine, but the
second update query only puts the txtstore value in one record and not the
others. For instance,
dogs with Dog Numbers: 1076, 1026 and 907 are all listed on the subform,
but the update only
works on the dog #1076 record and not the other two. FinalStore is blank for
both dog records with
Dog Number 1026 and 907. What would cause this to happen? I placed a
Me.Recalc in my code after the
first query runs. See code below.

Thanks ahead for any help.
Joan

Private Sub RecordInvoice_Click()
On Error GoTo Err_RecordInvoice_Click

Dim stDocName As String
Dim stQueryName As String
Dim FirstUpdate As String

'Run action query to append Dog Numbers of dogs on invoice, the
'Invoice Number of this invoice and the SalesPrice for each dog on the
'invoice to the Sales table.
stDocName = "SalesAppendQuery"
DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName, acViewNormal, acEdit
DoCmd.SetWarnings True
Me.Recalc

'For dogs that have been sold on this invoice, append the StoreCode
' from this form to the dog's FinalStore field.

FirstUpdate = "UpdateFinalStoreRetDog"
DoCmd.SetWarnings False
DoCmd.OpenQuery FirstUpdate, acViewNormal, acEdit
DoCmd.SetWarnings True
Me.Recalc
........
Click to expand...
Click to expand...
Click to expand...
 
Back
Top