Edit and Update of field not working

[Joan]

I have been trying to update a field in a table not in the record source of
my form. My form (CreditMemo) has a button that records the [Returned] ( Y
or N), [ReturnedComment] and [ReturnedDate] in the Sales table via an update
query for a dog that has been returned by the customer. This results in the
dog being added back to inventory (SalesScreen). However, in order that
salesmen who view the SalesScreen know whether a dog is available for sale
or not, the [Store] data from the first sale must be taken out of the
[Store] control. If the Store field is blank, the salemen then know that
the dog is available for sale. If the Store field is filled in, the dogs
has been sold but not invoice or shipped yet (then the DateSold field is
filled in at invoicing time). Please can anyone tell me why my code below,
is not taking the data out of the Store field and leaving it blank. When I
run the code, the Store field information is still in the control.

Joan

Dim stQueryName As String
Dim db As Database
Dim rs As DAO.Recordset
Dim strOldStore As String
Dim strNewStore As String

Me.Recalc
stQueryName = "qryReturned"
DoCmd.SetWarnings False
DoCmd.OpenQuery stQueryName, acViewNormal, acEdit
DoCmd.SetWarnings True

Set db = CurrentDb
Set rs = db.OpenRecordset("Dogs")
'Store original data
strOldStore = rs!Store
'Get new data ("") for record. Make [Store] empty on SalesScreen for
returned dog.
strNewStore = ""
With rs
.Edit
!Store = strNewStore
.Update
.Bookmark = .LastModified
End With
rs.Close
db.Close

End Sub

 

[Ken Snell]

I am a bit confused, as you say you're updating a field that isn't in your
form's recordsource, but then you want to know why that value is still being
displayed on the form?

But, although you're updating the "Dogs" table (query?), you never requery
any object that is bound to that recordset (rs). I'm guessing that that is
the problem, but...as noted in my first question, I'm not sure I'm
understanding exactly what you're doing.

 

[Greg Kraushaar]

Lets see If I have this straight....
YOu are doing returns on one form. You use this to update an unbound
table to restore the items status to "available"

Some one else on another computer, using another form (bound to the
table you have updated) is still seeing the item as unavailable.

You will need to refresh the data on the second user's screen
or if the data is in a list box, or combobox, you will need to requery
the control.

HTH

 

[Joan]

Ken,

I am updating a field that isn't in the record source of the Credit Memo
form. Well, it is but it isn't. The [Store] field is in both the Invoices
table which the Credit Memo affects and in the Dogs table which is the
[Store] field that I want to be blank for the particular dog that is
returned and issued a credit memo for. [Store] in the Invoices table
indicates the store that an invoice or credit memo is issued to. Store in
the dogs table is a field that is filled in by a salesman to indicate that a
dog has been sold but not yet invoiced ( at invoicing time I query for dogs
to put on the InvoiceStatement by whether or not this [Store] field in the
dogs table is filled in with a particular store) for all dogs records whose
DateSold field has not yet been entered.(This date is entered at Invoicing
time).

When wondering why the old store value is still being displayed on the form,
the form that I am referring to is the SalesScreen form. This form's record
source "EnterStoresQuery" is composed of many other queries to gather the
required data for the SalesScreen. Basically the [Store] field in this
query comes from the Dogs table. Hope this explains further what I am
trying to do?

Are you saying in your last paragraph that you suspect the problem is that I
have not requeried the SalesScreen form?

Joan

I am a bit confused, as you say you're updating a field that isn't in your
form's recordsource, but then you want to know why that value is still being
displayed on the form?

But, although you're updating the "Dogs" table (query?), you never requery
any object that is bound to that recordset (rs). I'm guessing that that is
the problem, but...as noted in my first question, I'm not sure I'm
understanding exactly what you're doing.


--
Ken Snell
<MS ACCESS MVP>

I have been trying to update a field in a table not in the record source of
my form. My form (CreditMemo) has a button that records the [Returned] ( Y
or N), [ReturnedComment] and [ReturnedDate] in the Sales table via an update
query for a dog that has been returned by the customer. This results in the
dog being added back to inventory (SalesScreen). However, in order that
salesmen who view the SalesScreen know whether a dog is available for sale
or not, the [Store] data from the first sale must be taken out of the
[Store] control. If the Store field is blank, the salemen then know that
the dog is available for sale. If the Store field is filled in, the dogs
has been sold but not invoice or shipped yet (then the DateSold field is
filled in at invoicing time). Please can anyone tell me why my code below,
is not taking the data out of the Store field and leaving it blank.

When

I
run the code, the Store field information is still in the control.

Joan

Dim stQueryName As String
Dim db As Database
Dim rs As DAO.Recordset
Dim strOldStore As String
Dim strNewStore As String

Me.Recalc
stQueryName = "qryReturned"
DoCmd.SetWarnings False
DoCmd.OpenQuery stQueryName, acViewNormal, acEdit
DoCmd.SetWarnings True

Set db = CurrentDb
Set rs = db.OpenRecordset("Dogs")
'Store original data
strOldStore = rs!Store
'Get new data ("") for record. Make [Store] empty on SalesScreen for
returned dog.
strNewStore = ""
With rs
.Edit
!Store = strNewStore
.Update
.Bookmark = .LastModified
End With
rs.Close
db.Close

End Sub

 

[Joan]

Right, I am doing returns on the Credit Memo form but am updating an unbound
table to restore the items status to "available" (assuming by unbound table
that you mean a table not used in the record source of the Credit Memo
form). But forget another computer. For now, I would like this to work on
one computer, this one. You are right though, I am using another form
(called SalesScreen) which is bound to a query which contains the table I am
trying to update. On the SalesScreen, the item is showing as not being
"available" because the Store control still contains data from the first
sale of the dog. How do I refresh the data on the other form(SalesScreen)
when it does not have the focus and may not even be opened?

