Two subforms + update query

J

Joan

Hi,

I am having trouble getting an update query to work I am running it on an
AfterUpdate event of a control on one subform and want to update the
recordset of another subform on the main form. I am using some of the
controls on the first subform as parameters for the update query. I've tried
passing the parameter values to the QueryDef via VBA but the update does not
run. The main form (EditMode) is an EditDeleteInvoice form and there is a
subform call EditDog with Dogs that have been sold and were invoiced
earlier. There is also a form called AddDogtoInv where the user can enter a
dog number of a dog that was added (sold) after the invoice was recorded but
before it is sent to the store(customer). The AddDogtoInv form essentially
adds the Dog Number entered and the Invoice Number of the main form to the
Sales table. The other dogs listed in the EditDog subform were earlier added
to the Sales table.


When I enter a Dog Number in the AddDogtoInv subform and tab, the dogs
record appears in the EditDog form but the [Store] field is not updated.
Could someone take a look at my code and see if you can see what I am doing
wrong? Thank you so much.

Joan

The following three lines of code on the After Update event of
Subform!AddDogtoInv![DogN], essentially updates the EditDog subform with the
just added dog's record to the Sales table.

Me.Recalc
Me.Requery
Forms![EditDeleteInvoice]![EditDog].Requery

Also below is the SQL of my update query:

PARAMETERS [Forms]![EditDeleteInvoice]![AddDogtoInv]![DogN] Long,
[Forms]![EditDeleteInvoice]![AddDogtoInv]![txtInvoiceNumber] Long;
UPDATE qryAddDogtoInv SET qryAddDogtoInv.Store =
Forms!EditDeleteInvoice!StoreCode
WHERE (((qryAddDogtoInv.[Dog
Number])=[Forms]![EditDeleteInvoice]![AddDogtoInv]![DogN]) AND
((qryAddDogtoInv.[Invoice
Number])=[Forms]![EditDeleteInvoice]![AddDogtoInv]![txtInvoiceNumber]));


And my code:

Private Sub DogN_AfterUpdate()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim stRet As String
Dim qdf As DAO.QueryDef
Dim rs2 As DAO.Recordset




Set db = CurrentDb
Set rs = db.OpenRecordset("qryAddDogtoInv", dbOpenDynaset) ' the
query for the AddDogtoInv subform
Set rs2 = db.OpenRecordset("qryInvoiceSubEdit", dbOpenDynaset) ' the
query for the EditDog subform
stDog = Me.DogN
stRet = Me.Returned
stInvoice = Me.txtInvoiceNumber

Me.Recalc
Me.Requery
Forms![EditDeleteInvoice]![EditDog].Requery

With rs
If .EditMode = adEditInProgress Then
Me.Recordset.Update
End If
.Requery
End With

Set qdf = db.QueryDefs("SaleReturnAddDogStore")

'Execute Update query to set [Store] in dog record added to
invoice(EditDog subform) to store value in the main form.
With qdf
.Parameters("[Forms]![EditDeleteInvoice]![AddDogtoInv]![DogN]")
= stDog

..Parameters("[Forms]![EditDeleteInvoice]![AddDogtoInv]![txtInvoiceNumber]")
= stInvoice
.Execute
End With


rs.Requery
Me.Recalc
rs2.Requery
Forms![EditDeleteInvoice]![AddDogtoInv].Requery

End Sub
 
G

Graham Mandeno

Hi Joan

Your code seems to be way too complicated for what is actually required.
For a start, I don't see the point of opening the two recordsets rs and rs2
as you are not actually doing anything with them (apart from a couple of
unnecessary Requerys). [Incidentally, you are also not closing these
recordsets, which can leak memory and cause problems, and you are comparing
the EditMode of a DAO.RecordSet (rs) with an ADO constant
(adEditInProgress). As it happens, adEditInProgress has the same value as
dbEditInProgress (the corresponding DAO constant), but conceptually you
might as well be comparing with Pi :)]

Now, I figure you want to update the record corresponding to the given DogN
and InvoiceNumber with the StoreCode from the main form. Correct?

You can do this without a parameter query:

strSQL = "UPDATE qryAddDogtoInv SET Store = " _
& Me.Parent!StoreCode _
& " WHERE [Dog Number] = " & Me.DogN _
& " AND [Invoice Number] = " & Me.txtInvoiceNumber
CurrentDb.Execute strSQL, dbFailOnError

(Of course you will need quotes around any of these "numbers" that are
actually text.)

To requery the subform, you simply call the Requery method on its Form
object:
Forms![EditDeleteInvoice]![AddDogToInv].Form.Requery

Of course you can also abbreviate this to Me!... (from the main form) or
Me.Parent!... (from another subform).

I can't help thinking there's an even easier way to do this through the
context of the open (sub)forms without a SQL update, but I don't really
understand your table/form structure :)

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


Joan said:
Hi,

I am having trouble getting an update query to work I am running it on an
AfterUpdate event of a control on one subform and want to update the
recordset of another subform on the main form. I am using some of the
controls on the first subform as parameters for the update query. I've tried
passing the parameter values to the QueryDef via VBA but the update does not
run. The main form (EditMode) is an EditDeleteInvoice form and there is a
subform call EditDog with Dogs that have been sold and were invoiced
earlier. There is also a form called AddDogtoInv where the user can enter a
dog number of a dog that was added (sold) after the invoice was recorded but
before it is sent to the store(customer). The AddDogtoInv form essentially
adds the Dog Number entered and the Invoice Number of the main form to the
Sales table. The other dogs listed in the EditDog subform were earlier added
to the Sales table.


When I enter a Dog Number in the AddDogtoInv subform and tab, the dogs
record appears in the EditDog form but the [Store] field is not updated.
Could someone take a look at my code and see if you can see what I am doing
wrong? Thank you so much.

Joan

The following three lines of code on the After Update event of
Subform!AddDogtoInv![DogN], essentially updates the EditDog subform with the
just added dog's record to the Sales table.

Me.Recalc
Me.Requery
Forms![EditDeleteInvoice]![EditDog].Requery

Also below is the SQL of my update query:

PARAMETERS [Forms]![EditDeleteInvoice]![AddDogtoInv]![DogN] Long,
[Forms]![EditDeleteInvoice]![AddDogtoInv]![txtInvoiceNumber] Long;
UPDATE qryAddDogtoInv SET qryAddDogtoInv.Store =
Forms!EditDeleteInvoice!StoreCode
WHERE (((qryAddDogtoInv.[Dog
Number])=[Forms]![EditDeleteInvoice]![AddDogtoInv]![DogN]) AND
((qryAddDogtoInv.[Invoice
Number])=[Forms]![EditDeleteInvoice]![AddDogtoInv]![txtInvoiceNumber]));


And my code:

Private Sub DogN_AfterUpdate()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim stRet As String
Dim qdf As DAO.QueryDef
Dim rs2 As DAO.Recordset




Set db = CurrentDb
Set rs = db.OpenRecordset("qryAddDogtoInv", dbOpenDynaset) ' the
query for the AddDogtoInv subform
Set rs2 = db.OpenRecordset("qryInvoiceSubEdit", dbOpenDynaset) ' the
query for the EditDog subform
stDog = Me.DogN
stRet = Me.Returned
stInvoice = Me.txtInvoiceNumber

Me.Recalc
Me.Requery
Forms![EditDeleteInvoice]![EditDog].Requery

With rs
If .EditMode = adEditInProgress Then
Me.Recordset.Update
End If
.Requery
End With

Set qdf = db.QueryDefs("SaleReturnAddDogStore")

'Execute Update query to set [Store] in dog record added to
invoice(EditDog subform) to store value in the main form.
With qdf
..Parameters("[Forms]![EditDeleteInvoice]![AddDogtoInv]![DogN]")
= stDog

..Parameters("[Forms]![EditDeleteInvoice]![AddDogtoInv]![txtInvoiceNumber]")
= stInvoice
.Execute
End With


rs.Requery
Me.Recalc
rs2.Requery
Forms![EditDeleteInvoice]![AddDogtoInv].Requery

