Edit/Add New Record - Never got helpful response first posting

I have a form that I want to use as both a data entry and a data
update form. I want the form to open up as a new record. When the user
enters the Order ID number in the OrderID box and hits enter or exits
that box and goes to another box, I want a query to run that goes out
to the Orders table and queries whether that Order ID number exists in
the table. (OrderID is the primary key). If it does, I want the rest
of the forms to populate with the information in the form. If not, I
want the user to be able to enter the new data into the new record.

Some info that may be of help.

Table name = Orders
Primary key = OrdersID
Form Name = OrdersForm
Field that is doing search = OrdersID
Some of the field names to be populated if OrdersID entered already
exist:
     ComapnyName
     CompnayID
     CompanyAddress
     ComapanyCity
etc.

I posted something similar once before and got a response telling me
not to have the data entry and data modify form the same, but I really
want it that way. I do not see why having the form perform both tasks
would be such a problem.

Can anyone help me here? I am at a loss as to how to do this? I can't
get existing data to display in the form.

I am using Access 2003.

Thanks in advance for any and all help.
0
Debbiedo
1/16/2008 11:50:23 PM
access.forms 6864 articles. 2 followers. Follow

5 Replies
1126 Views

Similar Articles

[PageSpeed] 29

>>I do not see why having the form perform both tasks would be such a problem.
It is designed that way.  If you open the form for data entry it WILL NOT 
display existing records.
You can open the form for editing with a query the has criteria from an 
unbound textbox that you enter your Order ID number in the 'Find OrderID' box 
and hits enter or exits that box and goes to another box. It calls an event 
or macro to requery a query to run that goes out to the Orders table and 
queries whether that Order ID number exists in the table. If it exist then it 
pulls up the record.  If it does not then it create a new record, filling in 
the OrderID.
-- 
KARL DEWEY
Build a little - Test a little


"Debbiedo" wrote:

> I have a form that I want to use as both a data entry and a data
> update form. I want the form to open up as a new record. When the user
> enters the Order ID number in the OrderID box and hits enter or exits
> that box and goes to another box, I want a query to run that goes out
> to the Orders table and queries whether that Order ID number exists in
> the table. (OrderID is the primary key). If it does, I want the rest
> of the forms to populate with the information in the form. If not, I
> want the user to be able to enter the new data into the new record.
> 
> Some info that may be of help.
> 
> Table name = Orders
> Primary key = OrdersID
> Form Name = OrdersForm
> Field that is doing search = OrdersID
> Some of the field names to be populated if OrdersID entered already
> exist:
>      ComapnyName
>      CompnayID
>      CompanyAddress
>      ComapanyCity
> etc.
> 
> I posted something similar once before and got a response telling me
> not to have the data entry and data modify form the same, but I really
> want it that way. I do not see why having the form perform both tasks
> would be such a problem.
> 
> Can anyone help me here? I am at a loss as to how to do this? I can't
> get existing data to display in the form.
> 
> I am using Access 2003.
> 
> Thanks in advance for any and all help.
> 
0
Utf
1/17/2008 12:25:00 AM
To allow you to see your existing records, set the form's AllowEdits,
AllowDeletes and AllowAdditions to YES.

To make the form open on a new record, use this code:

Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec
End Sub

I used the object names you've given for form, table and CompanyID field, and
ran up a test database, so this has been tested and works. 

If the textbox holding the CompanyID field is also named CompanyID, change it
to txtCompanyID. Your control name on the form should never be identical to
the underlying field name, even though Access will do this if you make the
form with the wizard or if you drag the fields from the field list to the
form. 

If you already have a different name for the textbox, substitute it in the
code below in place of txtCompanyID. This code assumes that the CompanyID is
defined in the underlying table as datatype Text. Even if it consists of all
digits, it doesn't/shouldn't be numeric. Only "numbers" used for math
should/need to be numeric datatype.  

Private Sub txtCompanyID_AfterUpdate()
Dim StrCriteria As String

If Me.NewRecord Then
 If DCount("CompanyID", "Orders", "[CompanyID] = '" & Me.txtCompanyID & "'")
< 1 Then
   'ID doesn't exist... continue entering new record
 Else
 StrCriteria = Me.txtCompanyID
 Me.Undo
  Set rs = Me.Recordset.Clone
    rs.FindFirst "[CompanyID] = '" & StrCriteria & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
 End If
End If
End Sub

Linq

-- 
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via http://www.accessmonster.com

