VBA – Form Field Question

[MVP - WannaB]

I have this Code, it prompts a user if they enter a value in a field that is
not already present in the table, and allows them to add it or select from
the list. Everything works OK but the line commented adds a blank record
with the new value in the selected field.
MY QUESTION: I either need to delete that last record after this macro runs,
which is not a good idea. OR Capture the records index number and make that
the current record, and change the
rst.AddNew to rst.Edit
I have tested replacing the AddNew with Edit and as it runs now it modifies
the first record in the Table, so I could create a dummy record, and make
that the first record, but again a bad idea…
Can some one tell me how I can capture the index number (Primary Key Auto
Incremented for this table) of the current record and use that to ensure that
record is still the current record when the record it edited??
Here's my code>>>
Private Sub cbo_Progress_NotInList(NewData As String, Response As Integer)
Dim strmsg As String
Dim rst As Recordset
Dim DB As Database

Const MB_YESNO = 4
Const MB_Question = 32
Const IDNO = 7

strmsg = "'" & NewData & "' is not in list. "
strmsg = strmsg & "Would you like to add it?"

If MsgBox(strmsg, MB_YESNO + MB_Question + vbDefaultButton2, "ADD DATA")
= IDNO Then
Response = DATA_ERRDISPLAY
Else
Set DB = DBEngine.Workspaces(0).Databases(0)
Set rst = DB.OpenRecordset("2DO")
rst.AddNew <<This line adds a new record
rst("Progress") = NewData
rst.Update
Response = DATA_ERRADDED
rst.Close
End If
End Sub

 

[Beetle]

What are you trying to do? Are you trying to add a new selection to the
row source of your combo box, or are you wanting to overwrite some data
in an existing record within a table?

 

[Larry Linson]

If you'd wanna be an Access MVP, you'd wanna remember that in Access, VBA
code is not "a macro".

Yes, .AddNew is the code for adding a new record. You just asked the user
if that's what they want to do, and you got a positive response, and you did
what they asked, so why would you want to delete the record? There are a
number of examples available for adding a new record without doing what you
seem to think is needful... that is, you gather the information before you
add the record.

"Index number of current record"? I'm not certain what you are asking
for... Access records do not have an "index number" property... they may
have a Primary Key (which may be one field, or may be multiple fields), but
do not necessarily have a PK... it's in the table definition, so you should
know if your records do.

If it is an AutoNumber, you may have to jump through some hoops to obtain it
for the most-recently-added record, particularly in a multi-user
environment.

Larry Linson
Microsoft Office Access MVP

 

[MVP - WannaB]

OK my mistake, too much recent work in Excel VBA/Macro

Your comments made me remember the purpose of this when I first wrote it, and
that was for entering new records. Now I've added it to a new form and in my
initial tests I was modifying an existing record. So Let's say someone needs to
change the value in of a field (Field2) on an existing record and the source of
this Field2 is a query on distinct values of Field2, the properties also state
that the value must be in list. So this code runs and as it is now it creates a
new record for that new value Field2, and then accepts that value into the
current record. Now I have 2 records with Field2 = "ME" one had no other info
other then the Index number and the ME in Field2.
So What I am really trying to do is capture the current record that the user
wants to modify and make that the current record when this code executes, just
before the rst.AddNew line which I will change to rst.Edit.

Sorry sometimes I Don't explain myself clearly on the first try,
Sometimes it take 3 or 4 try's
=========================================
If you'd wanna be an Access MVP, you'd wanna remember that in Access, VBA
code is not "a macro".

Yes, .AddNew is the code for adding a new record. You just asked the user
if that's what they want to do, and you got a positive response, and you did
what they asked, so why would you want to delete the record? There are a
number of examples available for adding a new record without doing what you
seem to think is needful... that is, you gather the information before you
add the record.

"Index number of current record"? I'm not certain what you are asking
for... Access records do not have an "index number" property... they may
have a Primary Key (which may be one field, or may be multiple fields), but
do not necessarily have a PK... it's in the table definition, so you should
know if your records do.

If it is an AutoNumber, you may have to jump through some hoops to obtain it
for the most-recently-added record, particularly in a multi-user
environment.

Larry Linson
Microsoft Office Access MVP

 

[Ken Sheridan]

