Generating a new Record

R

Ray C

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
 
R

Ray C

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
 
A

Arvin Meyer [MVP]

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
 
R

Ray C

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
 
S

Steve

Hello Ray,

A simpler way to do what you want is to make the rowsource of both
comboboxes a query based on your address table. Only include property "Name"
and the required property "Number". In the query, right click in the query
window where the address table is and click on Properties. Set Unique Values
to Yes. Now in either combobox if the user does not find the Name or Number,
in the drop down list, he automatically knows the address is not in the
address table. So you provide a button near the comboboxes to open your form
for entering new addresses in the address table. In the code for the button,
you can have it so when the form for entering new records closes, you
automatically requery both comboboxes so the new address will show up in
both comboboxes.

Steve
(e-mail address removed)
 
A

Arvin Meyer [MVP]

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
 
R

Ray C

Hi Arvin
Some of the mist and gloom is starting to clear away and I think I have a
bit of a handle on where to go here. Thank you so much for the extract of the
working code, it helps so much.

Thanks also to Steve, his suggestion looks good also. I will while away a
few hours over the easter weekend runing through your code and also
incorporating Steves input.

Once again, thank you both so much.

Regards RayC
 
R

Ray C

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
 
R

Ray C

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
 
A

Arvin Meyer [MVP]

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
 
R

Ray C

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Add New Record 7
Access Automatically Send a notification email once a record is added 0
Can't go to new record 1
Enable All Fields When Creating New Record 2
Add New Record Command Button 7
Copy record in one form into a new pop-up form 0
Blank Record 4
Create a button to edit and save new record 6

Top