0
Linq
1/17/2008 4:17:54 AM
On Jan 16, 8:17=A0pm, "Linq Adams via AccessMonster.com" <u28780@uwe>
wrote:
> To allow you to see your existing records, set the form's AllowEdits,
> AllowDeletes and AllowAdditions to YES.
>
> To make the form open on a new record, use this code:
>
> Private Sub Form_Load()
> DoCmd.GoToRecord , , acNewRec
> End Sub
>
> I used the object names you've given for form, table and CompanyID field, =
and
> ran up a test database, so this has been tested and works.
>
> If the textbox holding the CompanyID field is also named CompanyID, change=
 it
> to txtCompanyID. Your control name on the form should never be identical t=
o
> the underlying field name, even though Access will do this if you make the=

> form with the wizard or if you drag the fields from the field list to the
> form.
>
> If you already have a different name for the textbox, substitute it in the=

> code below in place of txtCompanyID. This code assumes that the CompanyID =
is
> defined in the underlying table as datatype Text. Even if it consists of a=
ll
> digits, it doesn't/shouldn't be numeric. Only "numbers" used for math
> should/need to be numeric datatype. =A0
>
> Private Sub txtCompanyID_AfterUpdate()
> Dim StrCriteria As String
>
> If Me.NewRecord Then
> =A0If DCount("CompanyID", "Orders", "[CompanyID] =3D '" & Me.txtCompanyID =
& "'")
> < 1 Then
> =A0 =A0'ID doesn't exist... continue entering new record
> =A0Else
> =A0StrCriteria =3D Me.txtCompanyID
> =A0Me.Undo
> =A0 Set rs =3D Me.Recordset.Clone
> =A0 =A0 rs.FindFirst "[CompanyID] =3D '" & StrCriteria & "'"
> =A0 =A0 If Not rs.EOF Then Me.Bookmark =3D rs.Bookmark
> =A0End If
> End If
> End Sub
>
> Linq
>
> --
> There's ALWAYS more than one way to skin a cat!
>
> Answers/posts based on Access 2000/2003
>
> Message posted viahttp://www.accessmonster.com

I changed the code and am now able to edit pre-existing records;
however,  if the data already existed, it does not display the
additional data fields in the form.

To clarify, if Order ID 123 already existed in the Orders table, I can
now edit it without getting that annoying primary key violation error
message upon exit, but the company name, company address etc. are not
populated on the form AfterUpdate on the txtOrdersID. These fields
remain blank.

How can I get the values of these fields to display when I enter an
existing Order ID number?

BTW, I apprecaited your very clear instructions and explanations Ling
as well as the code you wrote.
0
bjweller
1/17/2008 5:47:59 PM
On Jan 17, 9:47=A0am, bjwel...@gmail.com wrote:
> On Jan 16, 8:17=A0pm, "Linq Adams via AccessMonster.com" <u28780@uwe>
> wrote:
>
>
>
>
>
> > To allow you to see your existing records, set the form's AllowEdits,
> > AllowDeletes and AllowAdditions to YES.
>
> > To make the form open on a new record, use this code:
>
> > Private Sub Form_Load()
> > DoCmd.GoToRecord , , acNewRec
> > End Sub
>
> > I used the object names you've given for form, table and CompanyID field=
, and
> > ran up a test database, so this has been tested and works.
>
> > If the textbox holding the CompanyID field is also named CompanyID, chan=
ge it
> > to txtCompanyID. Your control name on the form should never be identical=
 to
> > the underlying field name, even though Access will do this if you make t=
he
> > form with the wizard or if you drag the fields from the field list to th=
e
> > form.
>
> > If you already have a different name for the textbox, substitute it in t=
he
> > code below in place of txtCompanyID. This code assumes that the CompanyI=
D is
> > defined in the underlying table as datatype Text. Even if it consists of=
 all
