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
10  1585 
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 AidesDescription. The Language table
may simply be Language. When you build a query, create an alias so that
Language is associated with AidesDescription. You do this by entering
in a column
AidesDescription : Language
Now, in the form, Language assumes the name value associated with
AidesDescription.
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.GoToRecord 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 AidesDescription " & _
"From Aides " & _
"Where AidesID <" & Me.AidesID & " And " & _
"AidesDescription = " & Me.AidesDescription
Dim rst As Recordset
Set rst = currentdb.openrecordset(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.
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 AidesDescription. The Language table
may simply be Language. When you build a query, create an alias so that
Language is associated with AidesDescription. You do this by entering
in a column
AidesDescription : Language
Now, in the form, Language assumes the name value associated with
AidesDescription.
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.GoToRecord 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 AidesDescription " & _
"From Aides " & _
"Where AidesID <" & Me.AidesID & " And " & _
"AidesDescription = " & Me.AidesDescription
Dim rst As Recordset
Set rst = currentdb.openrecordset(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 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.Caption = "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,
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 AidesDescription. The Language table
may simply be Language. When you build a query, create an alias so that
Language is associated with AidesDescription. You do this by entering
in a column
AidesDescription : Language
Now, in the form, Language assumes the name value associated with
AidesDescription.
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.GoToRecord 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 AidesDescription " & _
"From Aides " & _
"Where AidesID <" & Me.AidesID & " And " & _
"AidesDescription = " & Me.AidesDescription
Dim rst As Recordset
Set rst = currentdb.openrecordset(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.
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.OpenRecordset(strSQL)
I am at a total stand-still - appreciate the help!
Thanks -
Sara
Code below:
Select Case Me.fraSelectMaintenance
Case 1
DoCmd.OpenForm "frmMaintainStaffandPrograms"
Case 2
DoCmd.OpenForm "frmMaintainClientConditionsMenu "
Case 3
DoCmd.OpenForm "frmMaintainLanguage"
Case 4
DoCmd.OpenForm "frmMaintainArea"
Case 5
DoCmd.OpenForm "frmMaintainAides"
(You get the idea)
In frmMaintainArea (frmMaintainAides, frmMaintainLanguage, and about 20
others would all be nearly identical)
Private Sub Form_BeforeUpdate(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 " & _
"AreaRecordStatus = 'A'"
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset(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.Caption = "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,
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 AidesDescription. The Language table
may simply be Language. When you build a query, create an alias so that
Language is associated with AidesDescription. You do this by entering
in a column
AidesDescription : Language
Now, in the form, Language assumes the name value associated with
AidesDescription.
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.GoToRecord 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 AidesDescription " & _
"From Aides " & _
"Where AidesID <" & Me.AidesID & " And " & _
"AidesDescription = " & Me.AidesDescription
Dim rst As Recordset
Set rst = currentdb.openrecordset(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 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.OpenRecordset(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.fraSelectMaintenance
Case 1
DoCmd.OpenForm "frmMaintainStaffandPrograms"
Case 2
DoCmd.OpenForm "frmMaintainClientConditionsMenu "
Case 3
DoCmd.OpenForm "frmMaintainLanguage"
Case 4
DoCmd.OpenForm "frmMaintainArea"
Case 5
DoCmd.OpenForm "frmMaintainAides"
(You get the idea)
In frmMaintainArea (frmMaintainAides, frmMaintainLanguage, and about 20
others would all be nearly identical)
Private Sub Form_BeforeUpdate(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 " & _
"AreaRecordStatus = 'A'"
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset(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.Caption = "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,
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 AidesDescription. The Language table
may simply be Language. When you build a query, create an alias so that
Language is associated with AidesDescription. You do this by entering
in a column
AidesDescription : Language
Now, in the form, Language assumes the name value associated with
AidesDescription.
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.GoToRecord 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 AidesDescription " & _
"From Aides " & _
"Where AidesID <" & Me.AidesID & " And " & _
"AidesDescription = " & Me.AidesDescription
Dim rst As Recordset
Set rst = currentdb.openrecordset(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.
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.OpenRecordset(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.fraSelectMaintenance
Case 1
DoCmd.OpenForm "frmMaintainStaffandPrograms"
Case 2
DoCmd.OpenForm "frmMaintainClientConditionsMenu "
Case 3
DoCmd.OpenForm "frmMaintainLanguage"
Case 4
DoCmd.OpenForm "frmMaintainArea"
Case 5
DoCmd.OpenForm "frmMaintainAides"
(You get the idea)
In frmMaintainArea (frmMaintainAides, frmMaintainLanguage, and about 20
others would all be nearly identical)
Private Sub Form_BeforeUpdate(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 " & _
"AreaRecordStatus = 'A'"
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset(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.Caption = "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,
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 AidesDescription. The Language table
may simply be Language. When you build a query, create an alias so that
Language is associated with AidesDescription. You do this by entering
in a column
AidesDescription : Language
Now, in the form, Language assumes the name value associated with
AidesDescription.
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.GoToRecord 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 AidesDescription " & _
"From Aides " & _
"Where AidesID <" & Me.AidesID & " And " & _
"AidesDescription = " & Me.AidesDescription
Dim rst As Recordset
Set rst = currentdb.openrecordset(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 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.RecordsetClone.FindFirst "[ID] = " & ComboFindID
Me.Bookmark = Me.RecordsetClone.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.OpenRecordset(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.fraSelectMaintenance
Case 1
DoCmd.OpenForm "frmMaintainStaffandPrograms"
Case 2
DoCmd.OpenForm "frmMaintainClientConditionsMenu "
Case 3
DoCmd.OpenForm "frmMaintainLanguage"
Case 4
DoCmd.OpenForm "frmMaintainArea"
Case 5
DoCmd.OpenForm "frmMaintainAides"
(You get the idea)
In frmMaintainArea (frmMaintainAides, frmMaintainLanguage, and about 20
others would all be nearly identical)
Private Sub Form_BeforeUpdate(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 " & _
"AreaRecordStatus = 'A'"
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset(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.Caption = "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,
>>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 AidesDescription. The Language table
>>may simply be Language. When you build a query, create an alias so that
>>Language is associated with AidesDescription. You do this by entering
>>in a column
>> AidesDescription : Language
>>Now, in the form, Language assumes the name value associated with
>>AidesDescription.
>>
>>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.GoToRecord 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 AidesDescription " & _
>> "From Aides " & _
>> "Where AidesID <" & Me.AidesID & " And " & _
>> "AidesDescription = " & Me.AidesDescription
>> Dim rst As Recordset
>> Set rst = currentdb.openrecordset(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.
>
>
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.RecordsetClone.FindFirst "[ID] = " & ComboFindID
Me.Bookmark = Me.RecordsetClone.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.OpenRecordset(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.fraSelectMaintenance
Case 1
DoCmd.OpenForm "frmMaintainStaffandPrograms"
Case 2
DoCmd.OpenForm "frmMaintainClientConditionsMenu "
Case 3
DoCmd.OpenForm "frmMaintainLanguage"
Case 4
DoCmd.OpenForm "frmMaintainArea"
Case 5
DoCmd.OpenForm "frmMaintainAides"
(You get the idea)
In frmMaintainArea (frmMaintainAides, frmMaintainLanguage, and about 20
others would all be nearly identical)
Private Sub Form_BeforeUpdate(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 " & _
"AreaRecordStatus = 'A'"
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset(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.Caption = "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,
>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 AidesDescription. The Language table
>may simply be Language. When you build a query, create an alias so that
>Language is associated with AidesDescription. You do this by entering
>in a column
> AidesDescription : Language
>Now, in the form, Language assumes the name value associated with
>AidesDescription.
>
>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.GoToRecord 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 AidesDescription " & _
> "From Aides " & _
> "Where AidesID <" & Me.AidesID & " And " & _
> "AidesDescription = " & Me.AidesDescription
> Dim rst As Recordset
> Set rst = currentdb.openrecordset(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 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.RecordsetClone.FindFirst "[ID] = " & ComboFindID
Me.Bookmark = Me.RecordsetClone.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.OpenRecordset(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.fraSelectMaintenance
Case 1
DoCmd.OpenForm "frmMaintainStaffandPrograms"
>
Case 2
DoCmd.OpenForm "frmMaintainClientConditionsMenu "
>
Case 3
DoCmd.OpenForm "frmMaintainLanguage"
>
Case 4
DoCmd.OpenForm "frmMaintainArea"
>
Case 5
DoCmd.OpenForm "frmMaintainAides"
>
>(You get the idea)
>
>
>In frmMaintainArea (frmMaintainAides, frmMaintainLanguage, and about 20
>others would all be nearly identical)
>
>Private Sub Form_BeforeUpdate(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 " & _
"AreaRecordStatus = 'A'"
>
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset(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.Caption = "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,
>>>>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 AidesDescription. The Language table
>>>>may simply be Language. When you build a query, create an alias so that
>>>>Language is associated with AidesDescription. You do this by entering
>>>>in a column
>>>> AidesDescription : Language
>>>>Now, in the form, Language assumes the name value associated with
>>>>AidesDescription.
>>>>
>>>>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.GoToRecord 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 AidesDescription " & _
>>>> "From Aides " & _
>>>> "Where AidesID <" & Me.AidesID & " And " & _
>>>> "AidesDescription = " & Me.AidesDescription
>>>> Dim rst As Recordset
>>>> Set rst = currentdb.openrecordset(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.
>>>
>>>
Thanks, I got the email and replied.
Sara
salad wrote:
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.RecordsetClone.FindFirst "[ID] = " & ComboFindID
Me.Bookmark = Me.RecordsetClone.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.OpenRecordset(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.fraSelectMaintenance
Case 1
DoCmd.OpenForm "frmMaintainStaffandPrograms"
Case 2
DoCmd.OpenForm "frmMaintainClientConditionsMenu "
Case 3
DoCmd.OpenForm "frmMaintainLanguage"
Case 4
DoCmd.OpenForm "frmMaintainArea"
Case 5
DoCmd.OpenForm "frmMaintainAides"
(You get the idea)
In frmMaintainArea (frmMaintainAides, frmMaintainLanguage, and about 20
others would all be nearly identical)
Private Sub Form_BeforeUpdate(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 " & _
"AreaRecordStatus = 'A'"
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset(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.Caption = "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,
>>>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 AidesDescription. The Language table
>>>may simply be Language. When you build a query, create an alias so that
>>>Language is associated with AidesDescription. You do this by entering
>>>in a column
>>> AidesDescription : Language
>>>Now, in the form, Language assumes the name value associated with
>>>AidesDescription.
>>>
>>>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.GoToRecord 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 AidesDescription " & _
>>> "From Aides " & _
>>> "Where AidesID <" & Me.AidesID & " And " & _
>>> "AidesDescription = " & Me.AidesDescription
>>> Dim rst As Recordset
>>> Set rst = currentdb.openrecordset(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.
>>
>>
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Yaroslav Bulatov |
last post by:
I made an array of 10 million floats timed how long it takes to sum
the elements, here's what I got (millis):
gcc -O2: 21
Python with numarray: 104
Python with Numeric: 302...
|
by: Larry R Harrison Jr |
last post by:
I am looking for javascript and a basic tutorial on how to make mouse-over
drop-down menus--the type that when you "hover" over a subject links
relevant to that subject "emerge" which you can then...
|
by: news frontiernet.net |
last post by:
I have a project that uses mouse-over JS script to show and hide layers that
works well in MSIE 6.0 and Opera.
But, it does not work in NS nor Mozilla.
It is here: ...
|
by: Josh |
last post by:
Hi,
I am trying to capture the MouseDown event over a 3rd-party control (a
streaming video screen).
I am trying to draw a rubberbanding rectangle over an area of the
streaming screen (which...
|
by: Lars S. |
last post by:
Hello.
I read that one can call Web services over smtp, but i can't find an example.
Can somebody help me?
Thanks
Lars
|
by: Nikolay Petrov |
last post by:
I have a ListBox, which is binded to a DataTable.
I would like to display a tooltip when the mouse cursor hovers over the
items in the ListBox.
I don't know how to find the index if ListBox item,...
|
by: Sebastian |
last post by:
Hello all
I have a report where I have two nested groups.
I know there are only three standard options for running sum: None,
Over Group and Over All.
I have a MyTextBox in detail section where...
|
by: =?ISO-8859-1?Q?BJ=F6rn_Lindqvist?= |
last post by:
I want to use Python to connect to a SSH account over a HTTP proxy to
automate some operations. I thought paramiko would be able to do that,
but it can not (it seems).
Is there some other Python...
|
by: hanshirley88 |
last post by:
Hi,
I am doing an expense and funding report which contains a list of
consumers by Region and then by SiteID. For example,
Region: NE
Site: NEMV
1. Mary Doe
2. Len Willams
|
by: Andrew 2006 |
last post by:
Hi there,
I'm trying to understand the differences between sending XML over TCP
and XML over HTTP (SOAP). However I'm not having much luck finding
good articles on the www.
Can anyone...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |
