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

[Debbiedo]

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.

 

[KARL DEWEY]

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.

 

[Linq Adams via AccessMonster.com]

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

 

[bjweller]

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, changeit
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 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.

 

[bjweller]

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 identicalto
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 ofall
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

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.- 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

 

[Linq Adams via AccessMonster.com]

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