From your description it sounds like you are trying to use the NotInList
event to update the current row in the table to which the form is bound.
What you are trying to might be feasible, but not in the way you are
attempting, and it is not what the NotInList event is designed for; it's
really for inserting a row into a separate table referenced by a foreign key
field, to which the combo box is bound, in the form's underlying table. For
instance you might have a form based on a table Customers with a CityID
foreign key column referencing the primary key CityID column of a table
Cities. When a new city name is typed into the combo box a new row is
inserted into the Cities table with the city name in its City column and a
system-generated value in the autonumber CityID column. Here's an example of
code which does that:

Private Sub cboCities_NotInList(NewData As String, Response As Integer)

Dim cmd As ADODB.Command
Dim ctrl As Control
Dim strSQL As String, strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add new city to list?"

strSQL = "INSERT INTO Cities(City) VALUES(""" & _
NewData & """)"

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
cmd.CommandText = strSQL
cmd.Execute
Response = acDataErrAdded
Else
Response = acDataErrContinue
ctrl.Undo
End If

Set cmd = Nothing

End Sub

The above uses an ADO Command object to execute an SQL statement, but it
could equally well be done with DAO as in your code.

You might feel that, as the form's underlying table includes the text
Progress values rather than a surrogate numeric foreign key you don't need a
referenced table. This is not the case. Without a referenced table you
cannot control the integrity of the data in the Progress column in the form's
underlying table by enforcing referential integrity. You should therefore
create a referenced table with a Progress column as its primary key, insert
all distinct values of Progress from your current table into the new table (
a simple 'append' query will do this). Then you can create a relationship
between the tables on the Progress columns, enforcing referential integrity
and cascade updates. You then only need to amend your code so that it
inserts a row into the referenced table rather than the current table.

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

Your clarification confirms what I said in my initial reply (which will
appear below as its in a separate branch of the thread). Essentially you are
trying to reference a column by itself. While you can reference the primary
key of a table by a foreign key column in the same table, e.g. SupervisorID
might reference EmployeeID in an Employees table, you can't have one non-key
column referencing the same non-key column. You really do need a separate
referenced table of the distinct Progress values for this.

Ken Sheridan
Stafford, England

 

[Larry Linson]

Your comments made me remember the purpose of
this when I first wrote it, and that was for entering
new records.

And, that is what it does -- adds a new record... thus, it would not be the
code you would want to use to update an existing field, and I'd wonder why
you tried to use it for that purpose.

Now I've added it to a new form and in my initial
initial tests I was modifying an existing record. So Let's say someone
needs to
change the value in of a field (Field2) on an existing record and the
source of
this Field2 is a query on distinct values of Field2, the properties also
state
that the value must be in list. So this code runs and as it is now it
creates a
new record for that new value Field2, and then accepts that value into the
current record. Now I have 2 records with Field2 = "ME" one had no other
info
other then the Index number and the ME in Field2.

If I understand this, you have a Field, which is displayed in a ComboBox,
the RowSource of which is a "distinct" query on that very field, but which
you have set to "Limit to List", yet you don't really want to "limit to
list", you were only using that to trigger something?

So What I am really trying to do is capture the current
record

"Capture the current record"? Do you not have that current record displayed
on a bound form, with a Combo Box displaying the Field you want to Change?
Why would you need to "capture" it? Why would you need to write code to
update it? Bound forms automatically update when you move off the record
(including moving into a subform control), when you close the form, or when
you take action to explicitly update.

that the user wants to modify and make that the
current record when this code executes, just before the
rst.AddNew line which I will change to rst.Edit.

Sorry sometimes I Don't explain myself clearly on the first try,
Sometimes it take 3 or 4 try's

You know, you can really confuse people trying to help when you just post
the approach you think is required, but doesn't work, and that it's not
working. It really helps if you state what it is that you are trying to
accomplish, not how you think that ought to be done.

Maybe if you'd step back, take a deep breath, and describe what you have and
what you want to accomplish, you might get a good suggestion. I could
_guess_ but, hey, if I guessed wrongly, it'd waste your time, mine, and
other people who're reading.

Larry Linson
Microsoft Office Access MVP

 

[MVP - WannaB]

Thanks Ken, and Thanks Larry.
You both brought up good points. Larry, I need to think my posts over better
the first time around.
Ken, Yes, When I first wrote this I wrote it to pull from a Reference Table, and
as such creating a new record is the correct way to go as you mentioned. Then I
had the bright idea that I didn't need a reference table and I could just manage
the reference table as a part of the data table itself.

My primary goal here was to prevent users from entering duplicates like "USA",
"U.S.A" and "usa" without some sort of attempt at making them think about what
they were doing, but still allow them to add new data as needed, all while
simplifying data entry through the use of the ComboBox drop own selection. Then
I felt it would be a waste to create a whole separate table just to maintain
this list of selectable values when they already exist in the data table.

PLEASE CONFIRM: I'm thinking now that a separate table as nothing more then a
reference table is the best way to go even if it contains nothing more then a
primary key and the distinct values entered in the "Progress" field, ( also
referred to in one of my posts as Field2)???

Ken, it was your comments in the post below (in a separate branch of the thread)
regarding the proper use of the NotInList event that made me realize or remember
my initial purpose of this code.

Again thank you both very much with out all of you assistance I would have been
stewing over this all weekend...
===================================
Your clarification confirms what I said in my initial reply (which will
appear below as its in a separate branch of the thread). Essentially you are
trying to reference a column by itself. While you can reference the primary
key of a table by a foreign key column in the same table, e.g. SupervisorID
might reference EmployeeID in an Employees table, you can't have one non-key
column referencing the same non-key column. You really do need a separate
referenced table of the distinct Progress values for this.

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

Yep, a referenced table with a single column is fine. So to ensure users
only enter USA, UK , France, Germany etc. you'd have a table Countries with
the Country column as the primary key. Even if everything is in the USA , so
you have just one row, it would still be the right way to go because of the
need to control the integrity of the data, which a referenced table does.

The referencing table would also contain a Country column, in this case a
foreign key, so it should be indexed non-uniquely (duplicates allowed). Keys
using the actual names like this rather than arbitrary numeric CountryID keys
are known as 'natural' keys.

You need to be careful about whether a natural key is appropriate. A Cities
table for instance should not use the City column as a key, but have a
numeric CityID key (which can be an autonumber as the values are irrelevant
so long as they are unique). This is because city names can be duplicated.
I happen to know there are four Staffords in the USA for instance, because my
town is twinned with them. They are in different states so you'd have a
State column in the Cities table referencing the primary key of a States
table. These can be natural keys of course because state names are unique.

In your case you should be able to use the Progress columns in the two
tables as natural keys as, from what you've said, I take it this would have
unique values.

When you use a natural key such as State you'll generally not need to use
the States table in queries etc as the value is already in the referencing
table, e.g. Cities. So to find all cities in Massachusetts say you could
have a query:

SELECT City
FROM Cities
WHERE State = "MA";

without having to include the States table in the query. Even though you
won't refer to it in case like this, however, having a separate States column
is important as it enables you to control the integrity of what's entered in
the State column in the Cities table by enforcing referential integrity in
the relationship between Cities and States. When natural keys are used
cascade updates should also be enforced, so that if a value in the primary
key column of the referenced table is changed all the corresponding values in
the referencing table will change automatically. It unlikely to happen with
US states, but internationally it happens sometimes; Burma is now Myanmar for
example.

To find all customers in Boston Mass, however you'd have to join the two
tables on CityID. The criteria for the query would be on both the City and
State columns so the result set excludes customers in any other Bostons (I
know there is one more at least, in Georgia):

SELECT Firstname, LastName
FROM Customers INNER JOIN Cities
ON City.CityID = Customers.CityID
WHERE City = "Boston"
AND State = "MA";

You might be wondering why we can't just have a State column in the
Customers table as well as a CityID column. The answer is because it would
introduce redundancy; the CityID tells us which state the customer is in. In
the jargon a State column in Customers would be 'transitively functionally
dependent' on the key of Customers and the table would not be properly
normalized, to third normal form at least.

Ken Sheridan
Stafford, England

 

[MVP - WannaB]

Wow Ken, Thank you very much!!
When I read your post I realize how little I know, and that's a good thing.
If you're not a teacher, you could consider it.
This post was VERY informative and I really appreciate it.

 

[MVP - WannaB]

PART 2
So is it better to leave the properties of this field as is "TEXT" in the
table and just change the properties on the form... As I write this
something you wrote just came to mind, Referential integrity. I think that
would require a change in the table properties ?? Can you provide, Pros and
Cons??
Thanks again...
===========================