Am I Over-Complicating this?

[sara]

I have been volunteered to write a simple system to help a non-profit
enter and track information on the elders they serve. (It's actually
a fascinating activity, and very rewarding to be helping people like
this!)

I have a bunch of look-up tables to store information I'll have the
users choose form drop-down boxes (aides - hearing aid, cain, dentures,
etc) (Language the client speaks: English, Spanish, Russian, etc).

I want the administrators to be able to add, change and delete items
from these lists. I don't actually delete, just change the status to
"D".

I have a simple form - combo box to select from the list of existing
items.
AfterUpdate displays the item in a text box that can be edited.

ADD button closes the form and opens it in ADD mode.
DELETE button makes sure the user wants to delete the record, then
changes the status to "D".

My problem is I am worrited that I am trying to do too much with one
form. I have a CLOSE button that I check for Dirty, if Dirty, check to
make sure the record doesn't already exist (New Aid, or Changed an
existing Aid to one that already exists - maybe the user was confused?
and started typing a NEW item in the edit box) and SaveRecord or send a
message if it's a dup.

I can't just "Close". If I select an item from the drop-down, the form
is dirty, and then I get the mesage that the item already exists. Of
course it does! I selected it from the list and did nothing to it.

I am wondering if my approach is too complicated - it seemed so simple!
Maybe I should display another form for Update or something? I'm not
very sophisticated with Access, so the simpler the solution the better
for me.

HELP!!

thanks
Sara

[salad]

sara wrote:

I have been volunteered to write a simple system to help a non-profit
enter and track information on the elders they serve. (It's actually
a fascinating activity, and very rewarding to be helping people like
this!)

I have a bunch of look-up tables to store information I'll have the
users choose form drop-down boxes (aides - hearing aid, cain, dentures,
etc) (Language the client speaks: English, Spanish, Russian, etc).

I want the administrators to be able to add, change and delete items
from these lists. I don't actually delete, just change the status to
"D".

I have a simple form - combo box to select from the list of existing
items.
AfterUpdate displays the item in a text box that can be edited.

ADD button closes the form and opens it in ADD mode.
DELETE button makes sure the user wants to delete the record, then
changes the status to "D".

My problem is I am worrited that I am trying to do too much with one
form. I have a CLOSE button that I check for Dirty, if Dirty, check to
make sure the record doesn't already exist (New Aid, or Changed an
existing Aid to one that already exists - maybe the user was confused?
and started typing a NEW item in the edit box) and SaveRecord or send a
message if it's a dup.

I can't just "Close". If I select an item from the drop-down, the form
is dirty, and then I get the mesage that the item already exists. Of
course it does! I selected it from the list and did nothing to it.

I am wondering if my approach is too complicated - it seemed so simple!
Maybe I should display another form for Update or something? I'm not
very sophisticated with Access, so the simpler the solution the better
for me.

HELP!!

thanks
Sara

Do you have multiple lookup tables and 1 form to update items from those
tables?

In the KISS school of thought, I'd recommend you have a form for each
lookup table for modification.

If they all have the same database structure; ID, Description,
ActiveInactive flags, you could use one form for modifying. In this
instance you would change the recordsource when the form opens. Let's
say you have Aides and Language tables. You create the form on Aides
table. When you open the file, you pass the table to be updated. Ex:
Docmd.Openform "ModForm",,,,," Aides"
or
Docmd.Openform "ModForm",,,,," Language"

Since the form's default recordsource is Aides, you would check in the
OnOpen event something like
If Me.OpenArgs = "Language" then
Me.Recordsource = "Language"
Endif

The above specifies changing the recordsource to Language. You can use
either a table or a query. If the table structures are similar, but use
different names, you'd want to use a query. For example, the Aides
table may have a field name called AidesDescriptio n. The Language table
may simply be Language. When you build a query, create an alias so that
Language is associated with AidesDescriptio n. You do this by entering
in a column
AidesDescriptio n : Language
Now, in the form, Language assumes the name value associated with
AidesDescriptio n.

But if the tables are not similar, use a separate table. Maybe have a
button on the form that is used to modify the table records. You could
then create another form that determines which lookup tables to use. I
suggest using checkboxes; each checkbox defining the lookup table to
modify. Then use code like
Select Case Me.FrameTables
Case 1
Docmd.Openform "Aides"
Case 2
Docmd.Openform "Language
End Select

Now, if you have a command button to Add, enter something like
Docmd.GoToRecor d acDataForm, "Aides", acNewRec
will move to a new record.

I also suggest, if you haven't done so already, is to have an autonumber
as the key field for each lookup table. What I do is something like the
following in the BeforeUpdate event.
strSQL = "Select AidesDescriptio n " & _
"From Aides " & _
"Where AidesID <" & Me.AidesID & " And " & _
"AidesDescripti on = " & Me.AidesDescrip tion
Dim rst As Recordset
Set rst = currentdb.openr ecordset(strSQL )
If rst.Recordcount 0 then
msgbox "This description already exists.",,"No Mod"
Cancel = True
Endif
Aides ID is an autonumber. So if any other records of a different
record have the same description, the add/mod is canceled.

Maybe some of the stuff I mentioned is relevant.

[sara]

WOW! I have some work to do.

First, all table have a key as AutoNumber, so I've got that piece done.

All do have the same structure, so I'll work on the idea you had for
using one form, along with the code you suggested (I have to study that
a bit - don't quite get it yet, but I'm thinking it's trying to help me
with the "no dup" problem. I can see it on an Add; I have to also make
sure it works on the Update.

I'll work on this tomorrow and post then - good news (I hope) or
request for more help.

Thanks, salad.
Sara
salad wrote:

sara wrote:

I have been volunteered to write a simple system to help a non-profit
enter and track information on the elders they serve. (It's actually
a fascinating activity, and very rewarding to be helping people like
this!)

I have a bunch of look-up tables to store information I'll have the
users choose form drop-down boxes (aides - hearing aid, cain, dentures,
etc) (Language the client speaks: English, Spanish, Russian, etc).

I want the administrators to be able to add, change and delete items
from these lists. I don't actually delete, just change the status to
"D".

I have a simple form - combo box to select from the list of existing
items.
AfterUpdate displays the item in a text box that can be edited.

ADD button closes the form and opens it in ADD mode.
DELETE button makes sure the user wants to delete the record, then
changes the status to "D".

My problem is I am worrited that I am trying to do too much with one
form. I have a CLOSE button that I check for Dirty, if Dirty, check to
make sure the record doesn't already exist (New Aid, or Changed an
existing Aid to one that already exists - maybe the user was confused?
and started typing a NEW item in the edit box) and SaveRecord or send a
message if it's a dup.

I can't just "Close". If I select an item from the drop-down, the form
is dirty, and then I get the mesage that the item already exists. Of
course it does! I selected it from the list and did nothing to it.

I am wondering if my approach is too complicated - it seemed so simple!
Maybe I should display another form for Update or something? I'm not
very sophisticated with Access, so the simpler the solution the better
for me.

HELP!!

thanks
Sara

Do you have multiple lookup tables and 1 form to update items from those
tables?

In the KISS school of thought, I'd recommend you have a form for each
lookup table for modification.

If they all have the same database structure; ID, Description,
ActiveInactive flags, you could use one form for modifying. In this
instance you would change the recordsource when the form opens. Let's
say you have Aides and Language tables. You create the form on Aides
table. When you open the file, you pass the table to be updated. Ex:
Docmd.Openform "ModForm",,,,," Aides"
or
Docmd.Openform "ModForm",,,,," Language"

Since the form's default recordsource is Aides, you would check in the
OnOpen event something like
If Me.OpenArgs = "Language" then
Me.Recordsource = "Language"
Endif

The above specifies changing the recordsource to Language. You can use
either a table or a query. If the table structures are similar, but use
different names, you'd want to use a query. For example, the Aides
table may have a field name called AidesDescriptio n. The Language table
may simply be Language. When you build a query, create an alias so that
Language is associated with AidesDescriptio n. You do this by entering
in a column
AidesDescriptio n : Language
Now, in the form, Language assumes the name value associated with
AidesDescriptio n.

But if the tables are not similar, use a separate table. Maybe have a
button on the form that is used to modify the table records. You could
then create another form that determines which lookup tables to use. I
suggest using checkboxes; each checkbox defining the lookup table to
modify. Then use code like
Select Case Me.FrameTables
Case 1
Docmd.Openform "Aides"
Case 2
Docmd.Openform "Language
End Select

Now, if you have a command button to Add, enter something like
Docmd.GoToRecor d acDataForm, "Aides", acNewRec
will move to a new record.

I also suggest, if you haven't done so already, is to have an autonumber
as the key field for each lookup table. What I do is something like the
following in the BeforeUpdate event.
strSQL = "Select AidesDescriptio n " & _
"From Aides " & _
"Where AidesID <" & Me.AidesID & " And " & _
"AidesDescripti on = " & Me.AidesDescrip tion
Dim rst As Recordset
Set rst = currentdb.openr ecordset(strSQL )
If rst.Recordcount 0 then
msgbox "This description already exists.",,"No Mod"
Cancel = True
Endif
Aides ID is an autonumber. So if any other records of a different
record have the same description, the add/mod is canceled.

Maybe some of the stuff I mentioned is relevant.

[salad]

sara wrote:

WOW! I have some work to do.

First, all table have a key as AutoNumber, so I've got that piece done.

All do have the same structure,

I am unsure what that means? Do you have separate tables? Or is it the
same table with a flag to determine the groups? If it is the same table
with different groups then its a simple matter of filtering. If
separate tables, then use separate queries for the form's recordsource.

Remember, you can always filter the records in a form via a query,
passing the filter in the Docmd.Openform command, or via code by doing
something like
Me.Filter = "State = 'CA'"
Me.FilterOn = True

If separate tables, it's possible you use different names for a key
field; AidesID, LangID, CustID...etc. In your query, you may do
something like
ID : AidesID
This, in effect, makes the column name ID instead of AidesID. If you
look in the SQL (View/SQL from the menu) it will be something like
Select AidesID As ID, ...
This is helpful so the names from other queries can use the same column
name. Later on, if you want to change the labels, you can use the
Caption property of the label. Ex:
Me.LabelID.Capt ion = "ID Num"
so I'll work on the idea you had for

using one form, along with the code you suggested (I have to study that
a bit - don't quite get it yet, but I'm thinking it's trying to help me
with the "no dup" problem. I can see it on an Add; I have to also make
sure it works on the Update.

I'll work on this tomorrow and post then - good news (I hope) or
request for more help.

Good luck.

Thanks, salad.
Sara
salad wrote:

>>sara wrote:

>>>I have been volunteered to write a simple system to help a non-profit
enter and track information on the elders they serve. (It's actually
a fascinating activity, and very rewarding to be helping people like
this!)

I have a bunch of look-up tables to store information I'll have the
users choose form drop-down boxes (aides - hearing aid, cain, dentures,
etc) (Language the client speaks: English, Spanish, Russian, etc).

I want the administrators to be able to add, change and delete items
from these lists. I don't actually delete, just change the status to
"D".

I have a simple form - combo box to select from the list of existing
items.
AfterUpdat e displays the item in a text box that can be edited.

ADD button closes the form and opens it in ADD mode.
DELETE button makes sure the user wants to delete the record, then
changes the status to "D".

My problem is I am worrited that I am trying to do too much with one
form. I have a CLOSE button that I check for Dirty, if Dirty, check to
make sure the record doesn't already exist (New Aid, or Changed an
existing Aid to one that already exists - maybe the user was confused?
and started typing a NEW item in the edit box) and SaveRecord or send a
message if it's a dup.

I can't just "Close". If I select an item from the drop-down, the form
is dirty, and then I get the mesage that the item already exists. Of
course it does! I selected it from the list and did nothing to it.

I am wondering if my approach is too complicated - it seemed so simple!
Maybe I should display another form for Update or something? I'm not
very sophisticated with Access, so the simpler the solution the better
for me.

HELP!!

thanks
Sara

Do you have multiple lookup tables and 1 form to update items from those
tables?

In the KISS school of thought, I'd recommend you have a form for each
lookup table for modification.

If they all have the same database structure; ID, Description,
ActiveInactiv e flags, you could use one form for modifying. In this
instance you would change the recordsource when the form opens. Let's
say you have Aides and Language tables. You create the form on Aides
table. When you open the file, you pass the table to be updated. Ex:
Docmd.Openform "ModForm",,,,," Aides"
or
Docmd.Openform "ModForm",,,,," Language"

Since the form's default recordsource is Aides, you would check in the
OnOpen event something like
If Me.OpenArgs = "Language" then
Me.Recordsource = "Language"
Endif

The above specifies changing the recordsource to Language. You can use
either a table or a query. If the table structures are similar, but use
different names, you'd want to use a query. For example, the Aides
table may have a field name called AidesDescriptio n. The Language table
may simply be Language. When you build a query, create an alias so that
Language is associated with AidesDescriptio n. You do this by entering
in a column
AidesDescriptio n : Language
Now, in the form, Language assumes the name value associated with
AidesDescript ion.

But if the tables are not similar, use a separate table. Maybe have a
button on the form that is used to modify the table records. You could
then create another form that determines which lookup tables to use. I
suggest using checkboxes; each checkbox defining the lookup table to
modify. Then use code like
Select Case Me.FrameTables
Case 1
Docmd.Openform "Aides"
Case 2
Docmd.Openform "Language
End Select

Now, if you have a command button to Add, enter something like
Docmd.GoToRecor d acDataForm, "Aides", acNewRec
will move to a new record.

I also suggest, if you haven't done so already, is to have an autonumber
as the key field for each lookup table. What I do is something like the
following in the BeforeUpdate event.
strSQL = "Select AidesDescriptio n " & _
"From Aides " & _
"Where AidesID <" & Me.AidesID & " And " & _
"AidesDescripti on = " & Me.AidesDescrip tion
Dim rst As Recordset
Set rst = currentdb.openr ecordset(strSQL )
If rst.Recordcount 0 then
msgbox "This description already exists.",,"No Mod"
Cancel = True
Endif
Aides ID is an autonumber. So if any other records of a different
record have the same description, the add/mod is canceled.

Maybe some of the stuff I mentioned is relevant.

[sara]

Working on this - having problems.

I did as I believe you suggested and tried to implement the KISS method
- that's the level I need. I have an Option Group, with check boxes
(looks MUCH better than a million command buttons). I can choose a
check box, then click a button to "Go". Behind the cmd button I have
the Case structure. Works really nicely. (Code at the bottom here)

Now the problems:

1. I have the SQL from you to check to see if the Area/Aide, whatever,
is already on file. But I'm confused as it CAN'T be - I have a
UniqueIndex set up so you can't have the same Description and Status
twice (you could have deleted it and then added it back in, so Status
is included in the unique index).

2. I am getting a Type Mismatch error on
Dim rst As Recordset

