Can ACCESS prompt user on data change?

[smoknjo]

I have created a database that will track board members. One of the records
on my member table is "current" ... the field is a yes/no field. I have
reports that will display all members, all members of a specific board and
all members of that board being yes or no ... is there a way on a form, that
if a user is changing the member status from yes to no, that ACCESS will
prompt them with a question that says "would you like to add a new member?"
.... if they click yes, I can take them to another "add new member" form, if
no, they can exit to the main menu?

thank you for your tutelage

 

[Klatuu]

Use the Before Update event of the check box control:

Private Sub chkCurrent_BeforeUpdate(Cancel As Integer)
Dim lngAnswer As Long
If Me.chkCurrent.OldValue = True and Me.chkCurrent = False Then
lngAnswer = Msgbox("Yes = Change Status and Add New Member" &
vbNewLine & _
"No = Change Status Only" & vbNewLine & _
"Cancel = Do Not Change Status", vbYesNoCancel + vbQuestion, _
"MemberStatusChange")
Select Case lngAnswer
Case vbYes
Docmd.OpenForm "frmAddMember"
Docmd.Close acForm, Me.Name, acSaveNo
Case vbNo
Docmd.Close acForm, Me.Name, acSaveNo
Case vbCancel
Cancel = True

End Select
End If
Ens Sub

 

[smoknjo]

Dave, WOW ... thank you!

I cut and pasted the SQL code (which I've never used before) ... I changed ...

Case vbYes
Docmd.OpenForm "frmAddMember" ... to Docmd.OpenForm "Members &
Designees Form

Do I also need to alter the next two "docmd" commands? for example, should

Docmd.Close acForm, Me.Name, acSaveNo ... be changed to Docmd.Close
ac(my form name), Me.(name of what?), acSaveNo

also ... once I have entered in all that code ... what do I do next? How do
I save tell ACCESS to refer to this code?

Thank you Dave

 

[Klatuu]

Since you are using really bad naming, you need to enclose the name of the
form in brackets or it may cause problems:

Docmd.OpenForm "[Members & Designees Form]"

Do I also need to alter the next two "docmd" commands? for example, should

Docmd.Close acForm, Me.Name, acSaveNo ... be changed to Docmd.Close
ac(my form name), Me.(name of what?), acSaveNo

No, acForm is a constant that tells the Close method you want to close the
form.
Me.Name tells the Close method the name of the form you want to close
acSaveNo means don't save any changes to the form (not data, just changes to
the form object)

what do I do next?
Just to be sure the code is in the correct place, it needs to be in the
Before Update event of the check box. I don't know what your check box name
is, so I just made one up.

Private Sub chkCurrent_BeforeUpdate(Cancel As Integer)

To be sure it is correct, in form design view, select the check box and open
the properties dialog.
Select the events tab.
Find the text box labeled Before Update
Click on the small button with the 3 dots to the right of it.
The VB Editor will open with the cursor positioned in the event procedure.
Paste the code in there but don't include the Private Sub or End sub lines,
they are already there.

Now when you click the check box, the code will execute.

 

[smoknjo]

Thank you Dave, I do appreciate this support ... (apologies for my bad naming
structure, first DB)

okay ... so in VB all the code is either blue or black, there was some red
colored text which obviously is bad, I fixed the formatting ... so I'm good
there ... the field on my form that contains the current status is a list
box, not a check box, should I change that and any other dichotomous fields
to check boxes vs. list boxes? such as M/F (male/female)?

Since you are using really bad naming, you need to enclose the name of the
form in brackets or it may cause problems:

Docmd.OpenForm "[Members & Designees Form]"

Do I also need to alter the next two "docmd" commands? for example, should

Docmd.Close acForm, Me.Name, acSaveNo ... be changed to Docmd.Close
ac(my form name), Me.(name of what?), acSaveNo

No, acForm is a constant that tells the Close method you want to close the
form.
Me.Name tells the Close method the name of the form you want to close
acSaveNo means don't save any changes to the form (not data, just changes to
the form object)

