Is it possible to show both "Yes" and "No" answers from a "yes/no" checkbox?

[Colin Foster]

Hi All,
I have a subform where one of the fields is a yes/no checkbox to indicate
whether or not the record is "active": Active returns -1, inactive 0
On the main form, I need to be able to see either the "active" records, or
the "inactive" records or all records.
So, what I've done is to set up an option group to allow the selection...
one button selects the "Active" and one the "inactive"... however, that's
where the problem starts, I can't get the third button to show all items.
Basically, the option group result is used by the query that is used as the
source for the subform. I've tried using a statement within the criteria
that says...

IIf([forms]![customer form new]![selectcontact]=1,<1,[forms]![customer form
new]![selectcontact]) but this then shows all active records, rather than
all

If there is a way that I can achieve this, I'd be really interested to know!

Regards
Colin Foster

 

[Ken Snell [MVP]]

You're using an option group on the form to select which to show. Let's
assume that the option group has a value of 1 when you want the "active"
ones to show, a value of 2 when you want the "inactive" ones to show, and a
value of 3 when you want "all" to show.

The crtierion expression in the query would be something like this:

([forms]![customer form new]![selectcontact] - 2) Or ([forms]![customer form
new]![selectcontact] = 3)

 

[Colin Foster]

Ken,
You are a star!!

This works like a dream

Now I've got that working, I wonder if you could help with the next stage...

When I select the "Active" record, I need the details to be transferred into
th emain form, so I've put the following code behind the OnClick property of
the option group...

Private Sub SelectContact_Click()
Forms![customer form new]![FrmAltContacts].Requery

Me!Salutation = Forms![customer form
new]![FrmAltContacts].Form![Salutation]
Me!ContactFirstName = Forms![customer form
new]![FrmAltContacts].Form![ContactFirstName]
Me!ContactLastName = Forms![customer form
new]![FrmAltContacts].Form![ContactLastName]
Me!ContactTitle = Forms![customer form
new]![FrmAltContacts].Form![ContactTitle]
Forms![customer form new]![FrmAltContacts].Requery

End Sub

Sometimes it works & the information is transferred into th emain form (the
"me!" part of the above code) and sometimes it doesn't. I tried to attach it
to the "got focus" property of the "Active" action button, but this didn't
help either. I suspect that the problem is that in the code above, I need to
do something else to "tell" the main form to pick up the "Active" record
only. Any idea what this might be?

Once again, thanks for your help so far.

Regards
Colin

You're using an option group on the form to select which to show. Let's
assume that the option group has a value of 1 when you want the "active"
ones to show, a value of 2 when you want the "inactive" ones to show, and
a value of 3 when you want "all" to show.

The crtierion expression in the query would be something like this:

([forms]![customer form new]![selectcontact] - 2) Or ([forms]![customer
form new]![selectcontact] = 3)

--

Ken Snell
<MS ACCESS MVP>

Hi All,
I have a subform where one of the fields is a yes/no checkbox to indicate
whether or not the record is "active": Active returns -1, inactive 0
On the main form, I need to be able to see either the "active" records,
or the "inactive" records or all records.
So, what I've done is to set up an option group to allow the selection...
one button selects the "Active" and one the "inactive"... however, that's
where the problem starts, I can't get the third button to show all items.
Basically, the option group result is used by the query that is used as
the source for the subform. I've tried using a statement within the
criteria that says...

IIf([forms]![customer form new]![selectcontact]=1,<1,[forms]![customer
form new]![selectcontact]) but this then shows all active records, rather
than all

If there is a way that I can achieve this, I'd be really interested to
know!

Regards
Colin Foster

 

[Ken Snell [MVP]]

Use the AfterUpdate event of the option group, not the Click event, to run
the code.

This option group is in the main part of the form? Or in the subform? If
it's in the subform, then you cannot use the Me object to reference the
control on the main form. You'd need
Me.Parent!Salutation = Forms![customer form
new]![FrmAltContacts].Form![Salutation]

And, I would rewrite the above this way:
Me.Parent!Salutation = Me.[Salutation]

--

Ken Snell
<MS ACCESS MVP>

Ken,
You are a star!!

This works like a dream

Now I've got that working, I wonder if you could help with the next
stage...

When I select the "Active" record, I need the details to be transferred
into th emain form, so I've put the following code behind the OnClick
property of the option group...

Private Sub SelectContact_Click()
Forms![customer form new]![FrmAltContacts].Requery

Me!Salutation = Forms![customer form
new]![FrmAltContacts].Form![Salutation]
Me!ContactFirstName = Forms![customer form
new]![FrmAltContacts].Form![ContactFirstName]
Me!ContactLastName = Forms![customer form
new]![FrmAltContacts].Form![ContactLastName]
Me!ContactTitle = Forms![customer form
new]![FrmAltContacts].Form![ContactTitle]
Forms![customer form new]![FrmAltContacts].Requery

End Sub

Sometimes it works & the information is transferred into th emain form
(the "me!" part of the above code) and sometimes it doesn't. I tried to
attach it to the "got focus" property of the "Active" action button, but
this didn't help either. I suspect that the problem is that in the code
above, I need to do something else to "tell" the main form to pick up the
"Active" record only. Any idea what this might be?

Once again, thanks for your help so far.

Regards
Colin

You're using an option group on the form to select which to show. Let's
assume that the option group has a value of 1 when you want the "active"
ones to show, a value of 2 when you want the "inactive" ones to show, and
a value of 3 when you want "all" to show.

The crtierion expression in the query would be something like this:

([forms]![customer form new]![selectcontact] - 2) Or ([forms]![customer
form new]![selectcontact] = 3)

--

Ken Snell
<MS ACCESS MVP>

Hi All,
I have a subform where one of the fields is a yes/no checkbox to
indicate whether or not the record is "active": Active returns -1,
inactive 0
On the main form, I need to be able to see either the "active" records,
or the "inactive" records or all records.
So, what I've done is to set up an option group to allow the
selection... one button selects the "Active" and one the "inactive"...
however, that's where the problem starts, I can't get the third button
to show all items.
Basically, the option group result is used by the query that is used as
the source for the subform. I've tried using a statement within the
criteria that says...

IIf([forms]![customer form new]![selectcontact]=1,<1,[forms]![customer
form new]![selectcontact]) but this then shows all active records,
rather than all

If there is a way that I can achieve this, I'd be really interested to
know!

Regards
Colin Foster

 

[Colin Foster]

