A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Generating a new Record



 
 
Thread Tools Display Modes
  #11  
Old April 7th, 2010, 01:39 AM posted to microsoft.public.access.gettingstarted
Arvin Meyer [MVP][_2_]
external usenet poster
 
Posts: 2,310
Default Generating a new Record

strNewData should be NewData
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"Ray C" wrote in message
...
Hi Arvin, still struggling I am afraid. Although I now have the new
record
being added to the table correctly (using your code), I get errors when i
try
to run the requery

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

AddToList "frm_Store_Add", "StoreName", strNewData, Response ' Arvin
Mayer code
' Me.cmb_Find_Box_3.Requery
End Sub

I have the requery in the cmb_Find_Box_3_after update event but that
routine
does not get called (though it does in your test code???)
If I enter some dats that is not in the table (more correctly the list
that
is based on the table), the NotInList routine kicks in and the program
happilt toddles off to generate the new record (using your code). When it
returns from that task, you will see that i have
"Me.cmb_Find_Box_3.Requery.
However when the program gets to that line I get an error that says
"Run-Time
Error 2118" "You must save thr currentit feld before you run the requery
action"
If I take out that line, I get an error that says "The Text you entered
is
not an item in the list"
Either way, the table shows that my New Record is added but the record
does
not show as a new item in the Combo drop Down list.
Clearly it is a problem with the requery commant but why does your test
code
work ok bur mine gives problems? I guess the answer to that is just that
you
are the expert and i am the novice.
Thanks Arvin

Ray C




"Arvin Meyer [MVP]" wrote:

Ray,

The example at:

http://accessmvp.com/Arvin/NotInListDemo.zip

is eaxctly what you want. Try importing everything into your database and
changing all the names to fit with your names.

You won't be able to do both fields with a single combo box, so you'll
need
2 of them. I recommend getting 1 to work, then copying everything with
the
new names. The generic code function I listed is perfect for you, because
you can make the same NotInList function work with both combo boxes.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access

"Ray C" wrote in message
...
Hi Arvin, I am not sure if I explained my problem badly or if I am just
too
dumb to understand your solution but I have tried the code that you
provided
and I can not get it to generate a new Record in my Table.
Please let me try to explain my problem again and i would be pleased if
you
could give me any pointers as to where i am going wrong in my
understanding
of your answer.

I have a pretty standard table holding property information (Address
etc)
but eac property has a NameAddress info. The Table holdsname and
addresswith
the Name (normaly the Town where it is) and also a Property Number
(provided
by the Owner). The table has an "Auto Number Index" and holds other
information about the property. Because the user may wish to search the
Table
by either the property name or the property number, I have two combo
boxes
that providr drop down's for the user to select as needed. Once the
user
has
selected the required property "Name" or the required property
"Number" i
have a ruotine that searches through the Address Table, finds the
record
and
displays all the fields in that record. All of that is fine.
What I am wanting to do is for the user to be given the ability to Add
a
New
Record to the Table if the information they entered in the Combo Box is
not
found in the Address Table.

My Current attempt at a sollution to this is to use the notIn List
event
to
branch off to a routine that should generate a new *Blank" record that
will
be appended to the end of the adress Table, and show the user a form
full
of
Blank Fields (with the exception of the information they originally
searched
for). The usr should then fill in the blank fields before saving (or
rejecting) the "New Record" appended to the end of the Table.

As I tried to explain originally I experimented with the "DoCmd
GoToRecord
acForm, meName, acNewRec", this does generate a new Record in the Table
and
present the user with a series of blank Fields to complete but any
information that is enterd into the blank fields is inserted into the
previous record.

I hope that this helps.
Thanks for your patience
RayC

"Arvin Meyer [MVP]" wrote:

That depends upon the constant you use:

acDataErrDisplay - (Default) Displays the default message to the
user.
You can use this when you don't want to allow the user to add a new
value
to
the combo box list.

acDataErrContinue - Doesn't display the default message to the
user.
You can use this when you want to display a custom message to the
user.
For
example, the event procedure could display a custom dialog box asking
if
the
user wanted to save the new entry. If the response is Yes, the event
procedure would add the new entry to the list and set the Response
argument
to acDataErrAdded. If the response is No, the event procedure would
set
the
Response argument to acDataErrContinue.

acDataErrAdded - Doesn't display a message to the user but
enables
you to add the entry to the combo box list in the NotInList event
procedure.
After the entry is added, Microsoft Access updates the list by
requerying
the combo box. Microsoft Access then rechecks the string against the
combo
box list, and saves the value in the NewData argument in the field the
combo
box is bound to. If the string is not in the list, then Microsoft
Access
displays an error message.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"Ray C" wrote in message
...
Hi Arvin, thanks for coming back to me, it is much appreciated.

I don't think that I need the generic NotinList code, I have that. I
think
I
need to understand if the NotinList code will add a new record to
the
Table
or of it will modify the Drop Down List only. Forgive me pleas, I
get
confused when dealing with Tables and Forms.
Thanks RayC


"Arvin Meyer [MVP]" wrote:

If you're looking for generic NotInList code:

http://www.datastrat.com/Code/NotInListCode.txt

or a demo of that code is at:

http://accessmvp.com/Arvin/NotInListDemo.zip
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"Ray C" wrote in message
...
Can anyone help point out what i am doing wrong Please?
I want to generata a new record and I get to that point by having
the
user
enter a search for a record in a Table via a combo. When the
record
is
Found
in the Table, no problem The user can view the Record, Edit it,
etc.
When
the record is not Found in the Table, the "not in List" event
triggers
the
question "Not Found, Do You Want To Generate"? The answer "Yes"
triggers
the
Code
DoCmd.accNewRecord acForm,Me.Name,acNewRec
My Form then displays a blank series of Fields (Name Address,
etc)
and
my
thought proccess is to just tell the program to go into my normal
"Record
Edit Routine" to enter the information into the Blank Fields of
what
I
think
is the new Record.
HOWEVER, when I look at the Table the new record is there but the
informatiion I enter via my Edit Routine is saved in the previous
Records@
Fields.

Its as though the new record is generated after I have edited the
Previouse
Record's Fields (though the previous record's information is not
displayed
when I am editing what i think is the new record). Am I missing
something
like "Requery" or Append or something similar to fix the New
Record
as
the
New Last Record?

Everything worls fine EXCEPT when I look at the table, there is a
new
Record
under the name that i originally entered but any subsequent
information
i
entered into the blank Records are there BUT they are held in the
previous
records fields.
Any Help appreciated.

Regards Ray C


.



.



.



  #12  
Old April 7th, 2010, 12:39 PM posted to microsoft.public.access.gettingstarted
Ray C
external usenet poster
 
Posts: 215
Default Generating a new Record

Hi Arvin, Sorry for my Typo leading you astray, the code is "New Data" and
not "strNewData". in either event, the new data that is not currently a
record is passed to the form that you open in your code and does get appended
to the table as a new record. My issie is with the Requery command and where
that goes. as I tried nto explain in the body of my last message. I seem to
get errors wherever I put the "Requery" comand and without running "Requery"
somewhere after closing the form that is used to enter the new record
information, the new information (that is in the table) is not shown in the
combo box.

Hope that this makes sense.

Ray C

"Arvin Meyer [MVP]" wrote:

strNewData should be NewData
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"Ray C" wrote in message
...
Hi Arvin, still struggling I am afraid. Although I now have the new
record
being added to the table correctly (using your code), I get errors when i
try
to run the requery

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

AddToList "frm_Store_Add", "StoreName", strNewData, Response ' Arvin
Mayer code
' Me.cmb_Find_Box_3.Requery
End Sub

I have the requery in the cmb_Find_Box_3_after update event but that
routine
does not get called (though it does in your test code???)
If I enter some dats that is not in the table (more correctly the list
that
is based on the table), the NotInList routine kicks in and the program
happilt toddles off to generate the new record (using your code). When it
returns from that task, you will see that i have
"Me.cmb_Find_Box_3.Requery.
However when the program gets to that line I get an error that says
"Run-Time
Error 2118" "You must save thr currentit feld before you run the requery
action"
If I take out that line, I get an error that says "The Text you entered
is
not an item in the list"
Either way, the table shows that my New Record is added but the record
does
not show as a new item in the Combo drop Down list.
Clearly it is a problem with the requery commant but why does your test
code
work ok bur mine gives problems? I guess the answer to that is just that
you
are the expert and i am the novice.
Thanks Arvin

Ray C




"Arvin Meyer [MVP]" wrote:

Ray,

The example at:

http://accessmvp.com/Arvin/NotInListDemo.zip

is eaxctly what you want. Try importing everything into your database and
changing all the names to fit with your names.

You won't be able to do both fields with a single combo box, so you'll
need
2 of them. I recommend getting 1 to work, then copying everything with
the
new names. The generic code function I listed is perfect for you, because
you can make the same NotInList function work with both combo boxes.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access