(I assume it's on that stmt as the code stops (I have a breakpoint on
StrSQL to follow it - that's how I learn) and highlights the set rst
line.
Set rst = CurrentDb.OpenR ecordset(strSQL )
I am at a total stand-still - appreciate the help!
Thanks -
Sara

Code below:

Select Case Me.fraSelectMai ntenance
Case 1
DoCmd.OpenForm "frmMaintainSta ffandPrograms"

Case 2
DoCmd.OpenForm "frmMaintainCli entConditionsMe nu "

Case 3
DoCmd.OpenForm "frmMaintainLan guage"

Case 4
DoCmd.OpenForm "frmMaintainAre a"

Case 5
DoCmd.OpenForm "frmMaintainAid es"

(You get the idea)
In frmMaintainArea (frmMaintainAid es, frmMaintainLang uage, and about 20
others would all be nearly identical)

Private Sub Form_BeforeUpda te(Cancel As Integer)

' Check to make sure there is no Active record with this description
first

Dim strSQL As String
strSQL = "Select Area From tlkpArea " & _
"Where AreaKey <" & Me.txtAreaKey & " And " & _
"Area = '" & Me.txtArea & "' And " & _
"AreaRecordStat us = 'A'"

Dim rst As Recordset
Set rst = CurrentDb.OpenR ecordset(strSQL )

If rst.RecordCount 0 Then
MsgBox "This description already exists.", , "JFSMW -
No Mod"
Cancel = True
Exit Sub ' Get out so you don't execute Allen
Browne's audit code here
End If


salad wrote:

sara wrote:

WOW! I have some work to do.

First, all table have a key as AutoNumber, so I've got that piece done.

All do have the same structure,

I am unsure what that means? Do you have separate tables? Or is it the
same table with a flag to determine the groups? If it is the same table
with different groups then its a simple matter of filtering. If
separate tables, then use separate queries for the form's recordsource.

Remember, you can always filter the records in a form via a query,
passing the filter in the Docmd.Openform command, or via code by doing
something like
Me.Filter = "State = 'CA'"
Me.FilterOn = True

If separate tables, it's possible you use different names for a key
field; AidesID, LangID, CustID...etc. In your query, you may do
something like
ID : AidesID
This, in effect, makes the column name ID instead of AidesID. If you
look in the SQL (View/SQL from the menu) it will be something like
Select AidesID As ID, ...
This is helpful so the names from other queries can use the same column
name. Later on, if you want to change the labels, you can use the
Caption property of the label. Ex:
Me.LabelID.Capt ion = "ID Num"
so I'll work on the idea you had for

using one form, along with the code you suggested (I have to study that
a bit - don't quite get it yet, but I'm thinking it's trying to help me
with the "no dup" problem. I can see it on an Add; I have to also make
sure it works on the Update.

I'll work on this tomorrow and post then - good news (I hope) or
request for more help.

Good luck.

Thanks, salad.
Sara
salad wrote:

>sara wrote:
I have been volunteered to write a simple system to help a non-profit
enter and track information on the elders they serve. (It's actually
a fascinating activity, and very rewarding to be helping people like
this!)

I have a bunch of look-up tables to store information I'll have the
users choose form drop-down boxes (aides - hearing aid, cain, dentures,
etc) (Language the client speaks: English, Spanish, Russian, etc).

I want the administrators to be able to add, change and delete items
from these lists. I don't actually delete, just change the status to
"D".

I have a simple form - combo box to select from the list of existing
items.
AfterUpdate displays the item in a text box that can be edited.

ADD button closes the form and opens it in ADD mode.
DELETE button makes sure the user wants to delete the record, then
changes the status to "D".

My problem is I am worrited that I am trying to do too much with one
form. I have a CLOSE button that I check for Dirty, if Dirty, check to
make sure the record doesn't already exist (New Aid, or Changed an
existing Aid to one that already exists - maybe the user was confused?
and started typing a NEW item in the edit box) and SaveRecord or send a
message if it's a dup.

I can't just "Close". If I select an item from the drop-down, the form
is dirty, and then I get the mesage that the item already exists. Of
course it does! I selected it from the list and did nothing to it.

I am wondering if my approach is too complicated - it seemed so simple!
Maybe I should display another form for Update or something? I'm not
very sophisticated with Access, so the simpler the solution the better
for me.

HELP!!

thanks
Sara
Do you have multiple lookup tables and 1 form to update items from those
tables?

In the KISS school of thought, I'd recommend you have a form for each
lookup table for modification.

If they all have the same database structure; ID, Description,
ActiveInacti ve flags, you could use one form for modifying. In this
instance you would change the recordsource when the form opens. Let's
say you have Aides and Language tables. You create the form on Aides
table. When you open the file, you pass the table to be updated. Ex:
Docmd.Openform "ModForm",,,,," Aides"
or
Docmd.Openform "ModForm",,,,," Language"

Since the form's default recordsource is Aides, you would check in the
OnOpen event something like
If Me.OpenArgs = "Language" then
Me.Recordsource = "Language"
Endif

The above specifies changing the recordsource to Language. You can use
either a table or a query. If the table structures are similar, but use
different names, you'd want to use a query. For example, the Aides
table may have a field name called AidesDescriptio n. The Language table
may simply be Language. When you build a query, create an alias so that
Language is associated with AidesDescriptio n. You do this by entering
in a column
AidesDescriptio n : Language
Now, in the form, Language assumes the name value associated with
AidesDescripti on.

But if the tables are not similar, use a separate table. Maybe have a
button on the form that is used to modify the table records. You could
then create another form that determines which lookup tables to use. I
suggest using checkboxes; each checkbox defining the lookup table to
modify. Then use code like
Select Case Me.FrameTables
Case 1
Docmd.Openform "Aides"
Case 2
Docmd.Openform "Language
End Select

Now, if you have a command button to Add, enter something like
Docmd.GoToRecor d acDataForm, "Aides", acNewRec
will move to a new record.

I also suggest, if you haven't done so already, is to have an autonumber
as the key field for each lookup table. What I do is something like the
following in the BeforeUpdate event.
strSQL = "Select AidesDescriptio n " & _
"From Aides " & _
"Where AidesID <" & Me.AidesID & " And " & _
"AidesDescripti on = " & Me.AidesDescrip tion
Dim rst As Recordset
Set rst = currentdb.openr ecordset(strSQL )
If rst.Recordcount 0 then
msgbox "This description already exists.",,"No Mod"
Cancel = True
Endif
Aides ID is an autonumber. So if any other records of a different
record have the same description, the add/mod is canceled.

Maybe some of the stuff I mentioned is relevant.

[salad]

sara wrote:

Working on this - having problems.

I did as I believe you suggested and tried to implement the KISS method
- that's the level I need. I have an Option Group, with check boxes
(looks MUCH better than a million command buttons). I can choose a
check box, then click a button to "Go". Behind the cmd button I have
the Case structure. Works really nicely. (Code at the bottom here)

Now the problems:

1. I have the SQL from you to check to see if the Area/Aide, whatever,
is already on file. But I'm confused as it CAN'T be - I have a
UniqueIndex set up so you can't have the same Description and Status
twice (you could have deleted it and then added it back in, so Status
is included in the unique index).

Oh. I thought the Unique index would be the Autonumber field.

2. I am getting a Type Mismatch error on
Dim rst As Recordset

In the newer versions of Access I think, if you open up a code module
and click Tools/References, the 3rd option down may be something to do
with ADO. My option is DAO 3.x. You should have a ref to DAO. In that
case, the line is
Dim rst As DAO.Recordset

(I assume it's on that stmt as the code stops (I have a breakpoint on
StrSQL to follow it - that's how I learn) and highlights the set rst
line.
Set rst = CurrentDb.OpenR ecordset(strSQL )
I am at a total stand-still - appreciate the help!

Adding "DAO." in front of the recordset should assist.

Thanks -
Sara

Code below:

Select Case Me.fraSelectMai ntenance
Case 1
DoCmd.OpenForm "frmMaintainSta ffandPrograms"

Case 2
DoCmd.OpenForm "frmMaintainCli entConditionsMe nu "

Case 3
DoCmd.OpenForm "frmMaintainLan guage"

Case 4
DoCmd.OpenForm "frmMaintainAre a"

Case 5
DoCmd.OpenForm "frmMaintainAid es"

(You get the idea)
In frmMaintainArea (frmMaintainAid es, frmMaintainLang uage, and about 20
others would all be nearly identical)

Private Sub Form_BeforeUpda te(Cancel As Integer)

' Check to make sure there is no Active record with this description
first

Dim strSQL As String
strSQL = "Select Area From tlkpArea " & _
"Where AreaKey <" & Me.txtAreaKey & " And " & _
"Area = '" & Me.txtArea & "' And " & _
"AreaRecordStat us = 'A'"

Dim rst As Recordset
Set rst = CurrentDb.OpenR ecordset(strSQL )

If rst.RecordCount 0 Then
MsgBox "This description already exists.", , "JFSMW -
No Mod"
Cancel = True
Exit Sub ' Get out so you don't execute Allen
Browne's audit code here
End If


salad wrote:

>>sara wrote:

>>>WOW! I have some work to do.

First, all table have a key as AutoNumber, so I've got that piece done.

All do have the same structure,

I am unsure what that means? Do you have separate tables? Or is it the
same table with a flag to determine the groups? If it is the same table
with different groups then its a simple matter of filtering. If
separate tables, then use separate queries for the form's recordsource.

Remember, you can always filter the records in a form via a query,
passing the filter in the Docmd.Openform command, or via code by doing
something like
Me.Filter = "State = 'CA'"
Me.FilterOn = True

If separate tables, it's possible you use different names for a key
field; AidesID, LangID, CustID...etc. In your query, you may do
something like
ID : AidesID
This, in effect, makes the column name ID instead of AidesID. If you
look in the SQL (View/SQL from the menu) it will be something like
Select AidesID As ID, ...
This is helpful so the names from other queries can use the same column
name. Later on, if you want to change the labels, you can use the
Caption property of the label. Ex:
Me.LabelID.Capt ion = "ID Num"
so I'll work on the idea you had for

>>>using one form, along with the code you suggested (I have to study that
a bit - don't quite get it yet, but I'm thinking it's trying to help me
with the "no dup" problem. I can see it on an Add; I have to also make
sure it works on the Update.

I'll work on this tomorrow and post then - good news (I hope) or
request for more help.

Good luck.

>>>Thanks, salad.
Sara
salad wrote:
sara wrote:

>I have been volunteered to write a simple system to help a non-profit
>enter and track information on the elders they serve. (It's actually
>a fascinating activity, and very rewarding to be helping people like
>this!)
>
>I have a bunch of look-up tables to store information I'll have the
>users choose form drop-down boxes (aides - hearing aid, cain, dentures,
>etc) (Language the client speaks: English, Spanish, Russian, etc).
>
>I want the administrators to be able to add, change and delete items

>from these lists. I don't actually delete, just change the status to

>"D".
>
>I have a simple form - combo box to select from the list of existing
>items.
>AfterUpdat e displays the item in a text box that can be edited.
>
>ADD button closes the form and opens it in ADD mode.
>DELETE button makes sure the user wants to delete the record, then
>changes the status to "D".
>
>My problem is I am worrited that I am trying to do too much with one
>form. I have a CLOSE button that I check for Dirty, if Dirty, check to
>make sure the record doesn't already exist (New Aid, or Changed an
>existing Aid to one that already exists - maybe the user was confused?
>and started typing a NEW item in the edit box) and SaveRecord or send a
>message if it's a dup.
>
>I can't just "Close". If I select an item from the drop-down, the form
>is dirty, and then I get the mesage that the item already exists. Of
>course it does! I selected it from the list and did nothing to it.
>
>I am wondering if my approach is too complicated - it seemed so simple!
>Maybe I should display another form for Update or something? I'm not
>very sophisticated with Access, so the simpler the solution the better
>for me.
>
>HELP!!
>
>thanks
>Sara
>

Do you have multiple lookup tables and 1 form to update items from those
tables?

In the KISS school of thought, I'd recommend you have a form for each
lookup table for modification.

If they all have the same database structure; ID, Description,
ActiveInact ive flags, you could use one form for modifying. In this
instance you would change the recordsource when the form opens. Let's
say you have Aides and Language tables. You create the form on Aides
table. When you open the file, you pass the table to be updated. Ex:
Docmd.Openform "ModForm",,,,," Aides"
or
Docmd.Openform "ModForm",,,,," Language"

Since the form's default recordsource is Aides, you would check in the
OnOpen event something like
If Me.OpenArgs = "Language" then
Me.Recordsource = "Language"
Endif

The above specifies changing the recordsource to Language. You can use
either a table or a query. If the table structures are similar, but use
different names, you'd want to use a query. For example, the Aides
table may have a field name called AidesDescriptio n. The Language table
may simply be Language. When you build a query, create an alias so that
Language is associated with AidesDescriptio n. You do this by entering
in a column
AidesDescriptio n : Language
Now, in the form, Language assumes the name value associated with
AidesDescri ption.

But if the tables are not similar, use a separate table. Maybe have a
button on the form that is used to modify the table records. You could
then create another form that determines which lookup tables to use. I
suggest using checkboxes; each checkbox defining the lookup table to
modify. Then use code like
Select Case Me.FrameTables
Case 1
Docmd.Openform "Aides"
Case 2
Docmd.Openform "Language
End Select

Now, if you have a command button to Add, enter something like
Docmd.GoToRecor d acDataForm, "Aides", acNewRec
will move to a new record.

I also suggest, if you haven't done so already, is to have an autonumber
as the key field for each lookup table. What I do is something like the
following in the BeforeUpdate event.
strSQL = "Select AidesDescriptio n " & _
"From Aides " & _
"Where AidesID <" & Me.AidesID & " And " & _
"AidesDescripti on = " & Me.AidesDescrip tion
Dim rst As Recordset
Set rst = currentdb.openr ecordset(strSQL )
If rst.Recordcount 0 then
msgbox "This description already exists.",,"No Mod"
Cancel = True
Endif
Aides ID is an autonumber. So if any other records of a different
record have the same description, the add/mod is canceled.

Maybe some of the stuff I mentioned is relevant.

[sara]

Well, that got me over the DAO problem - I had read a few posts on the
DAO but they didn't mention the Dim change.

Your code executes fine - I get the message, but then I see in my
original code (the "close" button) I do not have a "Save" button -
just "Close". DoCmd.Close acform me.name.

So, if there is an error, I am getting the error message, then closing
the form. I want to keep the form open, but don't know how to do that.

And if I select an item from the dropdown ("Kitchen") then decide that
no, I wanted "Laundry", when I select Laundry, the code jumps to the
BeforeUpdate code - I didn't change anything yet - just chose a
different item to think about changing!

Should I have a "save" button? I am not sure what code would be in
it! I have no sense of form design, which is (obviously) a problem.

Inch by inch....And I thought updating these simple tables would be so
easy (Ha!)
Thank you -
Sara

salad wrote:

sara wrote:

Working on this - having problems.

I did as I believe you suggested and tried to implement the KISS method
- that's the level I need. I have an Option Group, with check boxes
(looks MUCH better than a million command buttons). I can choose a
check box, then click a button to "Go". Behind the cmd button I have
the Case structure. Works really nicely. (Code at the bottom here)

Now the problems:

1. I have the SQL from you to check to see if the Area/Aide, whatever,
is already on file. But I'm confused as it CAN'T be - I have a
UniqueIndex set up so you can't have the same Description and Status
twice (you could have deleted it and then added it back in, so Status
is included in the unique index).

Oh. I thought the Unique index would be the Autonumber field.

2. I am getting a Type Mismatch error on
Dim rst As Recordset

In the newer versions of Access I think, if you open up a code module
and click Tools/References, the 3rd option down may be something to do
with ADO. My option is DAO 3.x. You should have a ref to DAO. In that
case, the line is
Dim rst As DAO.Recordset

(I assume it's on that stmt as the code stops (I have a breakpoint on
StrSQL to follow it - that's how I learn) and highlights the set rst
line.
Set rst = CurrentDb.OpenR ecordset(strSQL )
I am at a total stand-still - appreciate the help!

Adding "DAO." in front of the recordset should assist.

Thanks -
Sara

Code below:

Select Case Me.fraSelectMai ntenance
Case 1
DoCmd.OpenForm "frmMaintainSta ffandPrograms"

Case 2
DoCmd.OpenForm "frmMaintainCli entConditionsMe nu "

Case 3
DoCmd.OpenForm "frmMaintainLan guage"

Case 4
DoCmd.OpenForm "frmMaintainAre a"

Case 5
DoCmd.OpenForm "frmMaintainAid es"

(You get the idea)
In frmMaintainArea (frmMaintainAid es, frmMaintainLang uage, and about 20
others would all be nearly identical)

Private Sub Form_BeforeUpda te(Cancel As Integer)

' Check to make sure there is no Active record with this description
first

Dim strSQL As String
strSQL = "Select Area From tlkpArea " & _
"Where AreaKey <" & Me.txtAreaKey & " And " & _
"Area = '" & Me.txtArea & "' And " & _
"AreaRecordStat us = 'A'"

Dim rst As Recordset
Set rst = CurrentDb.OpenR ecordset(strSQL )

If rst.RecordCount 0 Then
MsgBox "This description already exists.", , "JFSMW -
No Mod"
Cancel = True
Exit Sub ' Get out so you don't execute Allen
Browne's audit code here
End If


salad wrote:

>sara wrote:

WOW! I have some work to do.

First, all table have a key as AutoNumber, so I've got that piece done.

All do have the same structure,

I am unsure what that means? Do you have separate tables? Or is it the
same table with a flag to determine the groups? If it is the same table
with different groups then its a simple matter of filtering. If
separate tables, then use separate queries for the form's recordsource.

Remember, you can always filter the records in a form via a query,
passing the filter in the Docmd.Openform command, or via code by doing
something like
Me.Filter = "State = 'CA'"
Me.FilterOn = True

If separate tables, it's possible you use different names for a key
field; AidesID, LangID, CustID...etc. In your query, you may do
something like
ID : AidesID
This, in effect, makes the column name ID instead of AidesID. If you
look in the SQL (View/SQL from the menu) it will be something like
Select AidesID As ID, ...
This is helpful so the names from other queries can use the same column
name. Later on, if you want to change the labels, you can use the
Caption property of the label. Ex:
Me.LabelID.Capt ion = "ID Num"
so I'll work on the idea you had for

using one form, along with the code you suggested (I have to study that
a bit - don't quite get it yet, but I'm thinking it's trying to help me
with the "no dup" problem. I can see it on an Add; I have to also make
sure it works on the Update.

I'll work on this tomorrow and post then - good news (I hope) or
request for more help.
Good luck.

Thanks, salad.
Sara
salad wrote:
sara wrote:

I have been volunteered to write a simple system to help a non-profit
enter and track information on the elders they serve. (It's actually
a fascinating activity, and very rewarding to be helping people like
this!)

I have a bunch of look-up tables to store information I'll have the
users choose form drop-down boxes (aides - hearing aid, cain, dentures,
etc) (Language the client speaks: English, Spanish, Russian, etc).

I want the administrators to be able to add, change and delete items

from these lists. I don't actually delete, just change the status to

"D".

I have a simple form - combo box to select from the list of existing
items.
AfterUpda te displays the item in a text box that can be edited.

ADD button closes the form and opens it in ADD mode.
DELETE button makes sure the user wants to delete the record, then
changes the status to "D".

My problem is I am worrited that I am trying to do too much with one
form. I have a CLOSE button that I check for Dirty, if Dirty, check to
make sure the record doesn't already exist (New Aid, or Changed an
existing Aid to one that already exists - maybe the user was confused?
and started typing a NEW item in the edit box) and SaveRecord or send a
message if it's a dup.

I can't just "Close". If I select an item from the drop-down, the form
is dirty, and then I get the mesage that the item already exists. Of
course it does! I selected it from the list and did nothing to it.

I am wondering if my approach is too complicated - it seemed so simple!
Maybe I should display another form for Update or something? I'm not
very sophisticated with Access, so the simpler the solution the better
for me.

HELP!!

thanks
Sara
Do you have multiple lookup tables and 1 form to update items from those
tables?

In the KISS school of thought, I'd recommend you have a form for each
lookup table for modification.

If they all have the same database structure; ID, Description,
ActiveInacti ve flags, you could use one form for modifying. In this
instance you would change the recordsource when the form opens. Let's
say you have Aides and Language tables. You create the form on Aides
table. When you open the file, you pass the table to be updated. Ex:
Docmd.Openform "ModForm",,,,," Aides"
or
Docmd.Openform "ModForm",,,,," Language"

Since the form's default recordsource is Aides, you would check in the
OnOpen event something like
If Me.OpenArgs = "Language" then
Me.Recordsource = "Language"
Endif

The above specifies changing the recordsource to Language. You can use
either a table or a query. If the table structures are similar, but use
different names, you'd want to use a query. For example, the Aides
table may have a field name called AidesDescriptio n. The Language table
may simply be Language. When you build a query, create an alias so that
Language is associated with AidesDescriptio n. You do this by entering
in a column
AidesDescriptio n : Language
Now, in the form, Language assumes the name value associated with
AidesDescrip tion.

But if the tables are not similar, use a separate table. Maybe have a
button on the form that is used to modify the table records. You could
then create another form that determines which lookup tables to use. I
suggest using checkboxes; each checkbox defining the lookup table to
modify. Then use code like
Select Case Me.FrameTables
Case 1
Docmd.Openform "Aides"
Case 2
Docmd.Openform "Language
End Select

Now, if you have a command button to Add, enter something like
Docmd.GoToRecor d acDataForm, "Aides", acNewRec
will move to a new record.

I also suggest, if you haven't done so already, is to have an autonumber
as the key field for each lookup table. What I do is something like the
following in the BeforeUpdate event.
strSQL = "Select AidesDescriptio n " & _
"From Aides " & _
"Where AidesID <" & Me.AidesID & " And " & _
"AidesDescripti on = " & Me.AidesDescrip tion
Dim rst As Recordset
Set rst = currentdb.openr ecordset(strSQL )
If rst.Recordcount 0 then
msgbox "This description already exists.",,"No Mod"
Cancel = True
Endif
Aides ID is an autonumber. So if any other records of a different
record have the same description, the add/mod is canceled.

Maybe some of the stuff I mentioned is relevant.

[salad]

sara wrote:

Well, that got me over the DAO problem - I had read a few posts on the
DAO but they didn't mention the Dim change.

Your code executes fine - I get the message, but then I see in my
original code (the "close" button) I do not have a "Save" button -
just "Close". DoCmd.Close acform me.name.

So, if there is an error, I am getting the error message, then closing
the form. I want to keep the form open, but don't know how to do that.

Let's say I have a form. Basically, the info I want to update is a
record. Let's say I have 3 fields; ID (autonumber), Description (text
field), and Status (text). In this form, I would hide the field
ID...the operator will never care what the value of the counter is.
Both Description and Status would be visible.

Since this table is small, I might have a "Find" combobox (dropdown).
It's recordsource would be something similar to
SELECT ID, Description FROM TableName ORDER BY Description;

In the AfterUpdate event it would have code like
Me.RecordsetClo ne.FindFirst "[ID] = " & ComboFindID
Me.Bookmark = Me.RecordsetClo ne.Bookmark
Now what this does is find the record you want to modify and update.

In the AfterUpdate event of the form you might have code like
Me.ComboFindID. Requery
Me.ComboFindID = Me.ID
Now what this does is that you requery the Find combo since this may
have been a new record or the description has changed.

In this form, the operator can "find" and get to the record they want to
modify via the combo. Or they can go through the records via the
navigation boxes to find the record.

This method separates the records from a method to get to a record. I'm
not sure exactly what you are doing...If you are changeing or going to a
record from a combo.

And if I select an item from the dropdown ("Kitchen") then decide that
no, I wanted "Laundry", when I select Laundry, the code jumps to the
BeforeUpdate code - I didn't change anything yet - just chose a
different item to think about changing!

Should I have a "save" button? I am not sure what code would be in
it! I have no sense of form design, which is (obviously) a problem.

I'm not sure why you would need a save button. When you close the form
it will save the record...and if you go to a new record it will save the
record...so there's really no need unless you want to create one.

>
Inch by inch....And I thought updating these simple tables would be so
easy (Ha!)
Thank you -
Sara

salad wrote:

>>sara wrote:

>>>Working on this - having problems.

I did as I believe you suggested and tried to implement the KISS method
- that's the level I need. I have an Option Group, with check boxes
(looks MUCH better than a million command buttons). I can choose a
check box, then click a button to "Go". Behind the cmd button I have
the Case structure. Works really nicely. (Code at the bottom here)

Now the problems:

1. I have the SQL from you to check to see if the Area/Aide, whatever,
is already on file. But I'm confused as it CAN'T be - I have a
UniqueInde x set up so you can't have the same Description and Status
twice (you could have deleted it and then added it back in, so Status
is included in the unique index).

Oh. I thought the Unique index would be the Autonumber field.

>>>2. I am getting a Type Mismatch error on
Dim rst As Recordset

In the newer versions of Access I think, if you open up a code module
and click Tools/References, the 3rd option down may be something to do
with ADO. My option is DAO 3.x. You should have a ref to DAO. In that
case, the line is
Dim rst As DAO.Recordset

>>>(I assume it's on that stmt as the code stops (I have a breakpoint on
StrSQL to follow it - that's how I learn) and highlights the set rst
line.
Set rst = CurrentDb.OpenR ecordset(strSQL )
I am at a total stand-still - appreciate the help!

Adding "DAO." in front of the recordset should assist.

>>>Thanks -
Sara

Code below:

Select Case Me.fraSelectMai ntenance
Case 1
DoCmd.OpenForm "frmMaintainSta ffandPrograms"

Case 2
DoCmd.OpenForm "frmMaintainCli entConditionsMe nu "

Case 3
DoCmd.OpenForm "frmMaintainLan guage"

Case 4
DoCmd.OpenForm "frmMaintainAre a"

Case 5
DoCmd.OpenForm "frmMaintainAid es"

(You get the idea)
In frmMaintainArea (frmMaintainAid es, frmMaintainLang uage, and about 20
others would all be nearly identical)

Private Sub Form_BeforeUpda te(Cancel As Integer)

' Check to make sure there is no Active record with this description
first

Dim strSQL As String
strSQL = "Select Area From tlkpArea " & _
"Where AreaKey <" & Me.txtAreaKey & " And " & _
"Area = '" & Me.txtArea & "' And " & _
"AreaRecordStat us = 'A'"

Dim rst As Recordset
Set rst = CurrentDb.OpenR ecordset(strSQL )

If rst.RecordCount 0 Then
MsgBox "This description already exists.", , "JFSMW -
No Mod"
Cancel = True
Exit Sub ' Get out so you don't execute Allen
Browne's audit code here
End If


salad wrote:
sara wrote:
>WOW! I have some work to do.
>
>First, all table have a key as AutoNumber, so I've got that piece done.
>
>All do have the same structure,

I am unsure what that means? Do you have separate tables? Or is it the
same table with a flag to determine the groups? If it is the same table
with different groups then its a simple matter of filtering. If
separate tables, then use separate queries for the form's recordsource.

Remember, you can always filter the records in a form via a query,
passing the filter in the Docmd.Openform command, or via code by doing
something like
Me.Filter = "State = 'CA'"
Me.FilterOn = True

If separate tables, it's possible you use different names for a key
field; AidesID, LangID, CustID...etc. In your query, you may do
something like
ID : AidesID
This, in effect, makes the column name ID instead of AidesID. If you
look in the SQL (View/SQL from the menu) it will be something like
Select AidesID As ID, ...
This is helpful so the names from other queries can use the same column
name. Later on, if you want to change the labels, you can use the
Caption property of the label. Ex:
Me.LabelID.Capt ion = "ID Num"
so I'll work on the idea you had for
>using one form, along with the code you suggested (I have to study that
>a bit - don't quite get it yet, but I'm thinking it's trying to help me
>with the "no dup" problem. I can see it on an Add; I have to also make
>sure it works on the Update.
>
>I'll work on this tomorrow and post then - good news (I hope) or
>request for more help.
>

Good luck.
>Thanks, salad.
>Sara
>
>
>salad wrote:
>
>
>
>>sara wrote:
>>
>>
>>
>>
>>>I have been volunteered to write a simple system to help a non-profit
>>>enter and track information on the elders they serve. (It's actually
>>>a fascinating activity, and very rewarding to be helping people like
>>>this!)
>>>
>>>I have a bunch of look-up tables to store information I'll have the
>>>users choose form drop-down boxes (aides - hearing aid, cain, dentures,
>>>etc) (Language the client speaks: English, Spanish, Russian, etc).
>>>
>>>I want the administrators to be able to add, change and delete items
>>
>>>from these lists. I don't actually delete, just change the status to
>>
>>
>>>"D".
>>>
>>>I have a simple form - combo box to select from the list of existing
>>>items.
>>>AfterUpd ate displays the item in a text box that can be edited.
>>>
>>>ADD button closes the form and opens it in ADD mode.
>>>DELETE button makes sure the user wants to delete the record, then
>>>change s the status to "D".
>>>
>>>My problem is I am worrited that I am trying to do too much with one
>>>form. I have a CLOSE button that I check for Dirty, if Dirty, check to
>>>make sure the record doesn't already exist (New Aid, or Changed an
>>>existi ng Aid to one that already exists - maybe the user was confused?
>>>and started typing a NEW item in the edit box) and SaveRecord or send a
>>>messag e if it's a dup.
>>>
>>>I can't just "Close". If I select an item from the drop-down, the form
>>>is dirty, and then I get the mesage that the item already exists. Of
>>>course it does! I selected it from the list and did nothing to it.
>>>
>>>I am wondering if my approach is too complicated - it seemed so simple!
>>>Maybe I should display another form for Update or something? I'm not
>>>very sophisticated with Access, so the simpler the solution the better
>>>for me.
>>>
>>>HELP!!
>>>
>>>thanks
>>>Sara
>>>
>>
>>Do you have multiple lookup tables and 1 form to update items from those
>>tables?
>>
>>In the KISS school of thought, I'd recommend you have a form for each
>>lookup table for modification.
>>
>>If they all have the same database structure; ID, Description,
>>ActiveIna ctive flags, you could use one form for modifying. In this
>>instanc e you would change the recordsource when the form opens. Let's
>>say you have Aides and Language tables. You create the form on Aides
>>table. When you open the file, you pass the table to be updated. Ex:
>> Docmd.Openform "ModForm",,,,," Aides"
>> or
>> Docmd.Openform "ModForm",,,,," Language"
>>
>>Since the form's default recordsource is Aides, you would check in the
>>OnOpen event something like
>> If Me.OpenArgs = "Language" then
>> Me.Recordsource = "Language"
>> Endif
>>
>>The above specifies changing the recordsource to Language. You can use
>>either a table or a query. If the table structures are similar, but use
>>differe nt names, you'd want to use a query. For example, the Aides
>>table may have a field name called AidesDescriptio n. The Language table
>>may simply be Language. When you build a query, create an alias so that
>>Languag e is associated with AidesDescriptio n. You do this by entering
>>in a column
>> AidesDescriptio n : Language
>>Now, in the form, Language assumes the name value associated with
>>AidesDesc ription.
>>
>>But if the tables are not similar, use a separate table. Maybe have a
>>button on the form that is used to modify the table records. You could
>>then create another form that determines which lookup tables to use. I
>>suggest using checkboxes; each checkbox defining the lookup table to
>>modify. Then use code like
>> Select Case Me.FrameTables
>> Case 1
>> Docmd.Openform "Aides"
>> Case 2
>> Docmd.Openform "Language
>> End Select
>>
>>Now, if you have a command button to Add, enter something like
>> Docmd.GoToRecor d acDataForm, "Aides", acNewRec
>>will move to a new record.
>>
>>I also suggest, if you haven't done so already, is to have an autonumber
>>as the key field for each lookup table. What I do is something like the
>>followi ng in the BeforeUpdate event.
>> strSQL = "Select AidesDescriptio n " & _
>> "From Aides " & _
>> "Where AidesID <" & Me.AidesID & " And " & _
>> "AidesDescripti on = " & Me.AidesDescrip tion
>> Dim rst As Recordset
>> Set rst = currentdb.openr ecordset(strSQL )
>> If rst.Recordcount 0 then
>> msgbox "This description already exists.",,"No Mod"
>> Cancel = True
>> Endif
>>Aides ID is an autonumber. So if any other records of a different
>>record have the same description, the add/mod is canceled.
>>
>>Maybe some of the stuff I mentioned is relevant.
>
>

[sara]

This was SO helpful! But I'm not there yet...

First, I wasn't doing the AfterUpdate work, and I'll be that was some
of my problem.

Now, it seems to be working much better, but after a user tries to
enter a DUP, I get the message and then the form closes. How can I
prevent the form from closing if they got the dup message (that you
helped me with in the BeforeUpdate event?). I find the form close very
confusing.

Also, I am getting the "Write Conflict" message. I try to change
"Bedrooms" to "Bedroom" and find that it already exists. Form closes.
I open it again, and choose "Bedrooms" and try to delete it. I get the
"write conflict" message.

Posts on that seem to indicate that I should "update the field on the
form, not the recordset" but I don't know what that means - how to do
that.

I also saw posts that suggest I should say Me.Dirty = False. I tried
that (maybe in the wrong place) and it didn't work.

I have looked at another form I created and I guess I didn't test it
too well, as the same problems exist.

Can you help??? I'm sorry to impose like this - but I'm the only one
in my office who does this sort of thing, so there's no one to ask -
except here!

Sara

salad wrote:

sara wrote:

Well, that got me over the DAO problem - I had read a few posts on the
DAO but they didn't mention the Dim change.

Your code executes fine - I get the message, but then I see in my
original code (the "close" button) I do not have a "Save" button -
just "Close". DoCmd.Close acform me.name.

So, if there is an error, I am getting the error message, then closing
the form. I want to keep the form open, but don't know how to do that.

Let's say I have a form. Basically, the info I want to update is a
record. Let's say I have 3 fields; ID (autonumber), Description (text
field), and Status (text). In this form, I would hide the field
ID...the operator will never care what the value of the counter is.
Both Description and Status would be visible.

Since this table is small, I might have a "Find" combobox (dropdown).
It's recordsource would be something similar to
SELECT ID, Description FROM TableName ORDER BY Description;

In the AfterUpdate event it would have code like
Me.RecordsetClo ne.FindFirst "[ID] = " & ComboFindID
Me.Bookmark = Me.RecordsetClo ne.Bookmark
Now what this does is find the record you want to modify and update.

In the AfterUpdate event of the form you might have code like
Me.ComboFindID. Requery
Me.ComboFindID = Me.ID
Now what this does is that you requery the Find combo since this may
have been a new record or the description has changed.

In this form, the operator can "find" and get to the record they want to
modify via the combo. Or they can go through the records via the
navigation boxes to find the record.

This method separates the records from a method to get to a record. I'm
not sure exactly what you are doing...If you are changeing or going to a
record from a combo.

And if I select an item from the dropdown ("Kitchen") then decide that
no, I wanted "Laundry", when I select Laundry, the code jumps to the
BeforeUpdate code - I didn't change anything yet - just chose a
different item to think about changing!

Should I have a "save" button? I am not sure what code would be in
it! I have no sense of form design, which is (obviously) a problem.

I'm not sure why you would need a save button. When you close the form
it will save the record...and if you go to a new record it will save the
record...so there's really no need unless you want to create one.

Inch by inch....And I thought updating these simple tables would be so
easy (Ha!)
Thank you -
Sara

salad wrote:

>sara wrote:

Working on this - having problems.

I did as I believe you suggested and tried to implement the KISS method
- that's the level I need. I have an Option Group, with check boxes
(looks MUCH better than a million command buttons). I can choose a
check box, then click a button to "Go". Behind the cmd button I have
the Case structure. Works really nicely. (Code at the bottom here)

Now the problems:

1. I have the SQL from you to check to see if the Area/Aide, whatever,
is already on file. But I'm confused as it CAN'T be - I have a
UniqueIndex set up so you can't have the same Description and Status
twice (you could have deleted it and then added it back in, so Status
is included in the unique index).

Oh. I thought the Unique index would be the Autonumber field.
2. I am getting a Type Mismatch error on
Dim rst As Recordset

In the newer versions of Access I think, if you open up a code module
and click Tools/References, the 3rd option down may be something to do
with ADO. My option is DAO 3.x. You should have a ref to DAO. In that
case, the line is
Dim rst As DAO.Recordset
(I assume it's on that stmt as the code stops (I have a breakpoint on
StrSQL to follow it - that's how I learn) and highlights the set rst
line.
Set rst = CurrentDb.OpenR ecordset(strSQL )
I am at a total stand-still - appreciate the help!

Adding "DAO." in front of the recordset should assist.
Thanks -
Sara

Code below:

Select Case Me.fraSelectMai ntenance
Case 1
DoCmd.OpenForm "frmMaintainSta ffandPrograms"

Case 2
DoCmd.OpenForm "frmMaintainCli entConditionsMe nu "

Case 3
DoCmd.OpenForm "frmMaintainLan guage"

Case 4
DoCmd.OpenForm "frmMaintainAre a"

Case 5
DoCmd.OpenForm "frmMaintainAid es"

(You get the idea)
In frmMaintainArea (frmMaintainAid es, frmMaintainLang uage, and about 20
others would all be nearly identical)

Private Sub Form_BeforeUpda te(Cancel As Integer)

' Check to make sure there is no Active record with this description
first

Dim strSQL As String
strSQL = "Select Area From tlkpArea " & _
"Where AreaKey <" & Me.txtAreaKey & " And " & _
"Area = '" & Me.txtArea & "' And " & _
"AreaRecordStat us = 'A'"

Dim rst As Recordset
Set rst = CurrentDb.OpenR ecordset(strSQL )

If rst.RecordCount 0 Then
MsgBox "This description already exists.", , "JFSMW -
No Mod"
Cancel = True
Exit Sub ' Get out so you don't execute Allen
Browne's audit code here
End If


salad wrote:
sara wrote:
WOW! I have some work to do.

First, all table have a key as AutoNumber, so I've got that piece done.

All do have the same structure,

I am unsure what that means? Do you have separate tables? Or is it the
same table with a flag to determine the groups? If it is the same table
with different groups then its a simple matter of filtering. If
separate tables, then use separate queries for the form's recordsource.

Remember, you can always filter the records in a form via a query,
passing the filter in the Docmd.Openform command, or via code by doing
something like
Me.Filter = "State = 'CA'"
Me.FilterOn = True

If separate tables, it's possible you use different names for a key
field; AidesID, LangID, CustID...etc. In your query, you may do
something like
ID : AidesID
This, in effect, makes the column name ID instead of AidesID. If you
look in the SQL (View/SQL from the menu) it will be something like
Select AidesID As ID, ...
This is helpful so the names from other queries can use the same column
name. Later on, if you want to change the labels, you can use the
Caption property of the label. Ex:
Me.LabelID.Capt ion = "ID Num"
so I'll work on the idea you had for
using one form, along with the code you suggested (I have to study that
a bit - don't quite get it yet, but I'm thinking it's trying to help me
with the "no dup" problem. I can see it on an Add; I have to also make
sure it works on the Update.

I'll work on this tomorrow and post then - good news (I hope) or
request for more help.
Good luck.
Thanks, salad.
Sara
salad wrote:

>sara wrote:
>
>
>
>
>>I have been volunteered to write a simple system to help a non-profit
>>enter and track information on the elders they serve. (It's actually
>>a fascinating activity, and very rewarding to be helping people like
>>this!)
>>
>>I have a bunch of look-up tables to store information I'll have the
>>users choose form drop-down boxes (aides - hearing aid, cain, dentures,
>>etc) (Language the client speaks: English, Spanish, Russian, etc).
>>
>>I want the administrators to be able to add, change and delete items
>
>>from these lists. I don't actually delete, just change the status to
>
>
>>"D".
>>
>>I have a simple form - combo box to select from the list of existing
>>items.
>>AfterUpda te displays the item in a text box that can be edited.
>>
>>ADD button closes the form and opens it in ADD mode.
>>DELETE button makes sure the user wants to delete the record, then
>>changes the status to "D".
>>
>>My problem is I am worrited that I am trying to do too much with one
>>form. I have a CLOSE button that I check for Dirty, if Dirty, check to
>>make sure the record doesn't already exist (New Aid, or Changed an
>>existin g Aid to one that already exists - maybe the user was confused?
>>and started typing a NEW item in the edit box) and SaveRecord or send a
>>message if it's a dup.
>>
>>I can't just "Close". If I select an item from the drop-down, the form
>>is dirty, and then I get the mesage that the item already exists. Of
>>course it does! I selected it from the list and did nothing to it.
>>
>>I am wondering if my approach is too complicated - it seemed so simple!
>>Maybe I should display another form for Update or something? I'm not
>>very sophisticated with Access, so the simpler the solution the better
>>for me.
>>
>>HELP!!
>>
>>thanks
>>Sara
>>
>
>Do you have multiple lookup tables and 1 form to update items from those
>tables?
>
>In the KISS school of thought, I'd recommend you have a form for each
>lookup table for modification.
>
>If they all have the same database structure; ID, Description,
>ActiveInac tive flags, you could use one form for modifying. In this
>instance you would change the recordsource when the form opens. Let's
>say you have Aides and Language tables. You create the form on Aides
>table. When you open the file, you pass the table to be updated. Ex:
> Docmd.Openform "ModForm",,,,," Aides"
> or
> Docmd.Openform "ModForm",,,,," Language"
>
>Since the form's default recordsource is Aides, you would check in the
>OnOpen event something like
> If Me.OpenArgs = "Language" then
> Me.Recordsource = "Language"
> Endif
>
>The above specifies changing the recordsource to Language. You can use
>either a table or a query. If the table structures are similar, but use
>differen t names, you'd want to use a query. For example, the Aides
>table may have a field name called AidesDescriptio n. The Language table
>may simply be Language. When you build a query, create an alias so that
>Language is associated with AidesDescriptio n. You do this by entering
>in a column
> AidesDescriptio n : Language
>Now, in the form, Language assumes the name value associated with
>AidesDescr iption.
>
>But if the tables are not similar, use a separate table. Maybe have a
>button on the form that is used to modify the table records. You could
>then create another form that determines which lookup tables to use. I
>suggest using checkboxes; each checkbox defining the lookup table to
>modify. Then use code like
> Select Case Me.FrameTables
> Case 1
> Docmd.Openform "Aides"
> Case 2
> Docmd.Openform "Language
> End Select
>
>Now, if you have a command button to Add, enter something like
> Docmd.GoToRecor d acDataForm, "Aides", acNewRec
>will move to a new record.
>
>I also suggest, if you haven't done so already, is to have an autonumber
>as the key field for each lookup table. What I do is something like the
>followin g in the BeforeUpdate event.
> strSQL = "Select AidesDescriptio n " & _
> "From Aides " & _
> "Where AidesID <" & Me.AidesID & " And " & _
> "AidesDescripti on = " & Me.AidesDescrip tion
> Dim rst As Recordset
> Set rst = currentdb.openr ecordset(strSQL )
> If rst.Recordcount 0 then
> msgbox "This description already exists.",,"No Mod"
> Cancel = True
> Endif
>Aides ID is an autonumber. So if any other records of a different
>record have the same description, the add/mod is canceled.
>
>Maybe some of the stuff I mentioned is relevant.

[salad]

sara wrote:

This was SO helpful! But I'm not there yet...

First, I wasn't doing the AfterUpdate work, and I'll be that was some
of my problem.

Now, it seems to be working much better, but after a user tries to
enter a DUP, I get the message and then the form closes. How can I
prevent the form from closing if they got the dup message (that you
helped me with in the BeforeUpdate event?). I find the form close very
confusing.

AfterUpdate, if referring to the form's event, writes data after a
record has been written. Maybe you need to use the B4Update event so
that you can CANCEL the update. Ex:
If NZ(Me.Desc,"") = "" then
msgbox "Please enter a description"
Cancel = True
Endif

Also, I am getting the "Write Conflict" message. I try to change
"Bedrooms" to "Bedroom" and find that it already exists. Form closes.
I open it again, and choose "Bedrooms" and try to delete it. I get the
"write conflict" message.

This may be caused by using AfterUpdate instead of B4Update. Also, the
B4/After events for a textbox are different than those for the form.

Posts on that seem to indicate that I should "update the field on the
form, not the recordset" but I don't know what that means - how to do
that.

I also saw posts that suggest I should say Me.Dirty = False. I tried
that (maybe in the wrong place) and it didn't work.

I have looked at another form I created and I guess I didn't test it
too well, as the same problems exist.

Can you help??? I'm sorry to impose like this - but I'm the only one
in my office who does this sort of thing, so there's no one to ask -
except here!

I sent an email to you with a database containing a couple of
tables/forms. Did you get it? Could you open it? If so, I think
you'll see how it all works. I figured this back and forth
communicating was slow for something that should take a few minutes of
discussion.

>
Sara

salad wrote:

>>sara wrote:

>>>Well, that got me over the DAO problem - I had read a few posts on the
DAO but they didn't mention the Dim change.

Your code executes fine - I get the message, but then I see in my
original code (the "close" button) I do not have a "Save" button -
just "Close". DoCmd.Close acform me.name.

So, if there is an error, I am getting the error message, then closing
the form. I want to keep the form open, but don't know how to do that.

Let's say I have a form. Basically, the info I want to update is a
record. Let's say I have 3 fields; ID (autonumber), Description (text
field), and Status (text). In this form, I would hide the field
ID...the operator will never care what the value of the counter is.
Both Description and Status would be visible.

Since this table is small, I might have a "Find" combobox (dropdown).
It's recordsource would be something similar to
SELECT ID, Description FROM TableName ORDER BY Description;

In the AfterUpdate event it would have code like
Me.RecordsetClo ne.FindFirst "[ID] = " & ComboFindID
Me.Bookmark = Me.RecordsetClo ne.Bookmark
Now what this does is find the record you want to modify and update.

In the AfterUpdate event of the form you might have code like
Me.ComboFindID. Requery
Me.ComboFindID = Me.ID
Now what this does is that you requery the Find combo since this may
have been a new record or the description has changed.

In this form, the operator can "find" and get to the record they want to
modify via the combo. Or they can go through the records via the
navigation boxes to find the record.

This method separates the records from a method to get to a record. I'm
not sure exactly what you are doing...If you are changeing or going to a
record from a combo.

>>>And if I select an item from the dropdown ("Kitchen") then decide that
no, I wanted "Laundry", when I select Laundry, the code jumps to the
BeforeUpda te code - I didn't change anything yet - just chose a
different item to think about changing!

Should I have a "save" button? I am not sure what code would be in
it! I have no sense of form design, which is (obviously) a problem.

I'm not sure why you would need a save button. When you close the form
it will save the record...and if you go to a new record it will save the
record...so there's really no need unless you want to create one.

>>>Inch by inch....And I thought updating these simple tables would be so
easy (Ha!)
Thank you -
Sara

salad wrote:
sara wrote:
>Working on this - having problems.
>
>I did as I believe you suggested and tried to implement the KISS method
>- that's the level I need. I have an Option Group, with check boxes
>(looks MUCH better than a million command buttons). I can choose a
>check box, then click a button to "Go". Behind the cmd button I have
>the Case structure. Works really nicely. (Code at the bottom here)
>
>Now the problems:
>
>1. I have the SQL from you to check to see if the Area/Aide, whatever,
>is already on file. But I'm confused as it CAN'T be - I have a
>UniqueInde x set up so you can't have the same Description and Status
>twice (you could have deleted it and then added it back in, so Status
>is included in the unique index).

Oh. I thought the Unique index would be the Autonumber field.

>2. I am getting a Type Mismatch error on
Dim rst As Recordset

In the newer versions of Access I think, if you open up a code module
and click Tools/References, the 3rd option down may be something to do
with ADO. My option is DAO 3.x. You should have a ref to DAO. In that
case, the line is
Dim rst As DAO.Recordset

>(I assume it's on that stmt as the code stops (I have a breakpoint on
>StrSQL to follow it - that's how I learn) and highlights the set rst
>line.
Set rst = CurrentDb.OpenR ecordset(strSQL )
>
>
>I am at a total stand-still - appreciate the help!

Adding "DAO." in front of the recordset should assist.

>Thanks -
>Sara
>
>Code below:
>
>Select Case Me.fraSelectMai ntenance
Case 1
DoCmd.OpenForm "frmMaintainSta ffandPrograms"
>
Case 2
DoCmd.OpenForm "frmMaintainCli entConditionsMe nu "
>
Case 3
DoCmd.OpenForm "frmMaintainLan guage"
>
Case 4
DoCmd.OpenForm "frmMaintainAre a"
>
Case 5
DoCmd.OpenForm "frmMaintainAid es"
>
>(You get the idea)
>
>
>In frmMaintainArea (frmMaintainAid es, frmMaintainLang uage, and about 20
>others would all be nearly identical)
>
>Private Sub Form_BeforeUpda te(Cancel As Integer)
>
>' Check to make sure there is no Active record with this description
>first
>
Dim strSQL As String
strSQL = "Select Area From tlkpArea " & _
"Where AreaKey <" & Me.txtAreaKey & " And " & _
"Area = '" & Me.txtArea & "' And " & _
"AreaRecordStat us = 'A'"
>
Dim rst As Recordset
Set rst = CurrentDb.OpenR ecordset(strSQL )
>
If rst.RecordCount 0 Then
MsgBox "This description already exists.", , "JFSMW -
>No Mod"
Cancel = True
Exit Sub ' Get out so you don't execute Allen
>Browne's audit code here
End If
>
>
>
>
>salad wrote:
>
>
>
>>sara wrote:
>>
>>
>>
>>>WOW! I have some work to do.
>>>
>>>First, all table have a key as AutoNumber, so I've got that piece done.
>>>
>>>All do have the same structure,
>>
>>I am unsure what that means? Do you have separate tables? Or is it the
>>same table with a flag to determine the groups? If it is the same table
>>with different groups then its a simple matter of filtering. If
>>separat e tables, then use separate queries for the form's recordsource.
>>
>>Remembe r, you can always filter the records in a form via a query,
>>passing the filter in the Docmd.Openform command, or via code by doing
>>somethi ng like
>> Me.Filter = "State = 'CA'"
>> Me.FilterOn = True
>>
>>If separate tables, it's possible you use different names for a key
>>field; AidesID, LangID, CustID...etc. In your query, you may do
>>somethi ng like
>> ID : AidesID
>>This, in effect, makes the column name ID instead of AidesID. If you
>>look in the SQL (View/SQL from the menu) it will be something like
>> Select AidesID As ID, ...
>>This is helpful so the names from other queries can use the same column
>>name. Later on, if you want to change the labels, you can use the
>>Caption property of the label. Ex:
>> Me.LabelID.Capt ion = "ID Num"
>>
>>
>>so I'll work on the idea you had for
>>
>>
>>
>>>using one form, along with the code you suggested (I have to study that
>>>a bit - don't quite get it yet, but I'm thinking it's trying to help me
>>>with the "no dup" problem. I can see it on an Add; I have to also make
>>>sure it works on the Update.
>>>
>>>I'll work on this tomorrow and post then - good news (I hope) or
>>>reques t for more help.
>>>
>>
>>Good luck.
>>
>>
>>
>>>Thanks , salad.
>>>Sara
>>>
>>>
>>>salad wrote:
>>>
>>>
>>>
>>>
>>>>sara wrote:
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>>I have been volunteered to write a simple system to help a non-profit
>>>>>ente r and track information on the elders they serve. (It's actually
>>>>>a fascinating activity, and very rewarding to be helping people like
>>>>>this !)
>>>>>
>>>>>I have a bunch of look-up tables to store information I'll have the
>>>>>user s choose form drop-down boxes (aides - hearing aid, cain, dentures,
>>>>>etc) (Language the client speaks: English, Spanish, Russian, etc).
>>>>>
>>>>>I want the administrators to be able to add, change and delete items
>>>>
>>>>>from these lists. I don't actually delete, just change the status to
>>>>
>>>>
>>>>
>>>>>"D".
>>>>>
>>>>>I have a simple form - combo box to select from the list of existing
>>>>>item s.
>>>>>AfterU pdate displays the item in a text box that can be edited.
>>>>>
>>>>>ADD button closes the form and opens it in ADD mode.
>>>>>DELE TE button makes sure the user wants to delete the record, then
>>>>>change s the status to "D".
>>>>>
>>>>>My problem is I am worrited that I am trying to do too much with one
>>>>>form . I have a CLOSE button that I check for Dirty, if Dirty, check to
>>>>>make sure the record doesn't already exist (New Aid, or Changed an
>>>>>existi ng Aid to one that already exists - maybe the user was confused?
>>>>>and started typing a NEW item in the edit box) and SaveRecord or send a
>>>>>messag e if it's a dup.
>>>>>
>>>>>I can't just "Close". If I select an item from the drop-down, the form
>>>>>is dirty, and then I get the mesage that the item already exists. Of
>>>>>cour se it does! I selected it from the list and did nothing to it.
>>>>>
>>>>>I am wondering if my approach is too complicated - it seemed so simple!
>>>>>Mayb e I should display another form for Update or something? I'm not
>>>>>very sophisticated with Access, so the simpler the solution the better
>>>>>for me.
>>>>>
>>>>>HELP !!
>>>>>
>>>>>than ks
>>>>>Sara
>>>>>
>>>>
>>>>Do you have multiple lookup tables and 1 form to update items from those
>>>>table s?
>>>>
>>>>In the KISS school of thought, I'd recommend you have a form for each
>>>>looku p table for modification.
>>>>
>>>>If they all have the same database structure; ID, Description,
>>>>ActiveI nactive flags, you could use one form for modifying. In this
>>>>instanc e you would change the recordsource when the form opens. Let's
>>>>say you have Aides and Language tables. You create the form on Aides
>>>>table . When you open the file, you pass the table to be updated. Ex:
>>>> Docmd.Openform "ModForm",,,,," Aides"
>>>> or
>>>> Docmd.Openform "ModForm",,,,," Language"
>>>>
>>>>Since the form's default recordsource is Aides, you would check in the
>>>>OnOpe n event something like
>>>> If Me.OpenArgs = "Language" then
>>>> Me.Recordsource = "Language"
>>>> Endif
>>>>
>>>>The above specifies changing the recordsource to Language. You can use
>>>>eithe r a table or a query. If the table structures are similar, but use
>>>>differe nt names, you'd want to use a query. For example, the Aides
>>>>table may have a field name called AidesDescriptio n. The Language table
>>>>may simply be Language. When you build a query, create an alias so that
>>>>Languag e is associated with AidesDescriptio n. You do this by entering
>>>>in a column
>>>> AidesDescriptio n : Language
>>>>Now, in the form, Language assumes the name value associated with
>>>>AidesDe scription.
>>>>
>>>>But if the tables are not similar, use a separate table. Maybe have a
>>>>butto n on the form that is used to modify the table records. You could
>>>>then create another form that determines which lookup tables to use. I
>>>>sugge st using checkboxes; each checkbox defining the lookup table to
>>>>modif y. Then use code like
>>>> Select Case Me.FrameTables
>>>> Case 1
>>>> Docmd.Openform "Aides"
>>>> Case 2
>>>> Docmd.Openform "Language
>>>> End Select
>>>>
>>>>Now, if you have a command button to Add, enter something like
>>>> Docmd.GoToRecor d acDataForm, "Aides", acNewRec
>>>>will move to a new record.
>>>>
>>>>I also suggest, if you haven't done so already, is to have an autonumber
>>>>as the key field for each lookup table. What I do is something like the
>>>>followi ng in the BeforeUpdate event.
>>>> strSQL = "Select AidesDescriptio n " & _
>>>> "From Aides " & _
>>>> "Where AidesID <" & Me.AidesID & " And " & _
>>>> "AidesDescripti on = " & Me.AidesDescrip tion
>>>> Dim rst As Recordset
>>>> Set rst = currentdb.openr ecordset(strSQL )
>>>> If rst.Recordcount 0 then
>>>> msgbox "This description already exists.",,"No Mod"
>>>> Cancel = True
>>>> Endif
>>>>Aides ID is an autonumber. So if any other records of a different
>>>>recor d have the same description, the add/mod is canceled.
>>>>
>>>>Maybe some of the stuff I mentioned is relevant.
>>>
>>>