Automatic Save when leaving a tab page?

[Dennis]

Hi,

Background:

I'm running Access XP on Windows 7.

I have a work order form that has 5 tabs (pages). The first two tabs allow
data entry onto the main work order (source) record. The last three tabs are
sub-forms that allow me to enter:
1. Work order details. One record for each task and comments describing
what I did.
2. Time sheet information
3. Expense information.


The first two pages update the work order record in the tblWorkOrder table.
The last three pages create a single record for each transaction in the
tblWorkOrderDet table. The tblWorkOrderDet is a child of the tblWorkOrder
table.

What I would like to do is have Access automatically save the current work
order record when I exist the first tab (page). However, I do not see an
Event for leaving a page / tab.

Question:
Can I automatically save the Work Order record if it is dirty when I leave
the WO tab / page?
If so, how do I do it?


Thanks,



Dennis

 

[Dirk Goldgar]

Hi,

Background:

I'm running Access XP on Windows 7.

I have a work order form that has 5 tabs (pages). The first two tabs
allow
data entry onto the main work order (source) record. The last three tabs
are
sub-forms that allow me to enter:
1. Work order details. One record for each task and comments describing
what I did.
2. Time sheet information
3. Expense information.


The first two pages update the work order record in the tblWorkOrder
table.
The last three pages create a single record for each transaction in the
tblWorkOrderDet table. The tblWorkOrderDet is a child of the tblWorkOrder
table.

What I would like to do is have Access automatically save the current work
order record when I exist the first tab (page). However, I do not see an
Event for leaving a page / tab.

Question:
Can I automatically save the Work Order record if it is dirty when I leave
the WO tab / page?
If so, how do I do it?

While this could be done using the tab control's Change event, it's not
clear to me that you really need to do it. Could you explain your
reasoning? If the last three pages contain subforms, the main form's record
(displayed on the first two pages) will automatically be saved when the
focus goes to any of the subforms. And if the user never visits those tabs,
the main form's record will be automatically saved when the user moves to a
new record or closes the form. What's your reason for wanting to save it
immediately upon changing ages from the first tab page?

 

[Dennis]

Dirk,

Your comment: While this could be done using the tab control's Change
event, it's not clear to me that you really need to do it. Could you explain
your reasoning?
If the last three pages contain subforms, the main form's record (displayed
on the first two pages) will automatically be saved when the focus goes to
any of the subforms.

Response: I was not aware of this. The main record is keyed using an Auto
Assigned Number. The auto assigned number is displayed in an unbound field
on the main form. That field is blank on a new record when I go to the
sub-form.

From the sub-form, I access the key field using
form!frmWorkOrder!cboWorkOrderNo. The key field is null when I'm in my
sub-form on a new record.

Let me ask, when is the key field assigned - after the main form's "On
Insert" event? If that is the case, I can update my key field on the form.



Your comment: And if the user never visits those tabs, the main form's
record will be automatically saved when the user moves to a new record or
closes the form. What's your reason for wanting to save it immediately upon
changing ages from the first tab page?

Response: I use the automatically assigned key in an SQL source statement
for one of the combo boxes on the sub-forms.


Dennis

 

[Mike Painter]

Dirk,

Your comment: While this could be done using the tab control's Change
event, it's not clear to me that you really need to do it. Could you
explain your reasoning?
If the last three pages contain subforms, the main form's record
(displayed on the first two pages) will automatically be saved when
the focus goes to any of the subforms.

Response: I was not aware of this. The main record is keyed using
an Auto Assigned Number. The auto assigned number is displayed in an
unbound field on the main form. That field is blank on a new record
when I go to the sub-form.

That's because you use an unbound field. You may have to update it in
several places if you want it to show.You have to manually do what Acces
will do for you automatically.

From the sub-form, I access the key field using
form!frmWorkOrder!cboWorkOrderNo. The key field is null when I'm in
my sub-form on a new record.

The unbound field you use may still be null. Access in fact has assigned
that value to the table the field is in

Let me ask, when is the key field assigned - after the main form's "On
Insert" event? If that is the case, I can update my key field on the
form.

Anytime you mover from the form the table(s) are updated.
Anytime you add a value to a field in a new record the value is assigned.
Place the actual field next to your unbound field to see this happen.

Your comment: And if the user never visits those tabs, the main
form's record will be automatically saved when the user moves to a
new record or closes the form. What's your reason for wanting to
save it immediately upon changing ages from the first tab page?

Response: I use the automatically assigned key in an SQL source
statement for one of the combo boxes on the sub-forms.