"Ray C" wrote in message
...
Hi Arvin, I am not sure if I explained my problem badly or if I am just
too
dumb to understand your solution but I have tried the code that you
provided
and I can not get it to generate a new Record in my Table.
Please let me try to explain my problem again and i would be pleased if
you
could give me any pointers as to where i am going wrong in my
understanding
of your answer.

I have a pretty standard table holding property information (Address
etc)
but eac property has a NameAddress info. The Table holdsname and
addresswith
the Name (normaly the Town where it is) and also a Property Number
(provided
by the Owner). The table has an "Auto Number Index" and holds other
information about the property. Because the user may wish to search the
Table
by either the property name or the property number, I have two combo
boxes
that providr drop down's for the user to select as needed. Once the
user
has
selected the required property "Name" or the required property
"Number" i
have a ruotine that searches through the Address Table, finds the
record
and
displays all the fields in that record. All of that is fine.
What I am wanting to do is for the user to be given the ability to Add
a
New
Record to the Table if the information they entered in the Combo Box is
not
found in the Address Table.

My Current attempt at a sollution to this is to use the notIn List
event
to
branch off to a routine that should generate a new *Blank" record that
will
be appended to the end of the adress Table, and show the user a form
full
of
Blank Fields (with the exception of the information they originally
searched
for). The usr should then fill in the blank fields before saving (or
rejecting) the "New Record" appended to the end of the Table.

As I tried to explain originally I experimented with the "DoCmd
GoToRecord
acForm, meName, acNewRec", this does generate a new Record in the Table
and
present the user with a series of blank Fields to complete but any
information that is enterd into the blank fields is inserted into the
previous record.

I hope that this helps.
Thanks for your patience
RayC

"Arvin Meyer [MVP]" wrote:

That depends upon the constant you use:

acDataErrDisplay - (Default) Displays the default message to the
user.
You can use this when you don't want to allow the user to add a new
value
to
the combo box list.

acDataErrContinue - Doesn't display the default message to the
user.
You can use this when you want to display a custom message to the
user.
For
example, the event procedure could display a custom dialog box asking
if
the
user wanted to save the new entry. If the response is Yes, the event
procedure would add the new entry to the list and set the Response
argument
to acDataErrAdded. If the response is No, the event procedure would
set
the
Response argument to acDataErrContinue.

acDataErrAdded - Doesn't display a message to the user but
enables
you to add the entry to the combo box list in the NotInList event
procedure.
After the entry is added, Microsoft Access updates the list by
requerying
the combo box. Microsoft Access then rechecks the string against the
combo
box list, and saves the value in the NewData argument in the field the
combo
box is bound to. If the string is not in the list, then Microsoft
Access
displays an error message.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"Ray C" wrote in message
...
Hi Arvin, thanks for coming back to me, it is much appreciated.

I don't think that I need the generic NotinList code, I have that. I
think
I
need to understand if the NotinList code will add a new record to
the
Table
or of it will modify the Drop Down List only. Forgive me pleas, I
get
confused when dealing with Tables and Forms.
Thanks RayC


"Arvin Meyer [MVP]" wrote:

If you're looking for generic NotInList code:

http://www.datastrat.com/Code/NotInListCode.txt

or a demo of that code is at:

http://accessmvp.com/Arvin/NotInListDemo.zip
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"Ray C" wrote in message
...
Can anyone help point out what i am doing wrong Please?
I want to generata a new record and I get to that point by having
the
user
enter a search for a record in a Table via a combo. When the
record
is
Found
in the Table, no problem The user can view the Record, Edit it,
etc.
When
the record is not Found in the Table, the "not in List" event
triggers
the
question "Not Found, Do You Want To Generate"? The answer "Yes"
triggers
the
Code
DoCmd.accNewRecord acForm,Me.Name,acNewRec
My Form then displays a blank series of Fields (Name Address,
etc)
and
my
thought proccess is to just tell the program to go into my normal
"Record
Edit Routine" to enter the information into the Blank Fields of
what
I
think
is the new Record.
HOWEVER, when I look at the Table the new record is there but the
informatiion I enter via my Edit Routine is saved in the previous
Records@
Fields.

Its as though the new record is generated after I have edited the
Previouse
Record's Fields (though the previous record's information is not
displayed
when I am editing what i think is the new record). Am I missing
something
like "Requery" or Append or something similar to fix the New
Record
as
the
New Last Record?