Hi Ken,
Done that, and again, sometimes it works & sometimes it doesnt :-(
The option group is on the main form.

I'm going to finish watching "Comic Relief" now (& then to bed!! - here in
the UK it's 22:50), but I'll check back tomorrow to see if you've had any
other ideas, or have spotted what I'm doing wrong!

Regards
Colin

Use the AfterUpdate event of the option group, not the Click event, to run
the code.

This option group is in the main part of the form? Or in the subform? If
it's in the subform, then you cannot use the Me object to reference the
control on the main form. You'd need
Me.Parent!Salutation = Forms![customer form
new]![FrmAltContacts].Form![Salutation]

And, I would rewrite the above this way:
Me.Parent!Salutation = Me.[Salutation]

--

Ken Snell
<MS ACCESS MVP>

Ken,
You are a star!!

This works like a dream

Now I've got that working, I wonder if you could help with the next
stage...

When I select the "Active" record, I need the details to be transferred
into th emain form, so I've put the following code behind the OnClick
property of the option group...

Private Sub SelectContact_Click()
Forms![customer form new]![FrmAltContacts].Requery

Me!Salutation = Forms![customer form
new]![FrmAltContacts].Form![Salutation]
Me!ContactFirstName = Forms![customer form
new]![FrmAltContacts].Form![ContactFirstName]
Me!ContactLastName = Forms![customer form
new]![FrmAltContacts].Form![ContactLastName]
Me!ContactTitle = Forms![customer form
new]![FrmAltContacts].Form![ContactTitle]
Forms![customer form new]![FrmAltContacts].Requery

End Sub

Sometimes it works & the information is transferred into th emain form
(the "me!" part of the above code) and sometimes it doesn't. I tried to
attach it to the "got focus" property of the "Active" action button, but
this didn't help either. I suspect that the problem is that in the code
above, I need to do something else to "tell" the main form to pick up the
"Active" record only. Any idea what this might be?

Once again, thanks for your help so far.

Regards
Colin

You're using an option group on the form to select which to show. Let's
assume that the option group has a value of 1 when you want the "active"
ones to show, a value of 2 when you want the "inactive" ones to show,
and a value of 3 when you want "all" to show.

The crtierion expression in the query would be something like this:

([forms]![customer form new]![selectcontact] - 2) Or ([forms]![customer
form new]![selectcontact] = 3)

--

Ken Snell
<MS ACCESS MVP>


Hi All,
I have a subform where one of the fields is a yes/no checkbox to
indicate whether or not the record is "active": Active returns -1,
inactive 0
On the main form, I need to be able to see either the "active" records,
or the "inactive" records or all records.
So, what I've done is to set up an option group to allow the
selection... one button selects the "Active" and one the "inactive"...
however, that's where the problem starts, I can't get the third button
to show all items.
Basically, the option group result is used by the query that is used as
the source for the subform. I've tried using a statement within the
criteria that says...

IIf([forms]![customer form new]![selectcontact]=1,<1,[forms]![customer
form new]![selectcontact]) but this then shows all active records,
rather than all

If there is a way that I can achieve this, I'd be really interested to
know!

Regards
Colin Foster

 

[Ken Snell [MVP]]

I'm guessing that the times that don't work are when you don't actually
change the choice in the option group, even though you click on the selected
option button. If yes, the AfterUpdate event isn't occurring unless you
change the value of the option group.

Can you note carefully the actions that you take when it works and when it
doesn't and let's see if it's because an event isn't happening in some
situations? Alternatively, you might consider using the form's BeforeUpdate
event to run the code, as that will fire any time you're ready to update a
record with changes; but also note that it won't "fire" if no edits are made
to the record (edit includes the creation of a new record).
--

Ken Snell
<MS ACCESS MVP>

Hi Ken,
Done that, and again, sometimes it works & sometimes it doesnt :-(
The option group is on the main form.

I'm going to finish watching "Comic Relief" now (& then to bed!! - here in
the UK it's 22:50), but I'll check back tomorrow to see if you've had any
other ideas, or have spotted what I'm doing wrong!

Regards
Colin

Use the AfterUpdate event of the option group, not the Click event, to
run the code.

This option group is in the main part of the form? Or in the subform? If
it's in the subform, then you cannot use the Me object to reference the
control on the main form. You'd need
Me.Parent!Salutation = Forms![customer form
new]![FrmAltContacts].Form![Salutation]

And, I would rewrite the above this way:
Me.Parent!Salutation = Me.[Salutation]

--

Ken Snell
<MS ACCESS MVP>

Ken,
You are a star!!

This works like a dream

Now I've got that working, I wonder if you could help with the next
stage...

When I select the "Active" record, I need the details to be transferred
into th emain form, so I've put the following code behind the OnClick
property of the option group...

Private Sub SelectContact_Click()
Forms![customer form new]![FrmAltContacts].Requery

Me!Salutation = Forms![customer form
new]![FrmAltContacts].Form![Salutation]
Me!ContactFirstName = Forms![customer form
new]![FrmAltContacts].Form![ContactFirstName]
Me!ContactLastName = Forms![customer form
new]![FrmAltContacts].Form![ContactLastName]
Me!ContactTitle = Forms![customer form
new]![FrmAltContacts].Form![ContactTitle]
Forms![customer form new]![FrmAltContacts].Requery

End Sub

Sometimes it works & the information is transferred into th emain form
(the "me!" part of the above code) and sometimes it doesn't. I tried to
attach it to the "got focus" property of the "Active" action button, but
this didn't help either. I suspect that the problem is that in the code
above, I need to do something else to "tell" the main form to pick up
the "Active" record only. Any idea what this might be?

Once again, thanks for your help so far.

Regards
Colin


You're using an option group on the form to select which to show. Let's
assume that the option group has a value of 1 when you want the
"active" ones to show, a value of 2 when you want the "inactive" ones
to show, and a value of 3 when you want "all" to show.

The crtierion expression in the query would be something like this:

([forms]![customer form new]![selectcontact] - 2) Or ([forms]![customer
form new]![selectcontact] = 3)

--

Ken Snell
<MS ACCESS MVP>


Hi All,
I have a subform where one of the fields is a yes/no checkbox to
indicate whether or not the record is "active": Active returns -1,
inactive 0
On the main form, I need to be able to see either the "active"
records, or the "inactive" records or all records.
So, what I've done is to set up an option group to allow the
selection... one button selects the "Active" and one the "inactive"...
however, that's where the problem starts, I can't get the third button
to show all items.
Basically, the option group result is used by the query that is used
as the source for the subform. I've tried using a statement within the
criteria that says...

IIf([forms]![customer form new]![selectcontact]=1,<1,[forms]![customer
form new]![selectcontact]) but this then shows all active records,
rather than all

If there is a way that I can achieve this, I'd be really interested to
know!

Regards
Colin Foster

 

[Colin Foster]

Hi Ken...

Spot on. I'll take you through the process thatI've just done...

1. Opened the database & went to the form which has 3 "contacts"... Colin
Foster (active check box selected), Richard Foster & Ken Snell (both
inactive)
2. Clicked the "Select Active" option button & "Colin Foster" appeared on
the main form (correct result!)
3. Clicked the "Select All" option button & changed the "Active" record to
Ken Snell (by unchecking "Colin Foster" & checking "Ken Snell")
4. Clicked the "Select Active" option button & nothing appeared on the main
form, i.e. the "Colin Foster" also disappeared (incorrect result!!)
5. Interestingly, Clicked the "Select InActive" option button & "Colin
Foster" appeared on the main form (interesting result!)
6. Closed the form down ("Ken Snell" still shown as active), reopened
selected "Active" option button & "Ken Snell" appeared.

So, I guess that what I now have to figure out is how to fire the "after
Update" event more effectively.

Hmmm... just before I decided to hit the "Send" button to forward this
response to you, I had a further "play"... by accident, after changing the
"active" person, I clicked on the "active" header on th esubform (it's
displayed as a datasheet) and, lo... it worked. Flushed with this success, I
tried it again and again and it still worked! Oh joy!

Now, the question is why (and do I care as long as it works... well I
suppose that I should!) and how can I do this programatically?

Interesting.... very interesting

Regards
Colin

I'm guessing that the times that don't work are when you don't actually
change the choice in the option group, even though you click on the
selected option button. If yes, the AfterUpdate event isn't occurring
unless you change the value of the option group.

Can you note carefully the actions that you take when it works and when it
doesn't and let's see if it's because an event isn't happening in some
situations? Alternatively, you might consider using the form's
BeforeUpdate event to run the code, as that will fire any time you're
ready to update a record with changes; but also note that it won't "fire"
if no edits are made to the record (edit includes the creation of a new
record).
--

Ken Snell
<MS ACCESS MVP>

Hi Ken,
Done that, and again, sometimes it works & sometimes it doesnt :-(
The option group is on the main form.

I'm going to finish watching "Comic Relief" now (& then to bed!! - here
in the UK it's 22:50), but I'll check back tomorrow to see if you've had
any other ideas, or have spotted what I'm doing wrong!

Regards
Colin

Use the AfterUpdate event of the option group, not the Click event, to
run the code.

This option group is in the main part of the form? Or in the subform? If
it's in the subform, then you cannot use the Me object to reference the
control on the main form. You'd need
Me.Parent!Salutation = Forms![customer form
new]![FrmAltContacts].Form![Salutation]

And, I would rewrite the above this way:
Me.Parent!Salutation = Me.[Salutation]

--

Ken Snell
<MS ACCESS MVP>

Ken,
You are a star!!

This works like a dream

Now I've got that working, I wonder if you could help with the next
stage...

When I select the "Active" record, I need the details to be transferred
into th emain form, so I've put the following code behind the OnClick
property of the option group...

Private Sub SelectContact_Click()
Forms![customer form new]![FrmAltContacts].Requery

Me!Salutation = Forms![customer form
new]![FrmAltContacts].Form![Salutation]
Me!ContactFirstName = Forms![customer form
new]![FrmAltContacts].Form![ContactFirstName]
Me!ContactLastName = Forms![customer form
new]![FrmAltContacts].Form![ContactLastName]
Me!ContactTitle = Forms![customer form
new]![FrmAltContacts].Form![ContactTitle]
Forms![customer form new]![FrmAltContacts].Requery

End Sub

Sometimes it works & the information is transferred into th emain form
(the "me!" part of the above code) and sometimes it doesn't. I tried to
attach it to the "got focus" property of the "Active" action button,
but this didn't help either. I suspect that the problem is that in the
code above, I need to do something else to "tell" the main form to pick
up the "Active" record only. Any idea what this might be?

Once again, thanks for your help so far.

Regards
Colin


You're using an option group on the form to select which to show.
Let's assume that the option group has a value of 1 when you want the
"active" ones to show, a value of 2 when you want the "inactive" ones
to show, and a value of 3 when you want "all" to show.

The crtierion expression in the query would be something like this:

([forms]![customer form new]![selectcontact] - 2) Or
([forms]![customer form new]![selectcontact] = 3)

--

Ken Snell
<MS ACCESS MVP>


Hi All,
I have a subform where one of the fields is a yes/no checkbox to
indicate whether or not the record is "active": Active returns -1,
inactive 0
On the main form, I need to be able to see either the "active"
records, or the "inactive" records or all records.
So, what I've done is to set up an option group to allow the
selection... one button selects the "Active" and one the
"inactive"... however, that's where the problem starts, I can't get
the third button to show all items.
Basically, the option group result is used by the query that is used
as the source for the subform. I've tried using a statement within
the criteria that says...

IIf([forms]![customer form
new]![selectcontact]=1,<1,[forms]![customer form
new]![selectcontact]) but this then shows all active records, rather
than all

If there is a way that I can achieve this, I'd be really interested
to know!

Regards
Colin Foster

 

[Ken Snell [MVP]]

Ahh... the answer to your question is that you're not "saving" the edited
record in the subform before you go to the option group. In a form/subform
setup, the "current" focus is on just one control in the main form -- when
you're in the subform, the subform control (the control that holds the
subform object) has focus for the main form, but there also is a focus
within the subform. When you change the data in a record in the subform by
clicking the checkbox, and then immediately click on the option group, the
subform record that you just edited is still dirty (it's still being edited)
and hasn't been saved to the underlying record source yet. Thus, the main
form does not "see" the changed data and thus the Ken Snell record does not
show in the subform because your change from Inactive to Active hasn't been
"saved" yet.

When you click on the header in the subform, you're moving focus from the
record that you just edited, and thus the subform saves the changes you
made. And thus you see the change when you click on the option group
setting.

With your form/subform setup, you may be able to use the Exit event of the
subform control (the control (on the main form) that holds the subform) to
save the current record in the subform, which will do what you seek. But
note that this code will run whenever you click from the subform to the main
form, so it is not the correct thing to do with all form/subform setups.

Assuming that FrmAltContacts is the name of the subform control, put code
like this on the Exit event of the subform control:

Private Sub FrmAltContacts_Exit((Cancel As Integer)
On Error Resume Next
Me![FrmAltContacts].Form.Dirty = False
DoEvents
Me![FrmAltContacts].Form.Requery
End Sub

That should cause the current record in the subform to be saved, whether
you've edited that record or not, and then will requery the subform to
reflect any changes you made to those records. And then your option group
setting should work correctly.


--

Ken Snell
<MS ACCESS MVP>

Hi Ken...

Spot on. I'll take you through the process thatI've just done...

1. Opened the database & went to the form which has 3 "contacts"... Colin
Foster (active check box selected), Richard Foster & Ken Snell (both
inactive)
2. Clicked the "Select Active" option button & "Colin Foster" appeared on
the main form (correct result!)
3. Clicked the "Select All" option button & changed the "Active" record to
Ken Snell (by unchecking "Colin Foster" & checking "Ken Snell")
4. Clicked the "Select Active" option button & nothing appeared on the
main form, i.e. the "Colin Foster" also disappeared (incorrect result!!)
5. Interestingly, Clicked the "Select InActive" option button & "Colin
Foster" appeared on the main form (interesting result!)
6. Closed the form down ("Ken Snell" still shown as active), reopened
selected "Active" option button & "Ken Snell" appeared.

So, I guess that what I now have to figure out is how to fire the "after
Update" event more effectively.

Hmmm... just before I decided to hit the "Send" button to forward this
response to you, I had a further "play"... by accident, after changing the
"active" person, I clicked on the "active" header on th esubform (it's
displayed as a datasheet) and, lo... it worked. Flushed with this success,
I tried it again and again and it still worked! Oh joy!

Now, the question is why (and do I care as long as it works... well I
suppose that I should!) and how can I do this programatically?

Interesting.... very interesting

Regards
Colin

I'm guessing that the times that don't work are when you don't actually
change the choice in the option group, even though you click on the
selected option button. If yes, the AfterUpdate event isn't occurring
unless you change the value of the option group.

Can you note carefully the actions that you take when it works and when
it doesn't and let's see if it's because an event isn't happening in some
situations? Alternatively, you might consider using the form's
BeforeUpdate event to run the code, as that will fire any time you're
ready to update a record with changes; but also note that it won't "fire"
if no edits are made to the record (edit includes the creation of a new
record).
--

Ken Snell
<MS ACCESS MVP>

Hi Ken,
Done that, and again, sometimes it works & sometimes it doesnt :-(
The option group is on the main form.

I'm going to finish watching "Comic Relief" now (& then to bed!! - here
in the UK it's 22:50), but I'll check back tomorrow to see if you've had
any other ideas, or have spotted what I'm doing wrong!

Regards
Colin

Use the AfterUpdate event of the option group, not the Click event, to
run the code.

This option group is in the main part of the form? Or in the subform?
If it's in the subform, then you cannot use the Me object to reference
the control on the main form. You'd need
Me.Parent!Salutation = Forms![customer form
new]![FrmAltContacts].Form![Salutation]

And, I would rewrite the above this way:
Me.Parent!Salutation = Me.[Salutation]

--

Ken Snell
<MS ACCESS MVP>

Ken,
You are a star!!

This works like a dream

Now I've got that working, I wonder if you could help with the next
stage...

When I select the "Active" record, I need the details to be
transferred into th emain form, so I've put the following code behind
the OnClick property of the option group...

Private Sub SelectContact_Click()
Forms![customer form new]![FrmAltContacts].Requery

Me!Salutation = Forms![customer form
new]![FrmAltContacts].Form![Salutation]
Me!ContactFirstName = Forms![customer form
new]![FrmAltContacts].Form![ContactFirstName]
Me!ContactLastName = Forms![customer form
new]![FrmAltContacts].Form![ContactLastName]
Me!ContactTitle = Forms![customer form
new]![FrmAltContacts].Form![ContactTitle]
Forms![customer form new]![FrmAltContacts].Requery

End Sub

Sometimes it works & the information is transferred into th emain form
(the "me!" part of the above code) and sometimes it doesn't. I tried
to attach it to the "got focus" property of the "Active" action
button, but this didn't help either. I suspect that the problem is
that in the code above, I need to do something else to "tell" the main
form to pick up the "Active" record only. Any idea what this might be?

Once again, thanks for your help so far.

Regards
Colin


You're using an option group on the form to select which to show.
Let's assume that the option group has a value of 1 when you want the
"active" ones to show, a value of 2 when you want the "inactive" ones
to show, and a value of 3 when you want "all" to show.

The crtierion expression in the query would be something like this:

([forms]![customer form new]![selectcontact] - 2) Or
([forms]![customer form new]![selectcontact] = 3)

--

Ken Snell
<MS ACCESS MVP>


Hi All,
I have a subform where one of the fields is a yes/no checkbox to
indicate whether or not the record is "active": Active returns -1,
inactive 0
On the main form, I need to be able to see either the "active"
records, or the "inactive" records or all records.
So, what I've done is to set up an option group to allow the
selection... one button selects the "Active" and one the
"inactive"... however, that's where the problem starts, I can't get
the third button to show all items.
Basically, the option group result is used by the query that is used
as the source for the subform. I've tried using a statement within
the criteria that says...

IIf([forms]![customer form
new]![selectcontact]=1,<1,[forms]![customer form
new]![selectcontact]) but this then shows all active records, rather
than all

If there is a way that I can achieve this, I'd be really interested
to know!

Regards
Colin Foster

 

[Colin Foster]

Ken,
Once again thanks for your detailed assitance - not just in working out the
code, but also the explanations that go with it; it is greatly appreciated

In fact this solution is a help to a similar problem that I had on another
database (once again someone had asked for changes that would have been
better resolved in the original build!!)

Having solved this problem (with your help, guidance and code - in fact I
seemed to do very little!), I realise that I had another where the name of
the contact for the quote kept changing each time that I changed the
contact. I then realised that the reason for this is that I was "just"
holding the name of the contact within the form as far as the quote was
concerned, so each time that the contact changed (even for a new quote), it
changed within the quote, too. So what I've now done is to asdd these
details into the quote table and set up a check box which, if checked, pulls
through the latest contact details, if not allows the user to manually
input.

So, I think that's it, now (except that I've got to pull it all together in
a report!), so once again thanks enormously for your help, support &
guidance.

Regards
Colin Foster

Ahh... the answer to your question is that you're not "saving" the edited
record in the subform before you go to the option group. In a form/subform
setup, the "current" focus is on just one control in the main form -- when
you're in the subform, the subform control (the control that holds the
subform object) has focus for the main form, but there also is a focus
within the subform. When you change the data in a record in the subform by
clicking the checkbox, and then immediately click on the option group, the
subform record that you just edited is still dirty (it's still being
edited) and hasn't been saved to the underlying record source yet. Thus,
the main form does not "see" the changed data and thus the Ken Snell
record does not show in the subform because your change from Inactive to
Active hasn't been "saved" yet.

When you click on the header in the subform, you're moving focus from the
record that you just edited, and thus the subform saves the changes you
made. And thus you see the change when you click on the option group
setting.

With your form/subform setup, you may be able to use the Exit event of the
subform control (the control (on the main form) that holds the subform) to
save the current record in the subform, which will do what you seek. But
note that this code will run whenever you click from the subform to the
main form, so it is not the correct thing to do with all form/subform
setups.

Assuming that FrmAltContacts is the name of the subform control, put code
like this on the Exit event of the subform control:

Private Sub FrmAltContacts_Exit((Cancel As Integer)
On Error Resume Next
Me![FrmAltContacts].Form.Dirty = False
DoEvents
Me![FrmAltContacts].Form.Requery
End Sub

That should cause the current record in the subform to be saved, whether
you've edited that record or not, and then will requery the subform to
reflect any changes you made to those records. And then your option group
setting should work correctly.


--

Ken Snell
<MS ACCESS MVP>

Hi Ken...

Spot on. I'll take you through the process thatI've just done...

1. Opened the database & went to the form which has 3 "contacts"... Colin
Foster (active check box selected), Richard Foster & Ken Snell (both
inactive)
2. Clicked the "Select Active" option button & "Colin Foster" appeared on
the main form (correct result!)
3. Clicked the "Select All" option button & changed the "Active" record
to Ken Snell (by unchecking "Colin Foster" & checking "Ken Snell")
4. Clicked the "Select Active" option button & nothing appeared on the
main form, i.e. the "Colin Foster" also disappeared (incorrect result!!)
5. Interestingly, Clicked the "Select InActive" option button & "Colin
Foster" appeared on the main form (interesting result!)
6. Closed the form down ("Ken Snell" still shown as active), reopened
selected "Active" option button & "Ken Snell" appeared.

So, I guess that what I now have to figure out is how to fire the "after
Update" event more effectively.

Hmmm... just before I decided to hit the "Send" button to forward this
response to you, I had a further "play"... by accident, after changing
the "active" person, I clicked on the "active" header on th esubform
(it's displayed as a datasheet) and, lo... it worked. Flushed with this
success, I tried it again and again and it still worked! Oh joy!

Now, the question is why (and do I care as long as it works... well I
suppose that I should!) and how can I do this programatically?

Interesting.... very interesting

Regards
Colin

I'm guessing that the times that don't work are when you don't actually
change the choice in the option group, even though you click on the
selected option button. If yes, the AfterUpdate event isn't occurring
unless you change the value of the option group.

Can you note carefully the actions that you take when it works and when
it doesn't and let's see if it's because an event isn't happening in
some situations? Alternatively, you might consider using the form's
BeforeUpdate event to run the code, as that will fire any time you're
ready to update a record with changes; but also note that it won't
"fire" if no edits are made to the record (edit includes the creation of
a new record).
--

Ken Snell
<MS ACCESS MVP>



Hi Ken,
Done that, and again, sometimes it works & sometimes it doesnt :-(
The option group is on the main form.

I'm going to finish watching "Comic Relief" now (& then to bed!! - here
in the UK it's 22:50), but I'll check back tomorrow to see if you've
had any other ideas, or have spotted what I'm doing wrong!

Regards
Colin

Use the AfterUpdate event of the option group, not the Click event, to
run the code.

This option group is in the main part of the form? Or in the subform?
If it's in the subform, then you cannot use the Me object to reference
the control on the main form. You'd need
Me.Parent!Salutation = Forms![customer form
new]![FrmAltContacts].Form![Salutation]

And, I would rewrite the above this way:
Me.Parent!Salutation = Me.[Salutation]

--

Ken Snell
<MS ACCESS MVP>

Ken,
You are a star!!

This works like a dream

Now I've got that working, I wonder if you could help with the next
stage...

When I select the "Active" record, I need the details to be
transferred into th emain form, so I've put the following code behind
the OnClick property of the option group...

Private Sub SelectContact_Click()
Forms![customer form new]![FrmAltContacts].Requery

Me!Salutation = Forms![customer form
new]![FrmAltContacts].Form![Salutation]
Me!ContactFirstName = Forms![customer form
new]![FrmAltContacts].Form![ContactFirstName]
Me!ContactLastName = Forms![customer form
new]![FrmAltContacts].Form![ContactLastName]
Me!ContactTitle = Forms![customer form
new]![FrmAltContacts].Form![ContactTitle]
Forms![customer form new]![FrmAltContacts].Requery

End Sub

Sometimes it works & the information is transferred into th emain
form (the "me!" part of the above code) and sometimes it doesn't. I
tried to attach it to the "got focus" property of the "Active" action
button, but this didn't help either. I suspect that the problem is
that in the code above, I need to do something else to "tell" the
main form to pick up the "Active" record only. Any idea what this
might be?

Once again, thanks for your help so far.

Regards
Colin


You're using an option group on the form to select which to show.
Let's assume that the option group has a value of 1 when you want
the "active" ones to show, a value of 2 when you want the "inactive"
ones to show, and a value of 3 when you want "all" to show.

The crtierion expression in the query would be something like this:

([forms]![customer form new]![selectcontact] - 2) Or
([forms]![customer form new]![selectcontact] = 3)

--

Ken Snell
<MS ACCESS MVP>


Hi All,
I have a subform where one of the fields is a yes/no checkbox to
indicate whether or not the record is "active": Active returns -1,
inactive 0
On the main form, I need to be able to see either the "active"
records, or the "inactive" records or all records.
So, what I've done is to set up an option group to allow the
selection... one button selects the "Active" and one the
"inactive"... however, that's where the problem starts, I can't get
the third button to show all items.
Basically, the option group result is used by the query that is
used as the source for the subform. I've tried using a statement
within the criteria that says...

IIf([forms]![customer form
new]![selectcontact]=1,<1,[forms]![customer form
new]![selectcontact]) but this then shows all active records,
rather than all

If there is a way that I can achieve this, I'd be really interested
to know!

Regards
Colin Foster

 

[Ken Snell [MVP]]

Glad to hear of your success.. I wish you well!

and, you're welcome!

 

[Colin Foster]

Hi Ken,
Don't know if you're still out there, but...


I thought that the report writing would be the easy bit, but I'm getting an
odd error...

"The database engine could not lock table 'tblJobs' because it is already in
use by another person or process"


Basically what I'm trying to do is from my main form click a button to open
up a seperate form which contains all of the details for the job; that works
fine. From this new form, I want to add a button to run a report that prints
out the details.

If I use a button on the new form to launch the report, I receive the above
error; if I launch it from the main form, I can see the report ('though it
shows all records, not just the one that I want to see).

I guess that it's because the new form is open and has locked the records,
so I tried adding a "DoCmd.Close" bit of code to run before the prining
code, however, this just closes the new form & gives the same error.

Any suggestions?

Regards
Colin

 

[Colin Foster]

Further...
managed to get the report working from my main form but still wonder
on the code for resolving the problem, but much less urgent, now... more of
a "like to know" rather than "need to know"

Regards
Colin

 

[Ken Snell [MVP]]

Same problem, just different symptom.

Without knowing all the details, I'm assuming that your new form allows
editing of tblJobs data. And that you're clicking a button whose location
doesn't force a save of the data. Thus, when the query for your report's
recordsource tries to get tblJobs table's data, that record is still locked
because it's still being edited on the form.

You'll need to modify your setup to ensure that the record in the new form
is saved before the report is run. Assuming that the Print Report button on
the new form is in the form's Header section, just add these lines of code
Me.Dirty = False
DoEvents
as the first steps of that button's Click code.

--

Ken Snell
<MS ACCESS MVP>

 

[Colin Foster]

Hi Ken,
Thanks for the further support - I realise that you are working with both
hands behind your back, one eye tightly shut and the other squinting as
you've not seen the other parts of the DB!

You're almost correct in your assumptions, however, I did set up a button to
Save the Record so that (in my view) should have forced the save of the
data. But it doesn't appear to.

In addition, I've tried attaching your extra code to get the following...

Private Sub PrintJobSheet_Click()
On Error GoTo Err_PrintJobSheet_Click

Me.Dirty = False
DoEvents

Dim stDocName As String
Dim strfilter As String

stDocName = "RptJobSheet"
strfilter = "tbljobs.jobno = forms![frmjobsheet]!jobno"
DoCmd.OpenReport stDocName, acPreview

Exit_PrintJobSheet_Click:
Exit Sub

Err_PrintJobSheet_Click:
MsgBox Err.Description
Resume Exit_PrintJobSheet_Click


End Sub

But I still end up with the same answer. Grrr!!

Any suggestions?
Regards
Colin

 

[Ken Snell [MVP]]

First, you're never passing the filter to the report. (This isn't the issue
with the "locking" problem, but I noted it in your code.)

Change these lines:
strfilter = "tbljobs.jobno = forms![frmjobsheet]!jobno"
DoCmd.OpenReport stDocName, acPreview

to these:
strfilter = "tbljobs.jobno = " & forms![frmjobsheet]!jobno
DoCmd.OpenReport stDocName, acPreview, , strfilter


If this "Me.Dirty = False" code isn't working, then there seems to be
another lock on the table somewhere else. Any other forms open at this same
time?

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,
Thanks for the further support - I realise that you are working with both
hands behind your back, one eye tightly shut and the other squinting as
you've not seen the other parts of the DB!

You're almost correct in your assumptions, however, I did set up a button
to Save the Record so that (in my view) should have forced the save of the
data. But it doesn't appear to.

In addition, I've tried attaching your extra code to get the following...

Private Sub PrintJobSheet_Click()
On Error GoTo Err_PrintJobSheet_Click

Me.Dirty = False
DoEvents

Dim stDocName As String
Dim strfilter As String

stDocName = "RptJobSheet"
strfilter = "tbljobs.jobno = forms![frmjobsheet]!jobno"
DoCmd.OpenReport stDocName, acPreview

Exit_PrintJobSheet_Click:
Exit Sub

Err_PrintJobSheet_Click:
MsgBox Err.Description
Resume Exit_PrintJobSheet_Click


End Sub

But I still end up with the same answer. Grrr!!

Any suggestions?
Regards
Colin

Same problem, just different symptom.

Without knowing all the details, I'm assuming that your new form allows
editing of tblJobs data. And that you're clicking a button whose location
doesn't force a save of the data. Thus, when the query for your report's
recordsource tries to get tblJobs table's data, that record is still
locked because it's still being edited on the form.

You'll need to modify your setup to ensure that the record in the new
form is saved before the report is run. Assuming that the Print Report
button on the new form is in the form's Header section, just add these
lines of code
Me.Dirty = False
DoEvents
as the first steps of that button's Click code.

 

[Colin Foster]

Hi Ken,

Thanks for this extra bit.

My main form is open. If I run the report from a button within that form, it
works ok (I just have to tell the user to input the specific job number),
then it runs. However is it likely that leaving this form open could be
causing the problem as I don't think that it refers to the "TblJobs" table
('though as I'm having to "bolt on" bits to this database - it really needs
a rebuild, but there's not the budget! - it might have happened). Assuming
that this is the case then, when the new form opens also tries to link to
this table, there is a locking problem.

Presumably one way around this would be to close the forms, run the report &
then reopen them. However, I tried that & it didn't seem to help.

Regards
Colin

First, you're never passing the filter to the report. (This isn't the
issue with the "locking" problem, but I noted it in your code.)

Change these lines:
strfilter = "tbljobs.jobno = forms![frmjobsheet]!jobno"
DoCmd.OpenReport stDocName, acPreview

to these:
strfilter = "tbljobs.jobno = " & forms![frmjobsheet]!jobno
DoCmd.OpenReport stDocName, acPreview, , strfilter


If this "Me.Dirty = False" code isn't working, then there seems to be
another lock on the table somewhere else. Any other forms open at this
same time?

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,
Thanks for the further support - I realise that you are working with both
hands behind your back, one eye tightly shut and the other squinting as
you've not seen the other parts of the DB!

You're almost correct in your assumptions, however, I did set up a button
to Save the Record so that (in my view) should have forced the save of
the data. But it doesn't appear to.

In addition, I've tried attaching your extra code to get the following...

Private Sub PrintJobSheet_Click()
On Error GoTo Err_PrintJobSheet_Click

Me.Dirty = False
DoEvents

Dim stDocName As String
Dim strfilter As String

stDocName = "RptJobSheet"
strfilter = "tbljobs.jobno = forms![frmjobsheet]!jobno"
DoCmd.OpenReport stDocName, acPreview

Exit_PrintJobSheet_Click:
Exit Sub

Err_PrintJobSheet_Click:
MsgBox Err.Description
Resume Exit_PrintJobSheet_Click


End Sub

But I still end up with the same answer. Grrr!!

Any suggestions?
Regards
Colin

Same problem, just different symptom.

Without knowing all the details, I'm assuming that your new form allows
editing of tblJobs data. And that you're clicking a button whose
location doesn't force a save of the data. Thus, when the query for your
report's recordsource tries to get tblJobs table's data, that record is
still locked because it's still being edited on the form.

You'll need to modify your setup to ensure that the record in the new
form is saved before the report is run. Assuming that the Print Report
button on the new form is in the form's Header section, just add these
lines of code
Me.Dirty = False
DoEvents
as the first steps of that button's Click code.

--

Ken Snell
<MS ACCESS MVP>


Hi Ken,
Don't know if you're still out there, but...


I thought that the report writing would be the easy bit, but I'm
getting an odd error...

"The database engine could not lock table 'tblJobs' because it is
already in use by another person or process"


Basically what I'm trying to do is from my main form click a button to
open up a seperate form which contains all of the details for the job;
that works fine. From this new form, I want to add a button to run a
report that prints out the details.

If I use a button on the new form to launch the report, I receive the
above error; if I launch it from the main form, I can see the report
('though it shows all records, not just the one that I want to see).

I guess that it's because the new form is open and has locked the
records, so I tried adding a "DoCmd.Close" bit of code to run before
the prining code, however, this just closes the new form & gives the
same error.

Any suggestions?

Regards
Colin

 

[Ken Snell [MVP]]

I must admit that I do not "see" the setup correctly in my head, I fear, for
me to provide additional diagnoses at the moment.

The error indicates a form or query has a table locked, and the report's
query cannot gain access to the table. But from your description, I'm not
"seeing" what the source of the lock might be.

If you'd like to zip up the database with the objects that you're using, and
email it to me with instructions on how to reproduce the situation, I'll
take a look and see if I can find the problem (time permitting). My email
address can be obtained from the reply address by removing this is not real
from the address.

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

Thanks for this extra bit.

My main form is open. If I run the report from a button within that form,
it works ok (I just have to tell the user to input the specific job
number), then it runs. However is it likely that leaving this form open
could be causing the problem as I don't think that it refers to the
"TblJobs" table ('though as I'm having to "bolt on" bits to this
database - it really needs a rebuild, but there's not the budget! - it
might have happened). Assuming that this is the case then, when the new
form opens also tries to link to this table, there is a locking problem.

Presumably one way around this would be to close the forms, run the report
& then reopen them. However, I tried that & it didn't seem to help.

Regards
Colin

First, you're never passing the filter to the report. (This isn't the
issue with the "locking" problem, but I noted it in your code.)

Change these lines:
strfilter = "tbljobs.jobno = forms![frmjobsheet]!jobno"
DoCmd.OpenReport stDocName, acPreview

to these:
strfilter = "tbljobs.jobno = " & forms![frmjobsheet]!jobno
DoCmd.OpenReport stDocName, acPreview, , strfilter


If this "Me.Dirty = False" code isn't working, then there seems to be
another lock on the table somewhere else. Any other forms open at this
same time?

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,
Thanks for the further support - I realise that you are working with
both hands behind your back, one eye tightly shut and the other
squinting as you've not seen the other parts of the DB!

You're almost correct in your assumptions, however, I did set up a
button to Save the Record so that (in my view) should have forced the
save of the data. But it doesn't appear to.

In addition, I've tried attaching your extra code to get the
following...

Private Sub PrintJobSheet_Click()
On Error GoTo Err_PrintJobSheet_Click

Me.Dirty = False
DoEvents

Dim stDocName As String
Dim strfilter As String

stDocName = "RptJobSheet"
strfilter = "tbljobs.jobno = forms![frmjobsheet]!jobno"
DoCmd.OpenReport stDocName, acPreview

Exit_PrintJobSheet_Click:
Exit Sub

Err_PrintJobSheet_Click:
MsgBox Err.Description
Resume Exit_PrintJobSheet_Click


End Sub

But I still end up with the same answer. Grrr!!

Any suggestions?
Regards
Colin

Same problem, just different symptom.

Without knowing all the details, I'm assuming that your new form allows
editing of tblJobs data. And that you're clicking a button whose
location doesn't force a save of the data. Thus, when the query for
your report's recordsource tries to get tblJobs table's data, that
record is still locked because it's still being edited on the form.

You'll need to modify your setup to ensure that the record in the new
form is saved before the report is run. Assuming that the Print Report
button on the new form is in the form's Header section, just add these
lines of code
Me.Dirty = False
DoEvents
as the first steps of that button's Click code.

--

Ken Snell
<MS ACCESS MVP>


Hi Ken,
Don't know if you're still out there, but...


I thought that the report writing would be the easy bit, but I'm
getting an odd error...

"The database engine could not lock table 'tblJobs' because it is
already in use by another person or process"


Basically what I'm trying to do is from my main form click a button to
open up a seperate form which contains all of the details for the job;
that works fine. From this new form, I want to add a button to run a
report that prints out the details.

If I use a button on the new form to launch the report, I receive the
above error; if I launch it from the main form, I can see the report
('though it shows all records, not just the one that I want to see).

I guess that it's because the new form is open and has locked the
records, so I tried adding a "DoCmd.Close" bit of code to run before
the prining code, however, this just closes the new form & gives the
same error.

Any suggestions?

Regards
Colin

 

[Colin Foster]

Hi Ken,
Thanks for the offer. I'll organise that tomorrow (it's 23:40 here & I'm
just of to "the land of nod"!

As I said, I've managed to make the report work from a different route, but
it would be nice if we can spot the problem ('though don't spend too much
time on it!)

Regards
Colin

I must admit that I do not "see" the setup correctly in my head, I fear,
for me to provide additional diagnoses at the moment.

The error indicates a form or query has a table locked, and the report's
query cannot gain access to the table. But from your description, I'm not
"seeing" what the source of the lock might be.

If you'd like to zip up the database with the objects that you're using,
and email it to me with instructions on how to reproduce the situation,
I'll take a look and see if I can find the problem (time permitting). My
email address can be obtained from the reply address by removing this is
not real from the address.

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

Thanks for this extra bit.

My main form is open. If I run the report from a button within that form,
it works ok (I just have to tell the user to input the specific job
number), then it runs. However is it likely that leaving this form open
could be causing the problem as I don't think that it refers to the
"TblJobs" table ('though as I'm having to "bolt on" bits to this
database - it really needs a rebuild, but there's not the budget! - it
might have happened). Assuming that this is the case then, when the new
form opens also tries to link to this table, there is a locking problem.

Presumably one way around this would be to close the forms, run the
report & then reopen them. However, I tried that & it didn't seem to
help.

Regards
Colin

First, you're never passing the filter to the report. (This isn't the
issue with the "locking" problem, but I noted it in your code.)

Change these lines:
strfilter = "tbljobs.jobno = forms![frmjobsheet]!jobno"
DoCmd.OpenReport stDocName, acPreview

to these:
strfilter = "tbljobs.jobno = " & forms![frmjobsheet]!jobno
DoCmd.OpenReport stDocName, acPreview, , strfilter


If this "Me.Dirty = False" code isn't working, then there seems to be
another lock on the table somewhere else. Any other forms open at this
same time?

--

Ken Snell
<MS ACCESS MVP>



Hi Ken,
Thanks for the further support - I realise that you are working with
both hands behind your back, one eye tightly shut and the other
squinting as you've not seen the other parts of the DB!

You're almost correct in your assumptions, however, I did set up a
button to Save the Record so that (in my view) should have forced the
save of the data. But it doesn't appear to.

In addition, I've tried attaching your extra code to get the
following...

Private Sub PrintJobSheet_Click()
On Error GoTo Err_PrintJobSheet_Click

Me.Dirty = False
DoEvents

Dim stDocName As String
Dim strfilter As String

stDocName = "RptJobSheet"
strfilter = "tbljobs.jobno = forms![frmjobsheet]!jobno"
DoCmd.OpenReport stDocName, acPreview

Exit_PrintJobSheet_Click:
Exit Sub

Err_PrintJobSheet_Click:
MsgBox Err.Description
Resume Exit_PrintJobSheet_Click


End Sub

But I still end up with the same answer. Grrr!!

Any suggestions?
Regards
Colin

Same problem, just different symptom.

Without knowing all the details, I'm assuming that your new form
allows editing of tblJobs data. And that you're clicking a button
whose location doesn't force a save of the data. Thus, when the query
for your report's recordsource tries to get tblJobs table's data, that
record is still locked because it's still being edited on the form.

You'll need to modify your setup to ensure that the record in the new
form is saved before the report is run. Assuming that the Print Report
button on the new form is in the form's Header section, just add these
lines of code
Me.Dirty = False
DoEvents
as the first steps of that button's Click code.

--

Ken Snell
<MS ACCESS MVP>


Hi Ken,
Don't know if you're still out there, but...


I thought that the report writing would be the easy bit, but I'm
getting an odd error...

"The database engine could not lock table 'tblJobs' because it is
already in use by another person or process"


Basically what I'm trying to do is from my main form click a button
to open up a seperate form which contains all of the details for the
job; that works fine. From this new form, I want to add a button to
run a report that prints out the details.

If I use a button on the new form to launch the report, I receive the
above error; if I launch it from the main form, I can see the report
('though it shows all records, not just the one that I want to see).

I guess that it's because the new form is open and has locked the
records, so I tried adding a "DoCmd.Close" bit of code to run before
the prining code, however, this just closes the new form & gives the
same error.

Any suggestions?

Regards
Colin

 

[Ken Snell [MVP]]

Colin -

I've reviewed your database and found the problem.

The error occurs because your "job sheet" form (the second form) is using
QryJobs as its recordsource. And the report that you're trying to open also
uses that same query. Because the "job sheet" form is open at the same time,
that query cannot be run by two separate objects.

One workaround would be to use a different query for the report. Then your
code should work as written.

However, if you want to keep the same query for both the form and the
report, you can rewrite your code to delete the form's Recordsource, run the
query, and then restore the Recordsource. Here is sample code:
------code start---------------
Private Sub PrintJobSheet_Click()
Dim strRecordSource As String, strFormFilter As String
Dim stDocName As String
Dim strfilter As String

On Error GoTo Err_PrintJobSheet_Click

Me.Dirty = False
DoEvents

stDocName = "RptJobSheet"
strfilter = "jobno = " & Me.JobNo.Value

' Store the current recordsource and filter strings
strRecordSource = Me.RecordSource
strFormFilter = Me.Filter

' Freeze screen so that the data do not appear to change
DoCmd.Echo False

' Remove the current recordsource so that the report can use it
Me.RecordSource = ""

DoCmd.OpenReport stDocName, acPreview, , strfilter

Exit_PrintJobSheet_Click:

' Restore the recordsource to what it was
Me.RecordSource = strRecordSource
' Restore the filter to what it was
Me.Filter = strFormFilter

' Turn filter back on
Me.FilterOn = True

' Return to the record that was active when the button was clicked
Me.RecordsetClone.FindFirst strfilter
Me.Bookmark = Me.RecordsetClone.Bookmark

' Unfreeze the screen
DoCmd.Echo True
Exit Sub


Err_PrintJobSheet_Click:
MsgBox Err.Description
Resume Exit_PrintJobSheet_Click

End Sub
----code end-------
--

Ken Snell
<MS ACCESS MVP>

Hi Ken,
Thanks for the offer. I'll organise that tomorrow (it's 23:40 here & I'm
just of to "the land of nod"!

As I said, I've managed to make the report work from a different route,
but it would be nice if we can spot the problem ('though don't spend too
much time on it!)

Regards
Colin

I must admit that I do not "see" the setup correctly in my head, I fear,
for me to provide additional diagnoses at the moment.

The error indicates a form or query has a table locked, and the report's
query cannot gain access to the table. But from your description, I'm not
"seeing" what the source of the lock might be.

If you'd like to zip up the database with the objects that you're using,
and email it to me with instructions on how to reproduce the situation,
I'll take a look and see if I can find the problem (time permitting). My
email address can be obtained from the reply address by removing this is
not real from the address.

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

Thanks for this extra bit.

My main form is open. If I run the report from a button within that
form, it works ok (I just have to tell the user to input the specific
job number), then it runs. However is it likely that leaving this form
open could be causing the problem as I don't think that it refers to the
"TblJobs" table ('though as I'm having to "bolt on" bits to this
database - it really needs a rebuild, but there's not the budget! - it
might have happened). Assuming that this is the case then, when the new
form opens also tries to link to this table, there is a locking problem.

Presumably one way around this would be to close the forms, run the
report & then reopen them. However, I tried that & it didn't seem to
help.

Regards
Colin


First, you're never passing the filter to the report. (This isn't the
issue with the "locking" problem, but I noted it in your code.)

Change these lines:
strfilter = "tbljobs.jobno = forms![frmjobsheet]!jobno"
DoCmd.OpenReport stDocName, acPreview

to these:
strfilter = "tbljobs.jobno = " & forms![frmjobsheet]!jobno
DoCmd.OpenReport stDocName, acPreview, , strfilter


If this "Me.Dirty = False" code isn't working, then there seems to be
another lock on the table somewhere else. Any other forms open at this
same time?

--

Ken Snell
<MS ACCESS MVP>



Hi Ken,
Thanks for the further support - I realise that you are working with
both hands behind your back, one eye tightly shut and the other
squinting as you've not seen the other parts of the DB!

You're almost correct in your assumptions, however, I did set up a
button to Save the Record so that (in my view) should have forced the
save of the data. But it doesn't appear to.

In addition, I've tried attaching your extra code to get the
following...

Private Sub PrintJobSheet_Click()
On Error GoTo Err_PrintJobSheet_Click

Me.Dirty = False
DoEvents

Dim stDocName As String
Dim strfilter As String

stDocName = "RptJobSheet"
strfilter = "tbljobs.jobno = forms![frmjobsheet]!jobno"
DoCmd.OpenReport stDocName, acPreview

Exit_PrintJobSheet_Click:
Exit Sub

Err_PrintJobSheet_Click:
MsgBox Err.Description
Resume Exit_PrintJobSheet_Click


End Sub

But I still end up with the same answer. Grrr!!

Any suggestions?
Regards
Colin

Same problem, just different symptom.

Without knowing all the details, I'm assuming that your new form
allows editing of tblJobs data. And that you're clicking a button
whose location doesn't force a save of the data. Thus, when the query
for your report's recordsource tries to get tblJobs table's data,
that record is still locked because it's still being edited on the
form.

You'll need to modify your setup to ensure that the record in the new
form is saved before the report is run. Assuming that the Print
Report button on the new form is in the form's Header section, just
add these lines of code
Me.Dirty = False
DoEvents
as the first steps of that button's Click code.

--

Ken Snell
<MS ACCESS MVP>


Hi Ken,
Don't know if you're still out there, but...


I thought that the report writing would be the easy bit, but I'm
getting an odd error...

"The database engine could not lock table 'tblJobs' because it is
already in use by another person or process"


Basically what I'm trying to do is from my main form click a button
to open up a seperate form which contains all of the details for the
job; that works fine. From this new form, I want to add a button to
run a report that prints out the details.

If I use a button on the new form to launch the report, I receive
the above error; if I launch it from the main form, I can see the
report ('though it shows all records, not just the one that I want
to see).

I guess that it's because the new form is open and has locked the
records, so I tried adding a "DoCmd.Close" bit of code to run before
the prining code, however, this just closes the new form & gives the
same error.

Any suggestions?

Regards
Colin

 

[Ken Snell [MVP]]

I forgot to mention also that your job sheets form currently has its Record
Locks property set to All Records. This property should be set to No Locks,
as your current setting is locking the entire table when this form is open.

Many of your other forms and subforms have a similar setting for this
property. All should be changed to No Locks.

--

Ken Snell
<MS ACCESS MVP>

Colin -

I've reviewed your database and found the problem.

The error occurs because your "job sheet" form (the second form) is using
QryJobs as its recordsource. And the report that you're trying to open
also uses that same query. Because the "job sheet" form is open at the
same time, that query cannot be run by two separate objects.

One workaround would be to use a different query for the report. Then your
code should work as written.

However, if you want to keep the same query for both the form and the
report, you can rewrite your code to delete the form's Recordsource, run
the query, and then restore the Recordsource. Here is sample code:
------code start---------------
Private Sub PrintJobSheet_Click()
Dim strRecordSource As String, strFormFilter As String
Dim stDocName As String
Dim strfilter As String

On Error GoTo Err_PrintJobSheet_Click

Me.Dirty = False
DoEvents

stDocName = "RptJobSheet"
strfilter = "jobno = " & Me.JobNo.Value

' Store the current recordsource and filter strings
strRecordSource = Me.RecordSource
strFormFilter = Me.Filter

' Freeze screen so that the data do not appear to change
DoCmd.Echo False

' Remove the current recordsource so that the report can use it
Me.RecordSource = ""

DoCmd.OpenReport stDocName, acPreview, , strfilter

Exit_PrintJobSheet_Click:

' Restore the recordsource to what it was
Me.RecordSource = strRecordSource
' Restore the filter to what it was
Me.Filter = strFormFilter

' Turn filter back on
Me.FilterOn = True

' Return to the record that was active when the button was clicked
Me.RecordsetClone.FindFirst strfilter
Me.Bookmark = Me.RecordsetClone.Bookmark

' Unfreeze the screen
DoCmd.Echo True
Exit Sub


Err_PrintJobSheet_Click:
MsgBox Err.Description
Resume Exit_PrintJobSheet_Click

End Sub
----code end-------
--

Ken Snell
<MS ACCESS MVP>

Hi Ken,
Thanks for the offer. I'll organise that tomorrow (it's 23:40 here & I'm
just of to "the land of nod"!

As I said, I've managed to make the report work from a different route,
but it would be nice if we can spot the problem ('though don't spend too
much time on it!)

Regards
Colin

I must admit that I do not "see" the setup correctly in my head, I fear,
for me to provide additional diagnoses at the moment.

The error indicates a form or query has a table locked, and the report's
query cannot gain access to the table. But from your description, I'm
not "seeing" what the source of the lock might be.

If you'd like to zip up the database with the objects that you're using,
and email it to me with instructions on how to reproduce the situation,
I'll take a look and see if I can find the problem (time permitting).
My email address can be obtained from the reply address by removing this
is not real from the address.

--

Ken Snell
<MS ACCESS MVP>


Hi Ken,

Thanks for this extra bit.

My main form is open. If I run the report from a button within that
form, it works ok (I just have to tell the user to input the specific
job number), then it runs. However is it likely that leaving this form
open could be causing the problem as I don't think that it refers to
the "TblJobs" table ('though as I'm having to "bolt on" bits to this
database - it really needs a rebuild, but there's not the budget! - it
might have happened). Assuming that this is the case then, when the new
form opens also tries to link to this table, there is a locking
problem.

Presumably one way around this would be to close the forms, run the
report & then reopen them. However, I tried that & it didn't seem to
help.

Regards
Colin


First, you're never passing the filter to the report. (This isn't the
issue with the "locking" problem, but I noted it in your code.)

Change these lines:
strfilter = "tbljobs.jobno = forms![frmjobsheet]!jobno"
DoCmd.OpenReport stDocName, acPreview

to these:
strfilter = "tbljobs.jobno = " & forms![frmjobsheet]!jobno
DoCmd.OpenReport stDocName, acPreview, , strfilter


If this "Me.Dirty = False" code isn't working, then there seems to be
another lock on the table somewhere else. Any other forms open at this
same time?

--

Ken Snell
<MS ACCESS MVP>



Hi Ken,
Thanks for the further support - I realise that you are working with
both hands behind your back, one eye tightly shut and the other
squinting as you've not seen the other parts of the DB!

You're almost correct in your assumptions, however, I did set up a
button to Save the Record so that (in my view) should have forced the
save of the data. But it doesn't appear to.

In addition, I've tried attaching your extra code to get the
following...

Private Sub PrintJobSheet_Click()
On Error GoTo Err_PrintJobSheet_Click

Me.Dirty = False
DoEvents

Dim stDocName As String
Dim strfilter As String

stDocName = "RptJobSheet"
strfilter = "tbljobs.jobno = forms![frmjobsheet]!jobno"
DoCmd.OpenReport stDocName, acPreview

Exit_PrintJobSheet_Click:
Exit Sub

Err_PrintJobSheet_Click:
MsgBox Err.Description
Resume Exit_PrintJobSheet_Click


End Sub

But I still end up with the same answer. Grrr!!

Any suggestions?
Regards
Colin

Same problem, just different symptom.

Without knowing all the details, I'm assuming that your new form
allows editing of tblJobs data. And that you're clicking a button
whose location doesn't force a save of the data. Thus, when the
query for your report's recordsource tries to get tblJobs table's
data, that record is still locked because it's still being edited on
the form.

You'll need to modify your setup to ensure that the record in the
new form is saved before the report is run. Assuming that the Print
Report button on the new form is in the form's Header section, just
add these lines of code
Me.Dirty = False
DoEvents
as the first steps of that button's Click code.

--

Ken Snell
<MS ACCESS MVP>


Hi Ken,
Don't know if you're still out there, but...


I thought that the report writing would be the easy bit, but I'm
getting an odd error...

"The database engine could not lock table 'tblJobs' because it is
already in use by another person or process"


Basically what I'm trying to do is from my main form click a button
to open up a seperate form which contains all of the details for
the job; that works fine. From this new form, I want to add a
button to run a report that prints out the details.

If I use a button on the new form to launch the report, I receive
the above error; if I launch it from the main form, I can see the
report ('though it shows all records, not just the one that I want
to see).

I guess that it's because the new form is open and has locked the
records, so I tried adding a "DoCmd.Close" bit of code to run
before the prining code, however, this just closes the new form &
gives the same error.

Any suggestions?

Regards
Colin