If you want teh busy work of an unbound field for some reason then place the
actual field on the table and make it invisible. Use that as your source.

Since subforms usually form a relationship with the main form based on the
key of the main form and Access takes care of this automatically why do you
need this in a combo box?

Be lazy and let the program handle the busy work.

 

[Dennis]

Mike,

The reason I used an unbound field is because all of the examples told me
to. it seems like I also tried to have the field bound and it caused
problems.

The field is the "key" field at the top of the form. I have a cascading
combo boxes at the top of the form. The first combo box allows the user to
select whcih customer they want to work with. The second combo box list all
of the work orders for the selected customer.

When the user choose a work order, the VBA code read that work order and let
the user work with it. Here is the code I have in the After Update Event on
my Work Order combo box:

Private Sub cboWorkOrder_AfterUpdate()
On Error GoTo Err_cboWorkOrder_AfterUpdate
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field
Dim rs As Recordset ' original
dim as object
Dim strFindFirst As String

' When user enters a Work Order, we have to jump to that record. If the
record does not
' exist, then we need to add it. The code below does this.

' Generate find string item number

strFindFirst = "([WorkOrder] = " & Me.cboWorkOrder & ")"
Set rs = Me.RecordsetClone
rs.FindFirst strFindFirst
If rs.NoMatch Then
DoCmd.GoToRecord , , acNewRec
Else
Me.Bookmark = rs.Bookmark ' Calls Form
Update and Form Current events!
End If
Call Cbo_Requery ' Requery
all of appropriate fields

Exit_cboWorkOrder_AfterUpdate:
Exit Sub