Everything worls fine EXCEPT when I look at the table, there is a
new
Record
under the name that i originally entered but any subsequent
information
i
entered into the blank Records are there BUT they are held in the
previous
records fields.
Any Help appreciated.

Regards Ray C


.



.



.



.

  #13  
Old April 7th, 2010, 04:37 PM posted to microsoft.public.access.gettingstarted
Arvin Meyer [MVP][_2_]
external usenet poster
 
Posts: 2,310
Default Generating a new Record

In the module there is the line:

Response = acDataErrAdded

That adds the entry into your combo box. You DO NOT need to requery the
combo. I suggest that you use the demo code and forms at:

http://accessmvp.com/Arvin/NotInListDemo.zip

To see how it works, then copy the exact code into each of your forms and
change the names of the form and fields to match yours. NewData and Response
do not get changed. They are intrinsic to the code.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access

"Ray C" wrote in message
...
Hi Arvin, Sorry for my Typo leading you astray, the code is "New Data" and
not "strNewData". in either event, the new data that is not currently a
record is passed to the form that you open in your code and does get
appended
to the table as a new record. My issie is with the Requery command and
where
that goes. as I tried nto explain in the body of my last message. I seem
to
get errors wherever I put the "Requery" comand and without running
"Requery"
somewhere after closing the form that is used to enter the new record
information, the new information (that is in the table) is not shown in
the
combo box.

Hope that this makes sense.

Ray C

"Arvin Meyer [MVP]" wrote:

strNewData should be NewData
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"Ray C" wrote in message
...
Hi Arvin, still struggling I am afraid. Although I now have the new
record
being added to the table correctly (using your code), I get errors when
i
try
to run the requery

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

AddToList "frm_Store_Add", "StoreName", strNewData, Response '
Arvin
Mayer code
' Me.cmb_Find_Box_3.Requery
End Sub

I have the requery in the cmb_Find_Box_3_after update event but that
routine
does not get called (though it does in your test code???)
If I enter some dats that is not in the table (more correctly the list
that
is based on the table), the NotInList routine kicks in and the program
happilt toddles off to generate the new record (using your code). When
it
returns from that task, you will see that i have
"Me.cmb_Find_Box_3.Requery.
However when the program gets to that line I get an error that says
"Run-Time
Error 2118" "You must save thr currentit feld before you run the
requery
action"
If I take out that line, I get an error that says "The Text you
entered
is
not an item in the list"
Either way, the table shows that my New Record is added but the record
does
not show as a new item in the Combo drop Down list.
Clearly it is a problem with the requery commant but why does your test
code
work ok bur mine gives problems? I guess the answer to that is just
that
you
are the expert and i am the novice.
Thanks Arvin

Ray C




"Arvin Meyer [MVP]" wrote:

Ray,

The example at:

http://accessmvp.com/Arvin/NotInListDemo.zip

is eaxctly what you want. Try importing everything into your database
and
changing all the names to fit with your names.

You won't be able to do both fields with a single combo box, so you'll
need
2 of them. I recommend getting 1 to work, then copying everything with
the
new names. The generic code function I listed is perfect for you,
because
you can make the same NotInList function work with both combo boxes.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access

"Ray C" wrote in message
...
Hi Arvin, I am not sure if I explained my problem badly or if I am
just
too
dumb to understand your solution but I have tried the code that you
provided
and I can not get it to generate a new Record in my Table.
Please let me try to explain my problem again and i would be pleased
if
you
could give me any pointers as to where i am going wrong in my
understanding
of your answer.

I have a pretty standard table holding property information (Address
etc)
but eac property has a NameAddress info. The Table holdsname and
addresswith
the Name (normaly the Town where it is) and also a Property Number
(provided
by the Owner). The table has an "Auto Number Index" and holds other
information about the property. Because the user may wish to search
the
Table
by either the property name or the property number, I have two combo
boxes
that providr drop down's for the user to select as needed. Once the
user
has
selected the required property "Name" or the required property
"Number" i
have a ruotine that searches through the Address Table, finds the
record
and
displays all the fields in that record. All of that is fine.
What I am wanting to do is for the user to be given the ability to
Add
a
New
Record to the Table if the information they entered in the Combo Box
is
not
found in the Address Table.

My Current attempt at a sollution to this is to use the notIn List
event
to
branch off to a routine that should generate a new *Blank" record
that
will
be appended to the end of the adress Table, and show the user a form
full
of
Blank Fields (with the exception of the information they originally
searched
for). The usr should then fill in the blank fields before saving (or
rejecting) the "New Record" appended to the end of the Table.

