Create a button to save/update record OR add new record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create a button in Access 2000 that will either Save/Update
the record that was modified, or if the record does not exist in the table,
add it as a new instance.

I know how to add the button using the Wizard, but I am at a loss for the
code to add to make this happen. In theory, I can visualize this, but in
coding, all I get is errors. So here is my theory:

1) The user enters data into the blank text box, or selects a reecord from a
combo box and then modifies existing data within the text box.

2) The user clicks on the "Save" button

3) The action will search the table using the primary key; if there already
is a record using that primary key, the values from the text boxes will be
seen as an Update, and the record will be updated. If the search turns up
that there is no such record associated with the primary key value, then the
action will be handled as an Add Record / Insert, and the values will be
entered into the table as a new record.

So my problems boils down to my inexperience in Access coding. I have
previous experience in SQL and PL/SQL coding, and I tried to create a basic
If/Then/Else procedure, with an Update option and an Insert option, but all I
get is errors.

Please help....
 
I am trying to create a button in Access 2000 that will either Save/Update
the record that was modified, or if the record does not exist in the
table,
add it as a new instance.
Click to expand...

You don't mention how the record is to be found, or search for in the first
place.

*Usually* the process is:

User looks for a record
User then modifies the record

If the user can't find the record, THEY THEN decide to add the
record....don't they?
1) The user enters data into the blank text box, or selects a reecord from
a
combo box and then modifies existing data within the text box.
Click to expand...

Ok, so either the user selects from a combo box.....if they don't, you
might want to have a add button.

(it is probably better to have the USER decide they want to new record, not
you figure this out by lack of using a comb box!).
2) The user clicks on the "Save" button
Click to expand...

Saving is automatic in ms-access. If you really want to write code to save,
the code behind the save button can be.

if me.dirty = true then
me.dirty = false
end if

Or, you can use just one line code

me.refresh
3) The action will search the table using the primary key; if there
already
is a record using that primary key, the values from the text boxes will be
seen as an Update, and the record will be updated. If the search turns up
that there is no such record associated with the primary key value, then
the
action will be handled as an Add Record / Insert, and the values will be
entered into the table as a new record.
Click to expand...