what do I do next?
Just to be sure the code is in the correct place, it needs to be in the
Before Update event of the check box. I don't know what your check box name
is, so I just made one up.

Private Sub chkCurrent_BeforeUpdate(Cancel As Integer)

To be sure it is correct, in form design view, select the check box and open
the properties dialog.
Select the events tab.
Find the text box labeled Before Update
Click on the small button with the 3 dots to the right of it.
The VB Editor will open with the cursor positioned in the event procedure.
Paste the code in there but don't include the Private Sub or End sub lines,
they are already there.

Now when you click the check box, the code will execute.
--
Dave Hargis, Microsoft Access MVP

Dave, WOW ... thank you!

I cut and pasted the SQL code (which I've never used before) ... I changed ...

Case vbYes
Docmd.OpenForm "frmAddMember" ... to Docmd.OpenForm "Members &
Designees Form

Do I also need to alter the next two "docmd" commands? for example, should

Docmd.Close acForm, Me.Name, acSaveNo ... be changed to Docmd.Close
ac(my form name), Me.(name of what?), acSaveNo

also ... once I have entered in all that code ... what do I do next? How do
I save tell ACCESS to refer to this code?

Thank you Dave

 

[Klatuu]

Not to worry, it is a common problem.
Some Basic rules.
Do not use spaces or special characters in names.
Use only letters, numbers, and the underscore _ character.

Do not use any Access or Jet reserved words as name
(Date, Month, Year, Name, etc.)

The best way to avoid theses situations is to use prefixes on all names.
The used to be a site that had a great reference for this, but it appears to
have been taken down. But basically, you develop a practice of naming all
thigs with a prefix. Here are some examples of mine:

frm - Form
tbl - Table
rpt - Report
mod - Module
qsel - Select query
qupd - Update Query
qapp - Append Query
qdel - Delete Query
quni - Union Query

txt - Text Box control
cbo - Combo Box
chk - Check Box
lst - List Box
cmd - Command Button
lbl - Label

str - String varialbe
lng - Long
int - Integer
dbl - Double
sng - Single
bln - Boolean (Y/N)
var - Variant
cur - Currency
dtm - Date

etc.

Now as to table field names, I don't prefix them. I usually use "camel case".
ThisIsAFieldName
I once worked where all field names were upper case separated by _
THIS_IS_A_FIELD_NAME

But, anyway, it is just a way to ensure you don't inadvertently use a
reserved name and it makes reading code easier because you can tell at a
glance exactly what kind of object you are dealing with.
--
Dave Hargis, Microsoft Access MVP

Thank you Dave, I do appreciate this support ... (apologies for my bad naming
structure, first DB)

okay ... so in VB all the code is either blue or black, there was some red
colored text which obviously is bad, I fixed the formatting ... so I'm good
there ... the field on my form that contains the current status is a list
box, not a check box, should I change that and any other dichotomous fields
to check boxes vs. list boxes? such as M/F (male/female)?

Since you are using really bad naming, you need to enclose the name of the
form in brackets or it may cause problems:

Docmd.OpenForm "[Members & Designees Form]"

Do I also need to alter the next two "docmd" commands? for example, should

Docmd.Close acForm, Me.Name, acSaveNo ... be changed to Docmd.Close
ac(my form name), Me.(name of what?), acSaveNo

No, acForm is a constant that tells the Close method you want to close the
form.
Me.Name tells the Close method the name of the form you want to close
acSaveNo means don't save any changes to the form (not data, just changes to
the form object)

what do I do next?
Just to be sure the code is in the correct place, it needs to be in the
Before Update event of the check box. I don't know what your check box name
is, so I just made one up.

Private Sub chkCurrent_BeforeUpdate(Cancel As Integer)

To be sure it is correct, in form design view, select the check box and open
the properties dialog.
Select the events tab.
Find the text box labeled Before Update
Click on the small button with the 3 dots to the right of it.
The VB Editor will open with the cursor positioned in the event procedure.
Paste the code in there but don't include the Private Sub or End sub lines,
they are already there.

Now when you click the check box, the code will execute.
--
Dave Hargis, Microsoft Access MVP