As I tried to explain originally I experimented with the "DoCmd
GoToRecord
acForm, meName, acNewRec", this does generate a new Record in the
Table
and
present the user with a series of blank Fields to complete but any
information that is enterd into the blank fields is inserted into
the
previous record.

I hope that this helps.
Thanks for your patience
RayC

"Arvin Meyer [MVP]" wrote:

That depends upon the constant you use:

acDataErrDisplay - (Default) Displays the default message to
the
user.
You can use this when you don't want to allow the user to add a new
value
to
the combo box list.

acDataErrContinue - Doesn't display the default message to
the
user.
You can use this when you want to display a custom message to the
user.
For
example, the event procedure could display a custom dialog box
asking
if
the
user wanted to save the new entry. If the response is Yes, the
event
procedure would add the new entry to the list and set the Response
argument
to acDataErrAdded. If the response is No, the event procedure would
set
the
Response argument to acDataErrContinue.

acDataErrAdded - Doesn't display a message to the user but
enables
you to add the entry to the combo box list in the NotInList event
procedure.
After the entry is added, Microsoft Access updates the list by
requerying
the combo box. Microsoft Access then rechecks the string against
the
combo
box list, and saves the value in the NewData argument in the field
the
combo
box is bound to. If the string is not in the list, then Microsoft
Access
displays an error message.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"Ray C" wrote in message
...
Hi Arvin, thanks for coming back to me, it is much appreciated.

I don't think that I need the generic NotinList code, I have
that. I
think
I
need to understand if the NotinList code will add a new record to
the
Table
or of it will modify the Drop Down List only. Forgive me pleas, I
get
confused when dealing with Tables and Forms.
Thanks RayC


"Arvin Meyer [MVP]" wrote:

If you're looking for generic NotInList code:

http://www.datastrat.com/Code/NotInListCode.txt

or a demo of that code is at:

http://accessmvp.com/Arvin/NotInListDemo.zip
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"Ray C" wrote in message
...
Can anyone help point out what i am doing wrong Please?
I want to generata a new record and I get to that point by
having
the
user
enter a search for a record in a Table via a combo. When the
record
is
Found
in the Table, no problem The user can view the Record, Edit
it,
etc.
When
the record is not Found in the Table, the "not in List" event
triggers
the
question "Not Found, Do You Want To Generate"? The answer
"Yes"
triggers
the
Code
DoCmd.accNewRecord acForm,Me.Name,acNewRec
My Form then displays a blank series of Fields (Name Address,
etc)
and
my
thought proccess is to just tell the program to go into my
normal
"Record
Edit Routine" to enter the information into the Blank Fields
of
what
I
think
is the new Record.
HOWEVER, when I look at the Table the new record is there but
the
informatiion I enter via my Edit Routine is saved in the
previous
Records@
Fields.

Its as though the new record is generated after I have edited
the
Previouse
Record's Fields (though the previous record's information is
not
displayed
when I am editing what i think is the new record). Am I
missing
something
like "Requery" or Append or something similar to fix the New
Record
as
the
New Last Record?

Everything worls fine EXCEPT when I look at the table, there
is a
new
Record
under the name that i originally entered but any subsequent
information
i
entered into the blank Records are there BUT they are held in
the
previous
records fields.
Any Help appreciated.

Regards Ray C


.



.



.



.



  #14  
Old April 12th, 2010, 11:10 AM posted to microsoft.public.access.gettingstarted
Ray C
external usenet poster
 
Posts: 215
Default Generating a new Record

Hi Arvin
In your demo code, you have the sub

Private Sub cmb_Find_Box_3_AfterUpdate()

Me.cmb_Find_Box_3.Requery

End Sub
in your "frmEvents" (I changed the name of the Combo Box to fit my code)
When I integrated your code into mine. I found that the Record was generated
in the table OK but I could not find the record afterwards using my normal
enquiry.
What I needed to do was to shut down and re open the whole database and only
then could I find the new record with my enquiry.

What I have done is tto add the following line

Private Sub cmb_Find_Box_3_AfterUpdate()

Me.cmb_Find_Box_3.Requery
ME.Requery
End Sub
and this seems to have sorted the problem out. Does this sound right to you?

Thanks and regards RayC


"Arvin Meyer [MVP]" wrote:

In the module there is the line:

Response = acDataErrAdded

That adds the entry into your combo box. You DO NOT need to requery the
combo. I suggest that you use the demo code and forms at:

http://accessmvp.com/Arvin/NotInListDemo.zip