> > digits, it doesn't/shouldn't be numeric. Only "numbers" used for math
> > should/need to be numeric datatype. =A0
>
> > Private Sub txtCompanyID_AfterUpdate()
> > Dim StrCriteria As String
>
> > If Me.NewRecord Then
> > =A0If DCount("CompanyID", "Orders", "[CompanyID] =3D '" & Me.txtCompanyI=
D & "'")
> > < 1 Then
> > =A0 =A0'ID doesn't exist... continue entering new record
> > =A0Else
> > =A0StrCriteria =3D Me.txtCompanyID
> > =A0Me.Undo
> > =A0 Set rs =3D Me.Recordset.Clone
> > =A0 =A0 rs.FindFirst "[CompanyID] =3D '" & StrCriteria & "'"
> > =A0 =A0 If Not rs.EOF Then Me.Bookmark =3D rs.Bookmark
> > =A0End If
> > End If
> > End Sub
>
> > Linq
>
> > --
> > There's ALWAYS more than one way to skin a cat!
>
> > Answers/posts based on Access 2000/2003
>
> > Message posted viahttp://www.accessmonster.com
>
> I changed the code and am now able to edit pre-existing records;
> however, =A0if the data already existed, it does not display the
> additional data fields in the form.
>
> To clarify, if Order ID 123 already existed in the Orders table, I can
> now edit it without getting that annoying primary key violation error
> message upon exit, but the company name, company address etc. are not
> populated on the form AfterUpdate on the txtOrdersID. These fields
> remain blank.
>
> How can I get the values of these fields to display when I enter an
> existing Order ID number?
>
> BTW, I apprecaited your very clear instructions and explanations Ling
> as well as the code you wrote.- Hide quoted text -
>
> - Show quoted text -

Duh....I had the Cycle property set to current record. Changed it to
All Records and it work.

Thanks for the help.

Deb
0
bjweller
1/17/2008 6:01:12 PM
I have never figured why in the world Access has this set as the Default
behavior for Cycle! It frequently does exactly what it did with you, make
people think code isn't working, or that records are "disappearing!"

Glad you got it working!

Linq

-- 
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via http://www.accessmonster.com

0
Linq
1/17/2008 6:33:02 PM
Reply:

Similar Artilces:

Get Help with Microsoft Office From Paid Experts!!\
Hi Everyone, My name is Richard Day. I have just launched my new Online Consulting Firm. My firm does something that has never been done before - Bring Computing Experts and customers together online, in a cost-effective one-on-one environment. The environment of the firm is based around a forum. The forum style allows users to browse the questions and answers of other members of the forums before you ask your own questions. Please Visit us at www.TheEnterKey.com. We currently offer expert help in the following fields: - WEB LANGUAGES (HTML & XHTML, Javascript, XML, CSS, Perl, PHP...

Need help in Cleveland Ohio USA
I am looking for a CRM user in the Cleveland Ohio area to talk with about implementing CRM. Please email me if if interested. Thanks. ...

Cmd Click --- check the values first,append record and then delete them.
Hi all, I want to execute below code but it seems absolutely fail, please help: Private Sub AddSpec_cmd_Click() On Error GoTo Err_AddSpec_cmd_Click If IsNull(Me.Model) Then MsgBox " Enter Model Name", vbOKOnly, "Model name empty" End If Model.SetFocus If Inputvoltage.Value < 11 Then MsgBox " Input correct voltage rate ", vbOKOnly, "Input voltage" End If Dim db As Database Set db = CurrentDb db.Execute "Appendix model spec1_qry", dbFailOnError db.Execute &quo...

Adding new record to a form
I have a form with a command button to add a new record. The first field on the form is a lookup field where I would like to select an item to fill in the field, the rest of the fields require data entry. When clicking the add record button a blank form opens but I cannot add any data to it. I can see the option in the lookup field but cannot select one. On the other fields that require data entry, I cannot enter anything. On the forms property I have 'allow additions, yes'. How can I get this to work? Bg1 - What is the recordsource for your form? If it is a query, t...

Please help...
I have publisher 2003 installed and got a file from a previous version and when it goes thru converting then opening I can't edit the doc at all, not even save as. What gives? It is a large file 207mb buth that shouldn't make a difference. DerekM <DerekM@discussions.microsoft.com> was very recently heard to utter: > I have publisher 2003 installed and got a file from a previous > version and when it goes thru converting then opening I can't edit > the doc at all, not even save as. Are you able to edit new documents? -- Ed Bennett - MVP Microsoft Publisher ...

Exch2k3 cluster keeps failing! Need urgent help please...
I am having *major* issues with my exchange cluster today, and I don't know what else to do. I have a 2-node A/P exch2k3 cluster, and all of a sudden the "smtp virtual server instance" would go into an "online-pending" state. Once this happens, everyone loses their connection to the server. After a while, it goes from online-pending, to failed. That problem is bad enough, but another problem is that, it didn't failover to the next node, and I've run diagnostics on the server nodes, and they seem to be fine hardware-wise. These nodes are connected to an ...

Import Pipe Delimited File, Parse out certian Fields, create new f
In Excel 2000, how do I take a pipe delimited file, strip out column(field) 6, 10, 11, 21. Then take that data, create a new pipe delimited file, and put the stripped 4 fields in columns 4,5,6,7 of a 15 column(field) new pipe delimited file? I want to do this automatically on a file that contains 1000+ records. What goes in columns 1-3 and 8-15 of the new file? In article <4696CAA0-1DD3-45E3-9627-3FE001611AD2@microsoft.com>, "StarBoy2000" <StarBoy2000@discussions.microsoft.com> wrote: > In Excel 2000, how do I take a pipe delimited file, strip out column(fiel...

Help with Publisher 2003
I currently have a trial version of Publisher 2003 and I am trying to email a two page newsletter. I do not want it as an attachment but in the email. Currently, Publisher 2003 only allows to email current page. Is there a way around this? Thanks After managing to set up OE-QuoteFix on his new PC, Ed reads a message from Mike <@>... > I currently have a trial version of Publisher 2003 and I am trying to > email a two page newsletter. I do not want it as an attachment but > in the email. Currently, Publisher 2003 only allows to email current > page. Is there a way arou...

How to Add Streaming Video To Your Website
How to Add Streaming Video To Your Website www.abcsun.net ...

Sending and receiving new messages
How do I set up Outlook to send and receive messages automatically upon opening the program? On Sun, 20 Mar 2005 20:53:02 -0800, "Chris" <Chris@discussions.microsoft.com> wrote: >How do I set up Outlook to send and receive messages automatically upon >opening the program? Depends what version of Outlook that you're using. You need to set a polling interval, which tells Outlook how often to do an automatic send/receive. -- <<<SgtRich>>> Email: Microsoft Outlook 2003 News (Text): Fort� Agent 2.0 News (Binaries): News Rover 10.2 ...

Office XP transfer of files to new PC
I am transfering files from my old PC to a new one and I want to take my Office XP just as it is on the old one to the new one; settings and all. Anyone know how to do this? First, you must reinstall Office. Then you can configure Outlook to use the same Outlook Data File (PST file) you were using before instead of the new blank one it created when you installed Outlook: - Go to Tools > Options > Mail Setup > Data Files > Add... - Add the PST you'd like as your new default, then Close > OK to exit the Options dialog - Go to Tools > E-mail Accounts > View or chan...

Need help undating money 2000 to 2004
Hi, I am currently using money 2000 because I have been unable to upgrade to money 2004, it says it can't do this, so does anyone know where I can get a download/trial of money 2002 to bridge the gap? Or is there another way to do this? Thanks in advance Ange What exactly is the error message? If it is "Incompatible version" see http://money.mvps.org/faq/article/96.aspx -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. I do not respond to any emails that I have not specifically asked for. ...

Moved Office 2007 to new disk problems ...
Hello, I installed a new disk and moved everything over. Office 2007 complained. I had to reactive. Outlook now has a "not implemented" message box and is unusable. I ran diagnostics. I ran repair. I re-ran setup from install disk. I tried to uninstall, could not even uninstall! Problem still persists. Any help? And how did you 'move everything'? "RobinZ" <RobinZ@discussions.microsoft.com> wrote in message news:EBAC881C-396E-4AF4-B843-DBC0899DE2AD@microsoft.com... > Hello, > > I installed a new disk and moved everything over. Offi...

New utility: The Viewport Explorer
I was recently reviewing some material in which I believed the description of the coordinate transformations was erroneous. The author insisted that the material was absolutely correct. To demonstrate the error, I wrote a little program that showed exactly what was happening. It was a useful little program, so I enhanced it a bit and it is now available for everyone. What it allows you to do is experiment with SetWindowOrg, SetViewportOrg, SetWindowExt, SetViewportExt, and mapping modes, and explore the effects of this on a displayed image. It also illustrates how to do logical to device coord...

Help with writing a script 09-25-10
I need to know how to create a vbscript using notepad ++ that will do the following: · Prompt the user for a folder name & a file name · If the folder does not exist, create the folder & the file. Let the user know that the folder & the file have been created. · If the folder does exist but the file does not, create the file. Let the user know the file has been created. · If the both the folder & the file exist, print the following info about the file: when the file was created, when the file was accessed la...

Automatically Numbering Records
I'm new to MS Access. I need help to automatically number a field sequentially without using AutoNumber. The format of the numbering is four digits “0000”. And "9999" maximum is more than enough. If possible provide step-by-step directions. Thank You -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/200708/1 You can use the DMax Function to retrieve the largest last used number from this field in your table and use that value (+1, of course) as your next value when you create a new record. You want step-by-step directions, b...

Email OWA via website problems, please help!
Right ill try and explain this as simple as i can. This is the problem that i have with allowing email via the website. At our organisation we have 9 servers in total, These are and do the following: Data: Students profiles and software distribution Phylz: NAS Storage server for Staff and students work areas Catalogue: Primary DNS Server & Primary Domain Controller Email: Email, Secondary DNS Server, Secondary Domain Controller Imaging: Backup/Restore Server Print: Print Server Squid: Internet Proxy Server/Filtering Server Bigbrother: Video Server/Streaming Server Remote: RDC Server/Int...

How to select one record of each from a column
Hello, I have a large worksheet with data. Here are the columns (a, b & c): Order # Employee Hrs. 1000 Mike 2 2000 Mike 1 1000 Paul 2 3000 Rose 3 8000 Mike 1 3000 Mike 2 1000 Rose 1 4000 Paul 1 7000 Jhon 3 5000 Rose 2 1000 Jhon 3 6000 Mike 1 9000 Mike 3 1000 Mike 2 Is there a formula to select only one of each record on "Order #" (column a) and put them on another column (column d)??? Once the formula is created, column d should look like this: 1000 2000 3000 4000 5000 6000 7000 8000 9000 Help ...

Query that can number records on the fly???
How can I make a query number each line in sequence of records found? Example if a query return 6 lines of records, I would like the query to return 1 for record 1, 2 for record 2 and so on. Example: 1 Red House 2 Blue House 3 Orange House 4 Green House 5 Yellow House You'd need to include a "ranking" field to give you that number. What is the SQL statement of the current query? How is the "number sequence" to be defined? -- Ken Snell <MS ACCESS MVP> "Michael Kintner" <michaelkintner@nospam.com> wrote in message news:13gn7ln...

Meeting invitation responses going to deleted user
I have a user (call him "Kevin"). He sends a meeting invitation to anyone. That person responds (doesn't matter what the response is). Kevin gets the response, but the invited user gets a bounce message saying the response couldn't be deliverd to "Mike Foobar". "Mike Foobar" no longer works here (he used to work for Kevin), and his AD account and Exchange mailboxes were deleted months ago. Exchange 2003, Windows Server 2003. We had a number of problems with our old Exchange 2000 hardware, so I set up a new server, moved all the mailboxes, and then ...

Recorded macros not working using the shortcut keys
Hello, I have recorded two macros that work fine except they do not run using the shortcut keys I defined. I've stepped through the macros and the shortcut keys are in the scripts, but I can only run them by pulling up the list of macros and selecting the macro and clicking run. I'm using Office 2007 in XP. TIA, Robert When you pull up the list of Macros and select your macro, go to the options box and create a shortcut key. On Fri, 13 Feb 2009 06:11:40 -0800 (PST), CurlyDave <davesexcel@gmail.com> wrote: >When you pull up the list of Macros and select your macro, go to...

Help with MS word
Can someone help me with the following: - Adding fields to document properties so that it can be easily retrieved from share point - Populating these document property fields in the document such that when the values in the document property are changed the word document updates itself - Getting images list in the 'Table of contents' page of the document - Getting the external references in the 'Table of contents' of the document - Preparing the template such that when the base template is updated, documents based on this template within the sharepoint are updated ...

Counting number of records based on criteria
Hello, (Try again to get the question in the google-groups. Perhaps not pushed on the send button). For counting records as expression in a report in ms.access I'm using =count(*) which shows all records. Now I also want to know how many of them has registered a specific data in a specific field. I'm trying to use =count([Field1]="999") to count how many of the selection reported has registered data 999. This option isn't work. Can somebody give me the correct solution. thanks, Johan The 999 needs to go in the criteria for the query driving the...

Newbie basic help
Hi all, I'm very new to this and trying to work out a formula to put numbers i order, sounds simple but i just cant work it out! I just need to be able to take a selection of rows with random number and display them in numerical order somewhere else. I've found the MAX and MIN commands to get the highest and lower bu not got a clue about how to do the rest. Any help would be very much appreciated. Thanks Ad -- Message posted from http://www.ExcelForum.com Assuming that the numbers are not generated by the RAND function, why not just sort them (Data>Sort). -- HTH Bob Phill...

need to retrieve deleted message.Help
Hi there.I have permanatly deleted a message that i need to retrieve can you help me. > Hi there.I have permanatly deleted a message that i need to retrieve > can you help me. How did you delete it? What type of mail server? -- -f.h. Unless you have a backup or use exchange server, it's probably gone. See http://www.outlook-tips.net/howto/recover_deleted.htm -- Diane Poremsky [MVP - Outlook] Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips by email: dailytips-subscribe-request@lists.outl...