End Sub
Click to expand...
 
J

Joan

Hi Graham,

Thank you! I followed your example and was able to update the record
corresponding to the given
DogN and InvoiceNumber with the StoreCode from the main form. But now I want
to run another update
on fields in the recordset but not on the form itself. For this one the SQL
is quite long (see below) and I don't
believe my syntax is correct, because it doesn't work. Would you mind
taking a look at it to see what may be wrong?

Joan

Private Sub DogN_AfterUpdate()

Dim strSQL As String
Dim strSQL2 As String

'Update the record corresponding to the given DogN and InvoiceNumber on
subform AddDogtoInv with _
the StoreCode from the main form

strSQL = "UPDATE qryAddDogtoInv SET Store = " & """" & Me.Parent!StoreCode
& """" & "WHERE [Dog Number] =" & Me.DogN & " AND [Invoice Number]=" &
Me.txtInvoiceNumber
CurrentDb.Execute strSQL, dbFailOnError
Me.Recalc
Me.Requery
Forms![EditDeleteInvoice]![EditDog].Form.Requery

' If the [Returned] field corresponding to the given DogN and InvoiceNumber
on subform AddDogtoInv is Not Null then _
update the corresponding record's ReturnedStore, ReturnedInvoice,
ReturnedSaleDate, and ReturnedSalePrice fields. These fields _
are part of the recordset for AddDogtoInv but are not on the form.

If Me.Returned <> Null Then

strSQL2 = "UPDATE qryAddDogtoInv SET ReturnedSaleDate = & #" &
Me.Parent!dteDate & "#" & "ReturnedStore =" & _
"""" & Me.Parent!StoreCode & """" & "ReturnedInvoice =" &
Me.Parent!txtInvoiceNumber & "ReturnedSalePrice =" & _
"" & Forms!EditDeleteInvoice!EditDog!SalesPrice & "" & "WHERE [Dog
Number]=" & Me.DogN & "[Invoice Number] =" & Me.txtInvoiceNumber & _
"Returned <> Null"


CurrentDb.Execute strSQL2, dbFailOnError
Me.Recalc
Me.Requery
Forms![EditDeleteInvoice]![EditDog].Form.Requery
End If
End Sub






Graham Mandeno said:
Hi Joan

Your code seems to be way too complicated for what is actually required.
For a start, I don't see the point of opening the two recordsets rs and rs2
as you are not actually doing anything with them (apart from a couple of
unnecessary Requerys). [Incidentally, you are also not closing these
recordsets, which can leak memory and cause problems, and you are comparing
the EditMode of a DAO.RecordSet (rs) with an ADO constant
(adEditInProgress). As it happens, adEditInProgress has the same value as
dbEditInProgress (the corresponding DAO constant), but conceptually you
might as well be comparing with Pi :)]

Now, I figure you want to update the record corresponding to the given DogN
and InvoiceNumber with the StoreCode from the main form. Correct?

You can do this without a parameter query:

strSQL = "UPDATE qryAddDogtoInv SET Store = " _
& Me.Parent!StoreCode _
& " WHERE [Dog Number] = " & Me.DogN _
& " AND [Invoice Number] = " & Me.txtInvoiceNumber
CurrentDb.Execute strSQL, dbFailOnError

(Of course you will need quotes around any of these "numbers" that are
actually text.)

To requery the subform, you simply call the Requery method on its Form
object:
Forms![EditDeleteInvoice]![AddDogToInv].Form.Requery

Of course you can also abbreviate this to Me!... (from the main form) or
Me.Parent!... (from another subform).

I can't help thinking there's an even easier way to do this through the
context of the open (sub)forms without a SQL update, but I don't really
understand your table/form structure :)

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


Joan said:
Hi,