To see how it works, then copy the exact code into each of your forms and
change the names of the form and fields to match yours. NewData and Response
do not get changed. They are intrinsic to the code.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access

"Ray C" wrote in message
...
Hi Arvin, Sorry for my Typo leading you astray, the code is "New Data" and
not "strNewData". in either event, the new data that is not currently a
record is passed to the form that you open in your code and does get
appended
to the table as a new record. My issie is with the Requery command and
where
that goes. as I tried nto explain in the body of my last message. I seem
to
get errors wherever I put the "Requery" comand and without running
"Requery"
somewhere after closing the form that is used to enter the new record
information, the new information (that is in the table) is not shown in
the
combo box.

Hope that this makes sense.

Ray C

"Arvin Meyer [MVP]" wrote:

strNewData should be NewData
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"Ray C" wrote in message
...
Hi Arvin, still struggling I am afraid. Although I now have the new
record
being added to the table correctly (using your code), I get errors when
i
try
to run the requery

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

AddToList "frm_Store_Add", "StoreName", strNewData, Response '
Arvin
Mayer code
' Me.cmb_Find_Box_3.Requery
End Sub

I have the requery in the cmb_Find_Box_3_after update event but that
routine
does not get called (though it does in your test code???)
If I enter some dats that is not in the table (more correctly the list
that
is based on the table), the NotInList routine kicks in and the program
happilt toddles off to generate the new record (using your code). When
it
returns from that task, you will see that i have
"Me.cmb_Find_Box_3.Requery.
However when the program gets to that line I get an error that says
"Run-Time
Error 2118" "You must save thr currentit feld before you run the
requery
action"
If I take out that line, I get an error that says "The Text you
entered
is
not an item in the list"
Either way, the table shows that my New Record is added but the record
does
not show as a new item in the Combo drop Down list.
Clearly it is a problem with the requery commant but why does your test
code
work ok bur mine gives problems? I guess the answer to that is just
that
you
are the expert and i am the novice.
Thanks Arvin

Ray C




"Arvin Meyer [MVP]" wrote:

Ray,

The example at:

http://accessmvp.com/Arvin/NotInListDemo.zip

is eaxctly what you want. Try importing everything into your database
and
changing all the names to fit with your names.

You won't be able to do both fields with a single combo box, so you'll
need
2 of them. I recommend getting 1 to work, then copying everything with
the
new names. The generic code function I listed is perfect for you,
because
you can make the same NotInList function work with both combo boxes.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access

"Ray C" wrote in message
...
Hi Arvin, I am not sure if I explained my problem badly or if I am
just
too
dumb to understand your solution but I have tried the code that you
provided
and I can not get it to generate a new Record in my Table.
Please let me try to explain my problem again and i would be pleased
if
you
could give me any pointers as to where i am going wrong in my
understanding
of your answer.

I have a pretty standard table holding property information (Address
etc)
but eac property has a NameAddress info. The Table holdsname and
addresswith
the Name (normaly the Town where it is) and also a Property Number
(provided
by the Owner). The table has an "Auto Number Index" and holds other
information about the property. Because the user may wish to search
the
Table
by either the property name or the property number, I have two combo
boxes
that providr drop down's for the user to select as needed. Once the
user
has
selected the required property "Name" or the required property
"Number" i
have a ruotine that searches through the Address Table, finds the
record
and
displays all the fields in that record. All of that is fine.
What I am wanting to do is for the user to be given the ability to
Add
a
New
Record to the Table if the information they entered in the Combo Box
is
not
found in the Address Table.

My Current attempt at a sollution to this is to use the notIn List
event
to
branch off to a routine that should generate a new *Blank" record
that
will
be appended to the end of the adress Table, and show the user a form
full
of
Blank Fields (with the exception of the information they originally
searched
for). The usr should then fill in the blank fields before saving (or
rejecting) the "New Record" appended to the end of the Table.

As I tried to explain originally I experimented with the "DoCmd
GoToRecord
acForm, meName, acNewRec", this does generate a new Record in the
Table
and
present the user with a series of blank Fields to complete but any
information that is enterd into the blank fields is inserted into
the
previous record.

I hope that this helps.
Thanks for your patience
RayC

"Arvin Meyer [MVP]" wrote:

That depends upon the constant you use:

acDataErrDisplay - (Default) Displays the default message to
the
user.
You can use this when you don't want to allow the user to add a new
value
to
the combo box list.