Dave, WOW ... thank you!

I cut and pasted the SQL code (which I've never used before) ... I changed ...

Case vbYes
Docmd.OpenForm "frmAddMember" ... to Docmd.OpenForm "Members &
Designees Form

Do I also need to alter the next two "docmd" commands? for example, should

Docmd.Close acForm, Me.Name, acSaveNo ... be changed to Docmd.Close
ac(my form name), Me.(name of what?), acSaveNo

also ... once I have entered in all that code ... what do I do next? How do
I save tell ACCESS to refer to this code?

Thank you Dave



:

Use the Before Update event of the check box control:

Private Sub chkCurrent_BeforeUpdate(Cancel As Integer)
Dim lngAnswer As Long
If Me.chkCurrent.OldValue = True and Me.chkCurrent = False Then
lngAnswer = Msgbox("Yes = Change Status and Add New Member" &
vbNewLine & _
"No = Change Status Only" & vbNewLine & _
"Cancel = Do Not Change Status", vbYesNoCancel + vbQuestion, _
"MemberStatusChange")
Select Case lngAnswer
Case vbYes
Docmd.OpenForm "frmAddMember"
Docmd.Close acForm, Me.Name, acSaveNo
Case vbNo
Docmd.Close acForm, Me.Name, acSaveNo
Case vbCancel
Cancel = True

End Select
End If
Ens Sub
--
Dave Hargis, Microsoft Access MVP


:

I have created a database that will track board members. One of the records
on my member table is "current" ... the field is a yes/no field. I have
reports that will display all members, all members of a specific board and
all members of that board being yes or no ... is there a way on a form, that
if a user is changing the member status from yes to no, that ACCESS will
prompt them with a question that says "would you like to add a new member?"
... if they click yes, I can take them to another "add new member" form, if
no, they can exit to the main menu?

thank you for your tutelage

 

[Beth Whitty]

I have a drop down field called "sales" and what i want to happen in the form is if the field is not null and they change the value I want it to prompt the user 'do you really want to change this person?' and if they say yes then it goes to what they chose and if if the say no it goes back to what it was.

am i wrong in thinking that the code you gave here would work? any help would be GREATLY appreciated

thanks!
B

EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com/default.aspx?ref=ng

 

[Beth Whitty]

this is what i have so far but of course it's not working correctly:

Private Sub sales_BeforeUpdate(Cancel As Integer)
Dim lngAnswer As Long
If Me.sales.OldValue = Not Null And Me.sales = Not Me.sales.OldValue Then
lngAnswer = MsgBox("Do you really want to change this sales person?", vbYesNoCancel, Question)
Select Case lngAnswer
Case vbYes
Me.sales = Me.sales
Case vbNo
Me.sales = Me.sales.OldValue
Case vbCancel
Cancel = True

End Select
End If
End Sub


EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com/default.aspx?ref=ng

 

[Ken Snell \(MVP\)]

Try this:

Private Sub sales_BeforeUpdate(Cancel As Integer)
Dim lngAnswer As Long
If Not Me.sales.OldValue Is Null And Me.sales <> Me.sales.OldValue Then
lngAnswer = MsgBox("Do you really want to change this sales person?",
vbYesNoCancel, Question)
Select Case lngAnswer
Case vbYes
' do nothing
Case vbNo, vbCancel
Cancel = True
Me.Sales.Undo
End Select
End If
End Sub

 

[AccessVandal via AccessMonster.com]

Hi Ken,

Do you think it would be better to use...

If Not IsNull(Me.sales.OldValue) And ......

 

[John Spencer (MVP)]

Personally, I might use

IF Me.Sales.OldValue & "" <> Me.Sales & "" Then

OR

IF IsNull(Me.Sales.OldValue) = False Or _
Me.Sales.OldValue <> Me.Sales & "" Then

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[AccessVandal via AccessMonster.com]

Thanks John,

IF IsNull(Me.Sales.OldValue) = False Or

Looks good, I do think "Not IsNull()" might not work sometimes. Thanks for
the heads up.