I am having trouble getting an update query to work I am running it on an
AfterUpdate event of a control on one subform and want to update the
recordset of another subform on the main form. I am using some of the
controls on the first subform as parameters for the update query. I've tried
passing the parameter values to the QueryDef via VBA but the update does not
run. The main form (EditMode) is an EditDeleteInvoice form and there is a
subform call EditDog with Dogs that have been sold and were invoiced
earlier. There is also a form called AddDogtoInv where the user can
Click to expand...
enter
a
dog number of a dog that was added (sold) after the invoice was recorded but
before it is sent to the store(customer). The AddDogtoInv form essentially
adds the Dog Number entered and the Invoice Number of the main form to the
Sales table. The other dogs listed in the EditDog subform were earlier added
to the Sales table.


When I enter a Dog Number in the AddDogtoInv subform and tab, the dogs
record appears in the EditDog form but the [Store] field is not updated.
Could someone take a look at my code and see if you can see what I am doing
wrong? Thank you so much.

Joan

The following three lines of code on the After Update event of
Subform!AddDogtoInv![DogN], essentially updates the EditDog subform with the
just added dog's record to the Sales table.

Me.Recalc
Me.Requery
Forms![EditDeleteInvoice]![EditDog].Requery

Also below is the SQL of my update query:

PARAMETERS [Forms]![EditDeleteInvoice]![AddDogtoInv]![DogN] Long,
[Forms]![EditDeleteInvoice]![AddDogtoInv]![txtInvoiceNumber] Long;
UPDATE qryAddDogtoInv SET qryAddDogtoInv.Store =
Forms!EditDeleteInvoice!StoreCode
WHERE (((qryAddDogtoInv.[Dog
Number])=[Forms]![EditDeleteInvoice]![AddDogtoInv]![DogN]) AND
((qryAddDogtoInv.[Invoice
Number])=[Forms]![EditDeleteInvoice]![AddDogtoInv]![txtInvoiceNumber]));


And my code:

Private Sub DogN_AfterUpdate()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim stRet As String
Dim qdf As DAO.QueryDef
Dim rs2 As DAO.Recordset




Set db = CurrentDb
Set rs = db.OpenRecordset("qryAddDogtoInv", dbOpenDynaset) ' the
query for the AddDogtoInv subform
Set rs2 = db.OpenRecordset("qryInvoiceSubEdit", dbOpenDynaset) ' the
query for the EditDog subform
stDog = Me.DogN
stRet = Me.Returned
stInvoice = Me.txtInvoiceNumber

Me.Recalc
Me.Requery
Forms![EditDeleteInvoice]![EditDog].Requery

With rs
If .EditMode = adEditInProgress Then
Me.Recordset.Update
End If
.Requery
End With

Set qdf = db.QueryDefs("SaleReturnAddDogStore")

'Execute Update query to set [Store] in dog record added to
invoice(EditDog subform) to store value in the main form.
With qdf
.Parameters("[Forms]![EditDeleteInvoice]![AddDogtoInv]![DogN]")
= stDog
Click to expand...
..Parameters("[Forms]![EditDeleteInvoice]![AddDogtoInv]![txtInvoiceNumber]")
= stInvoice
.Execute
End With


rs.Requery
Me.Recalc
rs2.Requery
Forms![EditDeleteInvoice]![AddDogtoInv].Requery

End Sub
Click to expand...
Click to expand...
 
G

Graham Mandeno

Hi Joan

A good trick for debugging SQL is to paste it into a query and try to run
it. If you set a breakpoint on the CurrentDb.Execute line, then type
?strSQL2 in the debug window, you will get to see the full SQL string.
Often the problem will be immediately obvious, but if not, you can copy the
string from the debug window and paste it into the SQL window of a new,
empty query. Attempting to run a query with errors will often give you a
more informative message than a db.Execute.

In this case, I can see at least one problem, and that is that you have
omitted the commas between the fields you are SETting in your UPDATE SQL.
Also, you have omitted crucial blanks, included an "&" inside the string,
left out ANDs between criteria, and finally have an invalid <> comparison
with Null:

Your resulting strSQL2 string will be something like this:

UPDATE qryAddDogtoInv SET ReturnedSaleDate = & #1/16/04#ReturnedStore
="A1234"ReturnedInvoice =999ReturnedSalePrice =89.95WHERE [Dog
Number]=123[Invoice Number] =999Returned <> Null