You don't clearly define what you mean by action? Why are we now searching
the table again via the PK? I though we already searched for, and now are
editing the record? (surely you are not updating data..but not displaying
the existing data first? - This don't make sense!! )

It seems rather simply that either the uses searches for and DISPLAYS the
record they want to edit, or they add a new record. I don't understand why
we would be searching the table again after you ALREADY found and display
the data for editing? (am I missing something here?).

If you want to use a combo box to search for a record, then use the comb box
wizard (it has a option to build a search combo where you select a value,
and the form jumps to that record).

If the user does not find what they want in that combo box, then I have to
assume they will then use the record navigation buttons on the bottom of the
screen to move to a new record (or you perhaps add a button via the wizard
to "goto a new record" do this).

As a personal choice, while ms-access can build the "search combo" for you,
I tend to NOT like using it. My perf is build a search screen. Let the user
seach, and then let them pick from the results. This allows the user to
search, and if they don't find what they are looking for, then you provide a
"add" button.

So, you can use the wizard to build the combo, and also a button to navigate
to a new record.

However, if you are looking for something better..then perhaps my thoughts
and ideas outlined here would be of help:

Here is some screen shots and ideas of what I mean here:

http://www.members.shaw.ca/AlbertKallal/Search/index.html


Last but not least, it is possible you are using un-bound forms, but then
that is a LOT of work, and ms-access is likely the wrong tool here....

And, here is some seach screens and ideas:

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm
 
Try this code to be created on the onclick event

Dim MyDb as DataBase, Myrec as recordset
Set MyDB=codeDb() ' Current MDB
Set MyRec = MyDb.openrecordset("Select MyFieldName From MyTableName Where
MyKeyField = " & Me.KeyFieldNameOnForm) ' use that if the key field is
number

Set MyRec = MyDb.openrecordset("Select MyFieldName From MyTableName Where
MyKeyField = '" & Me.KeyFieldNameOnForm & "'") ' use that if the key field
is string

If MyRec.eof then ' No records
Myrec.addnew
else
MyRec.edit ' record exist
End if

MyRec!MyFieldName = Me.MyFieldNameInForm
MyRec.update
==================================
If you need more help, I need to know the name of
1. the table
2. text and key field name in the form and type
3. text and key field name in the table
==================================
 
Thanks for the replies, most boards I go to don't get any hits for days or
weeks, you people are on it, thanks.

Albert - When the form is opened up, all 8 of the text boxes are empty. The
user then uses the combo box (due to limited amount of data) to "search" for
the appropriate "Office" location, then when selected, the text boxes will
auto-populate with the values associated. From there, the user can modify
the values and press the Save button and te record will be updated.
Conversely, if the user cannot locate the record, he/she can enter the data
into the text fields, click the same Save button, and the values entered will
be inserted into the table to create a new record. I was hoping that the
code in Access could determine whether to update or add a new record based on
the presence of either an existing or new primary key value, and not rely on
the user searching and making the decision to Add or Save, but rather have
the same button handle both tasks.

Ofer - Thanks for the code, but where in the "Private Sub" function would
this go? As for the info you requested:
1. The table name is "Office"
2. The unbound text boxes in the form are as follows:
Office_Code
Office_Name
Business_address
Business_City
Business_ZIP
Mailing_Address
Mailing_City
Mailing_Zip
3. The field names in the Office table are:
Office_ID (Primary Key)
Office_Name
Business_ST_Add
Business_City
Business_ZIP
Mailing_ST_Add
Mailing_City
Mailing_ZIP

The values in the form should be entered into the correlating table fields
in the same order, and in most cases, share the same name. If you need any
additional info, let me know and I'll get it to you.

Thanks for both of your help. I will try to try both of your suggestions
while I await what Ofer is brewing.
 
If you created a button on the form to add or edit the records, then this
code should be on the onclick event of the button

Private Sub ButtonName_Click()
On Error GoTo Err_ButtonName_Click


Dim MyDb As Database, Myrec As Recordset
Set MyDb = CodeDb() ' Current MDB
Set Myrec = MyDb.OpenRecordset("Select * From Office Where Office_ID = " &
Me.Office_Code)
If Myrec.EOF Then ' No records
Myrec.AddNew
Else
Myrec.Edit ' record exist
End If

Myrec!Office_Name = Me.Office_Name
Myrec!Business_City = Me.Business_City
Myrec!Business_ST_Add = Me.Business_address
Myrec!Business_ZIP = Me.Business_ZIP
Myrec!Mailing_City = Me.Mailing_City
Myrec!Business_ZIP = Me.Business_ZIP
Myrec!Mailing_ST_Add = Me.Mailing_Address
Myrec.Update


Exit_ButtonName_Click:
Exit Sub

Err_ButtonName_Click:
MsgBox Err.Description
Resume Exit_ButtonName_Click

End Sub
 
Thanks Ofer, really appreciate your help and effort. When I saved the code,
everything looked good, then when I tried to use the button, I received the
following error: "Compile Error: User-defined type not defined" , and the
highlighted text in the code section was the line of code "Dim MyDb As
Database".

Any suggestions?

Thnks again!
 
. I was hoping that the
code in Access could determine whether to update or add a new record based
on
the presence of either an existing or new primary key value, and not rely
on
the user searching and making the decision to Add or Save, but rather have
the same button handle both tasks.
Click to expand...

You can do the above, but the problem is that the form is bound to the
table. So, after you "find" a record using the comb box, the user edits the
record (and, hits your save button - but..as mentioned, that save process is
automatic in ms-access anyway). You are using the wrong concepts here for
ms-access. What works in FoxPro, or vb.net does NOT WORK for ms-access (the
trick here is modify your approach to how things work based on the tools you
use. As developers, if you don't modify your approach, then you will wind up
fighting the development process all the way).

The problem is now after the user edits, that record is STILL on the screen,
and STILL attached to the table. If the user starts editing the record in
hopes of adding a new record, you are fact editing the record that is still
displayed. (this is how bound forms work). I suppose you could put some code
in the save button to "empty" the record, but then again you are starting to
write too much code and fighting how the ms-access UI works (as mentioned,
if you fight the UI, then you start to write tons of code, and get no
benefits for this work).

One approach is to use the combo box not in list. Thus, if the user types in
a value/search in the combo that does not exist, then you could THEN prompt
the user to add the new entry. Remember, with a bound form, when the record
is displayed, it can be edited.

So, you need some caution if you are use the SAME form for searching, as any
record displayed will be edited if the user types into the form. It is going
to be VERY difficult to control editing of existing records if you allow a
user to start typing into a form that is bound to a table.

The combo box does have a "not in list" event that allows you to trap
entries typed in that are "not in the list".

As mentioned, a better choice might be to provide a search screen, and a
button to launch the main edit form after a record is found.

Further, you don't mention what kind of search. Is this a close name match,
or some part number (or id) that you search for?

If you are searching via some pk, then just provide a un-bound search form.

Place a text box on the form, and allow the user to type in a number, and
hit enter key (or a button if you wish).

Lets assume we are searching for invoice numbers. We will build a form with
a text box

Enter Customer Invoice number [ ]

This simple form we will turn off all the extra junk (record selectors,
navigation buttons, auto re-size etc.). The result is a plane jane form with
just the above text box.

The code in the after update event of the text box could be:

Private Sub txtInvoicePrompt_AfterUpdate()

Dim strSqlWhere As String

strSql = "InvoiceNumber = '" & Me!txtInvoicePrompt & "' "

If DCount("*", "tblCustInvoice", strSql) > 0 Then
' invoice exist...lets display the invoice form
DoCmd.OpenForm "frmCustInvoice", , , strSql
Else
If MsgBox("Invoice not found, add a new one?", _
vbQuestion + vbYesNo, "Add new") = vbYes Then
DoCmd.OpenForm "frmCustInvoice", , , , acFormAdd
End If

End If

End Sub

So, the above would accomplish our goal. Further, since we always launch the
invoice form to ONE record,then you can hide/disable the record navigation
buttons on the bottom of the form (which by the way allow you to move
around...and also ADD NEW records).
 
It sound like you are missing reference to Microsoft DAO 3.6 Object Library.
While in code, select tools, reference, and see if you missing it.
If you missing it , browse for dao360.DLL
 
Ofer, thanks for the tip. I located the reference and have "used" it. The
previous error is no longer, but now I'm getting a different error: "Compile
error: Method or data member not found", with the code

Else
Myrec.Edit ' record exist
End If


having "Myrec.Edit" highlighted.

I appreciate all the help you are providing. Any suggestions for this one?
Thanks again in advance...
 
I assume you copied and paste my code, so you so you did declare the recordset.
Iy should work, can you post the code you have if you made any changes.
 
Here's the code:

Private Sub SAVE_BUTTON_Click()
On Error GoTo Err_SAVE_BUTTON_Click


Dim MyDb As Database, Myrec As Recordset
Set MyDb = CodeDb() ' Current MDB
Set Myrec = MyDb.OpenRecordset("Select * From Office Where Office_ID = " &
Me.Office_Code)
If Myrec.EOF Then ' No records
Myrec.AddNew
Else
Myrec.Edit ' record exist
End If

Myrec!Office_Name = Me.Office_Name
Myrec!Business_City = Me.Business_City
Myrec!Business_ST_Add = Me.Business_address
Myrec!Business_ZIP = Me.Business_ZIP
Myrec!Mailing_City = Me.Mailing_City
Myrec!Business_ZIP = Me.Business_ZIP
Myrec!Mailing_ST_Add = Me.Mailing_Address
Myrec.Update


Exit_SAVE_BUTTON_Click:
Exit Sub

Err_SAVE_BUTTON_Click:
MsgBox Err.Description
Resume Exit_SAVE_BUTTON_Click

End Sub
 
Upon a little further investigation, when I tried to change the Myrec.Edit
statment, after I type "Myrec." a drop down menu is displayed with a list of
statement values/functions, and oddly enough, the "Edit" statement is not
present, but "Save" and "Update" are. I tried using both of those in the
place of "Edit" and it complies fine, but when I click the "SAVE" button, I
get the error "Too few Parameters. Expected 1."

Don't know if this would help or not.
 
Sorry for the delay
I have never seen this, that the edit doesn't apear on the list, so try
removing the edit line, It could be that its by default set to edit, unless
you tell it other wise.

Cange that :
If Myrec.EOF Then ' No records
Myrec.AddNew
Else
Myrec.Edit ' record exist
End If

To:
If Myrec.EOF Then ' No records
Myrec.AddNew
End If
 
Found the problem, in the reference locate the dao3.6 strait after the Access
11 reference.
If you have an ole automation refernce, the dao should be before it.
 
In my References option, there is no record of an Access 11 reference....I
tried browsing, but could not find it. Is this causing the problem?
 
You need reference to MSACC.OLB you might have a different Access, 8 or
something
If so put the other reference, dao3.6, just after it
 
I have a reference to MSACC9.OLB directly followed by a reference to the DAO
3.6

From what I have looked at and tested, correct me if I'm wrong, but the
problem lies in the SET of Myrec.

This is error starting to wear on me.
 
If you would like me to check the code, you can mail it to me
(e-mail address removed)

Compact the MDB
Chenge the name form MyMDNName.mdb to MyMDNName.jpg and zip it

Hotmail doesnt allow mdb attach, and it removes it

Don't give up.
 
I tried sending the file to you from two different addresses and it failed
due to the size of the Zip'ed file. Any other suggestions?
 
Back
Top