acDataErrContinue - Doesn't display the default message to
the
user.
You can use this when you want to display a custom message to the
user.
For
example, the event procedure could display a custom dialog box
asking
if
the
user wanted to save the new entry. If the response is Yes, the
event
procedure would add the new entry to the list and set the Response
argument
to acDataErrAdded. If the response is No, the event procedure would
set
the
Response argument to acDataErrContinue.

acDataErrAdded - Doesn't display a message to the user but
enables
you to add the entry to the combo box list in the NotInList event
procedure.
After the entry is added, Microsoft Access updates the list by
requerying
the combo box. Microsoft Access then rechecks the string against
the
combo
box list, and saves the value in the NewData argument in the field
the
combo
box is bound to. If the string is not in the list, then Microsoft
Access
displays an error message.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"Ray C" wrote in message
...
Hi Arvin, thanks for coming back to me, it is much appreciated.

I don't think that I need the generic NotinList code, I have
that. I
think
I
need to understand if the NotinList code will add a new record to
the
Table
or of it will modify the Drop Down List only. Forgive me pleas, I
get
confused when dealing with Tables and Forms.
Thanks RayC


"Arvin Meyer [MVP]" wrote:

If you're looking for generic NotInList code:

http://www.datastrat.com/Code/NotInListCode.txt

or a demo of that code is at:

http://accessmvp.com/Arvin/NotInListDemo.zip
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"Ray C" wrote in message
...
Can anyone help point out what i am doing wrong Please?
I want to generata a new record and I get to that point by
having
the
user
enter a search for a record in a Table via a combo. When the
record
is
Found
in the Table, no problem The user can view the Record, Edit
it,
etc.

  #15  
Old April 25th, 2010, 05:03 PM posted to microsoft.public.access.gettingstarted
Arvin Meyer [MVP][_2_]
external usenet poster
 
Posts: 2,310
Default Generating a new Record

It shouldn't be necessary, but if it works for you, go with it.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"Ray C" wrote in message
...
Hi Arvin
In your demo code, you have the sub

Private Sub cmb_Find_Box_3_AfterUpdate()

Me.cmb_Find_Box_3.Requery

End Sub
in your "frmEvents" (I changed the name of the Combo Box to fit my code)
When I integrated your code into mine. I found that the Record was
generated
in the table OK but I could not find the record afterwards using my normal
enquiry.
What I needed to do was to shut down and re open the whole database and
only
then could I find the new record with my enquiry.

What I have done is tto add the following line

Private Sub cmb_Find_Box_3_AfterUpdate()

Me.cmb_Find_Box_3.Requery
ME.Requery
End Sub
and this seems to have sorted the problem out. Does this sound right to
you?

Thanks and regards RayC


"Arvin Meyer [MVP]" wrote:

In the module there is the line:

Response = acDataErrAdded

That adds the entry into your combo box. You DO NOT need to requery the
combo. I suggest that you use the demo code and forms at:

http://accessmvp.com/Arvin/NotInListDemo.zip

To see how it works, then copy the exact code into each of your forms and
change the names of the form and fields to match yours. NewData and
Response
do not get changed. They are intrinsic to the code.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access

"Ray C" wrote in message
...
Hi Arvin, Sorry for my Typo leading you astray, the code is "New Data"
and
not "strNewData". in either event, the new data that is not currently a
record is passed to the form that you open in your code and does get
appended
to the table as a new record. My issie is with the Requery command and
where
that goes. as I tried nto explain in the body of my last message. I
seem
to
get errors wherever I put the "Requery" comand and without running
"Requery"
somewhere after closing the form that is used to enter the new record
information, the new information (that is in the table) is not shown in
the
combo box.

Hope that this makes sense.

Ray C

"Arvin Meyer [MVP]" wrote:

strNewData should be NewData
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"Ray C" wrote in message
...
Hi Arvin, still struggling I am afraid. Although I now have the new
record
being added to the table correctly (using your code), I get errors
when
i
try
to run the requery

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

AddToList "frm_Store_Add", "StoreName", strNewData, Response '
Arvin
Mayer code
' Me.cmb_Find_Box_3.Requery
End Sub

I have the requery in the cmb_Find_Box_3_after update event but that
routine
does not get called (though it does in your test code???)
If I enter some dats that is not in the table (more correctly the
list
that
is based on the table), the NotInList routine kicks in and the
program
happilt toddles off to generate the new record (using your code).
When
it
returns from that task, you will see that i have
"Me.cmb_Find_Box_3.Requery.
However when the program gets to that line I get an error that says
"Run-Time
Error 2118" "You must save thr currentit feld before you run the
requery
action"
If I take out that line, I get an error that says "The Text you
entered
is
not an item in the list"
Either way, the table shows that my New Record is added but the
record
does
not show as a new item in the Combo drop Down list.
Clearly it is a problem with the requery commant but why does your
test
code
work ok bur mine gives problems? I guess the answer to that is just
that
you
are the expert and i am the novice.
Thanks Arvin