Joan

Lets see If I have this straight....
YOu are doing returns on one form. You use this to update an unbound
table to restore the items status to "available"

Some one else on another computer, using another form (bound to the
table you have updated) is still seeing the item as unavailable.

You will need to refresh the data on the second user's screen
or if the data is in a list box, or combobox, you will need to requery
the control.

HTH

I have been trying to update a field in a table not in the record source of
my form. My form (CreditMemo) has a button that records the [Returned] ( Y
or N), [ReturnedComment] and [ReturnedDate] in the Sales table via an update
query for a dog that has been returned by the customer. This results in the
dog being added back to inventory (SalesScreen). However, in order that
salesmen who view the SalesScreen know whether a dog is available for sale
or not, the [Store] data from the first sale must be taken out of the
[Store] control. If the Store field is blank, the salemen then know that
the dog is available for sale. If the Store field is filled in, the dogs
has been sold but not invoice or shipped yet (then the DateSold field is
filled in at invoicing time). Please can anyone tell me why my code below,
is not taking the data out of the Store field and leaving it blank. When I
run the code, the Store field information is still in the control.

Joan

Dim stQueryName As String
Dim db As Database
Dim rs As DAO.Recordset
Dim strOldStore As String
Dim strNewStore As String

Me.Recalc
stQueryName = "qryReturned"
DoCmd.SetWarnings False
DoCmd.OpenQuery stQueryName, acViewNormal, acEdit
DoCmd.SetWarnings True

Set db = CurrentDb
Set rs = db.OpenRecordset("Dogs")
'Store original data
strOldStore = rs!Store
'Get new data ("") for record. Make [Store] empty on SalesScreen for
returned dog.
strNewStore = ""
With rs
.Edit
!Store = strNewStore
.Update
.Bookmark = .LastModified
End With
rs.Close
db.Close

End Sub

 

[Ken Snell]

I'm not sure that I'm completely following your description, but what I'm
understanding is that you've opened the SalesScreen form and are displaying
a value for Store field.

The user then is editing the value of Store on a second form, and then the
user returns to the SalesScreen form. And what you want is for the
SalesScreen form to display the new value of Store.

Is this right?

If yes, then the form on which the user is editing Store does need to tell
the SalesScreen form to requery in order to display the current value of
Store. Note that, when this is done, the SalesScreen form will not stay at
the current record, but will go to the first record. To "stay" at the
current record, you need to store the primary key value of that current
record, do the requery, and then move the recordset of the SalesScreen form
to that record using a FindFirst on that primary key value.

Post back if you need more info on how to do these things.

--
Ken Snell
<MS ACCESS MVP>

Ken,

I am updating a field that isn't in the record source of the Credit Memo
form. Well, it is but it isn't. The [Store] field is in both the Invoices
table which the Credit Memo affects and in the Dogs table which is the
[Store] field that I want to be blank for the particular dog that is
returned and issued a credit memo for. [Store] in the Invoices table
indicates the store that an invoice or credit memo is issued to. Store in
the dogs table is a field that is filled in by a salesman to indicate that a
dog has been sold but not yet invoiced ( at invoicing time I query for dogs
to put on the InvoiceStatement by whether or not this [Store] field in the
dogs table is filled in with a particular store) for all dogs records whose
DateSold field has not yet been entered.(This date is entered at Invoicing
time).

When wondering why the old store value is still being displayed on the form,
the form that I am referring to is the SalesScreen form. This form's record
source "EnterStoresQuery" is composed of many other queries to gather the
required data for the SalesScreen. Basically the [Store] field in this
query comes from the Dogs table. Hope this explains further what I am
trying to do?

Are you saying in your last paragraph that you suspect the problem is that I
have not requeried the SalesScreen form?

Joan

I am a bit confused, as you say you're updating a field that isn't in your
form's recordsource, but then you want to know why that value is still being
displayed on the form?

But, although you're updating the "Dogs" table (query?), you never requery
any object that is bound to that recordset (rs). I'm guessing that that is
the problem, but...as noted in my first question, I'm not sure I'm
understanding exactly what you're doing.


--
Ken Snell
<MS ACCESS MVP>

I have been trying to update a field in a table not in the record

source
of

my form. My form (CreditMemo) has a button that records the

[Returned]
( Y

or N), [ReturnedComment] and [ReturnedDate] in the Sales table via an update
query for a dog that has been returned by the customer. This results

in
the

dog being added back to inventory (SalesScreen). However, in order that
salesmen who view the SalesScreen know whether a dog is available for sale
or not, the [Store] data from the first sale must be taken out of the
[Store] control. If the Store field is blank, the salemen then know that
the dog is available for sale. If the Store field is filled in, the dogs
has been sold but not invoice or shipped yet (then the DateSold field is
filled in at invoicing time). Please can anyone tell me why my code below,
is not taking the data out of the Store field and leaving it blank.

When

I
run the code, the Store field information is still in the control.

Joan

Dim stQueryName As String
Dim db As Database
Dim rs As DAO.Recordset
Dim strOldStore As String
Dim strNewStore As String

Me.Recalc
stQueryName = "qryReturned"
DoCmd.SetWarnings False
DoCmd.OpenQuery stQueryName, acViewNormal, acEdit
DoCmd.SetWarnings True

Set db = CurrentDb
Set rs = db.OpenRecordset("Dogs")
'Store original data
strOldStore = rs!Store
'Get new data ("") for record. Make [Store] empty on SalesScreen for
returned dog.
strNewStore = ""
With rs
.Edit
!Store = strNewStore
.Update
.Bookmark = .LastModified
End With
rs.Close
db.Close

End Sub