....which would be complete gobbledegook to the SQL parser!

Try this instead:

strSQL2 = "UPDATE qryAddDogtoInv SET ReturnedSaleDate = #" _
& Me.Parent!dteDate & "#, ReturnedStore =""" _
& Me.Parent!StoreCode & """, ReturnedInvoice =" _
& Me.Parent!txtInvoiceNumber & ", ReturnedSalePrice =" & _
& Forms!EditDeleteInvoice!EditDog!SalesPrice _
& " WHERE [Dog Number]=" & Me.DogN _
& " AND [Invoice Number] =" & Me.txtInvoiceNumber _
& " AND Returned Is Not Null"

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Joan said:
Hi Graham,

Thank you! I followed your example and was able to update the record
corresponding to the given
DogN and InvoiceNumber with the StoreCode from the main form. But now I want
to run another update
on fields in the recordset but not on the form itself. For this one the SQL
is quite long (see below) and I don't
believe my syntax is correct, because it doesn't work. Would you mind
taking a look at it to see what may be wrong?

Joan

Private Sub DogN_AfterUpdate()

Dim strSQL As String
Dim strSQL2 As String

'Update the record corresponding to the given DogN and InvoiceNumber on
subform AddDogtoInv with _
the StoreCode from the main form

strSQL = "UPDATE qryAddDogtoInv SET Store = " & """" & Me.Parent!StoreCode
& """" & "WHERE [Dog Number] =" & Me.DogN & " AND [Invoice Number]=" &
Me.txtInvoiceNumber
CurrentDb.Execute strSQL, dbFailOnError
Me.Recalc
Me.Requery
Forms![EditDeleteInvoice]![EditDog].Form.Requery

' If the [Returned] field corresponding to the given DogN and InvoiceNumber
on subform AddDogtoInv is Not Null then _
update the corresponding record's ReturnedStore, ReturnedInvoice,
ReturnedSaleDate, and ReturnedSalePrice fields. These fields _
are part of the recordset for AddDogtoInv but are not on the form.

If Me.Returned <> Null Then

strSQL2 = "UPDATE qryAddDogtoInv SET ReturnedSaleDate = & #" &
Me.Parent!dteDate & "#" & "ReturnedStore =" & _
"""" & Me.Parent!StoreCode & """" & "ReturnedInvoice =" &
Me.Parent!txtInvoiceNumber & "ReturnedSalePrice =" & _
"" & Forms!EditDeleteInvoice!EditDog!SalesPrice & "" & "WHERE [Dog
Number]=" & Me.DogN & "[Invoice Number] =" & Me.txtInvoiceNumber & _
"Returned <> Null"


CurrentDb.Execute strSQL2, dbFailOnError
Me.Recalc
Me.Requery
Forms![EditDeleteInvoice]![EditDog].Form.Requery
End If
End Sub






Graham Mandeno said:
Hi Joan

Your code seems to be way too complicated for what is actually required.
For a start, I don't see the point of opening the two recordsets rs and rs2
as you are not actually doing anything with them (apart from a couple of
unnecessary Requerys). [Incidentally, you are also not closing these
recordsets, which can leak memory and cause problems, and you are comparing
the EditMode of a DAO.RecordSet (rs) with an ADO constant
(adEditInProgress). As it happens, adEditInProgress has the same value as
dbEditInProgress (the corresponding DAO constant), but conceptually you
might as well be comparing with Pi :)]

Now, I figure you want to update the record corresponding to the given DogN
and InvoiceNumber with the StoreCode from the main form. Correct?

You can do this without a parameter query:

strSQL = "UPDATE qryAddDogtoInv SET Store = " _
& Me.Parent!StoreCode _
& " WHERE [Dog Number] = " & Me.DogN _
& " AND [Invoice Number] = " & Me.txtInvoiceNumber
CurrentDb.Execute strSQL, dbFailOnError

(Of course you will need quotes around any of these "numbers" that are
actually text.)

To requery the subform, you simply call the Requery method on its Form
object:
Forms![EditDeleteInvoice]![AddDogToInv].Form.Requery

Of course you can also abbreviate this to Me!... (from the main form) or
Me.Parent!... (from another subform).

I can't help thinking there's an even easier way to do this through the
context of the open (sub)forms without a SQL update, but I don't really
understand your table/form structure :)

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


Joan said:
Hi,

I am having trouble getting an update query to work I am running it
Click to expand...
on
an
AfterUpdate event of a control on one subform and want to update the
recordset of another subform on the main form. I am using some of the
controls on the first subform as parameters for the update query. I've tried
passing the parameter values to the QueryDef via VBA but the update
Click to expand...
does
not
run. The main form (EditMode) is an EditDeleteInvoice form and there
Click to expand...
Click to expand...
is
a
subform call EditDog with Dogs that have been sold and were invoiced
earlier. There is also a form called AddDogtoInv where the user can
Click to expand...
enter
a
dog number of a dog that was added (sold) after the invoice was
Click to expand...
recorded
but
before it is sent to the store(customer). The AddDogtoInv form essentially
adds the Dog Number entered and the Invoice Number of the main form to the
Sales table. The other dogs listed in the EditDog subform were earlier added
to the Sales table.


When I enter a Dog Number in the AddDogtoInv subform and tab, the dogs
record appears in the EditDog form but the [Store] field is not updated.
Could someone take a look at my code and see if you can see what I am doing
wrong? Thank you so much.

Joan

The following three lines of code on the After Update event of
Subform!AddDogtoInv![DogN], essentially updates the EditDog subform
Click to expand...
with
the
just added dog's record to the Sales table.

Me.Recalc
Me.Requery
Forms![EditDeleteInvoice]![EditDog].Requery

Also below is the SQL of my update query:

PARAMETERS [Forms]![EditDeleteInvoice]![AddDogtoInv]![DogN] Long,
[Forms]![EditDeleteInvoice]![AddDogtoInv]![txtInvoiceNumber] Long;
UPDATE qryAddDogtoInv SET qryAddDogtoInv.Store =
Forms!EditDeleteInvoice!StoreCode
WHERE (((qryAddDogtoInv.[Dog
Number])=[Forms]![EditDeleteInvoice]![AddDogtoInv]![DogN]) AND
((qryAddDogtoInv.[Invoice
Number])=[Forms]![EditDeleteInvoice]![AddDogtoInv]![txtInvoiceNumber]));


And my code:

Private Sub DogN_AfterUpdate()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim stRet As String
Dim qdf As DAO.QueryDef
Dim rs2 As DAO.Recordset




Set db = CurrentDb
Set rs = db.OpenRecordset("qryAddDogtoInv", dbOpenDynaset) ' the
query for the AddDogtoInv subform
Set rs2 = db.OpenRecordset("qryInvoiceSubEdit", dbOpenDynaset) ' the
query for the EditDog subform
stDog = Me.DogN
stRet = Me.Returned
stInvoice = Me.txtInvoiceNumber

Me.Recalc
Me.Requery
Forms![EditDeleteInvoice]![EditDog].Requery

With rs
If .EditMode = adEditInProgress Then
Me.Recordset.Update
End If
.Requery
End With

Set qdf = db.QueryDefs("SaleReturnAddDogStore")

'Execute Update query to set [Store] in dog record added to
invoice(EditDog subform) to store value in the main form.
With qdf
.Parameters("[Forms]![EditDeleteInvoice]![AddDogtoInv]![DogN]")
= stDog
Click to expand...
Click to expand...
..Parameters("[Forms]![EditDeleteInvoice]![AddDogtoInv]![txtInvoiceNumber]")
= stInvoice
.Execute
End With


rs.Requery
Me.Recalc
rs2.Requery
Forms![EditDeleteInvoice]![AddDogtoInv].Requery

End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Subform problem 4
Getting update query to work 1
Update query after an append query?+ 5
Main Form and two Subforms 2
Passing a value from subform or main form to subform query 4
Update table on (sub)form exit 2
Not getting results I need 2
Using SQL Query Code in Form Module to Return Record Count 2

Top