Err_cboWorkOrder_AfterUpdate:
Call BaseUtil.Dsp_Err_Msg(Err.Number, Err.Description, cpstrFormName & "
- cboWorkOrder_AfterUpdate")
Resume Exit_cboWorkOrder_AfterUpdate

End Sub


I have modified my form to have the bound field next to the unbound field so
I can see when it changes. I will also modify my code to use the bound field.


your comment: Be lazy and let the program handle the busy work.
Response: I agree 100%. I want Access to do as much work for me as I can.
I'm still clawing my way up the learning cliff. I've shed a lot of blood and
sweat on the climb up the cliff.

It was my understanding that if you are using combo boxes as "key" fields at
the top of you form, that they had to be unbound. Is this not correct?


Dennis

 

[Dirk Goldgar]

The main record is keyed using an Auto Assigned Number.

Do you mean an autonumber field (generated by the database engine), or are
you talking about some sort of number you generate yourself in code?

The auto assigned number is displayed in an unbound field
on the main form. That field is blank on a new record when I go to the
sub-form.

I'm a little confused by what you've written, so please pardon me if I take
a moment to clarify terminology. We need to make a distinction here between
*fields*, which are part of the form's recordset, and *controls*, which are
user-interface objects on the form. (Yes, I'm aware that Microsoft doesn't
always make this distinction clear in its help files.) A control may be
"bound" to a field by having the name of a field in its ControlSource
property, or it may be "calculated" by having an expression as its
ControlSource, or it may be "unbound", having a blank ControlSource.

If you have an autonumber field, then it will be blank (Null) when you first
go to a new record, but will be assigned a value on the instant you modify
that record in any way; for example, by editing the value of any bound
control.

A subform is normally linked to the main form by the Link Master Fields and
Link Child Fields properties of the subform control. The Link Master Fields
property gives the name(s) of one or more fields *or controls* on the main
form whose values should determine which records are shown on the subform.
The Link Child Fields property gives the name(s) of corresponding
field(s) -- *not* controls -- on the subform whose values should correspond
to those of the Link Master Fields.

A Link Master Field would not usually be an unbound control. Usually it
would be a bound control or a field, or in some cases a calculated field. I
can conceive of a reason to use an unbound control for a Link Master Field,
but it doesn't sound like you have any reason to be doing so here.

From the sub-form, I access the key field using
form!frmWorkOrder!cboWorkOrderNo.

1. This is incorrect syntax, so I hope you are not reporting it accurately.

2. If your subform is properly linked via Link Master/Child Fields, it's
unlikely that you have any reason to attempt to access the main form's key
field.

3. An unbound control *cannot* be a key field. At best, it may display the
value of the key field. Remember, a key field is a field in a table. If
your control is unbound, it is not essentially linked to the table at all.
Only your code provides any connection between the unbound control and the
table's key field. It sounds like here you are experencing a problem
related to this disconnection.

The key field is null when I'm in my sub-form on a new record.

Although Acces will always save the main form record, if it is dirty, when
you move from the main form to a subform, it is possible to move to a
subform when you are on a new, unmodified main record. In that case, the
main form's key (Link Master) field may be Null. If that happens, Access
will happily let you fill out the subform, and only raise an error when you
try to save it, telling you that the record can't be saved because the key
field that relates it to the parent record is blank. It sounds to me as if
this is at least partly the problem you are trying to avoid. If so, you can
prevent it by using one or more events of the main form to only enable the
subform if the main form's key field is not Null.

For example, if your subform is on a tab page, you can disable that page
whenever the main form's primary key field is Null:

'------ start of example code #1 ------
Private Sub Form_Current()

Me.PageWithSubform.Enabled = Not IsNull(Me.MainID)

End Sub

Private Sub Form_Dirty(Cancel As Integer)

Me.PageWithSubform.Enabled = Not IsNull(Me.MainID)

End Sub
'------ end of example code #1 ------

Or you can use the tab control's Change event to prevent the user from
moving to the tab page with the subform unless the main form's primary key
field has been filled out:

'------ start of example code #2 ------
Private Sub Form_Current()

' Force user to begin new records on first tab page.
If Me.NewRecord Then
Me.tabMyTab = 0
End If

End Sub

Private Sub tabMyTab_Change()

' Suppose that the 4th page has the subform.
If Me.tabMyTab = 3 Then
If IsNull(Me.MainID) Then
Me.tabMyTab = 0
End If
End If

End Sub
'------ end of example code #2 ------

Let me ask, when is the key field assigned - after the main form's "On
Insert" event? If that is the case, I can update my key field on the
form.

If this is an autonumber field, not one that you generate in code, then it
is created and assigned the instant you modify any bound control, or
otherwise dirty the form. If you do that via the user interface, not by
code, then the form's Dirty event will fire.

I use the automatically assigned key in an SQL source statement
for one of the combo boxes on the sub-forms.

Then probably you're going to need to requery that combo box in the
subform's Current event, or else in the main form's Current event *and* its
AfterInsert event.

 

[Mike Painter]

Mike,

The reason I used an unbound field is because all of the examples
told me
to. it seems like I also tried to have the field bound and it caused
problems.

The field is the "key" field at the top of the form. I have a
cascading combo boxes at the top of the form. The first combo box
allows the user to select whcih customer they want to work with.
The second combo box list all of the work orders for the selected
customer.

The first combobox should be used to move the form to the proper customer
record.
The wizzard will walk you through that.
The Actual key for the record should be in a bound field. Unless you add new
clients through another program it is needed.

Being lazy at this point I use a subform to display all the related classes
in my case, work orders in your case. More detail is available and I can
color code expired classes. (But I add classes in another form )
Also all I have to do is relate the master child fields and I am done.
Your combo box needs a query based on ClientID = Forms!YourFormName!ClientID
and work order details.

Your code below moves you to a particular work order but where is this
shwon? As a secnd subform or another form?

When the user choose a work order, the VBA code read that work order
and let the user work with it. Here is the code I have in the After
Update Event on my Work Order combo box:

Private Sub cboWorkOrder_AfterUpdate()
On Error GoTo Err_cboWorkOrder_AfterUpdate
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field
Dim rs As Recordset '
original
dim as object
Dim strFindFirst As String

' When user enters a Work Order, we have to jump to that record.
If the record does not
' exist, then we need to add it. The code below does this.

' Generate find string item number

strFindFirst = "([WorkOrder] = " & Me.cboWorkOrder & ")"
Set rs = Me.RecordsetClone
rs.FindFirst strFindFirst
If rs.NoMatch Then
DoCmd.GoToRecord , , acNewRec
Else
Me.Bookmark = rs.Bookmark '
Calls Form Update and Form Current events!
End If
Call Cbo_Requery '
Requery
all of appropriate fields

Exit_cboWorkOrder_AfterUpdate:
Exit Sub

Err_cboWorkOrder_AfterUpdate:
Call BaseUtil.Dsp_Err_Msg(Err.Number, Err.Description,
cpstrFormName & " - cboWorkOrder_AfterUpdate")
Resume Exit_cboWorkOrder_AfterUpdate

End Sub


I have modified my form to have the bound field next to the unbound
field so I can see when it changes. I will also modify my code to
use the bound field.


your comment: Be lazy and let the program handle the busy work.
Response: I agree 100%. I want Access to do as much work for me as
I can. I'm still clawing my way up the learning cliff. I've shed a
lot of blood and sweat on the climb up the cliff.

It was my understanding that if you are using combo boxes as "key"
fields at the top of you form, that they had to be unbound. Is this
not correct?

If you do that then you have no easy way to add a new record.
I put search fields in the header, mark then "Lookup last name",etc and have
the actual key field on the form. Not doing this leads to problems when you
want to add new records, etc.

 

[Dennis]

Mike, Dirk,

I guess I did not explain myself very well. Let me try again and I’ll try
to state it using the correct terminology.



Your code below moves you to a particular work order but where is this
shwon? As a secnd subform or another form?


But let me first answer’s Dirk’s questions using the correct terminology.

1. The main record (Work Order) is keyed using an autonumber field
(generated by the database engine) - WorkOrderId

2. I have a form called frmWorkOrder. It has many controls on it. The
first two are unbound (blank ControlSource) cascading combo box controls.

The first combo box (cboCust) displays all customers, from which they select
a single customer. When the user chooses a customer, Access sets the
customer number in this field.

The second combo boxes (cboWorkOrderNo) uses a SQL statement to select all
work orders for the selected customer. The user then chooses a single work
order. When the user chooses a work order, access places a value in this
field. The code I provided above then get that record and displays it on the
first and second tabs on the main form.

One issues I have here is when a new Work Order is added, I do not know how
to display the new value of the field WorkOrder (which is assigned by the
Access autonumber field). I have to figure out a way to display the newly
assigned number.

The third tab contains a Work Order Comments sub-form. The fourth tab
contains the time and expense sub-form. The fifth tab contains the Part
sub-form. Each of these sub-forms are connected per Dirk’s comments.


Dirk’s comment: A Link Master Field would not usually be an unbound
control. Usually it would be a bound control or a field, or in some cases a
calculated field. I can conceive of a reason to use an unbound control for a
Link Master Field, but it doesn't sound like you have any reason to be doing
so here.

Response: The frmWorkOrder can be called from other forms. If it is
called, the customer number and work order are passed to it using the
OpenArgs. The On Open even extracts the variable values and set the value of
cboCust and cboWorOrderNo.

When I try to set a value for a bound field, I receive an error message
telling me that I can not change the value.

So, how can I pass in a value and set that bound field to the passed in value?


To clarify an earlier incorrect syntax statement.
I also access he Work Order number in SQL Statement for a Row Source in
combo boxes on the Time and Expense and Part sub-forms by using WorkOrder =
form!frmWorkOrder!cboWorkOrderNo in their SQL stmt.


Dirk’s comment: An unbound control *cannot* be a key field. At best, it
may display the value of the key field. Remember, a key field is a field in a
table.

Response: This is correct, I misstated. Sorry. Once of the two combo box
fields on the main form, cboWorkOrderNo, contains the value for the key to
the tblWorkOrder table.


Dirk’s comment: it is possible to move to a subform when you are on a new,
unmodified main record. In that case, the main form's key (Link Master) field
may be Null. If that happens, Access will happily let you fill out the
subform, and only raise an error when you try to save it, telling you that
the record can't be saved because the key field that relates it to the parent
record is blank. It sounds to me as if this is at least partly the problem
you are trying to avoid. If so, you can prevent it by using one or more
events of the main form to only enable the subform if the main form's key
field is not Null.

My Response: Oh, ok. I did not think of that. I can do that.





My Comment: Let me ask, when is the key field assigned - after the main
form's "On Insert" event? If that is the case, I can update my key field on
the form.

Your Response: If this is an autonumber field, not one that you generate in
code, then it is created and assigned the instant you modify any bound
control, or otherwise dirty the form. If you do that via the user interface,
not by code, then the form's Dirty event will fire.

My Response: Ah, I did not know this. But this answered by question.



My Comment: I use the automatically assigned key in an SQL source statement
for one of the combo boxes on the sub-forms.

Dirk’s Comment: Then probably you're going to need to requery that combo
box in the subform's Current event, or else in the main form's Current event
*and* its AfterInsert event.

My Response: Yes, I do have to requery.

I hope this answers your questions and clarify the bad uses of terminology
(my apologies - I will try harder the next time to use the corrct
terminology.)

Thanks,

Dennis

 

[Dennis]

Dirk,

Here is problem with binding the combo box cboWorkOrderNo to the field
WorkOrder.

I choose the customer from the firts combo box (cboCust) and that works
fine. The second combo box (cboworkOrderNo) is limited to just the work
orders for the desired customer.

I then click on the cboWorkOrderNo combo box and the list of work orders are
displayed. When I click on the work oder line in the drop down box, nothing
happens. The drop dow box does not disappear, and the desired value is not
displayed in the box.

Normally, when you click on a line in the drop down box, the drop down box
disappears and the value appears in the box.

That is why I have the combo box cboWorkOrderNo unbound.

Dennis

 

[Dennis]

Dirk, Mike,

I also went back and looked at the subform links properties. I am linnking
the main form and sub-form by data fields names in the record set NOT by
control object names.

Dennis

 

[Dirk Goldgar]

I guess I did not explain myself very well. Let me try again and I’ll try
to state it using the correct terminology.

Thanks, you've done a good job. Your setup is a lot clearer now.

One issues I have here is when a new Work Order is added, I do not know
how
to display the new value of the field WorkOrder (which is assigned by the
Access autonumber field). I have to figure out a way to display the
newly
assigned number.

What I would do is *not* rely on the unbound controls to display the
customer number and work-order ID of the current record. I would have bound
text boxes on the form to display those values. I would clearly mark the
unbound combo boxes as being used for navigation *only*. In the form's
Current event I would set them to the values of the customer number and
work-order ID from the form's current record. Since you have cascading
combo boxes, that means requerying cboWorkOrderNo after setting cboCust.

The frmWorkOrder can be called from other forms. If it is
called, the customer number and work order are passed to it using the
OpenArgs. The On Open even extracts the variable values and set the value
of
cboCust and cboWorOrderNo.

When I try to set a value for a bound field, I receive an error message
telling me that I can not change the value.

So, how can I pass in a value and set that bound field to the passed in
value?

You can't change the value of a bound control in the Open event, because the
form's recordset hasn't yet been loaded. What I think you want to do is use
the form's Load event to navigate to the indicated record. You don't have
to set the values of the combo boxes for this; you can use very similar
code to what you have in the AfterUpdate event of cboWorkOrderNo.

To clarify an earlier incorrect syntax statement.
I also access he Work Order number in SQL Statement for a Row Source in
combo boxes on the Time and Expense and Part sub-forms by using WorkOrder
=
form!frmWorkOrder!cboWorkOrderNo in their SQL stmt.

My point is that "form!frmWorkOrder!cboWorkOrderNo" is incorrect. It should
be "Forms!frmWorkOrder!cboWorkOrderNo". That is, there must be an "S" after
"Form".

However, these combo boxes on the subforms should refer not to the unbound
combo boxes, but to the bound text boxes.

 

[Dirk Goldgar]

Dirk,

Here is problem with binding the combo box cboWorkOrderNo to the field
WorkOrder.

I choose the customer from the firts combo box (cboCust) and that works
fine. The second combo box (cboworkOrderNo) is limited to just the work
orders for the desired customer.

I then click on the cboWorkOrderNo combo box and the list of work orders
are
displayed. When I click on the work oder line in the drop down box,
nothing
happens. The drop dow box does not disappear, and the desired value is
not
displayed in the box.

Normally, when you click on a line in the drop down box, the drop down box
disappears and the value appears in the box.

That is why I have the combo box cboWorkOrderNo unbound.

I never suggested that you should bind the combo box to the field -- not if
you're using the control for navigation. While it is possible to use the
same control for both navigation and data-binding, it's complicated to
manage.

 

[Dirk Goldgar]

Dirk, Mike,

I also went back and looked at the subform links properties. I am
linnking
the main form and sub-form by data fields names in the record set NOT by
control object names.

Good.

 

[Dennis]

Dirk,

I got everything working as per your instructions.

Your comment: What I would do is *not* rely on the unbound controls to
display the customer number and work-order ID of the current record. I would
have bound text boxes on the form to display those values.

My Response: Done.

Your comment: Since you have cascading combo boxes, that means requerying
cboWorkOrderNo after setting cboCust.

My Response - Already doing this.


Your comment: You can't change the value of a bound control in the Open
event, because the form's recordset hasn't yet been loaded. What I think you
want to do is use the form's Load event to navigate to the indicated record.
You don't have to set the values of the combo boxes for this; you can use
very similar code to what you have in the AfterUpdate event of cboWorkOrderNo.

My Response: Done - works fine.


Your comment: My point is that "form!frmWorkOrder!cboWorkOrderNo" is
incorrect. It should be "Forms!frmWorkOrder!cboWorkOrderNo". That is, there
must be an "S" after "Form".

My response: I looked again, and you are correct. It is forms. in my SQL
statement.


Your comment: However, these combo boxes on the subforms should refer not to
the unbound combo boxes, but to the bound text boxes.

My response: Done

Dennis

 

[Dirk Goldgar]

Dirk,

I got everything working as per your instructions.

It's all working now? Great! Good work.