Ray C




"Arvin Meyer [MVP]" wrote:

Ray,

The example at:

http://accessmvp.com/Arvin/NotInListDemo.zip

is eaxctly what you want. Try importing everything into your
database
and
changing all the names to fit with your names.

You won't be able to do both fields with a single combo box, so
you'll
need
2 of them. I recommend getting 1 to work, then copying everything
with
the
new names. The generic code function I listed is perfect for you,
because
you can make the same NotInList function work with both combo
boxes.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access

"Ray C" wrote in message
...
Hi Arvin, I am not sure if I explained my problem badly or if I
am
just
too
dumb to understand your solution but I have tried the code that
you
provided
and I can not get it to generate a new Record in my Table.
Please let me try to explain my problem again and i would be
pleased
if
you
could give me any pointers as to where i am going wrong in my
understanding
of your answer.

I have a pretty standard table holding property information
(Address
etc)
but eac property has a NameAddress info. The Table holdsname and
addresswith
the Name (normaly the Town where it is) and also a Property
Number
(provided
by the Owner). The table has an "Auto Number Index" and holds
other
information about the property. Because the user may wish to
search
the
Table
by either the property name or the property number, I have two
combo
boxes
that providr drop down's for the user to select as needed. Once
the
user
has
selected the required property "Name" or the required property
"Number" i
have a ruotine that searches through the Address Table, finds the
record
and
displays all the fields in that record. All of that is fine.
What I am wanting to do is for the user to be given the ability
to
Add
a
New
Record to the Table if the information they entered in the Combo
Box
is
not
found in the Address Table.

My Current attempt at a sollution to this is to use the notIn
List
event
to
branch off to a routine that should generate a new *Blank" record
that
will
be appended to the end of the adress Table, and show the user a
form
full
of
Blank Fields (with the exception of the information they
originally
searched
for). The usr should then fill in the blank fields before saving
(or
rejecting) the "New Record" appended to the end of the Table.

As I tried to explain originally I experimented with the "DoCmd
GoToRecord
acForm, meName, acNewRec", this does generate a new Record in the
Table
and
present the user with a series of blank Fields to complete but
any
information that is enterd into the blank fields is inserted into
the
previous record.

I hope that this helps.
Thanks for your patience
RayC

"Arvin Meyer [MVP]" wrote:

That depends upon the constant you use:

acDataErrDisplay - (Default) Displays the default message
to
the
user.
You can use this when you don't want to allow the user to add a
new
value
to
the combo box list.

acDataErrContinue - Doesn't display the default message to
the
user.
You can use this when you want to display a custom message to
the
user.
For
example, the event procedure could display a custom dialog box
asking
if
the
user wanted to save the new entry. If the response is Yes, the
event
procedure would add the new entry to the list and set the
Response
argument
to acDataErrAdded. If the response is No, the event procedure
would
set
the
Response argument to acDataErrContinue.

acDataErrAdded - Doesn't display a message to the user
but
enables
you to add the entry to the combo box list in the NotInList
event
procedure.
After the entry is added, Microsoft Access updates the list by
requerying
the combo box. Microsoft Access then rechecks the string against
the
combo
box list, and saves the value in the NewData argument in the
field
the
combo
box is bound to. If the string is not in the list, then
Microsoft
Access
displays an error message.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"Ray C" wrote in message
...
Hi Arvin, thanks for coming back to me, it is much
appreciated.

I don't think that I need the generic NotinList code, I have
that. I
think
I
need to understand if the NotinList code will add a new record
to
the
Table
or of it will modify the Drop Down List only. Forgive me
pleas, I
get
confused when dealing with Tables and Forms.
Thanks RayC


"Arvin Meyer [MVP]" wrote:

If you're looking for generic NotInList code:

http://www.datastrat.com/Code/NotInListCode.txt

or a demo of that code is at:

http://accessmvp.com/Arvin/NotInListDemo.zip
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"Ray C" wrote in message
...
Can anyone help point out what i am doing wrong Please?
I want to generata a new record and I get to that point by
having
the
user
enter a search for a record in a Table via a combo. When
the
record
is
Found
in the Table, no problem The user can view the Record, Edit
it,
etc.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:25 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.